LATERAL Joins

"For each customer, their 3 most recent orders." One approach costs less on paper. The other one is actually faster, measured.

A plain JOIN cannot reference the outer row while it is being planned — every table in a FROM clause is resolved independently of the others' actual row values. LATERAL breaks that restriction on purpose: a subquery marked LATERAL is allowed to reference columns from tables that appear earlier in the same FROM clause, and PostgreSQL genuinely executes it once per row those tables produce — which is exactly the mechanism "the 3 most recent orders for each customer" needs, since that is fundamentally a per-customer question, not a single set-based one.\n\nA window function with ROW_NUMBER() can solve the identical top-N-per-group problem in a single pass over a full join, without executing anything once per row — and its EXPLAIN cost estimate can genuinely come out lower than LATERAL's. That does not automatically make it faster. This lab measures both approaches for real, on the same data, and the result is a useful reminder from earlier in this certificate: EXPLAIN's cost is the planner's best estimate before running anything, and EXPLAIN ANALYZE's execution time is what actually happened — the two do not always point to the same winner.

LATERAL: A Subquery, Re-Executed Per Outer Row

SELECT c.id, c.name, o.id AS order_id, o.order_date, o.amount
FROM sql_customers c
JOIN LATERAL (
  SELECT * FROM sql_orders o WHERE o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 3
) o ON true
WHERE c.id IN (1,2) ORDER BY c.id, o.order_date DESC;

 id |   name    | order_id |     order_date      | amount
----+-----------+----------+---------------------+--------
  1 | Customer1 |    20000 | 2025-01-14 21:20:00 |     10
  1 | Customer1 |    19500 | 2025-01-14 13:00:00 |    110
  1 | Customer1 |    19000 | 2025-01-14 04:40:00 |     10
  2 | Customer2 |    19501 | 2025-01-14 13:01:00 |    111

c.id inside the LATERAL subquery references the outer customer row directly — something a plain, non-LATERAL subquery cannot do — and LIMIT 3 inside it means exactly 3 rows per customer, genuinely.

Two Real Plans for the Identical Problem

LATERAL:      Nested Loop (cost=0.29..5605.96) — Index Scan per customer, loops=500
Window fn:    Subquery Scan (cost=0.52..1907.03) over WindowAgg over Merge Join

The window-function version's estimated cost is roughly a third of LATERAL's. On paper, it looks like the clear winner.

Measured, Not Estimated

| Approach | Estimated Cost | Measured Execution Time | |----------|-----------------|---------------------------| | LATERAL | 5605.96 | 161.057 ms | | Window function (ROW_NUMBER) | 1907.03 | 371.099 ms |

LATERAL is measurably more than twice as fast here, despite its higher cost estimate. The reason is visible in the buffer counts: LATERAL touches 2,503 buffers total — 500 small, index-backed lookups of exactly 3 rows each — while the window-function version has to Merge Join and buffer all 20,071 rows of both tables before the window function can even begin filtering down to the top 3 per customer. The cost estimate reflects the planner's model of CPU and I/O cost; it is not a promise about which query will actually run faster on real, specific data.

LATERAL With a Set-Returning Function

SELECT p.order_id, i.sku, i.qty
FROM sql_order_payloads p, LATERAL jsonb_to_recordset(p.items) AS i(sku text, qty int)
ORDER BY p.order_id, i.sku;

 order_id | sku | qty
----------+-----+-----
        1 | ABC |   2
        1 | XYZ |   5
        2 | DEF |   1

jsonb_to_recordset is a set-returning function — it needs p.items from the outer row to know what to expand, which is exactly what LATERAL enables: each order's own JSONB array is unnested into its own rows, in the same query.

LATERAL

A modifier on a subquery or set-returning function in a FROM clause that allows it to reference columns from tables listed earlier in the same FROM clause. PostgreSQL genuinely evaluates a LATERAL subquery once for every row produced by those earlier tables, which is what makes per-row-dependent computations — like "the top 3 rows for this specific outer row" or "unnest this specific row's own JSONB array" — expressible at all; a non-LATERAL subquery cannot see the outer row's values.

Top-N per group

The general problem of finding the N highest (or most recent, or otherwise best-ranked) rows within each group in a table, rather than one overall top-N across everything. LATERAL with a per-group subquery and LIMIT, and a window function like ROW_NUMBER() with PARTITION BY, are two structurally different ways to express this same problem — one re-executing a small query per group, the other making one pass with a per-row rank computed and then filtered.

↪️ Get Each Customer's 3 Most Recent Orders With LATERAL

Write a JOIN LATERAL that runs a small correlated subquery once per customer, limited to their 3 most recent orders.

