Re: LIMIT in DECLARE CURSOR: request for comments

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 12:18 27/10/00 -0400, Tom Lane wrote:
>> 1. If DECLARE CURSOR does not contain a LIMIT, continue to plan on the
>> basis of 10%-or-so fetch (I'd consider anywhere from 5% to 25% to be
>> just as reasonable, if people want to argue about the exact number;
>> perhaps a SET variable is in order?).

> SET sounds good; will this work on a per-connection basis?

A SET variable would be connection-local, same as any other ...

> I don't suppose you'd consider 'OPTIMIZE FOR TOTAL COST' and 'OPTIMIZE FOR
> FAST START' optimizer hints?

I don't much care for adding such syntax to DECLARE CURSOR, if that's
what you're suggesting. LIMIT ALL would have the same effect as
'OPTIMIZE FOR TOTAL COST' anyway. LIMIT 1 (or a small number) would
have the effect of 'OPTIMIZE FOR FAST START', but would constrain you
to not fetch any more rows than that. If we had a SET variable then
you could twiddle that value to favor fast-start or total-cost concerns
over a continuous range, without constraining how many rows you actually
fetch from a LIMIT-less cursor.

> Also, does the change you have made to the executor etc mean that
> subselect-with-limit is now possible?

The executor will do it, but unless Kevin figures out how to fix the
grammar, you'll have to put the LIMIT into a view definition, not inline
in a subquery. View-with-LIMIT does work as of today.

regards, tom lane