LATERAL Join Plan Shapes #

LATERAL is the one place in SQL where per-row evaluation is the point rather than an accident. It lets a subquery in FROM reference columns from tables to its left, which makes the “top three rows per customer” and “most recent event per device” patterns expressible without a window function — and, crucially, executable as an index descent per outer row.

Because the semantics are per-row, the field that decides whether a LATERAL plan is brilliant or catastrophic is loops. This page shows how to read it and how to keep it small. The wider set of subquery shapes is covered in CTE and subquery planning.

The Plan Shape LATERAL Produces #

A LATERAL join is always executed as a nested loop: the outer relation is scanned, and for each of its rows the inner subquery is re-planned parameters-first and executed. In EXPLAIN it appears as an ordinary Nested Loop whose inner side carries the correlation as an Index Cond on a parameter ($0, $1).

That makes the cost model simple and unforgiving:

total inner cost ≈ outer rows × (inner startup + inner per-row cost × inner rows)

Three consequences follow. A small outer relation with an indexed inner side is close to optimal — LIMIT 3 inside the lateral means each iteration reads three index entries and stops. A large outer relation multiplies everything, so the plan degrades linearly. And a missing index on the inner correlation column turns each iteration into a full scan, which is how a LATERAL becomes the slowest thing in the database.

How a LATERAL join executes Four outer rows on the left each point to their own execution of the inner subquery on the right, which performs an index descent limited to three rows. A formula beneath shows total cost as outer rows multiplied by per-loop cost. outer rows customer 1001 customer 1002 customer 1003 customer 1004 Index Scan orders (customer_id = $0) LIMIT 3 Index Scan orders (customer_id = $0) LIMIT 3 Index Scan orders (customer_id = $0) LIMIT 3 Index Scan orders (customer_id = $0) LIMIT 3 3 rows 3 rows 2 rows 3 rows loops = outer rows · total cost scales linearly with it

Annotated EXPLAIN Evidence #

The canonical top-N-per-group query, with a supporting index:

EXPLAIN (ANALYZE)
SELECT c.id, o.*
  FROM customers c
  LEFT JOIN LATERAL (
    SELECT o.id, o.total_cents, o.created_at
      FROM orders o
     WHERE o.customer_id = c.id
     ORDER BY o.created_at DESC
     LIMIT 3
  ) o ON true
 WHERE c.region = 'emea';

Nested Loop Left Join  (actual time=0.06..48.9 rows=8412 loops=1)
  ->  Index Scan using customers_region_idx on customers c  (actual rows=2804 loops=1)
  ->  Limit  (actual time=0.01..0.01 rows=3 loops=2804)          -- 2804 iterations
        ->  Index Scan Backward using orders_customer_created_idx on orders o
              Index Cond: (customer_id = c.id)                   -- the correlation
              (actual time=0.01..0.01 rows=3 loops=2804)
Execution Time: 51.2 ms

Read loops=2804 first: the inner side ran once per outer row, exactly as designed. Then check that actual rows per loop is small — three, because the LIMIT stopped the index scan. Index Scan Backward confirms the index is supplying the ORDER BY … DESC ordering rather than a sort running inside every iteration.

The failure mode is visible in the same three fields:

  ->  Limit  (actual time=41.2..41.2 rows=3 loops=2804)
        ->  Sort  (actual time=41.2..41.2 rows=3 loops=2804)
              Sort Method: top-N heapsort  Memory: 27kB
              ->  Seq Scan on orders o  (actual rows=2851 loops=2804)   -- 8M row reads
Execution Time: 116402.8 ms

Same query, no supporting index. Each of 2,804 iterations scans and sorts, and the total collapses to two minutes. The fix is an index, not a rewrite.

