Re: Optimize ORDER BY ... LIMIT

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

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

Well I'm not all *that* excited about it either, it's just another plan and
there are an infinite number of possible plans out there we could infinite for
various corner cases.

But just in case it's not clear for anyone the usual use case for this paging
results on a web page. As much as I normally try to convince people they don't
want to do it that way they usually do end up with it implemented using
limit/offset. And Postgres currently is absolutely *awful* at running those
queries.

Often the killer requirement that makes it infeasible to create an index is
precisely that they want to be able to sort on any of a long list of possible
keys. Creating dozens of keys on every table isn't too appealing.

And in any case the query is often a join where the data in the sort key isn't
even all coming from the same table or where you need to use other indexes to
fetch the data prior to the sort.

I won't discourage anyone from working on OLAP queries and this is indeed a
similar idea. I suspect the same functionality in tuplesort of being able to
set a maximum number of tuples to keep will be useful there too.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com