The query that taught me LATERAL was a dashboard tile: the latest five orders for each customer on the account managers' watchlist. The original used row_number() over the full orders table and took 14 seconds. The rewrite — same result, CROSS JOIN LATERAL over the right index — ran in 40 milliseconds. That is a 350x difference from changing the shape of the query, not the hardware, and it is not exotic: top-N-per-group is one of the most common shapes in application SQL, and the two mainstream ways to write it have wildly different cost models.
What LATERAL actually does
LATERAL lets a subquery in the FROM clause reference columns from the tables listed before it. Read it as a loop: for each row of the driving table, evaluate the subquery with that row in scope, and join the results back. That is it. There is no magic and no new algebra — it is a correlated subquery promoted to the FROM clause, with all the expressiveness that promotion buys.
-- Latest 5 orders for each active tenant:
SELECT t.id, t.name, o.order_id, o.total, o.created_at
FROM tenants t
CROSS JOIN LATERAL (
SELECT o2.id AS order_id, o2.total, o2.created_at
FROM orders o2
WHERE o2.tenant_id = t.id
ORDER BY o2.created_at DESC
LIMIT 5
) o
WHERE t.status = 'active';Two properties make it more than syntactic sugar. Unlike a correlated subquery in the SELECT list, the lateral subquery can return zero, one, or many rows and as many columns as you like — that is what makes "five orders per tenant" expressible at all. And unlike a window function, the per-group work happens underneath a LIMIT, so the database stops working the moment it has your N rows. The whole trick is giving that LIMIT a cheap path to walk. The cost model falls straight out of the loop reading: driving rows times cost per evaluation. Forty active tenants times a five-row index dive is a few hundred row visits; nothing scans the 60-million-row orders table.
The index this pattern stands on
The subquery has a WHERE on the group column, an ORDER BY on the sort column, and a LIMIT — that is a written order for a composite btree: group column first for the equality, sort column second in the order you want. With DESC specified to match the query, PostgreSQL walks the index forward and never sorts anything.
CREATE INDEX orders_tenant_recent_idx
ON orders (tenant_id, created_at DESC);Read the plan with EXPLAIN (ANALYZE, BUFFERS). What you want to see is a Nested Loop whose outer side is your filtered driving scan and whose inner side is a Limit over an Index Scan using the composite index, with rows=5 and loops equal to the driving count. The BUFFERS numbers are the receipt: a handful of buffer hits per loop, not thousands. The one cost that creeps is heap fetches — each index entry still visits the table row unless the index covers the selected columns. If the table is vacuumed well and the visibility map is hot, index-only scans absorb that; the mechanics are in the notes on index-only scans and the visibility map. For wide SELECT lists, an INCLUDE clause on the composite index buys the same effect at storage cost.
When the window function wins instead
The window function version — row_number() over (partition by tenant_id order by created_at desc), then filter on rn — has a completely different cost model: read every row of orders, sort them unless a matching composite index can feed the WindowAgg in order, and number every partition from its start. Modern PostgreSQL softens the waste with a run condition, so a partition stops producing numbers once rn passes five, but nothing spares the traversal itself: the plan does not care how selective your driving set is, because it has no driving set. That sounds strictly worse, and for the watchlist case it is. But flip the proportions: if you need top-5 for every one of 50,000 tenants, the LATERAL plan performs 50,000 correlated index dives — random I/O, one nested-loop iteration at a time — while the window plan makes one sequential pass over storage that prefetch and parallel workers can chew efficiently. I have benchmarked the crossover on real data, and it usually sits where the driving set drops under a few percent of the groups. Window functions also win when there is no usable index — computed sort keys are the classic case — when N is large relative to group size, and when the query is already a full-table report where the extra numbering rides an existing scan. Pick by the cost model, not by habit.
-- The window function shape: one full pass, then filter.
SELECT tenant_id, id AS order_id, total, created_at
FROM (
SELECT tenant_id, id, total, created_at,
row_number() OVER (
PARTITION BY tenant_id ORDER BY created_at DESC
) AS rn
FROM orders
) s
WHERE rn <= 5;CROSS JOIN LATERAL vs LEFT JOIN LATERAL ... ON true
CROSS JOIN LATERAL behaves like an inner join: a tenant with zero orders disappears from the result. Sometimes that is what you want; for the dashboard it was not — account managers expect to see their quiet tenants too. The idiom is LEFT JOIN LATERAL (...) ON true. The ON true looks like a typo the first time you meet it. It is not: the correlation already lives inside the subquery's WHERE clause, so the join condition has nothing left to say, and true keeps every driving row while letting the lateral side contribute NULLs when it comes back empty. The plan shape stays a nested loop; the subquery still runs per outer row; you simply stop dropping groups with no rows.
-- Keep tenants with no orders, showing NULLs for the order columns:
SELECT t.id, t.name, o.order_id, o.created_at
FROM tenants t
LEFT JOIN LATERAL (
SELECT o2.id AS order_id, o2.created_at
FROM orders o2
WHERE o2.tenant_id = t.id
ORDER BY o2.created_at DESC
LIMIT 5
) o ON true
WHERE t.status = 'active';For the special case where N equals 1, also know DISTINCT ON: SELECT DISTINCT ON (tenant_id) ... ORDER BY tenant_id, created_at DESC reads from the same composite index and is terser. It computes one row per group across the filtered set without per-outer-row correlation, so it composes less flexibly with a selective driving list, but for "latest event per device" over a whole table it is often the cleanest spelling of the three.
Estimates pick the plan before you do
One trap worth naming: once you have written the lateral shape, the planner commits to it using its row estimate for the driving set — and that estimate is a single point of failure. If statistics say 40 tenants match status = 'active' when really 40,000 do, the planner will happily order 40 nested-loop index dives and deliver 40,000, and your 40-millisecond query becomes a 10-second one after a routine ANALYZE with no deploy in sight. When a top-N query suddenly regresses, compare estimated against actual rows on the driving node first — that mismatch, not the join type, is usually the culprit, and better statistics on the driving filter are often the whole fix. The debugging workflow for exactly that mismatch is covered in the notes on join performance and row estimates.
Watching these queries with MonPG
MonPG monitors PostgreSQL in production today, and top-N regressions are exactly the kind of query-level drift its pg_stat_statements history surfaces: the same normalized query, the same call count, mean time up two orders of magnitude after a stats refresh. The PostgreSQL monitoring surface shows how that history is collected and trended. Write the LATERAL shape, give it the composite index, verify the plan with BUFFERS — then let the trends tell you if the planner changes its mind later.