Comparing pg 9.4 with pg 12, CTE edition

Postgres 12 is around the corner, and as always is packed with new features. One of them being the option to allow the user to control the behaviour of CTE materialization. You can find the commit from Tom Lane here, which explains everything about this new change but TLDR is that so far, CTE's where fully materialized, so restrictions from the query that uses it won't apply to the CTE. Which is the right way doing this when you are using CTEs to INSERT/UPDATE/DELETE or when they are recursive. This means that when a CTE is side-effect-free and non-recursive it's safe to push the restrictions from the outer query.
So, from postgres 12, when it's safe or when the CTE is called only once, postgres will inline the CTE to the outer query, removing the optimization fence. User will be able to override this behaviour by using  MATERIALIZED / NOT MATERIALIZED keywords.

Here's an example :

drop table if exists test_cte;
create table test_cte as
    select generate_series(1,1000000) as id,
    floor(random() * 10 + 1)::int as random;
-- OLD WAY
EXPLAIN analyze
    WITH a_cte AS MATERIALIZED
        (
            SELECT random,count(*) FROM test_cte group by random
        )
    SELECT * FROM a_cte WHERE random = 5;
-- NEW
EXPLAIN analyze
    WITH a_cte AS NOT MATERIALIZED
        (
            SELECT random,count(*) FROM test_cte group by random
        )
    SELECT * FROM a_cte WHERE random = 5;

It's not really important to show all the differences in explain plans but rather to see how many rows it had to process in order to create the CTE by applying the condition directly:

-- Materialized (old):
->  Parallel Seq Scan on test_cte  (cost=0.00..8591.88 rows=416688 width=4) (actual  time=0.031..19.077 rows=333333 loops=3)

-- Not Materialized (New):
->  Parallel Seq Scan on test_cte  (cost=0.00..9633.59 rows=2083 width=4) (actual time=0.021..24.469 rows=33222 loops=3)
     Filter: (random = 5)
     Rows Removed by Filter: 300112

A production system running a 2TB on 9.4 with a dev that has a lot of free diskspace that we plan upgrading soon  makes a perfect candidate to test. Since 9.4 is the one going EOL and 12 being the latest, we could make an interesting performance comparisons.

Some details about this comparison :
I've used pg_stat_statements to extract from production 10 statements that are a combination of the most slow and most often used ones. I didn't filter out statements based on if the new behaviour will apply or not. So this comparison, while directed towards CTE's, it's not strictly about it. It mostly shows how long has postgres has come over the last years. The comparison run on 2 freshly analyzed copies of production, located in the same filesystem using common hardware.

Here's the results :





As you can see, when postgres 12 is faster the difference is substantial, after manually examining statements 4,6 and 7
I verified that it was because of the materialization. Interestingly, even by forcing the materialization, postgres 12 was still faster, unfortunately i didn't keep these numbers.

This feature is obviously welcome, and even if changing the default behaviour might sound a little scary, the benefits are just too big to ignore. That said, i think it would be great if this behaviour could be manipulated  in session instead of each individual statement.


Thanks for reading
Vasilis Ventirozos
https://www.credativ.com