Correlated SubPlans in SELECT Lists #

A customer listing adds a column: (SELECT max(placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order. For a page of 50 customers it is invisible. For the nightly export of 2 million customers the query takes 40 minutes, and the plan shows SubPlan 1 with loops=2000000.

Subqueries in FROM and WHERE are often flattened into joins, as described in subquery flattening and pull-up rules. Scalar subqueries in the SELECT list are not: they stay as per-row expressions.

The Planner Condition #

A scalar subquery that references columns of the outer query — a correlated subquery — is planned separately as a SubPlan. At execution time it is evaluated once for each outer row that reaches the node computing the SELECT list, with the outer values passed in as parameters. The planner does not convert target-list subqueries into joins, because a scalar subquery must return at most one row and has different null semantics from a join; turning it into a join safely requires aggregating or limiting the inner side, which it leaves to you.

Consequences:

Cost is linear in outer rows. Each evaluation is a separate small query. With an index on the correlation column, each costs one index descent; without one, each is a scan.
No set-based algorithms. A hash or merge join could compute all 2 million values in one pass over orders; a SubPlan cannot.
Evaluation point matters. If the subquery sits in the outer SELECT list above a LIMIT, it runs only for returned rows. If it sits below a sort — because the query orders by it, or a view places it lower — it runs for every candidate row.

An uncorrelated scalar subquery — no reference to the outer query — becomes an InitPlan, evaluated once. Only correlated ones loop.

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.email,
       (SELECT max(o.placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;

Seq Scan on customers c  (actual time=0.05..2410204.8 rows=2000412 loops=1)
  SubPlan 1
    ->  Aggregate  (actual time=1.20..1.20 rows=1 loops=2000412)
          ->  Seq Scan on orders o  (actual time=0.9..1.19 rows=47 loops=2000412)
                Filter: (customer_id = c.id)
                Rows Removed by Filter: 9401
Execution Time: 2410811.2 ms
-- loops=2000412: one subquery execution per customer
-- no index on orders.customer_id: each execution filters a scan
-- per-loop 1.2 ms × 2M loops ≈ 40 minutes

Rewritten as a grouped join:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.email, lo.last_order
FROM customers c
LEFT JOIN (
  SELECT customer_id, max(placed_at) AS last_order
  FROM orders GROUP BY customer_id
) lo ON lo.customer_id = c.id;

Hash Right Join  (actual time=21840.2..24102.6 rows=2000412 loops=1)
  Hash Cond: (orders.customer_id = c.id)
  ->  HashAggregate  (actual time=19804.6..21204.1 rows=1840220 loops=1)
        Group Key: orders.customer_id
        ->  Seq Scan on orders  (actual rows=94022110 loops=1)
  ->  Hash  (actual rows=2000412 loops=1)
        ->  Seq Scan on customers c  (actual rows=2000412 loops=1)
Execution Time: 24410.9 ms
-- orders read once, grouped once; 100× faster

Step-by-Step Resolution #

Find SubPlan entries and multiply their per-loop time by loops.

Choose the rewrite by how many outer rows you return.

Most outer rows, all needing the value: aggregate the inner table once and join, as above.
Few outer rows (paginated), or top-N per row: use LATERAL, which keeps per-row index probes but lets the planner treat it as a join:

SELECT c.id, c.email, lo.placed_at AS last_order
FROM customers c
LEFT JOIN LATERAL (
  SELECT placed_at FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY placed_at DESC LIMIT 1
) lo ON true
WHERE c.created_at >= now() - interval '7 days';

Index the correlation column with the ordering column for LATERAL and remaining SubPlans:
```
CREATE INDEX CONCURRENTLY orders_customer_placed_idx ON orders (customer_id, placed_at DESC);
```
max(placed_at) per customer then becomes a single index descent — the patterns in LATERAL join plan shapes.
Keep several correlated columns in one join. Three scalar subqueries against the same table are three SubPlans; one grouped subquery computing all three values is one pass.
Verify semantics. A scalar subquery errors if it returns more than one row; a join silently multiplies rows. Aggregate or limit the inner side so the join returns at most one row per outer row.

Before and After #

-- BEFORE: scalar subquery in SELECT list, no index
Seq Scan on customers  SubPlan 1 → Aggregate (loops=2000412) → Seq Scan on orders     Execution Time: 2410811.2 ms

-- AFTER: grouped LEFT JOIN
Hash Right Join → HashAggregate (orders) + Hash (customers)                          Execution Time: 24410.9 ms

Where ORMs put correlated subqueries #

ORM annotation APIs generate exactly this shape. Django’s Subquery(OuterRef(...)) inside annotate(), ActiveRecord scopes built with select("(SELECT …) AS …"), and SQLAlchemy’s scalar_subquery() with correlate() all emit per-row scalar subqueries. They are the right tool for paginated lists, where the SubPlan runs for 20 or 50 rows, and a poor one for exports and reports that return every row. When an annotated queryset is reused for a bulk job, check the plan’s loops before shipping it; the capture approaches are in ORM-generated EXPLAIN output.

Watch for subqueries that end up below a sort. ORDER BY last_order DESC LIMIT 20 must compute the subquery for every customer before the sort can pick 20, so a query that looks paginated still loops over the whole table. The LATERAL form does not escape that; ordering by a per-customer aggregate over all customers is inherently a full computation, and belongs in a precomputed column or summary table.

Common Pitfalls #

Judging a SubPlan by per-loop time. A millisecond looks harmless. Diagnostic signal: loops in the millions. Fix: multiply, then rewrite.

Ordering by the subquery column with LIMIT. Every row’s subquery is evaluated before sorting. Diagnostic signal: SubPlan loops equal to the full table despite LIMIT 20. Fix: precompute the value or restrict candidates first.

Join rewrite that duplicates rows. Joining raw orders returns one row per order. Diagnostic signal: more output rows than customers. Fix: aggregate or LIMIT 1 inside the joined subquery.

Several subqueries on the same table. Each is its own loop. Diagnostic signal: SubPlan 1, SubPlan 2, SubPlan 3 over one relation. Fix: compute all values in one grouped subquery.

Frequently Asked Questions #

What is a SubPlan in a PostgreSQL execution plan? #

It is a subquery the planner kept as an expression instead of flattening into a join. A correlated SubPlan is executed once per outer row, with the outer values passed in as parameters, and its loops count shows how many times it ran.

What is the difference between SubPlan and InitPlan? #

An InitPlan is an uncorrelated subquery that is evaluated once and its result reused. A SubPlan in this context is correlated with the outer query and is re-evaluated for each outer row.

Is LATERAL faster than a correlated subquery? #

LATERAL still evaluates per outer row, but it is planned as a join, can return several columns in one probe, and makes the per-row cost explicit. For all-rows reports, aggregating once and joining is usually faster than either.

CTE and Subquery Planning — parent guide: how subqueries are planned
Lateral Join Plan Shapes — sibling: the per-row join alternative
Semi-Join and Anti-Join Plan Shapes — SubPlans in WHERE clauses

Up: CTE and Subquery Planning