LATERAL, DISTINCT ON, and GROUPING SETS

Each of the three constructs here replaces a pattern that teams otherwise write as a loop in application code. LATERAL lets a subquery in FROM see the row to its left, which is how "the three most recent orders per customer" becomes one query. DISTINCT ON is the Postgres shortcut for "one row per group". GROUPING SETS computes several levels of aggregation in a single pass where the obvious version is a UNION ALL of three nearly identical queries.

All three exist in Cartwheel's backlog right now. The recent-orders screen fetches the customer list and then issues one query per customer for their last three orders. The operations dashboard finds the current state of each delivery by pulling two hours of delivery_events and keeping the newest row per order_id in a dictionary. The finance report unions three aggregate queries over orders to produce per-city, per-day and overall totals, and the three definitions have already drifted apart once.

LATERAL, a Correlated Subquery in FROM

A subquery in FROM is normally evaluated on its own and cannot cross-reference anything beside it. LATERAL removes that restriction: the sub-select may refer to columns of FROM items that appear before it in the list, or that sit on the left-hand side of the join it is on the right of. Evaluation is exactly what the name suggests — for each row of the source item, the lateral item is evaluated with that row's values, and the results are joined back to the row they were computed from.

SELECT c.id, c.email, r.id AS order_id, r.placed_at, r.total
  FROM customers c
  LEFT JOIN LATERAL (
       SELECT o.id, o.placed_at, o.total
         FROM orders o
        WHERE o.customer_id = c.id
        ORDER BY o.placed_at DESC
        LIMIT 3
  ) r ON true
 WHERE c.id = ANY(:customer_ids);

The join is LEFT JOIN … ON true rather than a comma or CROSS JOIN, because a cross join drops customers who have never ordered and the screen needs them with an empty list. The plan is a nested loop: for each of the 200 customers on the screen, an index scan on orders (customer_id, placed_at DESC) that reads three rows and stops. That is 600 rows touched out of 40 million. Without that index, the same loop repeats a sequential scan of orders 200 times.

Top-N per Group, Three Ways

The same question has three answers and they do not perform alike. LATERAL with LIMIT does repeated indexed lookups, so its cost scales with the outer set: unbeatable when the outer side is 200 rows and the inner side is indexed. A window function ranking with row_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) filtered to <= 3 sorts the whole input once, which is the right shape when you were going to read all of it anyway — a report over every customer, not a screen showing a few. DISTINCT ON handles the special case of N equal to one with a single sort and no join at all.

Which form is right is a question about the data rather than about the syntax — Chapter 9 reads all three plans off EXPLAIN side by side. Three things settle it: how many outer rows there are, whether the inner side is indexed on the join and sort keys, and whether the query needs the whole set ranked or only its head.

DISTINCT ON, First Row per Group

DISTINCT ON (expressions) keeps the first row of each set of rows sharing those expressions. "First" is decided by the query's ORDER BY, and one rule decides whether the query is even legal: the DISTINCT ON expressions must match the leftmost ORDER BY expressions. Without an ORDER BY the row you get from each group is unpredictable, and the query will happily return a different answer tomorrow.

SELECT DISTINCT ON (order_id)
       order_id, event_type, occurred_at
  FROM delivery_events
 WHERE occurred_at >= now() - interval '2 hours'
 ORDER BY order_id, occurred_at DESC;

The sort groups the rows by order_id and, within each order, puts the newest event first; DISTINCT ON then keeps the head of each group. Writing ORDER BY occurred_at DESC alone is rejected, and the error is worth reading rather than pattern-matching around: it is saying that DISTINCT ON is defined in terms of the sort, so the sort has to start where the grouping starts. The construct is Postgres-only; porting it means rewriting it as row_number() = 1.

GROUPING SETS, ROLLUP, and CUBE

A GROUP BY clause can define several independent groupings at once, and the result is the UNION ALL of them — computed from one scan instead of three. ROLLUP (a, b) is shorthand for the list and all its prefixes including the empty one, so it produces three levels: per a-and-b, per a, and a grand total. CUBE (a, b, c) produces the full power set, which is 2 to the power of the number of expressions — eight levels for three columns, and worth counting before you write it.

SELECT date_trunc('day', o.placed_at) AS day,
       c.city,
       count(*)     AS orders,
       sum(o.total) AS revenue,
       GROUPING(c.city) AS is_day_total
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
 WHERE o.placed_at >= '2026-08-01'
 GROUP BY ROLLUP (date_trunc('day', o.placed_at), c.city)
 ORDER BY day, c.city;

Columns that are not part of a given grouping set come back as NULL, which is indistinguishable from a genuine null city until you ask. GROUPING() answers: it returns an integer bitmask over its arguments, with the rightmost argument as the least significant bit, and a bit set to 1 wherever that expression was not part of the grouping that produced this row. Replacing a three-query UNION ALL with this removes two scans of orders and two places where the definition of revenue can drift. The dashboard renders a row with is_day_total = 1 in bold and never has to guess which rows are subtotals.

Set-Returning Functions in FROM

A function that returns a set can be written directly as a FROM item, and LATERAL is implicit there — the function may reference earlier items with no keyword. generate_series manufactures a dense axis, unnest expands an array back into rows, and jsonb_array_elements explodes a document into one row per element. The array case is one line against Cartwheel's products.tags: FROM products p, unnest(p.tags) AS t(tag) gives a row per product per tag, ready to group.

SELECT h.hour, count(d.id) AS events
  FROM generate_series(date_trunc('hour', now()) - interval '23 hours',
                       date_trunc('hour', now()),
                       interval '1 hour') AS h(hour)
  LEFT JOIN delivery_events d
         ON d.occurred_at >= h.hour
        AND d.occurred_at <  h.hour + interval '1 hour'
 GROUP BY h.hour
 ORDER BY h.hour;

The 24 rows of the axis exist whether or not any delivery happened in them, so an hour with no events shows up as a zero rather than vanishing from the chart — and an hour that vanishes is exactly the hour you needed to see. One detail decides whether it works: count(d.id) counts matched rows, while count(*) counts the axis row itself and reports 1 for every empty hour.

Choosing Between Them

None of these is cleverer than the others; they produce different plans, and the data decides which plan is right. LATERAL gives repeated indexed lookups and is priced by the size of the outer set. A window function gives one large sort and is priced by the size of the whole input. DISTINCT ON gives one sort and no join. GROUPING SETS gives one scan feeding several aggregation levels.

That closes the read side of Cartwheel's problem list. Three round trips became one query, a dictionary in Python became a sort, and three unioned aggregates became one scan. What is left is the write side: statements that need to hand back what they just changed, and statements that need to move rows between two tables without a window in which the rows exist in neither.