skip to content

In PostgreSQL, when is a WITH query folded into the outer query instead of materialized, and what do MATERIALIZED and NOT MATERIALIZED change?

level: seniorimportance: should knowfreq 55%

answer

  1. not always an optimization fence
  2. referenced once, plain SELECT
  3. volatile functions block folding
  4. CTE Scan in the plan
  5. keywords override the default

basics

~10 s

A non-recursive, side-effect-free WITH query referenced once is folded into the outer query by default; referenced twice it is computed once and stored. MATERIALIZED forces storing, NOT MATERIALIZED forces folding where allowed.

solid answer

~50 s

PostgreSQL folds a `WITH` query into the outer query, like a subquery in `FROM`, when it is non-recursive, a plain `SELECT` with no volatile functions, and referenced **once**; then outer `WHERE` conditions reach its tables and indexes. Referenced more than once, it is evaluated once and its result stored, shown as a `CTE Scan` in the plan, and outer filters cannot reach inside. `MATERIALIZED` forces that separate evaluation even for one reference, which is useful to compute an expensive function once or to stop the planner from merging levels. `NOT MATERIALIZED` forces folding even for several references, at the risk of duplicate work, and is ignored for recursive CTEs, data-modifying ones and those calling volatile functions such as `random()`. Advice that every CTE is an optimization fence dates from before PostgreSQL 12. Since 17 the planner also uses a materialized CTE's column statistics and sort order.

code

sql · 13 lines
sql
WITH latest AS MATERIALIZED (
  SELECT DISTINCT ON (symbol) symbol, price, traded_at
  FROM ticks
  ORDER BY symbol, traded_at DESC, id DESC
)
SELECT * FROM latest WHERE symbol = 'ACME';

WITH latest AS NOT MATERIALIZED (
  SELECT DISTINCT ON (symbol) symbol, price, traded_at
  FROM ticks
  ORDER BY symbol, traded_at DESC, id DESC
)
SELECT * FROM latest WHERE symbol = 'ACME';

go deeper

for a junior

Know that PostgreSQL can either merge a WITH query into the outer query or compute it once and store it, and that the keywords MATERIALIZED and NOT MATERIALIZED choose explicitly.

for a middle

List the four default conditions for folding and explain what a CTE Scan node in EXPLAIN tells you about a filter written outside the CTE.

for a senior

Show you can fix a slow report by choosing the keyword: push filters into a folded CTE, or force materialization to compute an expensive expression once, and verify with EXPLAIN ANALYZE.

for a principal

Set a team convention: write MATERIALIZED where single evaluation is a requirement rather than a hope, so behaviour does not depend on how many times someone later references the CTE.

