What do MATERIALIZED and NOT MATERIALIZED do on a CTE?
They control whether PostgreSQL computes the CTE once and stores the result (MATERIALIZED) or folds it into the outer query like a subquery (NOT MATERIALIZED). Before version 12, every CTE was materialized and acted as an optimisation fence.
Explain it simply
Materialized means "cook the dish fully, put it on a plate, then continue". Not materialized means "treat the step as part of the main cooking, and let the chef reorder things for efficiency".
Since PostgreSQL 12, a CTE is inlined automatically if it is non-recursive, has no side effects and is used only once. If it is used twice or more, it is computed once and reused.
Example
-- Force a single computation: expensive function result reused twice
WITH scored AS MATERIALIZED (
SELECT id, heavy_score(id) AS s FROM candidates
)
SELECT * FROM scored WHERE s > 80
UNION ALL
SELECT * FROM scored WHERE s < 20;
-- Allow the planner to push the outer filter into the CTE
WITH recent AS NOT MATERIALIZED (
SELECT * FROM orders WHERE created_at > now() - interval '30 days'
)
SELECT * FROM recent WHERE customer_id = 42;With MATERIALIZED, the filter customer_id = 42 cannot be pushed inside, so the whole CTE is computed first. With NOT MATERIALIZED, the planner can use an index on customer_id directly.
What to say in the interview
"MATERIALIZED forces one computed result and acts as a fence. NOT MATERIALIZED lets the planner inline it and push filters down. Since version 12 inlining is automatic for a single-use side-effect-free CTE, and I override it only when EXPLAIN shows a reason."
Common mistakes
- Assuming old advice, that CTEs are always slow, still applies to modern versions.