[postgis-users] Query performance issue

[Stephen Woodbridge]

Stephen Woodbridge wrote:

> OK, thanks, this looks much better:
> 
> explain update streets set prov=prov_ab from province b where
> streets.the_geom && b.the_geom and intersects(streets.the_geom, b.the_geom)
> 
> Nested Loop  (cost=0.00..3534.86 rows=1199269 width=326)
>   Join Filter: intersects("inner".the_geom, "outer".the_geom)
>   ->  Seq Scan on province b  (cost=0.00..1.14 rows=14 width=92196)
>   ->  Index Scan using streets_gidx on streets  (cost=0.00..38.12 rows=9 
> width=320)
>         Index Cond: (streets.the_geom && "outer".the_geom)
> 

Well the query plan looks better, but it has been running for 13+ hrs. 
Also on the query I changed the intersects() to distance() = 0.0 which I 
think is supposed to be a little quicker.

Is there any way to know how far it has proceeded? I would hate to abort 
the update only to find out later that it was 90% done?

I had another thought on doing this update, but I'm not sure if it is 
possible. The idea would be to eliminate 90%? of the distance 
calculation using logic similar to:

update streets set prov=prov_ab from province b where
   ( select count(*) from streets, province
      where streets.the_geom && province.the_geom )=1
or
   ( streets.the_geom && b.the_geom
     and distance(streets.the_geom, b.the_geom) = 0.0 );

So if the bbox of the street overlaps with a single province then set 
the value to the province. Any thoughts on the above SQL?

-Steve