PostgreSQL CTE Materialization Controls Planner Boundaries

A PostgreSQL common table expression can either become part of the surrounding query plan or remain a separately computed result. That distinction changes more than plan shape. It controls whether restrictions can move across the CTE boundary and whether repeated references can cause repeated computation.

Since PostgreSQL 12, a non-recursive, side-effect-free CTE is eligible for folding into its parent query. PostgreSQL normally folds such a CTE when the parent references it once. Multiple references normally lead to materialization instead. MATERIALIZED and NOT MATERIALIZED make that boundary explicit when the default does not fit the query.

Folding lets the planner optimize across the CTE

Consider a CTE that selects from a large relation while the outer query applies a selective predicate:

WITH candidate_orders AS (
    SELECT order_id, account_id, status
    FROM orders
)
SELECT order_id
FROM candidate_orders
WHERE account_id = 42017;

When the CTE is eligible for folding and referenced once, PostgreSQL can plan this much like a single query over orders. The account_id condition is visible while access paths are selected. An index on account_id, if suitable according to the planner’s cost model, can participate directly.

The CTE still provides a useful name in the SQL text, but it does not necessarily correspond to a separate execution stage. Query syntax and execution boundaries are distinct concepts.

This matters when a CTE is used mainly to organize a large statement. A side-effect-free CTE does not automatically impose a materialization cost in current PostgreSQL releases.

Materialization creates a real optimization boundary

Adding MATERIALIZED changes the planner’s freedom:

WITH candidate_orders AS MATERIALIZED (
    SELECT order_id, account_id, status
    FROM orders
)
SELECT order_id
FROM candidate_orders
WHERE account_id = 42017;

PostgreSQL computes the CTE as a separate result and then scans that result for the outer predicate. The surrounding query cannot push account_id = 42017 into the CTE scan of orders.

That boundary can increase work when the CTE produces many rows and the parent consumes only a small subset. It can also be intentional. A separately evaluated CTE prevents the parent query from reshaping that part of the plan through folding.

Materialization is therefore not just a storage detail. It determines which query levels the planner can optimize together.

Repeated references change the default

A CTE referenced more than once is normally materialized. One computation can then feed several consumers:

WITH active_accounts AS (
    SELECT account_id, region
    FROM accounts
    WHERE state = 'active'
)
SELECT a.account_id
FROM active_accounts AS a
JOIN active_accounts AS b
  ON b.region = a.region
WHERE a.account_id = 42017;

The default avoids executing the CTE independently for each reference. The cost is that restrictions attached to individual consumers cannot necessarily reach the base relation.

NOT MATERIALIZED permits folding even with repeated references:

WITH active_accounts AS NOT MATERIALIZED (
    SELECT account_id, region
    FROM accounts
    WHERE state = 'active'
)
SELECT a.account_id
FROM active_accounts AS a
JOIN active_accounts AS b
  ON b.region = a.region
WHERE a.account_id = 42017;

Now each reference can be optimized in the context where it appears. The selective condition on a.account_id can affect that branch directly. The other reference can receive a different access path based on its own conditions.

The price is possible duplicate computation. Folding is attractive when each consumer needs a narrow portion of the CTE output. Materialization can be preferable when producing the CTE result is costly and several consumers need much of the same result.

Expensive expressions can favor one-time evaluation

The distinction becomes clearer when the CTE contains computation rather than a simple projection:

WITH normalized AS MATERIALIZED (
    SELECT
        item_id,
        normalize_payload(payload) AS normalized_payload
    FROM event_items
)
SELECT x.item_id
FROM normalized AS x
JOIN normalized AS y
  ON y.normalized_payload = x.normalized_payload;

If normalize_payload is an expensive function, materializing the CTE can ensure that its result is computed once per source row and reused by both references. Forcing NOT MATERIALIZED can cause the expression to be evaluated separately in each folded branch.

This is a plan-level trade rather than a universal preference. Predicate pushdown can save substantial base-table work in one query, while avoiding repeated expression evaluation can dominate another. EXPLAIN (ANALYZE, BUFFERS) can expose the resulting execution counts, row counts, and access paths for the actual statement.

Volatile and recursive CTEs have stricter semantics

Planner folding is limited to CTEs for which merging is semantically safe. A non-recursive CTE must be side-effect-free for folding to apply. In this context, that means a plain SELECT without volatile functions.

A volatile function can produce effects or values that depend on evaluation count. PostgreSQL cannot freely duplicate or relocate that computation while preserving the same semantics. NOT MATERIALIZED does not turn such a CTE into an ordinary foldable subquery.

Recursive CTEs also retain their own evaluation model. Their working-table process depends on repeated evaluation of the recursive term, so the ordinary folding rules for non-recursive CTEs do not apply.

Data-modifying CTEs have execution semantics of their own as well. They are not a target for using NOT MATERIALIZED as a planner hint.

MATERIALIZED can act as an optimization fence

MATERIALIZED is occasionally useful when a query needs a deliberate planner boundary. It can prevent conditions or join decisions in the parent query from being combined with the CTE.

That use deserves restraint. An optimization fence can block a poor transformation, but it also blocks beneficial ones. It may preserve extra scans, larger intermediate results, or lost index opportunities as data distribution and planner behavior change.

A CTE marked MATERIALIZED should therefore express an execution constraint that is understood, not merely preserve a plan observed at one moment. PostgreSQL’s cost estimates, statistics, indexes, and planner implementation can all alter the surrounding plan over time.

Plan inspection reveals the actual boundary

SQL text alone does not show whether an eligible CTE was folded. EXPLAIN exposes the distinction.

A materialized CTE commonly appears with a named CTE plan and one or more CTE Scan nodes. A folded CTE disappears as a separate execution object; its base relations appear directly in the surrounding plan.

That difference is useful when reviewing a query whose CTE syntax looks harmless but whose runtime work is unexpectedly large. The relevant question is not whether the statement contains WITH. The relevant boundary is whether PostgreSQL computed the CTE separately or merged it into the parent plan.

CTE materialization is best treated as control over planner scope. Folding gives the optimizer a wider region in which to push restrictions and choose access paths. Materialization narrows that region while preserving a reusable, separately evaluated result. Explicit clauses are most useful when that execution boundary is part of the query’s intended behavior rather than an incidental formatting choice.