Re: pgsql/src/backend/nodes (copyfuncs.c outfuncs.c print.c)

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> Do you really think it's not such a good idea to have different optimizer
> behaviour for SELECT and DECLARE CURSOR? My expectation is that putting a
> SELECT statement inside a cursor should not change it's performance. I'd be
> interested to know the reasons for your choice.

I think it's an excellent idea to have different behaviors, and the
reason is that we know a stand-alone SELECT will deliver all its result
rows, whereas for DECLARE it's quite possible that not all the possible
result rows will be fetched. Moreover, the user is likely to fetch the
cursor's results in bite-size chunks, so he will be interested in
average response time as well as total time.

In the proposal as written, LIMIT ALL and LIMIT n will in fact give rise
to identical behavior in both contexts, and it's only the case without
an explicit LIMIT that will behave differently. (If we add a
SET-variable to control this, you could even make that behavior the same
too, by setting the variable to 1.0.)

regards, tom lane