PostgreSQL 9.4 aggregate filter clauses: They do pay off

02.2015 / Category: / Tags: |

In my previous posting on PostgreSQL 9.4 I wrote about aggregate FILTER clauses, which are a neat way to make partial aggregates more readable. Inspired by some comments to this blog post I decided to create a follow-up post, to see the impact this new FILTER clause has on performance.

Loading some demo data

To see the performance impact people can expect, here is some demo data:

The data set is roughly 4.5 GB of data. On the test box this still fits nicely into memory:

A classical approach

For years I have used the classical approach. The trick is that aggregation functions won't consider NULL values. So to do partial aggregation functions the idea is to just replace values on the fly. The code is as already stated in the previous post:

In this example the aggregation takes slightly over 6 seconds (with all hint bits set, etc.). This is not too bad.

The modern approach - filter clauses

Let us see now what happens when the new approach is used:

The query is almost one second faster than the original one. The overhead of the new FILTER clause is simply ways smaller than in the original version of the query. Of course, the more clauses you add the larger the difference will be.

More realistic scenarios

Before a more complex query is fired I decided to increase work_mem to make sure that all aggregations can happen in memory:

This time an additional GROUP BY clause is added to split the data set into smaller chunks. A GROUP BY clause is basically what most people will have in their queries in case FILTER is used:

The result is created in 22 seconds, which is actually quite ok.

The next query shows the same thing using the new syntax:

Summary - FILTER clauses

The important thing here is that the performance difference is again around 1 second. This makes sense because the performance gained by the FILTER clause is the same regardless of the grouping around it. This is somewhat expected.

In case you need any assistance, please feel free to contact us.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

0 0 votes
Article Rating
Notify of
1 Comment
Newest Most Voted
Inline Feedbacks
View all comments
David Fetter
David Fetter
9 years ago

My motivation was SQL code clarity, with performance as a nice-to-have which can later be improved.

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