Step 1 · The pattern
Find one customer's orders
The app asks for all orders belonging to one customer. With no index, the database reads every row and keeps the ones that match. On a small table that takes no time at all.
- query
- answered
In short
Index vs scan: how it works and how it breaks
To find rows matching a condition, a database has two choices. It can read the whole table and check every row, a sequential scan. Or it can use an index: a separate sorted structure, usually a B-tree, that leads straight to the matching rows in a handful of steps. On a small table the difference is invisible, which is how missing indexes reach production. The cost of a scan grows with the table. A query that took a millisecond at launch takes a second a year later, and because each one runs longer, more of them overlap. Eventually they use every CPU and connection the database has, and unrelated queries time out too. Adding an index on the filtered column brings the cost back to almost nothing. Indexes are not free: each one slows writes slightly and takes space. Use EXPLAIN to see which plan the database actually chose.
How this comes up in interviews
"A query that was fast at launch now takes over a second and the whole database is timing out. Nobody changed the query. What is going on?"
Then they ask
- How would you confirm which plan the database is using?
- Why does one slow query make unrelated queries on other tables time out?
- If indexes make reads fast, why not index every column?
A strong answer covers
- Without an index the database reads every row, and that cost grows with the table.
- Slower queries overlap, stack up and take every CPU and connection, so unrelated queries time out too.
- An index is a separate sorted structure, usually a B-tree, that leads to matching rows in a few steps.
- Each index slows writes slightly and takes space, so index the columns real queries filter on.
- Use EXPLAIN to see which plan the database chose.
Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.
On one page: the track cheat sheet.
Sources: PostgreSQL documentation: indexes ↗ · PostgreSQL documentation: using EXPLAIN ↗ · GitHub: Partitioning GitHub's relational databases to handle scale ↗ · Report a problem
Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.