## Folding versus materializing A **common table expression** (CTE) is a named subquery in a `WITH` clause. PostgreSQL can plan it in one of two ways: - **Folded (inlined):** the CTE is substituted into the outer query as if it were a subquery in `FROM`. The planner optimizes both levels together, so a condition in the outer `WHERE` can be applied to the CTE's base tables and their indexes. - **Materialized:** the CTE is planned and run separately, once per execution of the outer query, and its rows are kept for every reference. In `EXPLAIN` this appears as a `CTE Scan` over the CTE's own subplan. Outer conditions are applied only after the full result has been produced. ## The default rule The planner folds a CTE automatically only when **all** of these hold: 1. It is **not recursive**. 2. It is a plain `SELECT`: no `INSERT`, `UPDATE`, `DELETE` or `MERGE` anywhere inside. 3. It calls **no volatile functions** (`random()`, `clock_timestamp()`, `nextval()`); stable functions like `now()` do not block folding. 4. The outer query references it **exactly once**. A plain `SELECT` CTE that is never referenced is skipped entirely. A data-modifying CTE always runs **exactly once**, whether or not its output is read. ## What the keywords override | Written as | One reference | Two or more references | |---|---|---| | `WITH w AS (...)` | Folded if conditions 1-3 hold | Materialized | | `WITH w AS MATERIALIZED (...)` | Materialized | Materialized | | `WITH w AS NOT MATERIALIZED (...)` | Folded if conditions 1-3 hold | Folded if conditions 1-3 hold; work may repeat | `NOT MATERIALIZED` is **ignored**, not rejected, on a recursive or side-effect-full CTE, which is why adding it sometimes changes nothing. When to use each: - **`MATERIALIZED`** when an expensive expression must be computed once per row and shared, when a folded plan is worse because the planner misjudges the combined query, or when you want to guarantee single evaluation. - **`NOT MATERIALIZED`** when a CTE is referenced twice but each use needs only a few rows, so pushing each use's filter into the base table beats building a full temporary copy. ## Pushdown into a folded CTE: a ticker example ```sql WITH latest AS ( SELECT DISTINCT ON (symbol) symbol, price, traded_at FROM ticks ORDER BY symbol, traded_at DESC, id DESC ) SELECT * FROM latest WHERE symbol = 'ACME'; ``` The CTE is referenced once and is a plain `SELECT`, so it is folded. The planner then pushes `symbol = 'ACME'` into the subquery because it references only the `DISTINCT ON` column, and filtering whole groups cannot change which row survives. With an index on `(symbol, traded_at DESC)` the query touches one symbol's entries. A filter such as `WHERE price > 100` is **not** pushed down, because applying it before `DISTINCT ON` could promote an older tick to survivor. Write the same CTE `AS MATERIALIZED` and the plan computes the latest row for **every** symbol, then filters to `ACME` in the `CTE Scan`. ## Reading it in EXPLAIN - A **`CTE Scan on latest`** node means the CTE was materialized. - No CTE node, with the base table's scan showing the pushed-down condition, means it was folded. - Since PostgreSQL 17 the planner carries a materialized CTE's column statistics and sort order into the outer query, so estimates above a `CTE Scan` are less blind than in older releases. ## Common mistakes - **Assuming the fence.** Writing a CTE to stop the planner from merging levels, without the `MATERIALIZED` keyword, silently stops working when the CTE is referenced once. - **Assuming folding.** Referencing a CTE a second time, for example to add a count, switches it to materialized and can turn an index lookup into a full computation. - **Expecting `NOT MATERIALIZED` to override volatility.** A CTE that calls `clock_timestamp()` or `nextval()` stays materialized whatever you write. - **Expecting a skipped data-modifying CTE.** An `INSERT ... RETURNING` in `WITH` runs once even if nothing reads its output; writable CTEs are a topic of their own. ## Stale advice Before PostgreSQL 12, every CTE was materialized, and "CTEs are an optimization fence" was true; people wrote CTEs on purpose to stop the planner from merging levels. Since 12 that only holds for the cases above. Code relying on the old fence should say `MATERIALIZED` explicitly, and code that avoided CTEs for performance can usually use them freely. The neutral question of when to choose a CTE, a derived table or a view belongs to the SQL language topic; this answer is about how PostgreSQL plans the one you wrote.

  • Why might adding NOT MATERIALIZED to a CTE change nothing in the plan?
    PostgreSQL ignores `NOT MATERIALIZED` when the CTE is recursive, contains `INSERT`, `UPDATE`, `DELETE` or `MERGE`, or calls a volatile function such as `random()` or `clock_timestamp()`. Folding those could change how often side effects happen, so the CTE stays materialized.
  • A report CTE calls an expensive function per row and is joined to itself. Which keyword fits?
    Keep it materialized, which is already the default for two references, and write `MATERIALIZED` to make the intent survive edits. Folding would evaluate the function once per reference, doubling the expensive work.

A materialized CTE is a printed report: produced once, shared by every reader, but nobody can ask it for one page only. A folded CTE is a saved search: re-run where used, so each reader adds their own filter.

  • Every CTE in PostgreSQL is an optimization fence that the planner cannot see through.
  • NOT MATERIALIZED folds any CTE, including ones that call random() or modify rows.
  • A CTE referenced twice is folded and run twice by default.
  • A data-modifying CTE is skipped if the outer query never reads its output.
  • now() is volatile, so a CTE using it is always materialized.

Assumes PostgreSQL 18; CTE folding exists since 12, and since 17 the planner uses a materialized CTE's column statistics and sort order in outer estimates.