Ten years of improvements in PostgreSQL's optimizer

As a query optimization researcher, I’ve spent the last 10 years of my life playing with, learning from, and building on top of the most sophisticated open source query optimizer out there, PostgreSQL. I recently wondered how much PostgreSQL had improved over the decade since I started working on databases. While changelogs and opinion pieces were plentiful, I couldn’t find any strong empirical comparisons, so I decided to run the join order benchmark (JOB)¹ on PostgreSQL 8 through 16. I recorded the 90th percentile query latency for each database version.

I built each version² of PostgreSQL using GCC 13.2 inside a Docker container with Arch Linux. Since I wanted to measure the quality of the query optimizer, and not index/IO performance, I set shared_buffers to 8GB (large enough to hold the entire database). I also set work_mem to 8MB for all versions. Each query is executed once to warm the cache, then the median latency of 5 additional runs in recorded.

Overall, PostgreSQL’s tail performance has improved drastically, although versions 13 through 16 have been mostly stable. Comparing version 8 to version 16, PostgreSQL’s optimizer has dropped tail latency by nearly half in the last 10 years!

We can also investigate the entire query distribution (note the log scale):

We can use regression analysis to (1) confirm that the downward slope in latency is significant, and (2) quantify how much improvement is brought by each version of PostgreSQL. If we regress the PostgreSQL major version number against query latency, we see that each new major version of PostgreSQL brings, on average, a 15% performance improvement on the Join Order Benchmark (). However, a linear model is arguably a poor measure of the change ().

Of course, not all of these improvements are attributable to the query optimizer. Improvements to the execution engine – from parallel workers to just-in-time (JIT) compilation – also play a role. It would be interesting to investigate how each query plan in JOB has changed over the year… maybe next time!

Quantifying the improvement aside:

• Upgrade your database! Going from PostgreSQL 8 to 16 has the potential to massively improve your workload’s tail latency.
• Researchers should note that PostgreSQL is a bit of a moving target. Learned query optimization research has compared with different versions of PostgreSQL over time (e.g., Neo and Bao compare with version 11, whereas newer work compare with version 14, 15, or 16.) So just because an older technique improves on PostgreSQL by 30%, and a newer technique only improves on PostgreSQL by 25%, the newer technique may be comparing against a stronger PostgreSQL.

You can also check out the raw data for yourself.

Notes