[postgis-users] Query performance issue

[Stephen Woodbridge]

Hi all,

I think this might be the problem of small table with big polygons and 
large table of small geometries confusing the the query planner. I can't 
find what if any work-a-round there was for this problem in the archives.

explain update streets set prov=prov_ab from streets a, province b where
a.the_geom && b.the_geom and intersects(a.the_geom, b.the_geom);

Nested Loop  (cost=0.00..6358011493.24 rows=2157363205190 width=346)
   Join Filter: intersects("inner".the_geom, "outer".the_geom)
   ->  Nested Loop  (cost=0.00..1211602.30 rows=25184558 width=92536)
         ->  Seq Scan on province b  (cost=0.00..1.14 rows=14 width=92196)
         ->  Seq Scan on streets  (cost=0.00..68553.97 rows=1798897 
width=340)
   ->  Index Scan using streets_gidx on streets a  (cost=0.00..38.12 
rows=9 width=180)
         Index Cond: (a.the_geom && "outer".the_geom)

-- and --

explain select b.prov_ab from streets a, province b where
a.the_geom && b.the_geom and intersects(a.the_geom, b.the_geom);

Nested Loop  (cost=0.00..536.69 rows=1199269 width=6)
   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 a  (cost=0.00..38.12 
rows=9 width=160)
         Index Cond: (a.the_geom && "outer".the_geom)


This query ran for over 15 hours and I aborted it :(

both tables have gist indexes on the the_geom and I have run vaacum 
analyze on them both.

Any ideas would be appreciated, as I have similar updates to do for 3 
more tables after this one is done.

-Steve