Hi Try your queries using sql explain statements ( http://www.postgresql.org/docs/current/static/sql-explain.html). You should then be able to see if the postgresql query planner chooses a different strategy to process your queries. I suspect that postgresql isn't able to prefilter on gid when you use gid < 10, and instead computes all rows and at the very end filters out rows with gid less than 10. Use ST_DWITHIN with a reasonable distance (to stop cross joining the entire table) together with a gist index on your geometries if you want increased performance.
Åsmund On Fri, May 9, 2014 at 6:56 PM, Humberto Cereser Ibanez < [email protected]> wrote: > On Fri, 2014-05-09 at 17:17 +0100, Alexandre Neto wrote: > > 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; > > May be it is better to make a buffer on g1.geom and filter the g2.geom > that touch this buffer. For reduce your computation on ST_Distance. > > > > > > 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. > > > Did you create a index for gid (btree) and geom (gist)? > > > > Therefore, I must do something wrong for sure! > > > > > > Thank you for your help, > > > > > > Alexandre Neto > > Humberto Cereser Ibanez > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
