Important changes in the CTE in PostgreSQL 12

WITH w ASNOTMATERIALIZED (
    SELECT * 
    FROM very_very_big_table
)
SELECT * 
FROM w AS w1 
    JOIN w AS w2 
        ON w1.key = w2.refWHERE w2.key = 123;

Today in the PostgreSQL repository fell Komichi , allows you to control the processing of subqueries CTE behavior, namely: now you can explicitly specify whether the subquery separately materialize, or performed as part of a single large request.

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 operator, WITHwe sequentially, in small subqueries (by which a human-readable name is given) describe the logic of the work, and then give 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, the situation may be different, which is why in 12 Postgres was made Komichi , adding keywords MATERIALIZEDand NOT MATERIALIZEDwhich respectively indicate whether the request or inline materialize.

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.