CTE and Subquery Planning: Materialization, Pull-Up, and Optimization Fences #

A WITH clause and a nested SELECT look like the same thing to a reader and mean very different things to the planner: one may be flattened into the outer query and optimized as a single unit, while the other becomes a self-contained plan whose results are handed up a pipe. Knowing which happened — and being able to see it in the plan — is the difference between a query that runs in 20 ms and the same logic that runs in 20 seconds.

This page covers how PostgreSQL decides between inlining and materializing, how correlated and uncorrelated subqueries appear as SubPlan and InitPlan nodes, and how to read the plan tree that results. It builds directly on the operator-tree reading skills in the execution plan fundamentals section.

When the Planner Inlines a CTE and When It Fences #

Before PostgreSQL 12, every WITH clause was an unconditional optimization fence: it was planned and executed on its own, and no predicate from the outer query could reach inside it. That behavior is now conditional. A CTE is inlined — spliced into the outer query and planned as one problem — when all of these hold:

If any condition fails, the CTE is evaluated once into a tuplestore and every reference reads it back through a CTE Scan. That is the fence: no predicate pushdown across the boundary, no join reordering across it, and no index on the intermediate result.

Inlined CTE versus materialized CTE fence On the left, an inlined CTE produces a single plan tree where the outer filter reaches down to an index scan. On the right, a materialized CTE produces two separate stages: a sequential scan feeding a tuplestore, then a CTE Scan above it that applies the filter after the fact. Inlined (single reference) Fenced (MATERIALIZED / reused) Aggregate Nested Loop Index Scan on events Index Cond: outer filter pushed in one plan, one cost model, predicates flow down Aggregate CTE Scan on recent Filter: applied after the fence fence — nothing crosses Seq Scan on events two plans, materialized tuplestore between them same SQL text — the reference count and the MATERIALIZED keyword decide which tree you get

The practical rule: write the CTE for readability, then check the plan. If you see CTE Scan and did not want a fence, either reduce the query to a single reference or add NOT MATERIALIZED explicitly.

Reading CTE, SubPlan, and InitPlan Nodes #

Four markers tell you what the planner did with a nested construct. This annotated plan contains three of them:

Aggregate  (actual time=812.4..812.4 rows=1 loops=1)
  InitPlan 1 (returns $0)                          -- uncorrelated: evaluated ONCE
    ->  Result  (actual time=0.9..0.9 rows=1 loops=1)
  ->  CTE Scan on recent_orders  (actual rows=48120 loops=1)   -- the fence
        Filter: (total_cents > $0)
        Rows Removed by Filter: 951880              -- filter applied AFTER materialization
        CTE recent_orders
          ->  Seq Scan on orders  (actual rows=1000000 loops=1)
        SubPlan 2                                   -- correlated: per-row evaluation
          ->  Index Scan using refunds_order_id_idx on refunds
                Index Cond: (order_id = recent_orders.id)
                (actual time=0.01..0.01 rows=0 loops=48120)     -- loops = outer rows

A fourth marker, Recursive Union, appears under WITH RECURSIVE and always implies a fence; it has a non-recursive seed branch and a recursive branch that runs until the working table is empty.

Algorithm Internals: Pull-Up, Flattening, and Semi Joins #

Two distinct transformations run before costing begins, and both remove nodes from the tree you would otherwise see.

Subquery pull-up flattens a FROM (SELECT …) sub into the parent query. It applies when the subquery has no LIMIT, GROUP BY, DISTINCT, set operation, window function, or volatile target list. After pull-up, the subquery’s tables join the outer join-order search, so the planner is free to choose a hash join or reorder them entirely.

Sublink pull-up converts IN, EXISTS, NOT EXISTS, and ANY sublinks in WHERE into semi joins and anti joins. This is the single most valuable rewrite the planner performs: it turns a per-row SubPlan into a set-based Hash Semi Join costed like any other join.

