In PostgreSQL, when is a WITH query folded into the outer query instead of materialized, and what do MATERIALIZED and NOT MATERIALIZED change?
answer
- not always an optimization fence
- referenced once, plain SELECT
- volatile functions block folding
- CTE Scan in the plan
- keywords override the default
basics
~10 sA 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 sPostgreSQL 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 linesWITH 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
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.
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.
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.
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.