CTE Scan

A CTE Scan reads rows from a common table expression that was materialized (computed once into a tuplestore) rather than inlined. Each scan re-reads that stored result.

For CTEs marked AS MATERIALIZED, for WITH RECURSIVE, and (pre-PostgreSQL 12) for all CTEs.

A materialized CTE is a fence: it's computed once and can't be optimized with the surrounding query. If it's scanned many times, or could be inlined, that's often a tuning opportunity (AS NOT MATERIALIZED, or a temp table you can index).

The two CTE worlds

Common Table Expressions (WITH x AS (…)) come in two flavors in modern PostgreSQL:

1. Inlined CTEs — the planner treats them as ordinary subqueries and folds them into the outer query. No CTE Scan node appears at all. This is the default since PostgreSQL 12 for non-recursive, single-use CTEs.
2. Materialized CTEs — explicitly AS MATERIALIZED, or implicitly when the CTE is recursive or referenced multiple times. The CTE result is computed once into a tuplestore, and the rest of the query reads it through a CTE Scan node.

CTE Scan is a sign you're in world #2.

Why it can be slow

The CTE result lives in a tuplestore — flat memory or a temp file. The CTE Scan walks it linearly each time it's read. There is no index on a CTE; if the outer query needs to look up specific rows, the planner can do a nested loop that re-reads the whole CTE for every outer row. Our analyzer flags this with "CTE … rescanned N times."

If you see it:

• Remove the fence by using AS NOT MATERIALIZED (PG 12+) — lets the planner inline.

• Materialize to a temp table instead, and index it:

  CREATE TEMP TABLE c_tmp AS SELECT id, … FROM bigtable WHERE …;
  CREATE INDEX ON c_tmp (id);
  -- then use c_tmp in the rest of the query
  

When MATERIALIZED is still right

Sometimes you genuinely want a fence — to compute something once, avoid repeated side-effects in a function, or guarantee row counts for the optimizer. In those cases the cost of CTE Scan is the price, and a temp table is the cleanest escape if it becomes a bottleneck.

• CTE rescanned — a materialized CTE is read many times

Paste a plan into the analyzer →

Are CTEs an optimization fence in PostgreSQL?

Since PostgreSQL 12, simple CTEs are inlined by default and are not a fence. They remain materialized — a fence — when you write AS MATERIALIZED, when they're recursive, or when they're referenced multiple times with side effects.

• Subquery Scan
• Recursive Union
• WorkTable Scan
• Materialize