Re: Optimize ORDER BY ... LIMIT

Gregory Stark <stark(at)enterprisedb(dot)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