Re: [COMMITTERS] pgsql: Teach tuplesort.c about "top N" sorting, in which only the first

Magnus Hagander wrote:
> Gregory Stark wrote:
>> "Magnus Hagander" <magnus(at)hagander(dot)net> writes:
>>
>>>> What 3 columns? In-memory sorts, on-disk sorts, and on-disk size?
>>>> (Sum of how much spilled to disk).
>>> I was thinking in-mem sorts, on-disk sorts, limited-by-LIMIT sorts (that
>>> would be the new feature..)
>> Tom's code distinguished in-memory, top-N, on-disk with final merge postponed,
>> and on-disk with materialized result. Four categories. But I think the
>> distinction between the two types of in-memory and the two types of on-disk
>> sorts is only really useful when you're looking at an individual query. And
>> even then probably only useful to a Postgres hacker, not a DBA.
>
> Missed the two on-disk distinctions, yeah. But you're probably right
> that on-disk vs in-memory is enough, the interesting thing is to get
> indications on when you hit disk given what it does for performance.

Keep in mind that when the sort "goes to disk", it actually just means
that it used up work_mem and switches to merge sort with tapes. In a
typical configuration, there's plenty of RAM available to buffer the
tapes, so the terms on-disk and in-memory sorts are misleading. If I've
understood earlier discussion correctly, the quicksort -> tape sort
point is not even that interesting because the tape sort is actually not
that much slower than quicksort, as long as it fits in RAM.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com