Indexes
An index is the book’s table of contents. Use it.
▶ the lesson · narrated sketch video · tap for sound
the intro
An index is a sorted lookup structure that lets the database find rows without scanning the whole table. Queries on indexed columns go from seconds to milliseconds. The trade: indexes make reads fast and writes slightly slower, so index what you query, not everything.
fun fact ✦
A missing index on one column can be the difference between 4ms and 4 seconds. Same query, same data.
the docs
the deep dive
the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid
Nobody would read a 1,000-page book with no table of contents, yet that is what a database does on every un-indexed query: check every row. An index is a second, sorted structure the engine keeps beside your table, turning full scans into a few hops. The catch: every index must be updated on every write, so you are trading write speed for read speed.
sorted copies that turn scans into seeks
key terms
B-tree
The default index structure: a sorted, branching tree with a handful of hops to any row.
ten million rows: about 4 hopsSeq Scan
The no-index path: read every row, every time. Fine for small tables, fatal for big ones.
the plan says: Seq Scancomposite index
An index on several columns at once. Column order decides which queries it can serve.
INDEX (user_id, created_at)selectivity
How well a column narrows rows down. Indexes on low-selectivity columns barely help.
boolean has 2 values: weakhow it works
- 1Reproduce with EXPLAIN
Run EXPLAIN ANALYZE on the slow query and find the Seq Scan on the big table. That is your target.
- 2Index the filter columns
CREATE INDEX ON orders (user_id) matches the WHERE clause you actually run, not the one you imagine.
- 3Match the column order
A composite on (a, b) serves queries on a or a and b, but never on b alone. Lead with the equality column.
- 4Re-run and compare
The same EXPLAIN should now show an Index Scan and milliseconds. Keep what worked.
- 5Delete unused indexes
Every index taxes every write. pg_stat_user_indexes shows which ones nobody scans.
the code, explained
the before, the fix, the after
side by side
with the index
- lookups seek: milliseconds
- stays fast as the table grows
- every write updates it too
full table scan
- reads every row, every time
- fine under a few thousand rows
- writes stay cheap
Wrapping the column in a function hides it from the index: WHERE lower(email) = ... cannot use a plain index on email. Same for LIKE with a leading wildcard. Build an expression index, or restructure the query.
quick answers
Why is my index not being used?
A function on the column, a leading-wildcard LIKE, or a query returning most of the table — the planner prefers a scan there. Small tables skip indexes too.
How many indexes is too many?
When write latency suffers or indexes go unread. Check pg_stat_user_indexes: idx_scan near zero marks a deletion candidate.
Does UNIQUE need its own index?
It creates one automatically — uniqueness is enforced by the index, which is why duplicate inserts fail instantly.
quick hits about Indexes
short answers · interviews, tips, trivia & hidden gems