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

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