Re: [HACKERS] Solution for LIMIT cost estimation

"Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
>> That's my feeling too. I'm leaning towards letting the optimizer do the
>> best it can with the given query (which means using OFFSET+LIMIT as the
>> estimated number of tuples to be fetched),

> What about cursors ?
> I heard from Jan that we could specify 'LIMIT ALL' to tell optimizer that
> the response to get first rows is needed.

Hmm. Right now I have it coded to treat 'LIMIT ALL' the same as
no LIMIT clause, which is the way it ought to work AFAICS.

DECLARE CURSOR doesn't appear to support OFFSET/LIMIT at all (the
grammar will take the clause, but analyze.c throws it away...).

I have the LIMIT support in the planner coded to build plans for
DECLARE CURSOR queries on the assumption that 10% of the rows will
be fetched, which is the sort of compromise that will satisfy
nobody ;-).

A possible answer is to define OFFSET/LIMIT in DECLARE CURSOR as
being simply a hint to the optimizer about how much of the query
result will actually get fetched. I think we could do that by
tweaking analyze.c to pass through the clauses the same as it does
for regular select, and have the planner discard the clauses after
it's done using them. (We don't want them to get to the executor
and interfere with the actual behavior of FETCH commands, but I
don't see a reason why they can't live to reach the planner...)

Comments anyone?

regards, tom lane