Re: select vs cursor/fetch speed disparity

Bosco Rama <postgres@boscorama.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.

            regards, tom lane