Skip site navigation (1) Skip section navigation (2)

query plan different for "SELECT ..." and "DECLARE CURSOR ..."?

From: David Blasby <dblasby(at)refractions(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: query plan different for "SELECT ..." and "DECLARE CURSOR ..."?
Date: 2003-10-01 18:50:40
Message-ID: 3F7B2200.5040801@refractions.net (view raw or flat)
Thread:
Lists: 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 

---------------------------------------------------------------------------------------------------------------------------------------------------------
  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-# ;






Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2003-10-01 18:55:51
Subject: Re: Thoughts on maintaining 7.3
Previous:From: Manfred KoizarDate: 2003-10-01 18:02:52
Subject: Re: ADD FOREIGN KEY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group