MATERIALIZED vs NOT MATERIALIZED CTEs #

Since PostgreSQL 12 a WITH clause is a hint-free construct with one explicit override: you can force the old fence behavior with MATERIALIZED, or demand inlining with NOT MATERIALIZED. Choosing correctly is a two-variable problem — how many times the CTE is referenced, and how much a pushed-down predicate would have saved.

The planning rules behind the default are described in CTE and subquery planning; this page is the decision procedure.

The Condition That Decides It #

The default rule is mechanical: a CTE is inlined when it is referenced exactly once, is not recursive, contains no data-modifying statement, and calls no volatile function. Everything else materializes.

The override changes the trade you make:

The break-even therefore depends on selectivity. If the outer predicate discards 95% of the CTE’s rows, inlining wins even at three references. If the CTE is a heavy aggregation whose full result is needed each time, materializing wins at two.

Choosing the CTE mode from references and selectivity A two by two grid. One reference with a selective outer filter or without one both favour inlining. Multiple references with a selective filter is the ambiguous case that must be measured. Multiple references with no useful filter favours materializing. references outer filter selectivity → many one low high MATERIALIZED compute once, reuse the tuplestore measure both pushdown savings vs re-evaluation default (inlined) nothing to reuse anyway NOT MATERIALIZED let the predicate reach the index

Annotated EXPLAIN Evidence #

The clearest signal of a fence costing you money is a large Rows Removed by Filter on the CTE Scan:

WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE occurred_at > now() - interval '90 days'
)
SELECT count(*) FROM recent WHERE account_id = 4471;

Aggregate  (actual time=2841.6..2841.6 rows=1 loops=1)
  ->  CTE Scan on recent  (actual time=0.05..2836.1 rows=112 loops=1)
        Filter: (account_id = 4471)
        Rows Removed by Filter: 6041188          -- 6M rows materialized, 112 kept
        CTE recent
          ->  Seq Scan on events  (actual rows=6041300 loops=1)
              Buffers: shared read=91240, temp written=48210   -- tuplestore spilled
Execution Time: 2892.4 ms

Six million rows were written into a tuplestore — spilling to temporary files along the way — so that 112 could be counted. Remove the keyword and the predicate reaches the table:

WITH recent AS NOT MATERIALIZED (…same body…)
SELECT count(*) FROM recent WHERE account_id = 4471;

Aggregate  (actual time=0.42..0.42 rows=1 loops=1)
  ->  Index Scan using events_account_occurred_idx on events  (actual rows=112 loops=1)
        Index Cond: (account_id = 4471 AND occurred_at > now() - '90 days'::interval)
Execution Time: 0.47 ms

The CTE Scan is gone entirely, and both predicates became an Index Cond. This is the pushdown that a fence blocks.

The opposite case is just as real. Here the CTE is expensive and used three times:

-- NOT MATERIALIZED: the aggregation runs three times
->  HashAggregate  (actual rows=118422 loops=1)   -- occurrence 1
->  HashAggregate  (actual rows=118422 loops=1)   -- occurrence 2
->  HashAggregate  (actual rows=118422 loops=1)   -- occurrence 3
Execution Time: 4310.9 ms

-- MATERIALIZED: computed once, read three times from the tuplestore
CTE totals
  ->  HashAggregate  (actual rows=118422 loops=1)
->  CTE Scan on totals  (actual rows=118422 loops=3)
Execution Time: 1602.7 ms

Step-by-Step Decision Workflow #

  1. Count the references in the statement text. One reference means the keyword only matters if you are trying to stop inlining.

  2. Plan it both ways in the same session so buffer states are comparable:

    EXPLAIN (ANALYZE, BUFFERS) WITH x AS MATERIALIZED (…) SELECT … ;
EXPLAIN (ANALYZE, BUFFERS) WITH x AS NOT MATERIALIZED (…) SELECT … ;

  3. On the materialized plan, read two fields: Rows Removed by Filter on the CTE Scan (lost pushdown) and temp written in the buffers line (tuplestore spill). Either being large argues for inlining.

  4. On the inlined plan, count repeated subtrees. The same expensive node appearing once per reference is the cost of inlining.

  5. Watch the join estimates above a CTE Scan. The planner has no statistics for a tuplestore, so a materialized CTE feeding a join often produces the row-estimate errors described in spotting row estimate explosions.

  6. Choose, then pin the choice explicitly. Writing the keyword you want — either one — protects the query from behaving differently after a version upgrade or a refactor that adds a second reference.

Before and After #

-- BEFORE: MATERIALIZED, one reference, highly selective outer filter
CTE Scan on recent  (actual rows=112)  Rows Removed by Filter: 6041188  temp written=48210
Execution Time: 2892.4 ms

-- AFTER: NOT MATERIALIZED, predicate reaches the index
Index Scan using events_account_occurred_idx  (actual rows=112)  Buffers: shared hit=6
Execution Time: 0.47 ms

The metric that moved is not time alone but rows materialised: 6,041,300 down to 112, with the temporary write disappearing entirely.

Rows moved with and without the fence The fenced flow reads six million rows into a tuplestore and then filters them down to one hundred and twelve. The inlined flow applies the predicate at the index and reads only one hundred and twelve rows in total. MATERIALIZED — filter applied after the fence Seq Scan 6,041,300 rows tuplestore + temp files Filter → 112 rows 2892 ms NOT MATERIALIZED — predicate becomes an Index Cond Index Scan (account_id, occurred_at) 112 rows 0.47 ms no tuplestore, no temp I/O Where re-evaluation overtakes materialisation Two lines against the number of CTE references. Inlined cost rises step by step with each reference, while materialised cost stays flat after the first evaluation. They cross between the second and third reference. references to the CTE → inlined — one evaluation per reference MATERIALIZED — one evaluation, N cheap reads break-even 1 2 3 4

Common Pitfalls #

Adding MATERIALIZED to “cache” a single-reference CTE. There is nothing to reuse, and you lose every pushdown. Diagnostic signal: a CTE Scan with loops=1 and a large Rows Removed by Filter. Fix: drop the keyword.

Assuming a tuplestore is free. A materialized CTE larger than work_mem writes and re-reads temporary files, competing with the sorts and hashes in the same plan. Diagnostic signal: temp written in the BUFFERS output. Fix: inline, or reduce the CTE’s output before materialising.

Relying on the default after a refactor. Adding a second reference silently flips a previously inlined CTE into a fence, changing the plan without any change to the CTE body. Diagnostic signal: an unexplained CTE Scan appearing in a plan that never had one. Fix: state the keyword explicitly.

Using a CTE to force join order. It works, but it also blocks statistics and pushdown, so the “stable” plan may be stably bad. Diagnostic signal: a nested loop above a CTE Scan with an enormous estimate error. Fix: fix the estimates instead — CREATE STATISTICS or a corrected ANALYZE usually removes the temptation.

Frequently Asked Questions #

When is MATERIALIZED the right choice? When the CTE is genuinely expensive, is referenced more than once, and the outer query has no selective predicate that inlining would push down. It is also correct for CTEs containing volatile or costly function calls you want evaluated exactly once.

Does NOT MATERIALIZED guarantee inlining? It requests it, and the planner complies whenever inlining is legal. Recursive CTEs and CTEs containing data-modifying statements cannot be inlined at all, so the keyword is ignored for them.

How do I tell from the plan which one happened? A CTE Scan node means the CTE was evaluated into a tuplestore. If the CTE’s name never appears as a node and its base tables show up directly in the plan tree, it was inlined.