Query performance of the upcoming version 11 of PostgreSQL

10.2018 / Category: , / Tags: |

By Kaarel Moppel - Last week the first release candidate of the upcoming Postgres version 11 was released, stating that everything is going more or less as planned – great news! The 11 release is promising a lot of very cool stuff as usual, including – better partitioning, more parallezation and JIT capabilities (which are not activated by default though). This blog is about testing the query performance of v11.

Besides those headline features, there are hundreds or rather thousands (git diff --shortstat REL_10_5..REL_11_RC1 says "3192 files changed, 324206 insertions, 178499 deletions") of smaller changes, and some of them for sure boosting performance, also on some typical queries. Release notes didn't state any numerical performance indicators though as usually, so people have to find it out on their own – so that's what this blog post is about. I'm basically running 4 quite simple analytical/aggregate type of queries and laying out the numbers for you to evaluate – so jump at the summarizing table end of the post or get into the details by reading on.

Test query performance

I carried over 3 out of 4 test queries from the last year's similar test for 9.6 vs 10 (check it out here if you're into archaeology) and added one query to test index traversing performance. Queries are all tied to the schema generated by our good old friend pgbench, with one minor adjustment – creating a copy of the pgbench_accounts table be able to simulate a join on two 1 million rows tables. This time I also didn't disable the parallel features as they're "on" by default for both versions and thus probably won't be touched for most setups.

For pbench "scale" I chose 100, meaning 10 Million rows (1.3GB) will be populated into the pgbench_accounts table. This gives enough rows but still fits into memory so that runtimes are more predictable.

Hardware & Server Config

I spun up a lower end node on Linode, labelled "Linode 8GB" (4 cores, 8GB RAM, 160 GB SSD, Ubuntu 18.04) but because we're not testing hardware, but comparing 2 Postgres versions, it should be fine. Also, even though we've got an SSD, the disk doesn't really matter here, as it's a read-only test and I've chosen queries and the test data amount in a way that everything will fit into RAM. This is giving us an algorithmic comparison. For additional testing fairness my test script also performed queries in turns between 10.5 and 11 RC1 to try to suppress the effects of possible background load jitter of a shared virtual machine.

For running Postgres I used official the official Postgres project maintained repo for both 10 and 11. Concerning server settings I left everything to defaults, except below changes on both clusters for reasons described in comments.

Results of query performance

After running my test script (6h on both clusters), which basically does all the work out of the box and can be found here, I got the numbers below.

Query Instance Mean time (s) % Change Stddev time (ms) % Change
SUM 10.5 1.54 32.5
SUM 11RC1 1.39 -9.7 24.2 -25.5
JOIN 10.5 3.75 74.5
JOIN 11RC1 3.84 +2.3 72.0 -3.5
CUBE 10.5 15.0 852
CUBE 11RC1 9.58 -56.6 542 -57.2
INDEX 10.5 3.23 199
INDEX 11RC1 2.16 -49.5 167 -19.2

So what do these numbers tell us? 2 queries out of 4 have improved very significantly! +50% is very good, given how refined Postgres algorithms already are. The INDEX query can be easily explained by the fact that previously index access could not be parallelized. For the CUBE (grouping sets feature) I didn't find anything from the release notes. It would be interesting to know though so please comment if you have some insights. The other 2 queries are "on par" and the small 2.3% JOIN loss (although latencies are more consistent now) is being sufficiently compensated for on other fronts.

Concluding remarks

To sum it up – the general impression from 11 RC1 is very positive. The numbers look much better compared to last year's improvements. So hopefully it will be an awesome release and a relief for those who have to deal with tons of data - personally I'm itching to upgrade some systems already:)

NB! Not to forget – these are just "some" queries that I conjured up and it's also a RC release so although it looks very promising I wouldn't take those numbers as a guarantee for anything outside of the described test set.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram