PostgreSQL Subquery and CTE Optimization

PostgreSQL Subquery and CTE Optimization

Every SELECT in PostgreSQL is made of smaller SELECTs, even when it doesn't look that way. WHERE col IN (SELECT ...), WHERE EXISTS (SELECT ...), (SELECT count(*) FROM ... WHERE ...) in the column list, WITH x AS (SELECT ...) — these look syntactically different but all get rewritten into plan nodes at plan time. Which plan node the planner chooses determines whether your query runs in three milliseconds or three seconds, and the rules are different for each pattern.

This article is the fourth in the Complete Guide to PostgreSQL SQL Query Analysis & Optimization series. We assume you can read EXPLAIN output and are familiar with how the planner chooses join strategies. The running dataset is unchanged: 500k-row sim_bp_orders, 200k-row sim_bp_users, and friends, on Neon Postgres 17.8.

We'll cover: scalar and existence subqueries (SubPlan, EXISTS, IN), when correlated subqueries should be rewritten as joins, how CTEs are executed on modern PostgreSQL, when to use MATERIALIZED vs NOT MATERIALIZED, LATERAL joins, and recursive CTEs.

Scalar correlated subqueries — the SubPlan trap

A scalar subquery in the column list is the easiest way to accidentally write an O(n²) query:

SELECT u.user_id,
       u.email,
       (SELECT count(*)
          FROM sim_bp_orders o
         WHERE o.user_id = u.user_id
           AND o.status = 'pending') AS pending_count
FROM sim_bp_users u
WHERE u.status = 'active'
LIMIT 100;

The query reads naturally: "for each active user, count their pending orders." The plan is what that description implies:

Limit  (cost=0.42..1642.83 rows=100 width=33) (actual time=0.088..3.438 rows=100 loops=1)
  Buffers: shared hit=565 read=3
  ->  Index Scan using sim_bp_users_pkey on sim_bp_users u
        (cost=0.42..3118066 rows=189807 width=33)
        (actual time=0.087..3.433 rows=100 loops=1)
        Filter: ((u.status)::text = 'active'::text)
        SubPlan 1
          ->  Aggregate  (cost=16.24..16.25 rows=1 width=8)
                (actual time=0.033..0.033 rows=1 loops=100)
                ->  Bitmap Heap Scan on sim_bp_orders o
                      (actual time=0.032..0.033 rows=0 loops=100)
                      Recheck Cond: (o.user_id = u.user_id)
                      Filter: ((o.status)::text = 'pending'::text)
                      ->  Bitmap Index Scan on idx_sim_bp_orders_user_id
                            (actual time=0.029..0.029 rows=3 loops=100)
                            Index Cond: (o.user_id = u.user_id)
 Execution Time: 3.444 ms

Two signals. First, the SubPlan 1 node is inside the outer index scan — it runs once per outer row. actual time=0.033..0.033 rows=1 loops=100 tells you the subquery was executed 100 times (once per user returned). With LIMIT 100 it's cheap; without the limit, it would run 200,000 times (once per active user), and at ~0.03 ms per run that's six seconds of just-subquery time even before any other work.

Second, the MyDBA analyzer rule correlated_subplan fires on any plan node where PostgreSQL's JSON EXPLAIN format populates the Subplan Name field (the common case for correlated SubPlans). It's a heads-up that the query is executing per-outer-row work, which is almost always worth rewriting.

The SubPlan pattern is the direct translation of the English sentence into SQL. It's not wrong, and for queries with aggressive LIMITs on the outer side it's often fine. But it is almost always beatable by a rewrite — either an aggregating JOIN or a correlated aggregate pushed into a LATERAL — and both rewrites scale better as the outer set grows.

EXISTS, IN, and JOIN — three ways to express "filter by related rows"

For the "find rows that have at least one related row" pattern, SQL offers three syntactic choices. They don't all produce the same plan, and on PostgreSQL the difference is usually meaningful.

Here's the rewrite of the query above as an EXISTS — this time asking a boolean question, "find users who have at least one pending order" rather than counting:

SELECT u.user_id, u.email
FROM sim_bp_users u
WHERE u.status = 'active'
  AND EXISTS (
      SELECT 1 FROM sim_bp_orders o
      WHERE o.user_id = u.user_id
        AND o.status = 'pending'
  )
LIMIT 100;
Limit  (cost=0.85..137.98 rows=100 width=25) (actual time=0.089..3.238 rows=100 loops=1)
  Buffers: shared hit=726 read=1
  ->  Merge Semi Join  (actual time=0.088..3.234 rows=100 loops=1)
        Merge Cond: (u.user_id = o.user_id)
        ->  Index Scan using sim_bp_users_pkey on sim_bp_users u
              Filter: ((u.status)::text = 'active'::text)
        ->  Index Scan using idx_sim_bp_orders_user_id on sim_bp_orders o
              Filter: ((o.status)::text = 'pending'::text)
              Rows Removed by Filter: 586
 Execution Time: 3.240 ms

The planner picked a Merge Semi Join — a variant of Merge Join that stops at the first match per outer row. That's exactly what EXISTS semantics require: existence, not count. Both sides come in user_id-ordered streams (the left from the users primary-key btree; the right from idx_sim_bp_orders_user_id with the status='pending' predicate applied as a filter), and the merge walks them in lockstep. The plan has no per-outer-row SubPlan and no re-execution. The planner doesn't always pick Merge Semi Join for this shape — a Nested Loop Semi Join with an index probe is also common, particularly when the outer side has a very small LIMIT that the planner thinks it can exploit. Both shapes scale linearly in the outer set size, which is the important property; the SubPlan pattern was quadratic.

IN (SELECT ...) is a third way to express the same logic. Most of the time PostgreSQL's planner treats WHERE col IN (SELECT ...) and WHERE EXISTS (SELECT ... WHERE ... = col) identically, producing the same plan — a semi-join of some form. Two gotchas:

1. NOT IN with nullable columns is not equivalent to NOT EXISTS. If any value in the inner set is NULL, NOT IN returns unknown (effectively no rows). NOT EXISTS doesn't have that problem. Always prefer NOT EXISTS unless you've specifically proven the column is NOT NULL. 2. IN on an array literal (e.g. WHERE id IN (1, 2, 3)) is a completely different beast — it's just syntactic sugar for ANY (ARRAY[1,2,3]) and has nothing to do with subqueries.

An explicit JOIN on the related table works too, but duplicates outer rows for each matching inner row:

SELECT DISTINCT u.user_id, u.email
FROM sim_bp_users u
JOIN sim_bp_orders o ON o.user_id = u.user_id
WHERE u.status = 'active' AND o.status = 'pending';

The DISTINCT is required because a user with five pending orders would appear five times in the JOIN's output. This pattern is usually slower than EXISTS — the JOIN produces all matching rows then distincts them down — and it requires the reader to notice the DISTINCT, so there are two chances to get it wrong. Use EXISTS for existence questions, JOIN for data you actually want from the related table.

The rule of thumb:

LATERAL — top-N per group without window functions

A LATERAL join lets a subquery on the right side of a FROM reference columns from the left side. The semantics are "for each row on the left, evaluate this subquery with those columns bound, and join the result." It's the SQL-standard way to express "the last five logins per user", "the latest order per customer", "the most recent status message per ticket" — any "top-N per outer group" pattern.

SELECT u.user_id, u.email,
       latest.order_id,
       latest.total_amount_cents
FROM sim_bp_users u
CROSS JOIN LATERAL (
    SELECT order_id, total_amount_cents
    FROM sim_bp_orders o
    WHERE o.user_id = u.user_id
    ORDER BY o.created_at DESC
    LIMIT 1
) latest
WHERE u.status = 'active'
LIMIT 50;

"For each active user, return their most recent order." The plan:

Nested Loop  (actual time=0.016..0.452 rows=50 loops=1)
  Buffers: shared hit=314
  ->  Index Scan using sim_bp_users_pkey on sim_bp_users u
        Filter: ((u.status)::text = 'active'::text)
  ->  Subquery Scan on latest  (actual time=0.008..0.008 rows=1 loops=54)
        ->  Sort  (actual time=0.007..0.007 rows=1 loops=54)
              Sort Key: o.created_at DESC
              Sort Method: quicksort  Memory: 25kB
              ->  Bitmap Heap Scan on sim_bp_orders o
                    (actual time=0.003..0.006 rows=3 loops=54)
                    Recheck Cond: (o.user_id = u.user_id)
 Execution Time: 0.462 ms

0.46 ms. The planner ran the lateral subquery 54 times (one per user, until LIMIT 50 on the outer was satisfied after some users had zero orders). Each lateral execution was a cheap bitmap index scan + tiny sort. The sort finds the max created_at in memory — the LIMIT 1 bounded the work.

The equivalent without LATERAL is usually a window function (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) with an outer WHERE rn = 1) — that form often produces a worse plan on PostgreSQL when only the top 1 or 2 per group are needed, because it computes row numbers for every row before filtering. LATERAL with a LIMIT inside the subquery lets the planner stop reading after the top entries.

