Re: declared cursor uses slow plan

Kevin Neufeld <kneufeld@refractions.net> writes:
> I have a relatively simple query that takes about 150ms using explain
> analyze. However, when I wrap the same query in a declared cursor
> statement, the subsequent fetch statement takes almost 30seconds. For
> some reason, the planner decided to do a nested loop left join instead
> of a hash left join. Does anyone know why the planner would choose this
> course?

Plans for cursors are optimized partly for startup speed as opposed to
total time, on the assumption that you'd rather get some of the rows
sooner so you can crunch on them.

Probably there should be a knob you can fool with to adjust the strength
of the effect, but at present I think it's hard-wired.

The real problem here of course is that the total cost of the nestloop
is being underestimated so badly (the estimate is only 5x more than the
hash join where reality is 200x more).  It looks like this is mainly
because the number of matching rows from csn_waterbodies is badly
underestimated, which comes from the fact that we have no useful
statistics for geometric operators :-(.  I think that the PostGIS crew
is working that problem but I have no idea how far along they are...

            regards, tom lane