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 it is written changes. Press run, both execute 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 here is recorded: scenario text, row counts and parameter values are read from the database, and the numbers arrive when you click. If the service is asleep, the page says so instead of falling back to a figure typed in earlier.

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 to 20.

Aggregation without a covering index. date_trunc('month', placed_at) = … is true for 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; the page count 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; through the indexed column it’s 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 — fast statement by statement, which is exactly why it never 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 for the five most recent orders per customer, and EF Core 8 translates the Take into a ROW_NUMBER() over the entire orders table, ending up slower than the loop it replaced. “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.

Every scenario carries an equivalent SQL Server block: what the operator is called there, what changes in the syntax, and where the diagnosis stops carrying across. INCLUDE, for instance, came from SQL Server and Postgres borrowed it in v11; SQL Server, on the other hand, has no row-value constructor (a keyset predicate becomes a disjunction), its default frame is RANGE (which spools to disk where ROWS does not), and the everyday cause of an unexpected scan is CONVERT_IMPLICIT, not a missing index.

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

What it costs to leave open

It is a public endpoint that executes SQL: 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 a five-second statement_timeout; runs are rate limited per IP, and the 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, against a 0.5GB allowance.