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

On Tue, Aug 05, 2014 at 07:32:35PM -0700, Peter Geoghegan wrote:
> select * from (select * from tags order by tag offset 100000000) ii;
>
> Git master takes about 25 seconds to execute the query. Patched takes
> about 6.8 seconds. That seems very good, but this is not really new
> information.
>
> However, with work_mem set low enough to get an external sort, the
> difference is more interesting. If I set work_mem to 10 MB, then the
> query takes about 10.7 seconds to execute with a suitably patched
> Postgres. Whereas on master, it consistently takes a full 69 seconds.
> That's the largest improvement I've seen so far, for any case.

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.

> 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

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com