Two practical notes:

CTEs — materialised by default no longer

Before PostgreSQL 12, every WITH clause was an optimisation fence: the CTE was computed in full and stored in a temporary buffer, and the planner could not push predicates from the outer query into the CTE. That meant a WITH clause accidentally defeated predicate pushdown. People used this intentionally (the so-called "CTE trick" to force materialisation), but it also silently hurt a lot of queries.

PostgreSQL 12 reversed the default. Now a CTE referenced once and without data-modifying statements is inlined — the planner treats it like a subquery, and predicate pushdown works as expected. CTEs that are referenced multiple times or contain INSERT/UPDATE/DELETE are still materialised.

Two keywords let you override the default:

Typical cases:

-- Inlined by default — works like a subquery, predicates push in.
WITH recent_pending AS (
    SELECT order_id, user_id, created_at
    FROM sim_bp_orders
    WHERE status = 'pending'
)
SELECT rp.order_id, u.email
FROM recent_pending rp
JOIN sim_bp_users u ON u.user_id = rp.user_id
WHERE rp.created_at > now() - interval '7 days';

The created_at > now() - interval '7 days' filter is pushed into the CTE by the planner, so the combined filter (status = 'pending' AND created_at > ...) can use a single index scan rather than materialising all pending orders first.

-- Expensive aggregation referenced twice — worth materialising once.
WITH user_totals AS MATERIALIZED (
    SELECT user_id, sum(total_amount_cents) AS total
    FROM sim_bp_orders
    GROUP BY user_id
)
SELECT u.email, ut.total
FROM sim_bp_users u
JOIN user_totals ut ON ut.user_id = u.user_id
WHERE ut.total > 1000000

UNION ALL

SELECT u.email, 0
FROM sim_bp_users u
WHERE NOT EXISTS (SELECT 1 FROM user_totals WHERE user_id = u.user_id);

Without MATERIALIZED, the aggregation would run twice (once per reference). With it, it runs once and both references read from the materialised temp table.

The MyDBA analyzer rule cte_materialized fires whenever a CTE Scan node appears in the plan, suggesting NOT MATERIALIZED as a potential improvement. The rule is deliberately conservative — sometimes materialisation is exactly what you want, as in the example above — so treat it as a prompt to think, not a verdict.

Recursive CTEs

Recursive CTEs are for hierarchical data: trees, graphs, transitive closures, category parents, reporting chains. The syntax:

WITH RECURSIVE employee_tree AS (
    -- Base case: root of the tree
    SELECT employee_id, manager_id, name, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: children of previously-found rows
    SELECT e.employee_id, e.manager_id, e.name, et.depth + 1
    FROM employees e
    JOIN employee_tree et ON et.employee_id = e.manager_id
)
SELECT * FROM employee_tree;

Execution: PostgreSQL computes the base case, then repeatedly applies the recursive step to the previously-produced rows until no new rows are generated. Two practical concerns:

1. No termination without a base case. A recursive CTE that accidentally references itself in the base term, or whose recursive step produces the same rows forever, loops forever. Use depth < N as a guard when testing. 2. Index the join column. The recursive step joins the CTE's accumulated rows against the source table — without an index on the join column (employees.manager_id here), each iteration is a sequential scan.

For transitive-closure queries (shortest paths, graph traversals), recursive CTEs work but scale poorly beyond a few tens of thousands of rows. For heavier graph workloads look at the pg_graph family of extensions, or materialised adjacency tables maintained via triggers.

Subqueries in the FROM clause

SELECT ... FROM (SELECT ...) AS sub — a subquery in FROM — is semantically just a derived table. The planner inlines it the same way it inlines a CTE (in PostgreSQL 12+ behaviour), pushing predicates in and letting the rest of the optimisation machinery run.

There's one case where FROM subqueries matter for performance: forcing a specific scan or computation to happen once, rather than recomputing it per outer row. If you have SELECT ..., f(x) AS computed_val FROM t WHERE f(x) > 10, PostgreSQL may call f(x) twice per row (once for the filter, once for the projection) unless f is marked STABLE and the planner proves equivalence. Wrapping the expensive call in a FROM subquery sometimes ensures one-call-per-row evaluation.

Practical rules

Next steps

Subquery structure often determines whether the planner can find a good plan at all, but the individual predicates inside each subquery determine whether indexes get used. That's the subject of the next article in the series — WHERE Clause Optimisation — covering sargability, composite-index column ordering, and the operators that silently disable indexes.

For the broader pattern of "rewrite the query to match the planner's strengths," see Query Rewriting Techniques.