query plan different for "SELECT ..." and "DECLARE CURSOR ..."? - Mailing list pgsql-hackers

I've been noticing query planning to be different for a cursor-based 
select and normal select.

For example, my query looks like this:

=# SELECT <select clause>

The query takes about 1/4 of a second.

But, for:

=# BEGIN;
=# DECLARE mycursor BINARY CURSOR FOR SELECT <select clause>;
=# FETCH ALL IN mycursor;

The same [SQL] query is being used, but this takes about 6 seconds (20* 
longer).

Using explain, I see that the planner chose a different plan.

Why does this sort of thing happen?  How do I stop it?

I've included the query plans below if you think a specific example is 
important - but i'm more looking for a generic answer.  Sorry for the 
complexity.

NOTE: these are are PostGIS queries (&& is GIST indexed).  The reason a 
binary cursor is being used is because I use the WKB (well known binary) 
geometry representation as the transit 'language'.

thanks for your help,
dave


"SELECT plan"
QUERY PLAN 


---------------------------------------------------------------------------------------------------------------------------------------------------------
HashJoin  (cost=1993.11..2462.50 rows=9 width=40) (actual 
 
time=77.69..169.96 rows=67 loops=1)   Hash Cond: ("outer".lha_id = "inner".lha_id)   ->  Subquery Scan b
(cost=1983.00..2443.08rows=1839 width=16) 
 
(actual time=60.48..127.20 rows=67 loops=1)         ->  Aggregate  (cost=1983.00..2443.08 rows=1839 width=16) 
(actual time=60.47..127.08 rows=67 loops=1)               ->  Group  (cost=1983.00..2351.14 rows=18387 width=16) 
(actual time=59.68..115.57 rows=18387 loops=1)                     ->  Merge Join  (cost=1983.00..2305.17 rows=18387 
width=16) (actual time=59.67..93.81 rows=18387 loops=1)                           Merge Cond: ("outer".lha_id = 
"inner".lha_from_id)                           ->  Sort  (cost=8.77..8.99 rows=89 width=8) 
(actual time=0.44..0.48 rows=82 loops=1)                                 Sort Key: p.lha_id
   ->  Seq Scan on lha_pop p 
 
(cost=0.00..5.89 rows=89 width=8) (actual time=0.03..0.15 rows=89 loops=1)                           ->  Sort
(cost=1974.23..2020.19rows=18387 
 
width=8) (actual time=59.19..64.80 rows=18387 loops=1)                                 Sort Key: s.lha_from_id
                      ->  Seq Scan on msp_trip_summary s 
 
(cost=0.00..671.84 rows=18387 width=8) (actual time=1.70..31.31 
rows=18387 loops=1)                                       Filter: (distance > 200)   ->  Hash  (cost=10.11..10.11
rows=1width=36) (actual 
 
time=15.71..15.71 rows=0 loops=1)         ->  Seq Scan on lha_albers a  (cost=0.00..10.11 rows=1 
width=36) (actual time=1.06..15.54 rows=89 loops=1)               Filter: (the_geom && 'SRID=-1;BOX3D(250000 250000 
0,1900000 1900000 0)'::geometry) Total runtime: 173.97 msec
(18 rows)


and the "DECLARE" plan:                                                 QUERY PLAN 

------------------------------------------------------------------------------------------------------------ Nested
Loop (cost=1983.00..2476.17 rows=9 width=40)   Join Filter: ("outer".lha_id = "inner".lha_id)   ->  Seq Scan on
lha_albersa  (cost=0.00..10.11 rows=1 width=36)         Filter: (the_geom && 'SRID=-1;BOX3D(250000 250000 0,1900000 
 
1900000 0)'::geometry)   ->  Subquery Scan b  (cost=1983.00..2443.08 rows=1839 width=16)         ->  Aggregate
(cost=1983.00..2443.08rows=1839 width=16)               ->  Group  (cost=1983.00..2351.14 rows=18387 width=16)
          ->  Merge Join  (cost=1983.00..2305.17 rows=18387 
 
width=16)                           Merge Cond: ("outer".lha_id = 
"inner".lha_from_id)                           ->  Sort  (cost=8.77..8.99 rows=89 width=8)
  Sort Key: p.lha_id                                 ->  Seq Scan on lha_pop p 
 
(cost=0.00..5.89 rows=89 width=8)                           ->  Sort  (cost=1974.23..2020.19 rows=18387 
width=8)                                 Sort Key: s.lha_from_id                                 ->  Seq Scan on
msp_trip_summarys 
 
(cost=0.00..671.84 rows=18387 width=8)                                       Filter: (distance > 200) Total runtime:
0.41msec
 
(17 rows)



tap=# explain DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(the_geom)),'NDR'),LHA_ID::text from (
tap(#                         Select a.the_geom, a.lha_id, trips from 
lha_albers a,
tap(#                                 (SELECT (s.lha_from_id) as lha_id, 
(sum(s.count)::float / max(p.population)::float * 100) as trips
tap(#                                 from lha_pop p, msp_trip_summary s
tap(#                                 where  p.lha_id = s.lha_from_id 
AND s.distance > 200 Group by s.lha_from_id) b
tap(#                         where a.lha_id = b.lha_id
tap(#                 ) AS TBL WHERE the_geom && setSRID('BOX3D(250000 
250000,1900000 1900000)'::BOX3D, -1 )
tap-# ;