A query an agent wrote
It compiles, it returns the right rows on your laptop, and it passes review because the reviewer read the SQL and not the plan. The thesis track.
14 min · runs against the events dataset
Every model writes SQL, all day, and writes it badly in a specific and predictable way: correct on a hundred rows, catastrophic on ten million. The failure is not that the query is wrong. It is that it is right, and expensive, and nothing in the review process distinguishes the two.
The habit worth building is to read a generated query for the shape of what it will do before running it. Six shapes account for almost all of it, and every one of them is visible in the text.
- *A function around an indexed column.*
WHERE lower(email) = …,WHERE created_at::date = …,WHERE id + 0 = …. The index stores the column, and you asked about something else. - *`OFFSET` pagination.* Fine on page one, quadratic across the list, and the last page is the slowest request in the application.
- *An `IN` list with thousands of elements.* Each element is an equality; a few thousand of them and the planner correctly decides a sequential scan is cheaper — which is the right decision to a badly-shaped question.
- *`SELECT ` in a hot path.** It reads columns nobody wants and, more importantly, makes an index-only scan impossible: the index cannot cover a query that asks for everything.
- *A correlated subquery per row.* An N+1 written in one statement. Look at
loops. - *`ORDER BY random()`.* Sorts the entire table to return one row.
Two of the six in one query. Both are visible without running it.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| SortSort Key: idSort Method: quicksort Memory: 2kB | 40 | 20 | 1 | 0 | |
| → Seq Scan on eventsFilter: (lower(email) = '[email protected]')Rows Removed by Filter: 7,980 | 40 | 20 | 1 | 91 |
Now the same question, asked in a way the database can answer. Same rows, and a different order of magnitude.
The column itself, and only the columns wanted.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| SortSort Key: idSort Method: quicksort Memory: 2kB | 20 | 20 | 1 | 0 | |
| → Bitmap Heap Scan on eventsRecheck Cond: (email = '[email protected]') | 20 | 20 | 1 | 20 | |
| → Bitmap Index Scan on events_email_idxIndex Cond: (email = '[email protected]') | 20 | 20 | 1 | 3 |
The one that is correct and takes ninety seconds
The hardest case is the query with no mistake in it at all. The estimate matches the actual, the plan is the cheapest available, every index that could help exists — and it takes ninety seconds because the question genuinely requires reading that much data. No amount of tuning fixes it. The fix is to ask a different question, or to have precomputed the answer, and recognising which case you are in is most of the value of being able to read a plan.
Did it land?
- A query an agent wrote
An agent produced `SELECT * FROM events WHERE lower(email) = '[email protected]' ORDER BY id`. It returns the right rows. Write the query that returns the same id, actor_id and happened_on — and lets the database use the index it already has.