Unit 7: Performance: indexes and query plans
Know why a query is slow, read the plan the database chose, and write queries and indexes that let it find rows without reading the whole table.
The big picture
On six small tables everything is instant. On a hundred million rows, the difference between reading every row and jumping straight to the right ones is seconds versus milliseconds. Interviewers for backend and data roles always ask how you would make a slow query fast.
Lessons in this unit
- Indexes: how a B-tree makes lookups fast: An index is a separate, sorted copy of one or more columns, usually a B-tree, with pointers back to the rows. The database can then jump to the matching rows instead of scanning the whole table.
- Reading a query plan with EXPLAIN: EXPLAIN shows the plan the database chose for a query — which tables it scans, which indexes it searches, how it joins and sorts — without (or, with ANALYZE, while) running it.
- Index-friendly queries: sargable filters and composite indexes: A filter is sargable when the database can use an index for it: the indexed column stands alone on one side of the comparison. A composite index on (a, b) helps queries that filter on a, or on a and b, but not on b alone.
- How a database runs a join: nested loop, hash join, merge join: A database has three main ways to run a join. A nested loop looks up matches in the other table for each row of one. A hash join builds a hash table from one side and probes it with the other. A merge join walks two sorted inputs together. The optimiser picks one for each join.