-- BEFORE: sublink not flattened (it sits under an OR), evaluated per row
Seq Scan on customers c  (actual rows=200000 loops=1)
  Filter: (vip OR (SubPlan 1))
  SubPlan 1
    ->  Index Scan using orders_customer_id_idx on orders  (loops=200000)
Execution Time: 4210.6 ms

-- AFTER: rewritten so the sublink stands alone → planner converts it to a semi join
Hash Semi Join  (actual rows=61240 loops=1)
  Hash Cond: (c.id = o.customer_id)
  ->  Seq Scan on customers c  (actual rows=200000 loops=1)
  ->  Hash  (actual rows=488102 loops=1)
Execution Time: 218.3 ms

Nothing about the data changed. Moving the sublink out from under the OR made it flattenable, and a 200,000-iteration SubPlan became one hash build. When you cannot restructure the boolean logic, a UNION of two flattenable branches often achieves the same effect.

From per-row SubPlan to set-based semi join Top row shows an outer scan with an arrow looping back into a subquery once per row, labelled loops equals two hundred thousand. Bottom row shows the rewritten form: two scans feeding a hash semi join executed once. Not flattened — SubPlan per outer row Seq Scan customers SubPlan → Index Scan repeat: loops = 200,000 4210 ms Flattened — semi join executed once Seq Scan customers Hash on orders Hash Semi Join 218 ms

Memory, I/O, and Cost Behavior of a Materialized CTE #

A fenced CTE writes its entire result into a tuplestore. While it fits in work_mem the tuplestore lives in memory; beyond that it spills to a temporary file in base/pgsql_tmp and every reference reads it back from disk. EXPLAIN (ANALYZE, BUFFERS) exposes this as temp read= and temp written= blocks on the CTE Scan.

That gives materialization a clear cost profile:

Because the tuplestore competes for the same work_mem as the sorts and hashes around it, a materialized CTE in a plan that also spills is often the cheapest thing to remove first.

Step-by-Step Workflow for a Slow CTE Query #

  1. Identify what the planner actually did.

    EXPLAIN (ANALYZE, BUFFERS)
WITH recent AS (SELECT * FROM orders WHERE created_at > now() - interval '30 days')
SELECT count(*) FROM recent WHERE total_cents > 10000;

    Look for CTE Scan. If it is absent, the CTE was inlined and there is no fence to remove.

  2. Check whether a fence is costing you a pushdown. A large Rows Removed by Filter on the CTE Scan means the outer predicate ran too late.

  3. Test the inlined form explicitly rather than guessing:

    WITH recent AS NOT MATERIALIZED (SELECT * FROM orders WHERE created_at > now() - interval '30 days')
SELECT count(*) FROM recent WHERE total_cents > 10000;

  4. Count the references. If the CTE is used twice, inlining recomputes it twice. Compare the total cost of two inlined evaluations against one materialization plus tuplestore I/O before choosing.

  5. Attack SubPlan nodes by making them flattenable. Rewrite NOT IN (SELECT …) — which is not flattenable when the column is nullable — as NOT EXISTS, and lift sublinks out from under OR so the planner can build a semi join.

  6. Re-measure with BUFFERS. Confirm both that execution time fell and that temp written disappeared; a faster plan that still writes temporary files will regress under concurrency.

Common Pitfalls #

Assuming CTEs are still always a fence. Advice written before PostgreSQL 12 says a WITH clause always materializes. Diagnostic signal: no CTE Scan in the plan at all. Fix: read the plan for the version you actually run; add MATERIALIZED when you truly want the fence.

Using a CTE to “cache” a result that is referenced once. A single-reference CTE is inlined, so it caches nothing — but adding MATERIALIZED to force caching also blocks predicate pushdown. Diagnostic signal: a CTE Scan with a huge actual row count feeding a highly selective filter. Fix: remove the keyword and let the predicate reach the scan.

NOT IN against a nullable column. NOT IN cannot be converted to an anti join when the subquery column may be NULL, so it degrades to a per-row SubPlan. Diagnostic signal: SubPlan with a high loops count and a NOT (hashed SubPlan) label. Fix: use NOT EXISTS, or add NOT NULL to the column.

