Re: B-Tree support function number 3 (strxfrm() optimization)

On Tue, Aug 5, 2014 at 8:55 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> Comparator cost affects external sorts more than it affects internal sorts.
> When I profiled internal and external int4 sorting, btint4cmp() was 0.37% of
> the internal sort profile and 10.26% of the external sort profile.

Did you attempt to characterize where wall time was being spent? With
a difference like that, it seems likely that it's largely down to the
fact that quicksort is cache oblivious, rather than, say, that there
were more comparisons required.

>> I must admit that this did surprise me, but then I don't grok tape
>> sort. What's particularly interesting here is that when work_mem is
>> cranked up to 512MB, which is a high setting, but still not high
>> enough to do an internal sort, the difference closes in a bit. Instead
>> of 41 runs, there are only 2. Patched now takes 16.3 seconds.
>> Meanwhile, master is somewhat improved, and consistently takes 65
>> seconds to complete the sort.
>
>> Does anyone recall hearing complaints around higher work_mem settings
>> regressing performance?
>
> Jeff Janes has mentioned it:
> http://www.postgresql.org/message-id/CAMkU=1zVD82voXw1vBG1kWcz5c2G=SupGohPKM0ThwmpRK1Ddw@mail.gmail.com

I knew that I'd heard that at least once. Apparently some other
database systems have external sorts that tend to be faster than
equivalent internal sorts. I'd guess that that is an artifact of their
having a substandard internal sort, though.

--
Peter Geoghegan