psql -U postgres -d beer_db -c "SELECT c.id, c.name, o.id AS order_id, o.order_date, o.amount FROM sql_customers c JOIN LATERAL (SELECT * FROM sql_orders o WHERE o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 3) o ON true WHERE c.id IN (1,2) ORDER BY c.id, o.order_date DESC;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT c.id, c.name, o.id AS order_id, o.order_date, o.amount FROM sql_customers c JOIN LATERAL (SELECT * FROM sql_orders o WHERE o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 3) o ON true WHERE c.id IN (1,2) ORDER BY c.id, o.order_date DESC;" SET id | name | order_id | order_date | amount ----+-----------+----------+---------------------+-------- 1 | Customer1 | 20000 | 2025-01-14 21:20:00 | 10 1 | Customer1 | 19500 | 2025-01-14 13:00:00 | 110 1 | Customer1 | 19000 | 2025-01-14 04:40:00 | 10 2 | Customer2 | 19501 | 2025-01-14 13:01:00 | 111 2 | Customer2 | 19001 | 2025-01-14 04:41:00 | 11 2 | Customer2 | 18501 | 2025-01-13 20:21:00 | 111 (6 rows)

⚖️ Measure LATERAL Against the Window-Function Alternative

Run both the LATERAL version and a ROW_NUMBER window-function version at full scale, with EXPLAIN ANALYZE, and compare real execution time.

psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT c.id, o.id FROM sql_customers c JOIN LATERAL (SELECT * FROM sql_orders o WHERE o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 3) o ON true;"

psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT id, order_id FROM (SELECT c.id, o.id AS order_id, ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.order_date DESC) AS rn FROM sql_customers c JOIN sql_orders o ON o.customer_id = c.id) sub WHERE rn <= 3;"

student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT c.id, o.id FROM sql_customers c JOIN LATERAL (SELECT * FROM sql_orders o WHERE o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 3) o ON true;" SET QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.29..5605.96 rows=1500 width=8) (actual rows=1500.00 loops=1) Buffers: shared hit=2503 -> Seq Scan on sql_customers c (cost=0.00..8.00 rows=500 width=4) (actual rows=500.00 loops=1) Buffers: shared hit=3 -> Limit (cost=0.29..11.14 rows=3 width=48) (actual rows=3.00 loops=500) Buffers: shared hit=2500 -> Index Scan using sql_orders_customer_id_order_date_idx on sql_orders o (cost=0.29..144.93 rows=40 width=48) (actual rows=3.00 loops=500) Index Cond: (customer_id = c.id) Index Searches: 500 Buffers: shared hit=2500 Planning: Buffers: shared hit=193 read=3 Planning Time: 32.312 ms Execution Time: 161.057 ms (14 rows) student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT id, order_id FROM (SELECT c.id, o.id AS order_id, ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.order_date DESC) AS rn FROM sql_customers c JOIN sql_orders o ON o.customer_id = c.id) sub WHERE rn <= 3;" SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan on sub (cost=0.52..1907.03 rows=20000 width=8) (actual rows=1500.00 loops=1) Buffers: shared hit=20071 -> WindowAgg (cost=0.52..1707.03 rows=20000 width=24) (actual rows=1500.00 loops=1) Window: w1 AS (PARTITION BY c.id ORDER BY o.order_date ROWS UNBOUNDED PRECEDING) Run Condition: (row_number() OVER w1 <= 3) Storage: Memory Maximum Storage: 17kB Buffers: shared hit=20071 -> Merge Join (cost=0.43..1357.03 rows=20000 width=16) (actual rows=20000.00 loops=1) Merge Cond: (o.customer_id = c.id) Buffers: shared hit=20071 -> Index Scan using sql_orders_customer_id_order_date_idx on sql_orders o (cost=0.29..1084.13 rows=20000 width=16) (actual rows=20000.00 loops=1) Index Searches: 1 Buffers: shared hit=20067 -> Index Only Scan using sql_customers_pkey on sql_customers c (cost=0.15..21.65 rows=500 width=4) (actual rows=500.00 loops=1) Heap Fetches: 500 Index Searches: 1 Buffers: shared hit=4 Planning: Buffers: shared hit=148 Planning Time: 14.290 ms Execution Time: 371.099 ms (21 rows)

📦 Use LATERAL to Unnest a JSONB Array Per Row

Use jsonb_to_recordset with LATERAL to expand each order's own JSONB items array into individual rows.

psql -U postgres -d beer_db -c "SELECT p.order_id, i.sku, i.qty FROM sql_order_payloads p, LATERAL jsonb_to_recordset(p.items) AS i(sku text, qty int) ORDER BY p.order_id, i.sku;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT p.order_id, i.sku, i.qty FROM sql_order_payloads p, LATERAL jsonb_to_recordset(p.items) AS i(sku text, qty int) ORDER BY p.order_id, i.sku;" SET order_id | sku | qty ----------+-----+----- 1 | ABC | 2 1 | XYZ | 5 2 | DEF | 1 (3 rows)

Lab 3.3.3 complete. LATERAL joins, measured honestly against the alternative:\n\n\n Top-3-per-customer via LATERAL : ✅ correct, index-backed\n LATERAL vs window fn, measured : ✅ 161ms vs 371ms — estimate ≠ reality\n LATERAL + jsonb_to_recordset : ✅ per-row JSONB array unnested correctly\n

Enable JavaScript to run the live terminal and track your progress.