Correlated subqueries in the target list. A scalar subquery in SELECT is evaluated per output row and cannot be flattened. Diagnostic signal: SubPlan attached to a Result or scan node with loops equal to the output rows. Fix: rewrite as a LEFT JOIN LATERAL so the planner can choose a join method.

Recursive CTEs without a depth guard. WITH RECURSIVE over cyclic data runs until it exhausts memory or disk. Diagnostic signal: a Recursive Union whose actual rows dwarf any sane estimate. Fix: carry a depth column and bound it, or track visited keys in an array.

Trusting the row estimate above a CTE Scan. The planner has no statistics for a materialized intermediate, so joins above it are planned on guesses. Diagnostic signal: a nested loop chosen above a CTE Scan whose actual rows are thousands of times the estimate. Fix: inline the CTE so real table statistics apply, or materialize into a temporary table and ANALYZE it.

Chaining CTEs into a pipeline. A sequence of WITH a AS (…), b AS (SELECT … FROM a), c AS (SELECT … FROM b) reads beautifully and, when each step is referenced once, inlines into a single plan the optimizer can rearrange freely. The moment one step is referenced twice, that step fences and everything above it is planned against a statistics-free tuplestore. Diagnostic signal: one CTE Scan partway up a chain of otherwise inlined steps. Fix: either give the reused step its own materialisation deliberately, or duplicate the expression so each reference inlines.

Using a recursive CTE where a plain join would do. WITH RECURSIVE is the right tool for genuine graph traversal and hierarchy walking, and the wrong one for a fixed number of levels. Two or three self-joins are costed as ordinary joins with real statistics, while a recursive union is a fence with a working table and no distribution information at all. Diagnostic signal: a Recursive Union whose recursive branch runs a small, fixed number of iterations. Fix: unroll the levels into explicit joins and let the planner cost them.

Expecting a data-modifying CTE to see its own changes. WITH inserted AS (INSERT … RETURNING *) SELECT … FROM other_table runs both parts against the same snapshot, so the outer query does not observe the rows the CTE inserted except through the RETURNING set itself. This is a correctness trap rather than a performance one, but it surfaces as a plan question because the CTE is always materialised. Diagnostic signal: a query that returns fewer rows than the writer expects. Fix: read from the RETURNING output rather than re-querying the modified table.

Marker to meaning Four plan markers with their meanings: CTE Scan is a fence, InitPlan runs once, SubPlan runs per outer row, and Recursive Union is always a fence with a working table. CTE Scan materialised tuplestore — no pushdown crosses it InitPlan N uncorrelated — evaluated once, substituted as $N SubPlan N correlated — re-run per outer row, watch loops Recursive Union always a fence, with a working table per iteration

Frequently Asked Questions #

Are CTEs still an optimization fence in PostgreSQL? Only conditionally. Since PostgreSQL 12, a non-recursive, side-effect-free CTE referenced exactly once is inlined and planned together with the outer query. A CTE that is referenced more than once, marked MATERIALIZED, recursive, or data-modifying keeps the old fence behavior and appears as a CTE Scan.

What is the difference between a SubPlan and an InitPlan? An InitPlan does not depend on the outer row, so it is evaluated once and its result substituted as a parameter such as $0. A SubPlan is correlated and re-evaluated for each outer row, which the loops counter makes visible; a large loops value is the classic quadratic-cost signature.

Why did my subquery vanish from the plan? Because it was pulled up. A flattenable FROM subquery merges into the outer join tree and an IN/EXISTS sublink becomes a semi join, so there is no separate node left to display. That is normally the fastest outcome, since the join method then becomes a costed choice.

Does NOT MATERIALIZED guarantee inlining? It requests it, and the planner honours the request for any CTE that is legally inlinable. A recursive CTE or one containing a data-modifying statement cannot be inlined regardless of the hint.