Why a CTE Can Perform Worse: Materialization Fences

• Optimization fence – a boundary the planner will not cross; when a CTE is fenced, the engine computes its full result before the outer query can filter it.
• Materialization – the CTE's result is stored as a temporary set and the outer query reads from that stored set.
• Inlining – the CTE's body is merged into the main query, allowing the optimizer to push predicates down and use indexes.
• PostgreSQL 12 change – non-recursive CTEs referenced once are now inlined by default; multi-referenced and recursive CTEs are still materialized.
• MATERIALIZED / NOT MATERIALIZED – hints that force the planner to either store the result or inline the body, overriding the default.

What is an optimization fence?

An optimization fence is a boundary the query planner will not cross. When a CTE is a fence, the planner computes the CTE's result as written, fully and separately, and only then applies the outer query against that materialized result. The harm is that predicates from the outer query cannot be pushed down into the CTE.

-- CTE that scans ALL transactions (200 million rows)
WITH all_txns AS (
    SELECT * FROM transactions
)
SELECT * FROM all_txns
WHERE account_id = 'SAV2001';  -- wants only Rajesh's

If the CTE is materialized (fenced), the database first reads all 200 million rows into a temporary result, then filters to Rajesh's handful. If the CTE is inlined, the account_id = 'SAV2001' filter pushes into the base scan and only Rajesh's rows are read.

Why did old PostgreSQL materialize all CTEs?

Before PostgreSQL 12, every CTE was unconditionally materialized as a fence. The planner treated the CTE boundary as sacred and never pushed predicates through it. Developers who used CTEs for readability were surprised that rewriting the same logic as a subquery or join was often much faster.

-- On PostgreSQL 11: CTE is materialized (slow)
WITH account_txns AS (
    SELECT * FROM transactions  -- scans ALL 200M rows
)
SELECT * FROM account_txns
WHERE account_id = 'SAV2001';  -- filter applied AFTER full scan

-- Equivalent subquery (fast): filter is pushed down
SELECT * FROM (
    SELECT * FROM transactions
) t
WHERE account_id = 'SAV2001';  -- optimizer pushes filter into base scan

What changed in PostgreSQL 12?

From PostgreSQL 12, the default changed: a non-recursive CTE that is referenced exactly once and has no side effects is inlined into the main query by default. This means the optimizer can push predicates down and use indexes, making the CTE behave like a subquery.

CTEs that are still materialized by default:

• Recursive CTEs (WITH RECURSIVE).
• CTEs referenced more than once in the outer query.
• CTEs containing data-modifying statements (INSERT, UPDATE, DELETE).

-- On PostgreSQL 12+: CTE is inlined (fast)
WITH account_txns AS (
    SELECT * FROM transactions  -- NOT scanned fully
)
SELECT * FROM account_txns
WHERE account_id = 'SAV2001';  -- filter pushed into base scan

When do you force MATERIALIZED?

Use MATERIALIZED when the CTE is genuinely expensive and referenced multiple times. Computing it once and reusing the stored result beats recomputing.

-- Expensive CTE referenced three times: force materialization
WITH monthly_totals AS MATERIALIZED (
    SELECT account_id,
           DATE_TRUNC('month', txn_date) AS month,
           SUM(amount) AS total
    FROM   transactions
    WHERE  txn_date >= '2026-01-01'
    GROUP BY account_id, DATE_TRUNC('month', txn_date)
)
SELECT 'high_spenders' AS report, account_id, SUM(total)
FROM   monthly_totals GROUP BY account_id HAVING SUM(total) > 1000000
UNION ALL
SELECT 'low_activity', account_id, SUM(total)
FROM   monthly_totals GROUP BY account_id HAVING COUNT(*) < 3
UNION ALL
SELECT 'top_month', account_id, MAX(total)
FROM   monthly_totals GROUP BY account_id;

Other reasons to force MATERIALIZED:

• To pin a volatile function (like random() or now()) so it runs exactly once.
• To deliberately create a fence that stops the optimizer from choosing a bad plan.

When do you force NOT MATERIALIZED?

Use NOT MATERIALIZED when materialization is causing a wasteful full computation and you want the outer filter to push down, even though the CTE is referenced more than once.

-- Force inlining: let the account filter push down
WITH account_txns AS NOT MATERIALIZED (
    SELECT * FROM transactions
)
SELECT * FROM account_txns
WHERE account_id = 'SAV2001';

How do you diagnose the problem?

Use EXPLAIN ANALYZE to see whether the CTE is materialized and whether predicates are pushed down.

EXPLAIN ANALYZE
WITH account_txns AS (
    SELECT * FROM transactions
)
SELECT * FROM account_txns
WHERE account_id = 'SAV2001';

-- Materialized (bad): Seq Scan on transactions, then Filter
-- Inlined (good): Index Scan on transactions using idx_account_id

How does this compare across databases?

| Database | CTE Behavior | Hints Available |
|---|---|---|
| PostgreSQL < 12 | Always materialized | None |
| PostgreSQL 12+ | Inline single-use, materialize multi-use | MATERIALIZED / NOT MATERIALIZED |
| MySQL 8.0+ | Derived merge (inlines) for non-recursive | None (optimizer decides) |
| SQL Server | Usually inlines | No explicit hints |
| Oracle | Usually inlines, may materialize | MATERIALIZE / INLINE hints |

What should a senior engineer know?

• Materialization is not always bad. If a CTE is genuinely expensive and reused, MATERIALIZED computes it once and avoids redundant work.
• The skill is matching the hint to the situation: inline for filter pushdown on a single-use CTE, materialize for compute-once-reuse-many.
• In PostgreSQL, you can verify inlining by checking EXPLAIN for the absence of a CTE Scan node.
• Data-modifying CTEs (e.g., WITH deleted AS (DELETE FROM ... RETURNING *)) are always materialized because their side effects must execute exactly once.

What are common interview follow-up questions?

• What is the difference between a materialized CTE and a temporary table? (A CTE is statement-scoped; a temp table persists across statements.)
• Can you create an index on a materialized CTE? (No, it is a transient in-memory result; use a temp table if you need indexes.)
• Why does a recursive CTE always materialize? (Because it builds its result iteratively, each step referencing the prior one.)