CTE materialization: the fence that moved

The fence, and the day it moved

When you write a CTE, does Postgres actually separate it from the outer query, or does it fold everything together and let the planner optimize freely? The answer changed at a version boundary, and getting it wrong costs you seconds per query. Before Postgres 12, every CTE was an unconditional optimization fence. The planner computed each CTE exactly once, stored its full result in memory (materialized it), and then ran the outer query against that stored result. It never pushed the outer query’s filters down into the CTE. That made a CTE a predictable but often expensive boundary: handy when you wanted to compute something once and reuse it, ruinous when the outer query only needed a sliver of a huge CTE.

Postgres 12 changed the default to inlining: a CTE that is referenced once and is side-effect-free and not recursive is folded into the outer query, exactly as if you’d written it as a subquery — so predicates push down and indexes get used. If any of those conditions fails — the CTE is referenced more than once, or it contains a data-modifying statement (INSERT/UPDATE/DELETE ... RETURNING), or it’s recursive — Postgres still materializes it.

-- PG12+: referenced once, no side effects → inlined.
-- tenant_id = 7 pushes down into the CTE and hits an index.
WITH e AS (SELECT * FROM events)
SELECT * FROM e WHERE tenant_id = 7;

-- Referenced twice → materialized (computed once, reused).
WITH e AS (SELECT * FROM events WHERE created_at > now() - interval '1 day')
SELECT (SELECT count(*) FROM e) AS total,
       (SELECT count(*) FROM e WHERE tenant_id = 7) AS for_seven;

The two keywords that override the planner

You are not at the planner’s mercy. Two explicit keywords force the behavior:

-- Force materialization: compute once, even though referenced once.
WITH e AS MATERIALIZED (SELECT * FROM big_expensive_function_call())
SELECT * FROM e WHERE id = 42;

-- Force inlining: fold in even though referenced twice (you accept recomputation).
WITH e AS NOT MATERIALIZED (SELECT * FROM events WHERE tenant_id = 7)
SELECT ... ;

When to force MATERIALIZED: the CTE is genuinely expensive to compute (a costly function, a heavy aggregate) and referenced once, but inlining would cause that expensive work to run per outer row or be repeated; or the CTE has side effects you must run exactly once; or — the real-world rescue — inlining produced a worse plan and you want to restore the old fence behavior to break a bad join order. When to force NOT MATERIALIZED: the CTE is referenced multiple times so Postgres materialized it, but each reference only needs a filtered slice, and you’d rather the predicate push down than materialize the whole thing.

The trap cuts both ways. MATERIALIZED reintroduces the fence, so a selective WHERE in the outer query cannot push into the CTE — you may scan the entire base table to build the CTE, then filter. That’s the exact pre-12 footgun. Use MATERIALIZED deliberately, not as a reflex.

Spotting it in EXPLAIN

EXPLAIN tells you which path the planner took. A materialized CTE shows a CTE Scan node reading from a separately computed CTE <name> subplan; an inlined CTE shows no CTE Scan at all — the CTE’s tables appear directly in the main plan tree, and you’ll see the outer predicate applied as an Index Cond deep inside. So the diagnostic is simple: grep the plan for CTE Scan. Present means materialized; absent means inlined. The deep skill of reading these plans — cost numbers, estimate-vs-actual rows, join nodes — belongs to unit 08 and to the databases track’s execution-plans chapter; here you only need to recognize the fork.