[postgis-users] Problems with an update query

[Humberto Cereser Ibanez]

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