Indexes, and why a query goes from 4s to 4ms
By the end of today you can explain why an unindexed query on a large table is slow, what a B-tree index physically stores, read an EXPLAIN plan well enough to tell whether an index was used, and say what each index costs you on every write.
YesterdayOn Day 20 you learned that a tree lets you find things without checking every item. On Day 42 you wrote JOINs that quietly scanned whole tables.
TomorrowTomorrow, transactions: what happens when two people write to the rows you just learned to find quickly.
Why this matters
Indexes are the single highest-leverage thing you can understand about databases. Most 'the app got slow' incidents are a missing index, and most 'writes got slow' incidents are too many of them.
- B-tree index
- Sequential scan
- Query plan
- Index write cost
- Composite index
Learn it
80 minCopy this into Claude or ChatGPT. It quizzes you before it explains anything, which is deliberate. The resources under it are how you check what it told you.
Today's Master Prompt
Free · sign inA prompt written for this day alone: your level, the exact scope, what to leave out, and an instruction to quiz you before it explains anything. Paste it into Claude or ChatGPT and it teaches you today's material.
Check it against something that is not a model
An assistant can be fluent and wrong, and on a topic you met today you will not catch it. These cover the same ground and were made by people who do this for a living, so they are what you hold the explanation up against. They are other people's work and we only link to them, so judge them for yourself.
5 hand-picked resources
Free · sign inVideos, official docs and articles covering the same ground, each opened and annotated by hand. They are what you check the assistant against on a day you cannot yet catch it being wrong.
Build it
45 minCreate a local Postgres table with at least 100,000 generated rows (generate_series makes this one statement). Run a query filtering on an unindexed column with EXPLAIN ANALYZE and record the time. Add an index on that column, run the identical query again, and record it. Then insert 10,000 rows and time that, with and without the index in place.
Recall it
20 minAnswer out loud, reveal, then mark honestly whether you had it. That score is the only thing on this page you do not get to choose.
5 recall questions
Free · sign inQuestions you answer from memory, then grade yourself against the real answer. The score is carried into the mastery rating below it, so an honest miss cannot quietly become a tick.
Rate it
Completion and mastery are tracked separately. Be honest, because an inflated rating only means the concept resurfaces sooner.
Mastery tracking
Free · sign inRate yourself against five named criteria per concept. Completion and mastery are tracked separately, and anything you rate shakily comes back automatically on a spaced schedule.
Recap
- 01Without an index the database reads every row, so cost scales with table size
- 02A B-tree stays shallow because each node holds a page's worth of keys
- 03Every index is paid for on every write, forever
- 04Composite indexes only help queries that constrain the leftmost column
Your progress
Free · sign inMark days complete, pick up where you left off across devices, and watch completion and mastery diverge. Free, and the account exists only so ninety days of work cannot vanish with a cleared browser.
Extra time
OptionalOptional. Nothing in a later day depends on anything here.
If you have another hour
Explore what happens with a query that filters on two columns when you have two separate single-column indexes rather than one composite index. Run EXPLAIN and look for 'BitmapAnd', which is the planner combining them. Then compare against a real composite index.