
> 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

Mon, 30 Oct 2000 09:34:03 +0100
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: LIMIT in DECLARE CURSOR: request for comments

On Mon, 30 Oct 2000, Zeugswetter Andreas SB wrote:

> 
> > 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.

But what if you're doing fetch 10 rows, fetch 10 rows, ...
You're not limiting, because you want all of them, but you are only
pulling a small number at a time to say do expensive front end processing.
It might make sense to actually pull a plan which is lower startup and
higher per row.  Although the full cost is higher, you get a better
turnaround time on the first set and the cost difference per set may
be unnoticeable (it would depend on the particulars).


Mon, 30 Oct 2000 09:07:36 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: AW: LIMIT in DECLARE CURSOR: request for comments