Subject: Re: Using quicksort for every external sort run
To: Peter Geoghegan <pg@heroku.com>
References: 
 <CAM3SWZQVSpNeHHKKq-rjJddOcbpdmyHDJUMBBL2-AP2R+4YCHg@mail.gmail.com>
 <CA+TgmoY0jW7R1=NfCFrfdmEmH3RO06aiFJ46d9riD+ar3+gVZw@mail.gmail.com>
 <CAM-w4HPOTjeUqjahY+Dh2JzaiNRz_VU4Z9-9wVbeiJsFN4rmvw@mail.gmail.com>
 <CAM3SWZS8Ppe3pC8HJSKc+6HgOxUseBDm-d7uoaKzT1PdH6wb=g@mail.gmail.com>
 <CAM3SWZSmHQG6xk+na47VVzVUGU4CmDp6_ijYd+S=cetk3OEHXA@mail.gmail.com>
 <CAM3SWZRFzg1LUK8FBg_goZ8zL0n7k6q83qQjhOV8NDZioA5TEQ@mail.gmail.com>
 <CAM3SWZQ9_QK81eW_=ZciznnRyeFnJ4YcFrDgViaySpvyCqEDqA@mail.gmail.com>
 <CA+TgmoaXYtofbXdjzBneEjsx8a1Z9A+TVB1mSTUFAnrdNu=BTA@mail.gmail.com>
 <CAM3SWZTjR2wrO8zavt1LZf+5_HB--SWk_J9rPR9rXb+Jj7ATKw@mail.gmail.com>
 <56FB25BB.6070805@2ndquadrant.com>
 <CAM3SWZTOb9Yqi4iba8XEuAUso-P7yYKuToYYfff6NYshtB9pGw@mail.gmail.com>
 <7142bf2a-9460-b549-61f3-8cb32a3209f9@2ndquadrant.com>
 <CAM3SWZQzNDrAq_LM2NpuLk_hAkd5TnRGSAsL0WNnt63L9+XH7Q@mail.gmail.com>
 <27716590-56b5-baf0-71ee-d27f309c1acc@2ndquadrant.com>
 <CAM3SWZREyZCrvx8aHAK-8Uieo-bzuSkXPJpc9ni9ZMOANGRrqg@mail.gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <267c57d1-b0fc-aa9b-ecb6-cadfe1a36d8d@2ndquadrant.com>
Date: Sun, 3 Apr 2016 22:52:50 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CAM3SWZREyZCrvx8aHAK-8Uieo-bzuSkXPJpc9ni9ZMOANGRrqg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 04/03/2016 09:41 PM, Peter Geoghegan wrote:
> Hi Tomas,
...
>> 3) replacement_sort_mem GUC
>>
>> I'm not quite sure what's the plan with this GUC. It was useful for
>> development, but it seems to me it's pretty difficult to tune it in practice
>> (especially if you don't know the internals, which users generally don't).
>
> I agree.
>
>> So I think we should either remove the GUC entirely, or move it to the
>> developer section next to trace_sort (and removing it from the conf).
>
> I'll let Robert decide what's best here, but I see your point.
>
> Side note: trace_sort actually is documented. It's a bit weird that we
> have those TRACE_SORT macros at all IMV. I think we should rip those
> out, and assume every build enables TRACE_SORT, because that's
> probably true anyway.

What do you mean by documented? I thought this might be a good place is:

http://www.postgresql.org/docs/devel/static/runtime-config-developer.html

which is where trace_sort is documented.

>
> I do think that replacement selection could be put to good use for
> CREATE INDEX if the CREATE INDEX utility command had a "presorted"
> parameter. Specifically, an implementation of the "presorted" idea
> that I recently sketched [1] could do better than any presorted
> replacement selection case we've seen so far because it allows the
> implementation to optimistically create the index on-the-fly (if that
> isn't possible, throw an error), without a second pass over tuples
> sorted on tape. Nothing needs to be stored on a tape/temp file *at
> all*; the only thing that is stored externally is the index itself.
> But this patch doesn't add that feature, which can be worked on
> without the user needing to know about replacement_sort_mem in 9.6.
>
> So, I'm not in favor of ripping out the replacement selection code,
> but think it could make sense to effectively disable it entirely for
> the time being (with some developer feature to turn it back on for
> testing). In general, I share your misgivings about the new GUC,
> though.

OK.

>
>> I'm wondering whether 16MB default is not a bit too much, actually. As
>> explained before, that's not the amount of cache we should expect per
>> process, so maybe ~2-4MB would be a better default value?
>
> The obvious presorted case is where we have a SERIAL column, but as I
> mentioned even that isn't helped by RS. Moreover, it will be
> significantly hurt with a default maintenance_work_mem of 64MB. Your
> int4 CREATE INDEX cases clearly show this.
>
>> BTW couldn't we tune the value automatically for each sort, using the
>> pg_stats.correlation for the sort keys, when available (increasing the
>> replacement_sort_mem when correlation is close to 1.0)? Wouldn't that
>> improve at least some of the regressions?
>
> Maybe, but that seems hard. That information isn't conveniently
> available to the executor/tuplesort, and as we've seen with CREATE
> INDEX int4 cases, it's far from clear that we'll win even when there
> definitely is presorted input. Replacement selection needs more than a
> simple correlation to win, so you'll end up building a cost model with
> many new problems if this is to work.

Sure, that's non-trivial and definitely not a 9.6 material. I'm also 
wondering whether we need to do choose replacement_sort_mem at planning 
time, or whether it could be done in the executor based on actually 
observed data ...

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers