# [postgis-users] Query performance issue

Stephen Woodbridge woodbri at swoodbridge.com
Wed Oct 12 10:17:30 PDT 2005

```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

```