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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Blasby <dblasby(at)refractions(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: query plan different for "SELECT ..." and "DECLARE CURSOR ..."?
Date: 2003-10-01 22:57:05
Message-ID: 10449.1065049025@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
David Blasby <dblasby(at)refractions(dot)net> writes:
> I've been noticing query planning to be different for a cursor-based 
> select and normal select.

IIRC, in a DECLARE context the planner puts more weight on the startup
cost than the total cost, on the theory that you might not be planning
to fetch the whole result, and even if you are you may prefer to overlap
some frontend and backend processing by fetching the results
incrementally rather than all at once.

There was some talk of introducing a control variable to affect this
weighting, but it's not there yet.

In any case, I'd think the real issue here is that the planner thinks
these two plans are nearly the same cost, when in reality there's an
order-of-magnitude difference.  As far as I can see the problem is with
the estimation of this scan result:

>           ->  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 2500000,1900000 1900000 0)'::geometry)

The factor-of-89 error in row count here translates directly to a
factor-of-89 underestimation of the cost of the nestloop plan.

You may need to bite the bullet and try to devise some real selectivity
estimation techniques for your geometric operators.  The stuff in
src/backend/utils/adt/geo_selfuncs.c at the moment is all just stubs :-(

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-10-01 23:09:10
Subject: Re: Thoughts on maintaining 7.3
Previous:From: Maksim LikharevDate: 2003-10-01 22:50:04
Subject: Re: FreeSpaceMap hashtable out of memory

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