Subquery Flattening and Pull-Up Rules #

The fastest subquery is the one that stops being a subquery. Before costing anything, PostgreSQL tries to rewrite nested constructs into ordinary joins — a transformation that turns a per-row loop into a set operation the planner can cost, reorder, and parallelise. When it succeeds, the subquery vanishes from the plan. When it fails, you get a SubPlan whose loops counter tracks the outer row count.

This page lists the rules precisely, so you can look at a query and predict which outcome you will get. The surrounding context is in CTE and subquery planning.

The Two Transformations and Their Conditions #

Subquery pull-up applies to a subquery in FROM. Its tables are merged into the outer join tree, after which they participate in join-order search like any other relation. It is blocked by anything that gives the subquery its own row-set identity:

Sublink pull-up applies to IN, NOT IN, EXISTS, NOT EXISTS, and = ANY in WHERE. It converts them into semi joins and anti joins. It is blocked by:

What blocks each transformation Two parallel tracks. The FROM subquery track lists LIMIT, GROUP BY, DISTINCT, set operations and window functions as blockers, otherwise the subquery is merged into the join tree. The sublink track lists OR context, nullable NOT IN and target-list position as blockers, otherwise it becomes a semi or anti join. FROM (SELECT …) sub WHERE … IN / EXISTS (…) blocked by LIMIT / OFFSET GROUP BY / aggregates DISTINCT UNION / INTERSECT / EXCEPT window functions otherwise: tables join the outer join tree blocked by sublink under an OR NOT IN on a nullable column sublink in the target list correlation above the join level (volatile predicates too) otherwise: Semi Join or Anti Join

Annotated Evidence: Blocked and Unblocked #

The NOT IN case is the most expensive one in practice, because it looks harmless:

-- customers.id is NOT NULL, but orders.customer_id is nullable
EXPLAIN (ANALYZE)
SELECT * FROM customers c WHERE c.id NOT IN (SELECT customer_id FROM orders);

Seq Scan on customers c  (actual time=0.03..5210.4 rows=81578 loops=1)
  Filter: (NOT (hashed SubPlan 1))            -- cannot be an anti join
  SubPlan 1
    ->  Seq Scan on orders  (actual rows=8000000 loops=1)
Execution Time: 5288.1 ms

Rewritten as NOT EXISTS, the planner is free to use an anti join:

EXPLAIN (ANALYZE)
SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Hash Anti Join  (actual time=412.7..640.2 rows=81578 loops=1)
  Hash Cond: (c.id = o.customer_id)
  ->  Seq Scan on customers c  (actual rows=200000 loops=1)
  ->  Hash  (actual rows=8000000 loops=1)  Batches: 4
Execution Time: 688.5 ms

Same answer, 7.7× faster, and now the join method is a costed choice that can use a hash join or a merge join as the data warrants.

The OR blocker is subtler:

-- Blocked: the sublink is one branch of an OR
Seq Scan on customers c  (actual rows=94120 loops=1)
  Filter: (c.vip OR (SubPlan 1))
  SubPlan 1
    ->  Index Scan using orders_customer_id_idx on orders  (loops=200000)   -- per row
Execution Time: 4210.6 ms

-- Unblocked by splitting into a UNION of two flattenable branches
Unique  (actual rows=94120 loops=1)
  ->  Sort  (actual rows=98442 loops=1)
        ->  Append
              ->  Seq Scan on customers  (Filter: vip)
              ->  Hash Semi Join  (Hash Cond: (c.id = o.customer_id))
Execution Time: 302.9 ms

Step-by-Step Resolution Workflow #

  1. Find the un-flattened construct. Search the plan for SubPlan, Subquery Scan, and hashed SubPlan. A loops value equal to the outer row count confirms per-row evaluation.

  2. Identify which rule blocked it using the lists above. In production code the blocker is almost always NOT IN on a nullable column, a sublink under OR, or a LIMIT inside a FROM subquery.

  3. Apply the matching rewrite:

    -- nullable NOT IN → anti join
SELECT * FROM customers c
 WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- or make the column provably non-null so NOT IN can be converted
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;

  4. Split OR-guarded sublinks into a UNION, letting each branch flatten independently. Use UNION rather than UNION ALL only if duplicates are possible.

  5. Move a scalar sublink out of the target list into a LEFT JOIN LATERAL, which keeps the per-row semantics but lets the planner cost the join — see LATERAL join plan shapes.

  6. Confirm the transformation happened. The plan should now contain Semi Join, Anti Join, or the base tables directly, and the SubPlan should be gone.

SubPlan replaced by an anti join Left tree: a sequential scan with a filter that calls a SubPlan evaluated per row. Right tree: a hash anti join over two scans, executed once, with the resulting execution times shown beneath each tree. NOT IN — per-row SubPlan NOT EXISTS — anti join Seq Scan customers SubPlan · loops = 200,000 5288 ms Hash Anti Join Seq Scan customers Hash on orders 688 ms

Before and After #

-- BEFORE
Filter: (NOT (hashed SubPlan 1))   SubPlan scan rows=8,000,000   Execution Time: 5288.1 ms
-- AFTER
Hash Anti Join  Hash Cond: (c.id = o.customer_id)  Batches: 4     Execution Time:  688.5 ms

The diagnostic metric is the disappearance of the SubPlan line, not the timing — a SubPlan that happens to be fast today becomes quadratic the moment the outer relation grows.

Why a fast SubPlan today is a slow one tomorrow Two lines against outer row count. The SubPlan line rises linearly and steeply because it is re-evaluated per row. The semi join line rises gently because the hash is built once. outer rows → SubPlan Semi Join looks fine in testing fails at production scale

Common Pitfalls #

Trusting NOT IN because it is fast on small data. It is a per-row construct; the cost is linear in outer rows times subquery cost. Diagnostic signal: NOT (hashed SubPlan) in the filter. Fix: NOT EXISTS, or a NOT NULL constraint.

Adding LIMIT inside a FROM subquery for “safety”. It blocks pull-up entirely and freezes the subquery as its own plan. Diagnostic signal: a Subquery Scan node wrapping a Limit. Fix: apply the limit in the outer query if the semantics allow.

Correlated scalar subqueries in SELECT. They cannot be flattened and run once per output row. Diagnostic signal: SubPlan attached to a Result node. Fix: rewrite as a LEFT JOIN LATERAL or a pre-aggregated join.

Assuming a flattened plan is automatically faster. Pull-up widens the join-order search, which can expose bad estimates. Diagnostic signal: a semi join with an estimate far from actual. Fix: correct the statistics, as described in autovacuum and plan stability.

Frequently Asked Questions #

Why is NOT EXISTS faster than NOT IN? NOT EXISTS maps cleanly onto an anti join. NOT IN cannot, because a single NULL in the subquery result makes the predicate unknown for every row, so the planner must evaluate it per row unless the column is provably NOT NULL.

What stops a FROM subquery being flattened? LIMIT/OFFSET, DISTINCT, GROUP BY or any aggregate, a set operation, a window function, or a volatile target-list expression. Each gives the subquery semantics that would be lost if its tables were merged into the outer join tree.

Does an OR prevent a sublink from becoming a semi join? Yes. A semi join replaces a predicate that must hold for every returned row, and an OR branch need not hold. Splitting the query into a UNION of two independently flattenable branches usually restores the set-based plan.