Indexes and what makes a query slow
A sorted lookup table, so the database stops reading every row.
What problem does an index solve?
Without an index, answering "find the rows where email is this" means reading the whole table. At a thousand rows nobody notices. At fifty million rows it is why your page takes nine seconds.
An index is a second structure, kept in sorted order, that turns that full scan into a few jumps.
What an index costs you
Every concept in here states a cost. A technique with no price is a technique you have not understood yet — and naming the cost out loud is most of what separates a senior answer from a junior one.
When to reach for an index, and when not to
Use it when
- A column shows up in a WHERE, JOIN or ORDER BY, on a table big enough to matter.
- You need to enforce uniqueness. A unique index is how you reliably stop duplicate emails.
- A query reads only a few columns, and you can answer it entirely from the index without touching the table.
Do not, when
- The table is small. Reading 5,000 rows is fast, and the database may ignore your index anyway.
- The column has very few different values, like a true/false flag, and the query matches most rows. A full scan is cheaper.
- The table takes heavy writes and the index is rarely used. You are taxing every write to speed up a query nobody runs.
How an index actually works
log2 of 50 million is about 26, so a sorted lookup needs about 26 comparisons. A B-tree node holds hundreds of keys, so those comparisons touch only 3 or 4 pages. Doubling the table adds one comparison, not double the work. That is why an index feels like magic, and why missing one is invisible until the table grows.
The default index is a B-tree. It is sorted, so it handles exact matches, ranges and ORDER BY from the same structure. A hash index only handles exact matches. An inverted index maps each word to the documents that contain it, which is how text search works. A geospatial index sorts by location, so "near me" becomes answerable.
Composite indexes have one rule that catches people out. An index on (country, city, name) can answer a query that filters on country, or on country and city, or on all three. It cannot answer one that filters on city alone. Think of a phone book sorted by surname then first name: it is useless for finding everyone called James. So put the column you always filter on first, and put exact-match columns before range columns.
The usual reasons a query is slow, in the order to check them. There is no index on the column you filter by. There is one, but the query hides it: wrapping the column in a function, or comparing two different types, stops the database using it. The filter matches most of the table, so a full scan is the right plan. You are sorting a large result the index cannot give you in order. You are paging with a large OFFSET, which makes the database walk past and throw away everything before it. Or the query is fine and it returns 100,000 rows over the network, which is a payload problem, not a database one.
The N+1 query is worth naming because it is so common. You fetch 100 orders, then loop and fetch each order's customer one at a time. That is 101 round trips where one join, or one batched lookup, would do. It is hard to see in development, where tables are small, and expensive in production.
When asked how you would debug it, say EXPLAIN. Read the plan. Look for a full scan on a large table, and check whether the estimated row count matches reality. A plan built on out-of-date statistics chooses badly.
- Adding an index on every column "to be safe". You have now made every write slower, for queries nobody runs.
- Assuming an index exists because the column is important. It exists because someone created it.
- Missing the N+1, because each single query looks fast in the log.
The an index follow-up an interviewer will ask
"One user reports a slow request. How do you debug it?"
Work down the stack instead of guessing. First: is it slow for everyone, or only for them? A chart of that endpoint's p50 and p99 answers this in seconds. If p99 is bad but p50 is fine, it depends on the data, which means that user has far more rows than a typical user. Second: get a trace of the request and see where the time went. Was it one slow call, or two hundred fast ones? Two hundred fast ones is the N+1. Third: if it is one slow query, run EXPLAIN with their parameters, not with typical ones, because the plan can be different. A common outcome: they are a heavy account, the query has no index for their combination of filters, and the database switched to a full scan once their row count passed a threshold. The fix is usually an index or a change to paging. The cost of that index is slower writes on the table.
Your turn
You have an index on (userid, createdat). Which of these two queries uses it — filtering by userid alone, or filtering by createdat alone? Why?
Learn it from the source
This page gets you interview-ready. These take you to where the idea is actually specified or measured — read them when you want the real depth.
- Use The Index, Luke! ↗Markus Winand's free book on SQL indexing. The best explanation of why your index is not being used.
- PostgreSQL — Index Types ↗B-tree, hash, GiST, GIN and BRIN, and what each one is for.