declared cursor uses slow plan

Why would postgres use a different query plan for declared cursors than
without?

I have a relatively simple query that takes about 150ms using explain
analyze. However, when I wrap the same query in a declared cursor
statement, the subsequent fetch statement takes almost 30seconds. For
some reason, the planner decided to do a nested loop left join instead
of a hash left join. Does anyone know why the planner would choose this
course?

For those interested, the results of the planner are:

EXPLAIN ANALYZE SELECT a.wb_id, a.group_code, a.area, a.type, a.source,
a.fcode, asbinary((a.the_geom), 'XDR'), c.name, b.gnis_id FROM
csn_waterbodies a LEFT JOIN (csn_named_waterbodies as b JOIN
all_gnis_info as c ON b.gnis_id = c.gnis_id) on a.wb_id = b.wb_id WHERE
the_geom && GeometryFromText('POLYGON ((998061.4211119856
820217.228917891, 1018729.3748344192 820217.228917891,
1018729.3748344192 827989.3006519538, 998061.4211119856
827989.3006519538, 998061.4211119856 820217.228917891))', 42102);

QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hash Left Join (cost=1554.46..1611.26 rows=5 width=1048) (actual
time=144.620..150.277 rows=208 loops=1)
Hash Cond: ("outer".wb_id = "inner".wb_id)
-> Index Scan using csn_waterbodies_the_geom_idx on csn_waterbodies
a (cost=0.00..6.40 rows=5 width=1026) (actual time=0.192..2.838
rows=208 loops=1)
Index Cond: (the_geom && 'SRID=42102;POLYGON((998061.421111986
820217.228917891,1018729.37483442 820217.228917891,1018729.37483442
827989.300651954,998061.421111986 827989.300651954,998061.421111986
820217.228917891))'::geometry)
Filter: (the_geom && 'SRID=42102;POLYGON((998061.421111986
820217.228917891,1018729.37483442 820217.228917891,1018729.37483442
827989.300651954,998061.421111986 827989.300651954,998061.421111986
820217.228917891))'::geometry)
-> Hash (cost=1535.13..1535.13 rows=7734 width=26) (actual
time=143.717..143.717 rows=0 loops=1)
-> Merge Join (cost=0.00..1535.13 rows=7734 width=26) (actual
time=6.546..134.906 rows=7203 loops=1)
Merge Cond: ("outer".gnis_id = "inner".gnis_id)
-> Index Scan using csn_named_waterbodies_gnis_id_idx on
csn_named_waterbodies b (cost=0.00..140.37 rows=7215 width=8) (actual
time=0.035..10.796 rows=7204 loops=1)
-> Index Scan using all_gnis_info_gnis_id_idx on
all_gnis_info c (cost=0.00..1210.19 rows=41745 width=22) (actual
time=0.014..60.387 rows=42757 loops=1)
Total runtime: 150.713 ms
(11 rows)

DECLARE thread_33000912 CURSOR FOR SELECT ...

QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Nested Loop Left Join (cost=0.00..8165.43 rows=5 width=1048)
Join Filter: ("outer".wb_id = "inner".wb_id)
-> Index Scan using csn_waterbodies_the_geom_idx on csn_waterbodies
a (cost=0.00..6.40 rows=5 width=1026)
Index Cond: (the_geom && 'SRID=42102;POLYGON((998061.421111986
820217.228917891,1018729.37483442 820217.228917891,1018729.37483442
827989.300651954,998061.421111986 827989.300651954,998061.421111986
820217.228917891))'::geometry)
Filter: (the_geom && 'SRID=42102;POLYGON((998061.421111986
820217.228917891,1018729.37483442 820217.228917891,1018729.37483442
827989.300651954,998061.421111986 827989.300651954,998061.421111986
820217.228917891))'::geometry)
-> Merge Join (cost=0.00..1535.13 rows=7734 width=26)
Merge Cond: ("outer".gnis_id = "inner".gnis_id)
-> Index Scan using csn_named_waterbodies_gnis_id_idx on
csn_named_waterbodies b (cost=0.00..140.37 rows=7215 width=8)
-> Index Scan using all_gnis_info_gnis_id_idx on all_gnis_info
c (cost=0.00..1210.19 rows=41745 width=22)
(9 rows)

Cheers,
Kevin

--
Kevin Neufeld,
Refractions Research Inc.,
kneufeld(at)refractions(dot)net
Phone: (250) 383-3022
Fax: (250) 383-2140