[postgis-users] Query performance issue
woodbri at swoodbridge.com
Thu Oct 13 09:11:43 PDT 2005
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
> 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
( 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?
More information about the postgis-users