Re: Optimize ORDER BY ... LIMIT

Gregory Stark <stark@enterprisedb.com> writes:
> I've been looking at doing the following TODO item:
>     Allow ORDER BY ... LIMIT # to select high/low value without sort or index
>     using a sequential scan for highest/lowest values

> I think this is pretty important to cover at some point because really _not_
> doing this just wrong.

I can't get all *that* excited about it, since an index solves the
problem.

> The way I see to do this is to still use a Sort node and use a tuplesort but
> to arrange to get the information of the maximum number of tuples needed to
> the tuplesort so it can throw out tuples as it sorts.

The implementation that was proposed in the earlier discussion did not
involve hacking the sort code beyond recognition ;-).

I believe a better way to think about this would be as an aggregate that
remembers the top N rows.  It can't quite be an aggregate as it stands
(unless we want to invent aggregates that can return SETOF?)  but I
think there might be some useful overlap with the SQL2003
window-function concept.
        regards, tom lane