Indexes & Query Plans
Read EXPLAIN ANALYZE line by line, turn a 2-second sequential scan into a sub-millisecond index lookup, and learn why the planner sometimes ignores your index, or trusts statistics that lie.
An interactive System Design lesson: 23 steps, about 35 minutes, on a live simulation in your browser.
The shop's database is PostgreSQL 17 with 1,000,000 users and 10,000,000 orders. Every table has its primary key and nothing else. The "My orders" page runs one query: SELECT * FROM orders WHERE user_id = 42.
It returns 5 rows and takes 2.1 s. To find them, Postgres read every one of the table's 93,458 pages (8 kB each, about 730 MB) from disk and threw away 9,999,995 rows that did not match. That is a Seq Scan: start at the first page, stop at the last, test every row.
What you will learn
The slow query
- Two seconds to find five orders: Without an index, a WHERE clause is a filter applied to every row in the table. The cost grows with the table, not with the answer.
- Reading EXPLAIN ANALYZE: Each plan line is a guess followed by the truth: estimated cost and rows, then actual time, rows and loops. The bug is usually where the two disagree.
- Run it again
A B-tree index
- Build the index: A B-tree is a sorted copy of one column with pointers back to the rows. Three levels cover ten million keys; a lookup reads one page per level, then the row.
- The same query, with the index
- Index Cond, Recheck, and read= becoming hit=: Index Cond means the index found the rows; Filter means rows were fetched and then thrown away. A plan with a large "Rows Removed by Filter" is reading too much.
- Drill: index without blocking writes
Sorting without sorting
- The ten newest orders: ORDER BY … LIMIT without a matching index still reads the whole table: the database cannot know the top ten without seeing every row.
- An index that is already sorted
When the index is ignored
- Break it: a function on the column: An index serves the exact expression it was built on. Wrap the column in a function, a cast or arithmetic, and the index is invisible to that query.
- Index the expression
- Break it: a leading wildcard: A B-tree answers equality, ranges and prefixes, because those follow its sort order. Anything that starts with a wildcard needs another kind of index.
- Two columns, one index: A composite index works left to right: equality on the leading columns, then at most one range. A query that skips the first column cannot use it as a search.
- An index on a common value: An index pays off when it rules most of the table out. For a value that matches a large share of rows, a Seq Scan is the correct plan, not a missed index.
What an index costs
- Every index taxes every write: Reads pay for missing indexes; writes pay for every index that exists. Each one costs every INSERT, most UPDATEs and every DELETE.
When the plan is wrong
- Break it: fifty fast queries: N+1 is invisible query by query: each statement is fast. The cost is in the count of round trips, so you find it by counting queries per request, not by reading plans.
- One query instead of fifty
- Break it: statistics that lie: The planner plans from statistics, not from the data. When the estimated and actual rows on the same line differ by orders of magnitude, suspect stale statistics before anything else.
- ANALYZE, then plan again
Finding slow queries
- The slow-query log: Find slow queries from evidence: the slow-query log for statements over a threshold, pg_stat_statements for total time per query shape. Then EXPLAIN (ANALYZE, BUFFERS) the worst one.
- Drill: ask for the real plan
Recap & playground
- Cheat sheet
- Playground