Re: Any better plan for this query?..

On 5/19/09 5:01 AM, "Matthew Wakeling" <matthew(at)flymine(dot)org> wrote:

> On Tue, 19 May 2009, Simon Riggs wrote:
>>> Speaking of avoiding large sorts, I'd like to push again for partial
>>> sorts. This is the situation where an index provides data sorted by
>>> column "a", and the query requests data sorted by "a, b". Currently,
>>> Postgres sorts the entire data set, whereas it need only group each
>>> set of identical "a" and sort each by "b".
>>
>> Partially sorted data takes much less effort to sort (OK, not zero, I
>> grant) so this seems like a high complexity, lower value feature. I
>> agree it should be on the TODO, just IMHO at a lower priority than some
>> other features.
>
> Not arguing with you, however I'd like to point out that partial sorting
> allows the results to be streamed, which would lower the cost to produce
> the first row of results significantly, and reduce the amount of RAM used
> by the query, and prevent temporary tables from being used. That has to be
> a fairly major win. Queries with a LIMIT would see the most benefit.
>

I will second that point --
Although for smaller sorts, the partial sort doesn't help much and is just
complicated -- once the sort is large, it reduces the amount of work_mem
needed significantly for large performance gain, and large concurrent query
scale gain.
And those benefits occur without using LIMIT.