# Sorting improvements in PostgreSQL 9.2: the case for micro-optimisation

DevFeed: [Sorting improvements in PostgreSQL 9.2: the case for micro-optimisation](<https://devfeed.tech/articles/sorting-improvements-in-postgresql-9-2-the-case-for-micro-optimisation-33644.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2012/08/sorting-improvements-in-postgresql-92.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2012-08-02T02:04:00Z

Content type: article

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Sorting](<https://devfeed.tech/topics/sorting.md>), [Compiler](<https://devfeed.tech/topics/compiler.md>), [Programming](<https://devfeed.tech/topics/programming.md>), [inlining](<https://devfeed.tech/topics/inlining.md>)

Tags: [c](<https://devfeed.tech/tags/c.md>), [compiler](<https://devfeed.tech/tags/compiler.md>), [cpu](<https://devfeed.tech/tags/cpu.md>), [inlining](<https://devfeed.tech/tags/inlining.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sorting](<https://devfeed.tech/tags/sorting.md>)

## AI overview

This article explains the development of PostgreSQL 9.2 sorting improvements. The approach specialized quicksort code, used compiler inlining and generated specializations, and reduced indirection in comparator calls. The article states that simple in-memory integer and floating-point sorting became about 23% faster.

## Source excerpt

There has been much discussion of performance improvements in the upcoming 9.2 release of PostgreSQL. Recently, I noticed that Regina Obe and Leo Hsu's new book, "PostgreSQL: Up and running" prominently listed "Sorting improvements that improve in-memory sorting operations by as much as 20%" as a performance feature of that release. While they do get things about right there, I'm not sure that this improvement warrants such prominent placement, at least in sheer terms of its likely impact on the performance of production PostgreSQL systems - we packed a lot of great performance improvements into 9.2. The likely reason that it was picked up on in the book, and the real reason for this blogpost, is the story behind the development of the optimisation, which I for one find kind of interesting, and worth sharing. It's more interesting from the perspective of someone with a general interest in systems programming or PostgreSQL's design philosophy than a casual user, though. If you're a casual user, the short version is that simple queries that perform in-memory sorting of integers and floats will be about 23% faster. I wrote a rough prototype of the patch, that had a number of ideas, and proved the viability of the approach. Principal among those ideas was specialisation of the quicksort code: Formatting the code such that the compiler had compile-time knowledge of functions, with inlining used as an enabling optimisation, and a few variations produced. So rather than using complex indirection involving function pointers, a macro infrastructure was used to generate multiple specialisations, allowing the compiler to optimise the code more effectively as a result of being able to integrate everything. A secondary problem was that comparators (i.e. the comparison functions that all sorting within Postgres currently needs) were accessed in a round-about away. Roughly speaking, tuplesort (the part of the code that deals with sorting tuples, perhaps as part of a query's execut