[postgis-users] Problems with an update query

[Alexandre Neto]

Hello all,

I'm trying to update a table of polygons with the distance to the nearest
polygon in the same table and of the same class. I suspect that there is
something fishy about the way my query is build.

I was using something like this:

UPDATE cosc.cosn1
SET enn = c.ENN
FROM (SELECT DISTINCT ON(g1.gid)  g1.gid As ref_gid,
ST_Distance(g1.geom,g2.geom) As ENN
FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2
WHERE g1.gid < g2.gid AND g1.class = g2.class
ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c
WHERE gid = c.ref_gid;

It's was taking a long time, but that wasn't surprising. I got my results
in 150000ms.

While doing some tests I tried no narrow the query a bit by specifying the
gid of a feature in the bottom WHERE statement (of the update).

UPDATE cosc.cosn1
SET enn = c.ENN
FROM (SELECT DISTINCT ON(g1.gid)  g1.gid As ref_gid,
ST_Distance(g1.geom,g2.geom) As ENN
FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2
WHERE g1.gid < g2.gid AND g1.class = g2.class
ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c
WHERE gid = 2 AND gid = c.ref_gid;

Also not surprising, this took a lot less, around 300ms

What started to bug me was the fact that using a different condition to
narrow the query by only updating 10 features...

UPDATE cosc.cosn1
SET enn = c.ENN
FROM (SELECT DISTINCT ON(g1.gid)  g1.gid As ref_gid,
ST_Distance(g1.geom,g2.geom) As ENN
FROM "cosc"."cosn1" As g1, "cosc"."cosn1" As g2
WHERE g1.gid < g2.gid AND g1.class = g2.class
ORDER BY g1.gid, ST_Distance(g1.geom,g2.geom)) as c
WHERE gid < 10 AND gid = c.ref_gid;

It needed 147510ms to show the results, almost the same as updating the all
table!

Even more weird was the fact that if I manual set gid = x from [1,10] none
of the queries toke more that 400ms to perform.

Therefore, I must do something wrong for sure!

Thank you for your help,

Alexandre Neto
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140509/9e4bf47b/attachment.html>