Important changes in the CTE in PostgreSQL 12

WITH w AS NOT MATERIALIZED ( SELECT * FROM very_very_big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123; 

Today, a commit has been dropped into the PostgreSQL repository, which allows you to control the processing behavior of the CTE subqueries, namely: you can now explicitly specify whether the subquery will materialize separately or run as part of a single large query.

This will go into PostgreSQL 12, and this is a big deal. Let's look at why

Programmers love CTEs because it can significantly improve the readability of the code. Well, indeed, some analytical queries can work with dozens of tables and various groupings and filters. Write all this in one big request - guaranteed to get something unreadable. Therefore, with the help of the WITH operator, we sequentially, in small subqueries (which specify a human-readable name), describe the operation logic, and then output the result. Very comfortably.

More precisely, it would be very convenient if it were not for one thing: the current PostgreSQL executes these subqueries separately from each other, materializes them (writes the result into a temporary table). This can lead to a significant slowdown compared to one large unreadable monster. Especially if CTE subqueries return millions of rows.

However, there are situations when such a separate implementation works for the good: there is such an optimization trick, when it is better to perform a part of a complex query separately, but the postgres do not understand this themselves. Then we bring this part to the CTE subquery.

In general, situations are different, which is why a commit was made in Postgres 12, adding the keywords MATERIALIZED and NOT MATERIALIZED , which indicate whether the query should materialize or inline, respectively.

Moreover, the default behavior has changed. Now the CTE subquery will default to inline if its result is used once. Otherwise, it will materialize as before.