Sorting Performance Improvement for Postgres 9.2

Bruce Momjian 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.

Bruce Momjian

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.