Re: [SQL] trivial problem

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> FAQ says:
> See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P>

> This only prevents all row results from being transferred to the client.
> The entire query must be evaluated, even if you only want just the first
> few rows. Consider a query that has an <I>order by.</I>  There is no way
> to return any rows until the entire query is evaluated and sorted.<P>

That FAQ entry is not right.  It might be right for FETCH, but not for
SELECT ... LIMIT.  With a LIMIT, the executor will stop once it has
generated the requested number of rows.  Of course, how much computation
is needed to reach that point depends greatly on the query and the query
plan.

If an explicit sort step is being used to implement ORDER BY, then the
FAQ is correct.  But if the ORDER BY is implemented by an index scan,
rather than an explicit sort step, then presto: we just run the
indexscan for the first N tuples and stop.  (6.5.* is not very bright
about avoiding a sort step, but current sources are much better.)

Still to be done: modify the optimizer to be aware of this fact so that
it will be more likely to choose an indexscan when a small LIMIT is
present.  Right now it chooses indexscan vs. explicit sort on the basis
of cost to return all the tuples, which is bogus if there's a LIMIT.
Ideally it'd also take LIMIT into account when choosing join types,
etc...
        regards, tom lane