SQL Performance Lab

What it is

Five pairs of queries. Each pair asks the same question of the same database and returns the same rows; only the way the question is written changes. You press run, both execute at that moment against 2.65 million rows of orders, customers and line items, and the page shows two timings and two real execution plans captured with EXPLAIN (ANALYZE, BUFFERS).

Nothing on that page is a recording. The scenario text, the row counts and the parameter values are read out of the database; the numbers arrive when you click. If the service is asleep the page says so rather than falling back to a figure I typed in earlier — which is the whole point of building it this way.

The five

Deep pagination. OFFSET 500000 does not skip rows, it produces them and throws them away. The keyset version carries the previous page’s last row as a cursor. Same index, same twenty rows, and the scan drops from 500,020 rows to 20.

Aggregation without a covering index. date_trunc('month', placed_at) = … is true for exactly the same rows as a range predicate, but the planner cannot invert a function it has no index on. The fix is the range plus INCLUDE, and the interesting number is not the clock — it is the page count, which falls by two orders of magnitude.

Join through the unindexed column. order_items carries a denormalised copy of order_id with no index on it, the way real tables do after an import. Joining through it forces a hash join over the whole table; joining through the indexed column gives a nested loop over the primary key.

Row by agonising row. A plpgsql loop computing a running total re-sums the quarter on every one of its ninety iterations. Every statement inside it is fast, which is exactly why nothing shows up in a slow query log. A window function does it in one pass.

The EF Core N+1. Forty customers, forty-one round trips. The fix is a filtered Include — but ask instead for the five most recent orders per customer and EF Core 8 translates the Take into a ROW_NUMBER() over the entire orders table, and the single query becomes slower than the loop it replaced. That result is on the page too. “Use Include” is not the lesson; reading the SQL your ORM produced is.

About the engine

My production work is SQL Server. This runs on Postgres, because the demo had to cost nothing and Neon’s free tier does not ask for a card.

Rather than leave that as an apology, every scenario carries a SQL Server block: what the same operator is called there, what changes in the syntax, and where the diagnosis stops carrying across. Some of it is identical — INCLUDE came from SQL Server, and Postgres borrowed it in version 11. Some of it is not: SQL Server has no row-value constructor, so a keyset predicate has to be written as a disjunction; its default window frame is RANGE, which spools to disk where ROWS does not; and the everyday cause of a scan where you expected a seek is CONVERT_IMPLICIT rather than a missing index.

Knowing both planners is worth more than running the one that matches my CV.

What it costs to leave open

It is a public endpoint that executes SQL, so: the queries are compile-time constants on the server and the request carries only a scenario id; the database role is read-only, scoped to one schema, with statement_timeout set to five seconds; runs are rate limited per IP, and the whole service is capped at four concurrent queries because the free-tier compute is a quarter of a vCPU.

The dataset is sized to that constraint too — 273MB of tables and indexes, measured rather than estimated, against a 0.5GB allowance.