Postgresql 12 and CTEs

Until now the CTEs have always been materialized by the DB; if I had executed
something like:

explain analyze WITH x as (
SELECT relkind, COUNT(*) FROM pg_class GROUP BY relkind
)
SELECT FROM x WHERE relkind = 'r';


the result would have been something like that:

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
CTE Scan on x (cost=16.15..16.24 rows=1 width=9) (actual time=0.310..0.315 rows=1 loops=1)
Filter: (relkind = 'r'::"char")
Rows Removed by Filter: 3
CTE x
-> HashAggregate (cost=16.11..16.15 rows=4 width=9) (actual time=0.306..0.308 rows=4 loops=1)
Group Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00..14.41 rows=341 width=1) (actual time=0.014..0.082 rows=341 loops=1)
Planning time: 0.174 ms
Execution time: 0.380 ms
(9 righe)


The query within the CTE would always be executed, materialized and then read.
Now if you only executed the query that is inside the CTE:

postgres = # explain analyze SELECT relkind, COUNT (*) FROM pg_class GROUP BY relkind;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------
HashAggregate (cost=16.11..16.15 rows=4 width=9) (actual time=0.310..0.312 rows=4 loops=1)
Group Key: relkind
-> Seq Scan on pg_class (cost=0.00..14.41 rows=341 width=1) (actual time=0.027..0.094 rows=341 loops=1)
Planning time: 0.158 ms
Execution time: 0.391 ms
(5 righe)


the result is very different from that obtained previously.

This has always allowed DBAs to be able to force the planner to execute some query "blocks" before others. This was used to force postgresql to first filter some data in order to
influence "heavily" on the planner's decisions.

Postgresql >= 12.x

Starting from Postgresql version 12 things change:
two new options were introduced during the execution of a CTE: MATERIALIZED / NOT MATERIALIZED

So if I do something like:

postgres=# explain analyze WITH x AS MATERIALIZED (
SELECT relkind, COUNT(*) FROM pg_class GROUP BY relkind
)
SELECT FROM x WHERE relkind = 'r';


I get a query execution plan very similar to the one I would have obtained if I had executed a CTE on a version of Postgresql lower than 12.


If instead you execute:

postgres=# explain analyze WITH x AS NOT MATERIALIZED (
SELECT relkind, COUNT(*) FROM pg_class GROUP BY relkind
)
SELECT FROM x WHERE relkind = 'r';


I get an execution plan very similar to the previous case without CTE executed on a version of Postgresql <12.

QUERY PLAN
-----------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..18.33 rows=4 width=9) (actual time=0.439..0.441 rows=1 loops=1)
Group Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00..17.94 rows=70 width=1) (actual time=0.016..0.323 rows=70 loops=1)
Filter: (relkind = 'r'::"char")
Rows Removed by Filter: 325
Planning Time: 0.178 ms
Execution Time: 0.484 ms
(7 rows)


With the NOT MATERIALIZED option the data is not materialized and the planner enters the CTE by no longer materializing the data as it did with the versions previous.



Question: What if on a Postgresql 12 I execute something like:

postgres=# explain analyze WITH x AS (
SELECT relkind, COUNT(*) FROM pg_class GROUP BY relkind
)
SELECT FROM x WHERE relkind = 'r';

The planner without any options behaves exactly as if the NOT MATERIALIZED option was set.

QUERY PLAN
-----------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..18.33 rows=4 width=9) (actual time=0.284..0.287 rows=1 loops=1)
Group Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00..17.94 rows=70 width=1) (actual time=0.015..0.190 rows=70 loops=1)
Filter: (relkind = 'r'::"char")
Rows Removed by Filter: 325
Planning Time: 0.115 ms
Execution Time: 0.321 ms
(7 rows)



Conclusion:
From version 12, we have to insert the MATERIALIZED option if we want to have on our queries the same behavior we had with the previous versions.
Personally I would expect a reverse behavior for reasons of retro compatibility .... but ...Enjoy