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
---------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (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.08 rows=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.19 rows=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=1 width=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_albers a (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.08 rows=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_summary s (cost=0.00..671.84 rows=18387 width=8)
Filter: (distance > 200)
Total runtime: 0.41 msec
(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-# ;
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
