February 16, 2012
As part of the work on Postgres 9.2, yesterday Robert Haas applied a much-discussed (December, January, February) patch by Peter Geoghegan to improve sort performance. Having recently blogged about how work_mem affects sort performance, I thought I would re-run my tests to see the improvement:
Temporary Storage | Old | New -----------------------+--------+------- magnetic | 68 | 67 magnetic (writeback) | 50 | 48 SSD | 36 | 32 tmpfs | 30 | 27 none (high work_mem) | 12 | 9.5
As you can see, the new patch shaves about 3 seconds off of my test. That isn't a huge win for sorts that have to hit the disk, but for in-memory sorts (the last row), it yields a 20% improvement, which closely matches the ~25% reported by testers of the patch. This is one of the many sort improvements coming in Postgres 9.2.