Re: select vs cursor/fetch speed disparity

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.

regards, tom lane