Re: select vs cursor/fetch speed disparity

Hi Tom,

Tom Lane wrote:
> Bosco Rama <postgres(at)boscorama(dot)com> writes:
>> I have a strange disparity between a query that is run as a
>> straight select and the same query via a cursor. I hope I can
>> jog someone's memory with the description as I have been unable
>> to create a sanitized and/or reduced data set & schema that will
>> reproduce this ... so far. :-(
>
> Cursors are biased towards fast-start plans on the theory that you
> may not be intending to fetch the whole result. Queries with ORDER BY
> and/or LIMIT are particularly likely to see plan changes as a
> consequence of that. In 8.4 and up you can frob the
> cursor_tuple_fraction setting to adjust this preference. Use
> "EXPLAIN query" vs "EXPLAIN DECLARE CURSOR FOR query" to see what
> sort of plan you're getting.

I'll take a look at that setting and try the two 'explain's. However,
would that really account for an increase in time by a factor of ~630?
Just wondering.

(BTW, I'm still working on a public version of the data & schema that
reproduce this.)

Bosco.