AW: LIMIT in DECLARE CURSOR: request for comments

> After thinking some more about yesterday's discussions, I propose that
> we adopt the following planning behavior for cursors:
>
> 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?). 10% seems to be a reasonable
> compromise between delivering tuples promptly and not choosing a plan
> that will take forever if the user fetches the whole result.

Imho that was a wrong assumption in the first place. The default assumption
imho needs to be 100 %. Especially if you fixed the limit clause enabling people
to optimize the few rows fetched case.

> 3. If DECLARE CURSOR contains "LIMIT ALL", plan on the assumption that
> all tuples will be fetched, ie, select lowest-total-cost plan.
>
> (Note: LIMIT ALL has been in the grammar right along, but up to now
> it has been entirely equivalent to leaving out the LIMIT clause. This
> proposal essentially suggests allowing it to act as a planner
> hint that
> the user really does intend to fetch all the tuples.)
>
> Comments?

Imho an explicit statement to switch optimizer mode from all rows to first rows
would be a lot easier to understand and is what other DB vendors do.

Andreas