The CTE Materialization Fence: MATERIALIZED and NOT MATERIALIZED
Before PostgreSQL 12 every WITH clause was computed in full before the rest of the query ran, which blocked the planner from pushing a WHERE condition inside it; since 12 simple CTEs are inlined instead. You can force either behaviour with WITH x AS MATERIALIZED or AS NOT MATERIALIZED. Materialising deliberately is right when an expensive CTE is used several times; letting it inline is right when you want the outer filter to reach the inner scan.
Questions this Concept answers
- Why does a materialized CTE often make a query slower than the same logic written as a subquery?
CTE materialization: the fence that moved â Skein
This article explains how PostgreSQL handles a WITH query, also called a CTE. Older versions always ran the CTE on its own and stored the result, which stopped the planner from pushing filters inside…