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=supgohpkm0thwmprk1...@mail.gmail.com -- Noah Misch EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
