Greatest-N-Per-Group in Production: MySQL Derived Tables vs PostgreSQL LATERAL

The dashboard was one sentence of business logic: show the current status of every device in the fleet. Four thousand two hundred devices, each writing a status row every thirty seconds into device_status, roughly eleven million new rows a day, and the query behind the dashboard was the classic greatest-n-per-group shape — for each device_id, fetch the row with the latest recorded_at. On the MySQL 5.7 system it was written the way everyone wrote it there: join device_status to a derived table that groups by device_id and takes MAX(recorded_at), then join back to pick up the rest of the columns. It had run at 200 milliseconds when the table held ninety million rows. Two years later, at 2.1 billion rows, it ran at 38 seconds and the operations dashboard finished loading sometime after breakfast.

After the migration to PostgreSQL 16, that query shape lasted exactly one code review. The rewrite to a LATERAL join took the endpoint from a forty-second full-table aggregation to 11 milliseconds, with the same result set and the same index. The important lesson is not that PostgreSQL is faster — it is that the two engines offer completely different machinery for this question, and the MySQL idiom was always a workaround for a feature the engine lacked when the code was written. This article is the field comparison: why the old shape degrades, how LATERAL inverts it, where DISTINCT ON fits, and what MySQL 8 actually fixed.

Why does the join-to-GROUP-BY idiom degrade so badly?

Because the inner query has to compute the answer for every row that will ever exist, not just the rows you want. The derived table SELECT device_id, MAX(recorded_at) FROM device_status GROUP BY device_id must scan and aggregate the entire status table — all 2.1 billion rows — to produce 4,200 group maxima. MySQL materializes that result into a temporary derived table, and then the outer query joins back against the base table to recover the other columns, which costs another 4,200 primary-key lookups. The join-back is cheap; the aggregation is not, and its cost grows linearly with table size even though the size of the answer never changes.

MySQL 8.0 improved the mechanics — derived tables can receive automatically generated indexes, and derived condition pushdown can narrow what gets materialized — but none of that changes the fundamental shape: the engine still visits every status row to compute the maxima, because nothing in the query tells it that one row per device would be enough. An index on (device_id, recorded_at) lets MySQL read the groups in order and sometimes skip the filesort, yet it still walks the whole index. The query is O(table) when the business question is O(devices), and every incident review we ran on this dashboard came back to that mismatch.

How does LATERAL turn the query inside out?

LATERAL lets a subquery in the FROM clause reference columns from preceding FROM items, which means you can run one small, index-driven lookup per device instead of one giant aggregation over all devices:

-- PostgreSQL: one index probe per device, 4,200 probes total
SELECT d.device_id, s.recorded_at, s.status, s.battery_mv
FROM devices d
CROSS JOIN LATERAL (
  SELECT s.recorded_at, s.status, s.battery_mv
  FROM device_status s
  WHERE s.device_id = d.device_id
  ORDER BY s.recorded_at DESC
  LIMIT 1
) s;

-- requires: CREATE INDEX ON device_status (device_id, recorded_at DESC);

Each iteration of the outer loop takes one device_id, descends the composite index to that device’s most recent entry, reads exactly one row, and stops. The plan is a nested loop whose inner side is an index scan with a limit, and its cost scales with the number of devices — 4,200 probes — not with the 2.1 billion rows behind them. This is the same logical trick as the correlated-subquery form, but LATERAL makes it explicit, composable, and able to return multiple columns without repeating the subquery per column.

The behavioral difference that matters operationally: on MySQL the query got slower every single day as the table grew, which is the worst kind of degradation — no release caused it, so no rollback fixes it. On PostgreSQL with the LATERAL shape, the dashboard latency has been flat for a year while the table added another four billion rows. Flat cost curves are what you want from an operational query.

Where does DISTINCT ON fit, and what did MySQL 8 actually change?

PostgreSQL offers a second idiom worth knowing: SELECT DISTINCT ON (device_id) … ORDER BY device_id, recorded_at DESC returns the first row per device after sorting. It is more compact than LATERAL and reads naturally for exactly-one-row-per-group questions, but it sorts or hashes broadly before deduplicating, so at fleet scale it behaves more like the old aggregation than like LATERAL — we benchmarked it at 6 seconds on the same table, acceptable for a nightly report, wrong for a live dashboard. Its other trap is portability: DISTINCT ON is PostgreSQL-only, and the ORDER BY must begin with the DISTINCT ON expressions or the query fails to parse.

Honesty about MySQL 8 matters here, because it closed much of this gap. Window functions arrived in 8.0, so ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY recorded_at DESC) filtered to row one works — the trade-offs of that shape are covered in the window functions comparison. Less known: MySQL 8.0.14 added LATERAL derived tables as well, so the per-device probe pattern ports back to MySQL in principle. In practice we measured MySQL’s LATERAL at about four times slower than PostgreSQL’s on this workload — 47 milliseconds versus 11 — because the optimizer’s costing around lateral derived tables is younger, but both are orders of magnitude better than the materialized-group-by idiom. The window-function route on MySQL landed at 90 milliseconds, still a four-hundred-fold improvement over the legacy query. The real divide is not engine versus engine; it is pre-8.0 idioms versus post-8.0 ones, and an enormous amount of production MySQL SQL was written before 8.0 existed.

How do you verify which shape a query is using?

Read the plan, because both engines will happily run the slow shape without complaint. On MySQL, EXPLAIN showing a derived table with a full scan or full index scan, plus Using temporary or Using filesort, is the aggregation idiom; the tell is the row estimate on the derived table scaling with the base table. On PostgreSQL, the healthy LATERAL plan shows a nested loop whose inner node is an index scan with loops equal to the device count — 4,200 loops of a one-row scan, never one scan of billions of rows. The full plan-reading workflow, including EXPLAIN ANALYZE versus MySQL’s EXPLAIN ANALYZE differences, is in the EXPLAIN comparison. When the query feeds a dashboard, also check the plan after a bulk load or a statistics refresh; the LOAD DATA versus COPY notes cover why plan stability right after a load is exactly when to look.

Where MonPG fits when the dashboard slows down

A greatest-n-per-group query degrading is a silent regression: same code, same index, steadily rising latency, no deploy to blame. MonPG’s PostgreSQL monitoring tracks per-statement latency and buffer-read trends over time, so the day a plan flips from per-device index probes back to a full aggregation — usually after a statistics change or a table crossing a size threshold — the regression appears as a named query with a latency curve, not as a ticket saying the dashboard feels slow again.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will watch: derived-table queries whose examined-row counts grow every day while the returned-row count stays fixed. Until then, the MySQL monitoring page tracks that work. The portable lesson: greatest-n-per-group is a per-group probe problem, and any query that aggregates the whole table to answer it is a latency incident with a two-year fuse.

Related documentation

• MySQL: hash joins
• PostgreSQL: table expressions and LATERAL