A common table expression gives a query result a name within one SQL statement. It can make a complex report readable by naming populations and intermediate calculations before the final SELECT. That name is not a persistent table, and it does not by itself promise a cached result, a particular scan count, or a sequence of procedural steps.

Scope first, execution second

A WITH clause belongs to its statement. A later statement cannot reuse its CTE name, and there is no schema object to index or query after execution. Use a temporary table, view, or another appropriate database object if results or definitions must outlive that statement. A CTE is useful when its scope and its named result match the work being expressed.

In modern PostgreSQL, a nonrecursive SELECT CTE without volatile functions can be folded into its parent query. By default, this happens when it is referenced once. Folding lets outer restrictions participate in optimization, so an outer primary-key lookup can reach an index on the underlying table instead of filtering a separately produced intermediate result.

When the same eligible CTE is referenced more than once, PostgreSQL normally materializes it instead. That can avoid repeating expensive calculations, but it can also prevent a restrictive condition at one use from being pushed into the shared computation. This distinction has applied since PostgreSQL 12; assuming that every WITH is always an optimization fence describes older behavior, not the current default.

Materialization is a tradeoff

MATERIALIZED requests separate computation. NOT MATERIALIZED permits folding even with repeated references, potentially repeating work while allowing each use to access only the rows it needs. PostgreSQL ignores NOT MATERIALIZED for recursive or non-side-effect-free CTEs; it does not authorize duplicating arbitrary writes or volatile operations.

A materialized SELECT CTE is not necessarily evaluated to exhaustion. Its consumers may demand only part of its result, though sorts, joins, or other operations can require more. Materialization also does not create a reusable indexed table for later statements. Inspect the plan and measure the actual workload before forcing a boundary merely because the word sounds like a cache.

The useful comparison is total work: repeated selective index probes may be cheaper than building and scanning a large shared result, while repeating an expensive calculation may be worse. A readable name and an efficient execution plan are compatible, but neither implies the other. Keep the business predicate in one authoritative definition and choose execution controls only when the evidence supports them.

When it breaks

Data-modifying CTEs follow different rules. INSERT, UPDATE, or DELETE inside WITH executes once and to completion even if its output is not consumed. The named relation is the RETURNING output, not a freshly rescanned target table. A modifying CTE without RETURNING still performs its modification but cannot supply rows to a later reference.

The modifying substatements and main statement use the same snapshot, and their actual modification order is not a procedural top-to-bottom guarantee. Pass changed rows through RETURNING when another part of the statement needs them. Avoid attempting to modify the same row twice in one statement and expecting textual order to decide the winner. A plain SELECT-looking outer query does not make a data-modifying WITH read-only.

Check it quickly

Save this as cte.sql and run psql -X -v ON_ERROR_STOP=1 -f cte.sql in a disposable PostgreSQL 18 database. It compares four read plans, then moves two temporary rows using DELETE RETURNING and INSERT. The final rollback removes the fixture and reverses its changes.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE items (id integer PRIMARY KEY, value text);
INSERT INTO items SELECT g,'item-'||g FROM generate_series(1,10000) AS g;
ANALYZE items;
EXPLAIN (COSTS OFF)
WITH source AS (SELECT * FROM items) SELECT * FROM source WHERE id=7;
EXPLAIN (COSTS OFF)
WITH source AS MATERIALIZED (SELECT * FROM items) SELECT * FROM source WHERE id=7;
EXPLAIN (COSTS OFF)
WITH source AS (SELECT * FROM items)
SELECT a.id FROM source a JOIN source b USING(id) WHERE a.id=7;
EXPLAIN (COSTS OFF)
WITH source AS NOT MATERIALIZED (SELECT * FROM items)
SELECT a.id FROM source a JOIN source b USING(id) WHERE a.id=7;
CREATE TEMP TABLE moved (id integer,value text);
WITH removed AS (DELETE FROM items WHERE id<=2 RETURNING *)
INSERT INTO moved SELECT * FROM removed;
SELECT (SELECT count(*) FROM items) AS remaining,(SELECT count(*) FROM moved) AS moved;
DO $$ BEGIN
  IF (SELECT array_agg(id ORDER BY id) FROM moved) IS DISTINCT FROM ARRAY[1,2] THEN
    RAISE EXCEPTION 'RETURNING handoff failed';
  END IF;
  IF (SELECT count(*) FROM items) <> 9998 THEN RAISE EXCEPTION 'DELETE did not execute'; END IF;
END $$;
ROLLBACK;

On PostgreSQL 18.6, the single-reference default plan used items_pkey directly. Explicit MATERIALIZED introduced a CTE Scan over a planned sequential scan. The default double reference also shared a materialized CTE, while NOT MATERIALIZED produced two index-based accesses. These plain EXPLAIN statements show planned structure; they do not measure execution time or prove a production performance improvement.

The modifying statement actually executed and left 9,998 rows in items and two in moved. Assertions verify that the transferred identifiers are exactly one and two and that the source rows were deleted. RETURNING provides the handoff between operations. Changing the outer statement’s demand is not a safe method for suppressing a modifying CTE; keep such experiments transactional and inspect all WITH bodies before executing unfamiliar SQL.

Sources