Re: [HACKERS] Solution for LIMIT cost estimation

At 23:24 13/02/00 -0500, Tom Lane wrote:
>
>Perhaps we should stick to two alternatives:
>
>1. If LIMIT is present, optimize on an assumption that X% of the
>tuples are fetched, where X does *not* depend on the specific
>values given for OFFSET or LIMIT. (But we could make X a settable
>parameter...)
>
>2. Optimize using OFFSET+LIMIT as the expected number of tuples to
>fetch. Document that varying OFFSET or LIMIT will not necessarily
>generate consistent results unless you specify ORDER BY to force a
>consistent tuple order.
>
>I don't really like #1, but I can see where #2 might cause some
>unhappiness as well. Comments, opinions?

#1 seems pretty nasty as a concept, unless of course this actually reflects
the way that PG retrieves rows. My guess is that it will have to retrieve
rows 1 to (offset + limit), not (offset) to (offset + limit), so the whole
appreximation should be based on #2.

[Aside: I suspect that trying to solve problems for people who want to use
context free (web) interfaces to retrieve blocks of rows is not a job for
the optimizer. It is far more suited to cursors and/or local temporary
tables, both of which require some context].

#2 seems more correct, in that it reflects a good estimation, but
pessimistic: with good indexes defined, the query may well only need to do
a scan of the index to get up to the 'offset-th' row. This, I am sure, must
be faster than retrieving all rows up to OFFSET.

This leaves two questions:

a. Does the optimizer know how to do 'index-only' queries (where all fields
are satisfied by the index)

b. Just to clarify, OFFSET does affect the tuples actually returned,
doesn't it?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/