Step-by-Step Tuning Workflow #

  1. Read loops on the inner side. It should equal the outer node’s actual rows. Anything else means you are reading a different node than you think.

  2. Compute the real inner cost: loops × actual time per loop. EXPLAIN reports per-loop averages, so a 0.01 ms inner node at 2,804 loops is 28 ms, not 0.01 ms — the most common misreading of a lateral plan.

  3. Index the correlation plus the ordering, in that order:

    CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

    The leading column matches the correlation predicate; the second supplies the ORDER BY so the LIMIT can stop early. Column order follows the rules in multicolumn index column ordering.

  4. Shrink the outer side before the lateral runs. Filters and LIMIT on the outer relation multiply through every iteration, so pushing them down is worth more here than anywhere else.

  5. Compare against the window-function form when the outer set is large:

    SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) rn
    FROM orders o
) t WHERE rn <= 3;

    One sorted pass over everything, versus one index descent per group — the crossover depends on group count and index availability, so measure both.

  6. Use LEFT JOIN LATERAL … ON true when outer rows must survive. A plain CROSS JOIN LATERAL silently drops outer rows whose inner subquery returns nothing.

Where LATERAL stops being the cheaper shape Two lines against outer row count. The lateral join line rises linearly from near zero. The window function line starts high because of the full sort but rises slowly. They cross at a moderate outer row count, after which the window function is cheaper. outer rows (groups) → total cost LATERAL (loops × index descent) window function (one sorted pass) crossover few groups, indexed inner → LATERAL many groups → single pass wins

Before and After #

-- BEFORE: no index on (customer_id, created_at) — sort inside every iteration
->  Sort  (actual time=41.2..41.2 rows=3 loops=2804)   Seq Scan rows=2851 loops=2804
Execution Time: 116402.8 ms

-- AFTER: index supplies correlation and ordering, LIMIT stops after three entries
->  Index Scan Backward using orders_customer_created_idx  (actual time=0.01..0.01 rows=3 loops=2804)
Execution Time: 51.2 ms

loops is identical in both plans — 2,804. The metric that changed is the per-loop cost, which is the only lever a lateral join really has once the outer set is fixed.

Per-loop timing is not the total The reported inner time of nought point nought one milliseconds is multiplied by two thousand eight hundred and four loops to give the real inner cost of twenty-eight milliseconds, which is the figure to compare against other nodes. reported per loop 0.01 ms × loops 2,804 = real inner cost 28 ms compare this figure with sibling nodes — never the per-loop average

Common Pitfalls #

Reading per-loop timings as totals. EXPLAIN divides inner timings by loops. Diagnostic signal: a plan whose node times sum to far less than Execution Time. Fix: multiply by loops before comparing nodes.

CROSS JOIN LATERAL where LEFT JOIN LATERAL … ON true was meant. Outer rows with no inner match disappear from the result. Diagnostic signal: fewer output rows than the outer scan produced. Fix: use the left-join form.

Ordering inside the lateral without an index to match. Every iteration sorts. Diagnostic signal: a Sort node with a high loops count. Fix: index the correlation column plus the ordering column with matching direction.

Using LATERAL over a large outer set out of habit. Linear scaling in loops eventually loses to a single sorted pass. Diagnostic signal: loops in the hundreds of thousands. Fix: compare with the window-function form, or reduce the outer set first.

Frequently Asked Questions #

Is a LATERAL join just a correlated subquery? The semantics match, but the plan surface is far better. Because it lives in FROM, it can return several columns and rows, it can preserve outer rows with LEFT JOIN LATERAL, and the planner costs the inner side as a real path rather than as an opaque SubPlan.

Why is the loops count so high? By definition the inner side runs once per outer row. That is efficient when the outer set is small and the inner side is a bounded index lookup, and it is the dominant cost when the outer set is large or the inner side lacks a supporting index.

When does a window function beat a LATERAL top-N? When there are many groups and no index that supports the per-group lookup. The window plan pays one sort over everything; the lateral plan pays one descent per group. Measure both on production-shaped data — the crossover moves with group count and index quality.