RE: [HACKERS] Solution for LIMIT cost estimation

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> "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 ;-).
>

Probably your change would work well in most cases.
It's nice.
However it seems more preferable to be able to select first/all rows hint.

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

The following was the reply from Jan 16 months ago.
Unfortunately PostgreSQL optimizer wasn't able to choose index scan
for queires with no qualification at that time.

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp

Re: [HACKERS] What about LIMIT in SELECT ? [1998/10/19]

Hiroshi Inoue wrote:

> When using cursors,in most cases the response to get first(next) rows
> is necessary for me,not the throughput.
> How can we tell PostgreSQL optimzer that the response is necessary ?

With my LIMIT patch, the offset and the row count are part of
the querytree. And if a LIMIT is given, the limitCount elemet
of the querytree (a Node *) isn't NULL what it is by default.

When a LIMIT is given, the optimizer could assume that first
rows is wanted (even if the limit is ALL maybe - but I have
to think about this some more). And this assumption might let
it decide to use an index to resolve an ORDER BY even if no
qualification was given.

Telling the optimizer that first rows wanted in a cursor
operation would read

DECLARE CURSOR c FOR SELECT * FROM mytab ORDER BY a LIMIT ALL;

Jan