Database indexes: the B-tree from first principles
Build the B-tree up from why it exists, then the eight concrete reasons the database ignores the index you added.
Indexes I — the B-tree from first principles, and the eight reasons yours is ignored
The sentence to own: an index is a data structure, not a setting. You do not "turn on" an index any more than you turn on a hash map. You build a specific structure with specific ordering, it answers specific questions in logarithmic time, and it answers every other question not at all.
And the corollary that separates people who have tuned a database from people who have read about it: when Postgres ignores your index it is almost always right, and your job is to find out what it knew that you didn't.
Part 1 — The structure, and the arithmetic that makes it shocking
A POSTGRES B-TREE IS A B+TREE: ALL DATA LIVES IN THE LEAVES, and the
leaves are a DOUBLY LINKED LIST.
┌───────── ROOT (1 page) ─────────┐
│ ... | 4200 | 8900 | ... │
└────┬───────┬────────┬───────────┘
┌─────────┘ │ └─────────┐
┌───▼───┐ ┌───▼───┐ ┌───▼───┐ INTERNAL
│ ..... │ │ ..... │ │ ..... │ (1–2 levels)
└───┬───┘ └───┬───┘ └───┬───┘
┌─────▼─────┐ ┌──────▼────┐ ┌───────▼───┐
◄──► │ k→ctid ×N │◄──►│ k→ctid ×N │◄──► │ k→ctid ×N │ ◄──► LEAVES
└───────────┘ └───────────┘ └───────────┘
EACH LEAF ENTRY IS (KEY, ctid) — the ctid is the physical
address of the row: (page number, item number).
THE FANOUT ARITHMETIC. This is the whole reason databases work.
8 KB page ÷ ~20 bytes per entry ≈ ~400 ENTRIES PER PAGE
height 1 → 400 rows
height 2 → 160,000 rows
height 3 → 64,000,000 rows
height 4 → 25,600,000,000 ROWS
⇒ FINDING ONE ROW IN A BILLION-ROW TABLE IS ***FOUR PAGE READS***.
And the top two levels are permanently in shared buffers, so it
is really two reads, one of which is usually cached too.
⇒ THIS IS WHY "ADD AN INDEX" TURNS 4 SECONDS INTO 0.2 ms. Not a
percentage improvement — a change of complexity class, O(n) → O(log n).
THE FOUR THINGS THE STRUCTURE GIVES YOU, AND THEY ARE NOT OBVIOUS:
① EQUALITY: `WHERE id = 42` — descend. obvious.
② RANGE: `WHERE created_at > X` — descend ONCE, then WALK
THE LEAF LIST. The linked leaves are why ranges are cheap.
③ SORT ORDER FOR FREE: the leaves ARE sorted, so
`ORDER BY created_at DESC LIMIT 10` reads TEN ENTRIES and stops.
No sort node, no `work_mem`, no disk spill. THIS IS THE
SINGLE MOST UNDERAPPRECIATED PROPERTY OF AN INDEX — people think
"indexes speed up WHERE", and half the wins are ORDER BY + LIMIT.
④ MIN/MAX: `SELECT max(created_at)` is one descent to the last leaf.
AND THE THING IT CANNOT DO: answer a question whose ordering it does
not have. An index on `(a)` cannot help `ORDER BY b`. There is no
"partial credit" — this is a data structure, not a hint.
Part 2 — Three ways an index gets used, and why the third exists
① INDEX SCAN — descend, then for EACH match fetch the heap page.
RANDOM IO PER ROW. Excellent for 1 row, terrible for 500,000.
② INDEX ONLY SCAN — every column the query needs is IN the index,
so the heap is never touched. 10–100× less IO.
THE CATCH NOBODY EXPECTS: the index does not know whether a row
is VISIBLE to your transaction — visibility lives in the heap
tuple header (Day 186). SO POSTGRES CONSULTS THE *VISIBILITY
MAP*, and only skips the heap for pages marked all-visible.
⇒ AN INDEX-ONLY SCAN ON A RECENTLY-WRITTEN TABLE STILL HITS
THE HEAP, AND `EXPLAIN` SAYS SO: `Heap Fetches: 48213`.
⇒ THE FIX IS `VACUUM`. Which is Day 194, and this is the
first place you will feel it.
③ BITMAP HEAP SCAN — the compromise for "many but not most" rows:
build a bitmap of matching PAGES in memory, then read those pages
IN PHYSICAL ORDER (sequential-ish IO instead of random).
IT IS ALSO HOW POSTGRES COMBINES TWO INDEXES — `BitmapAnd` /
`BitmapOr`. Seeing a bitmap scan is not a problem; seeing
`Recheck Cond` with a huge `lossy` count means it ran out of
`work_mem` and degraded to page granularity.
Part 3 — The eight reasons your index is ignored
① You wrapped the column in a function
-- ❌ THE INDEX ON `email` IS USELESS HERE:
WHERE lower(email) = 'a@b.com'
-- The index stores `email`, not `lower(email)`. Postgres cannot invert
-- an arbitrary function. IT IS NOT BEING STUPID — IT LITERALLY DOES
-- NOT HAVE THAT ORDERING.
CREATE INDEX ON users (lower(email)); -- ✅ an EXPRESSION INDEX
-- THE SNEAKY ONES — a function you did not notice you wrote:
WHERE date(created_at) = '2026-08-17' -- ❌ function on the column
WHERE created_at::date = '2026-08-17' -- ❌ same thing, cast
WHERE created_at >= '2026-08-17' -- ✅ SARGABLE
AND created_at < '2026-08-18'
WHERE amount / 100 > 50 -- ❌
WHERE amount > 5000 -- ✅ move the maths
WHERE age(dob) > interval '18 years' -- ❌
WHERE dob < now() - interval '18 years' -- ✅
-- THE RULE: ***KEEP THE COLUMN BARE ON THE LEFT.*** The word for a
-- predicate that can use an index is SARGABLE. Use it; interviewers
-- notice.
② A type mismatch you cannot see
-- `user_id` is bigint, you pass a string, ORM or hand-written:
WHERE user_id = '42' -- FINE — literal is coerced to bigint
-- BUT: joining `varchar` to `int`, or `numeric` to `bigint`, can add
-- an implicit cast ON THE COLUMN SIDE, and that is case ①.
-- ⇒ IN `EXPLAIN`, LOOK FOR `::text` OR `::numeric` AROUND YOUR
-- COLUMN IN THE FILTER. That cast is the whole bug.
-- Most common real cause: two tables that model the same id with
-- different types because two people created them a year apart.
③ LIKE '%foo' — and the LIKE 'foo%' trap that follows
WHERE name LIKE '%smith' -- ❌ NO B-TREE CAN DO THIS. A B-tree is
-- sorted by PREFIX. A trailing anchor is
-- a different question entirely.
-- Day 190: trigram index. Day 199: FTS.
WHERE name LIKE 'smith%' -- ✅ a prefix IS a range scan …
-- … UNLESS YOUR DATABASE IS IN A NON-C COLLATION, which it is.
-- Then the index's sort order isn't byte order and the prefix trick
-- doesn't apply. THE FIX:
CREATE INDEX ON users (name text_pattern_ops);
-- THIS IS ONE OF THE MOST COMMON "MY INDEX ISN'T USED AND I CANNOT
-- SEE WHY" CASES IN THE WILD, AND IT IS INVISIBLE IN THE SQL.
④ Low selectivity — and here the planner is smarter than you
`WHERE status = 'active'` where 70% of rows are active.
AN INDEX SCAN WOULD DO 700,000 RANDOM PAGE FETCHES.
A SEQUENTIAL SCAN READS 1,000,000 ROWS IN PAGE ORDER, WITH
READAHEAD, AND IS ***GENUINELY FASTER***.
⇒ THE RULE OF THUMB: above roughly 5–10% of the table, a
sequential scan wins. The exact number depends on `random_page_cost`.
⇒ SO "IT'S DOING A SEQ SCAN" IS NOT A DIAGNOSIS. THE QUESTION IS
ALWAYS "HOW MANY ROWS DOES IT EXPECT, AND IS THAT ESTIMATE RIGHT?"
The fix when you DO need it: a PARTIAL index (Day 190) —
`CREATE INDEX ... WHERE status = 'pending'` — because the 2% case
is the one worth indexing.
⑤ The table is small
Under ~1,000 rows the whole table is a handful of pages and lives in
shared buffers. A SEQ SCAN IS CORRECT. This is why "it uses
the index in production but not in my test database" happens, and it
is why performance tests on 100 rows tell you nothing.
⑥ Statistics are stale or too coarse
-- The planner does not count rows; it ESTIMATES from a sample stored
-- in `pg_statistic`: histogram, most-common-values, n_distinct.
ANALYZE orders; -- refresh them
SELECT last_analyze, last_autoanalyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname = 'orders';
-- `n_mod_since_analyze` LARGE + `last_autoanalyze` OLD ⇒ THE PLANNER
-- IS WORKING FROM FICTION. Classic after a bulk load or a big
-- backfill: the table has 50M rows and the stats say 1,000.
-- AND THE SUBTLER ONE: CORRELATED COLUMNS.
WHERE city = 'Chennai' AND country = 'India'
-- Postgres assumes independence and multiplies the selectivities, so
-- it estimates ~0 rows and picks a nested loop that runs 400,000 times.
CREATE STATISTICS city_country (dependencies)
ON city, country FROM addresses; -- EXTENDED STATS
ANALYZE addresses;
-- THIS FIXES A WHOLE CLASS OF "WHY IS IT PICKING A NESTED LOOP"
-- THAT NO AMOUNT OF INDEXING WILL TOUCH. Very few people know it.
-- For a skewed column, raise the sample:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
⑦ Wrong leading column (composite indexes)
INDEX ON (a, b) — THINK OF IT AS A PHONE BOOK SORTED BY
(SURNAME, FIRST NAME).
`WHERE a = ?` ✅ `WHERE a = ? AND b = ?` ✅
`WHERE a > ? ORDER BY b` ⚠️ partial
`WHERE b = ?` ❌ FIND EVERYONE CALLED "PRIYA" IN A
PHONE BOOK SORTED BY SURNAME. You read the whole book.
⇒ THE LEFTMOST-PREFIX RULE. Day 190 derives the ordering
properly, including the equality-then-range rule.
⑧ OR, NOT, <>, and IS NOT NULL
WHERE a = 1 OR b = 2
-- Postgres CAN handle this with a BitmapOr over two indexes — but
-- only if BOTH columns are indexed. One missing index and the whole
-- thing is a seq scan. Rewriting as a UNION of two indexed queries
-- is sometimes dramatically faster; measure both.
WHERE status <> 'done'
-- INEQUALITY IS RARELY SELECTIVE — it usually means "most of the
-- table". If `done` is 95% of rows, invert it: a PARTIAL INDEX
-- `WHERE status <> 'done'` is tiny and perfect.
Part 4 — Reading the evidence instead of guessing
-- 1. IS THE INDEX USED AT ALL, EVER? (the highest-value query here)
SELECT relname, indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- EVERY ROW HERE IS AN INDEX THAT COSTS YOU WRITE THROUGHPUT, DISK
-- AND VACUUM TIME AND HAS NEVER ONCE BEEN READ. Reset the counters
-- (`pg_stat_reset()`), wait a full business cycle including the
-- monthly report, THEN drop. Never drop on a week of data.
-- 2. WHICH TABLES ARE BEING SCANNED?
SELECT relname, seq_scan, seq_tup_read, idx_scan,
seq_tup_read / NULLIF(seq_scan, 0) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 10;
-- HIGH `seq_scan` ON A SMALL TABLE IS FINE. High `seq_tup_read` on a
-- big one is where your IO went.
-- 3. THE DIAGNOSTIC THAT IS NEVER A FIX:
SET enable_seqscan = off; -- session only
EXPLAIN ANALYZE SELECT ...;
-- THIS DOES NOT DISABLE SEQ SCANS. It adds a huge cost penalty so
-- the planner avoids them if it can. USE IT TO ANSWER ONE
-- QUESTION: "WHAT WOULD THE INDEX PLAN HAVE COST?" If the forced
-- index plan is SLOWER, the planner was right and you can stop.
-- If it is 50× faster, you have an estimation problem — go to ⑥.
-- NEVER SHIP THIS SETTING. It is a probe, not a remedy.
Part 5 — What an index costs, and building one on a live table
INDEXES ARE NOT FREE. THE BILL, IN FOUR PARTS:
① EVERY INSERT WRITES EVERY INDEX. 6 indexes ⇒ 7 structures updated
per row, each with its own WAL. Rule of thumb: each index adds
roughly 5–10% to insert cost, more if the key is random (UUID v4!).
② THE ONE PEOPLE MISS — HOT UPDATES.
Postgres can do a "Heap-Only Tuple" update — a new row version on
the SAME page with NO index updates — BUT ONLY IF NO INDEXED
COLUMN CHANGED.
⇒ INDEXING `updated_at` (WHICH CHANGES ON EVERY UPDATE)
DESTROYS HOT FOR THE ENTIRE TABLE. Every update now writes every
index. People add that index for a sort they run twice a day
and pay for it on every write, forever.
③ DISK AND CACHE. An index on a big table can exceed the table. It
competes for the SAME shared buffers your queries need.
④ VACUUM. Every index must be scanned during vacuum (Day 194).
SO THE QUESTION IS NEVER "WOULD AN INDEX HELP THIS QUERY?" (usually
yes). IT IS "DOES THIS QUERY MATTER ENOUGH TO TAX EVERY WRITE?"
-- BUILDING ON A LIVE TABLE — Day 138's lesson, one more time:
CREATE INDEX ON orders (user_id);
-- TAKES A SHARE LOCK: READS CONTINUE, ***ALL WRITES BLOCK*** FOR THE
-- WHOLE BUILD. On 100M rows that is minutes of failed checkouts.
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
-- ✅ Two table passes, waits for old transactions, ~2–3× slower.
-- AND THE THREE THINGS THAT BITE:
-- (a) CANNOT RUN INSIDE A TRANSACTION BLOCK ⇒ in Alembic you need
-- `with op.get_context().autocommit_block():`
-- (b) IF IT FAILS IT LEAVES AN ***INVALID*** INDEX BEHIND —
-- NOT used by queries, but STILL UPDATED ON EVERY WRITE. The
-- worst of both worlds, and silent.
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
-- ⇒ DROP INDEX CONCURRENTLY, then rebuild.
-- (c) It can be blocked indefinitely by a long-running transaction.
-- THE FOREIGN KEY INDEX POSTGRES DOES NOT CREATE FOR YOU:
-- A PRIMARY KEY gets an index automatically. A FOREIGN KEY DOES
-- NOT. So `DELETE FROM users WHERE id = 5` must scan `orders` for
-- referencing rows — a full scan, while holding locks.
SELECT c.conrelid::regclass AS table, a.attname AS unindexed_fk_column
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
AND NOT EXISTS (SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND i.indkey[0] = c.conkey[1]);
-- RUN THIS ON YOUR CAPSTONE. You will find some.
# In SQLAlchemy / Alembic, so it is in the schema and not in someone's
# psql history:
class Order(Base):
__tablename__ = "orders"
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
__table_args__ = (
Index("ix_orders_user_created", "user_id", "created_at"),
Index("ix_orders_email_lower", func.lower(email)), # expr
Index("ix_orders_pending", "created_at",
postgresql_where=text("status = 'pending'")), # partial
)
# AND IN THE MIGRATION, FOR ANY TABLE THAT IS NOT EMPTY:
# op.create_index(..., postgresql_concurrently=True) + autocommit_block
Common mistakes
| Mistake | Correction |
|---|---|
| Treating "seq scan" as a diagnosis | Ask how many rows it expects and whether the estimate is right. |
WHERE date(created_at) = ... |
A function on the column kills the index. Use a half-open range. |
WHERE amount / 100 > 50 |
Move the arithmetic to the constant side. |
Expecting LIKE '%x' to use a B-tree |
Prefix-sorted structure, trailing anchor. Use trigram or FTS. |
Expecting LIKE 'x%' to work in any collation |
Needs text_pattern_ops outside the C collation. |
| Indexing a column where 70% of rows match | The seq scan is genuinely faster. Consider a partial index on the rare value. |
| Testing index behaviour on 100 rows | Under ~1,000 rows a seq scan is correct. You've proven nothing. |
Forgetting ANALYZE after a bulk load |
The planner works from fiction until autoanalyze catches up. |
| Assuming the planner understands correlated columns | It multiplies selectivities. Use CREATE STATISTICS. |
Indexing (a, b) and querying on b |
Leftmost-prefix rule. It's a phone book sorted by surname. |
Shipping enable_seqscan = off |
It's a probe that answers "what would the index plan cost", not a fix. |
Indexing updated_at casually |
It changes on every update, which destroys HOT updates for the whole table. |
| Dropping unused indexes after a week | Wait a full business cycle including monthly reports. |
CREATE INDEX on a large live table |
Blocks all writes for the build. Use CONCURRENTLY. |
Not checking for invalid indexes after a failed CONCURRENTLY |
An invalid index is never used but still updated on every write. |
| Assuming foreign keys are indexed | Only primary keys are. Unindexed FKs make parent deletes scan the child table. |
| Expecting an index-only scan on a hot table | Visibility comes from the heap. Watch Heap Fetches; the fix is VACUUM. |
Interview questions
Q: How does a B-tree index actually make a query fast?
It's a B+tree: internal nodes are just a routing map, all the keys live in the leaves, and the leaves are a doubly linked list. The arithmetic is the striking part — an 8 KB page holds roughly four hundred entries, so four levels address about twenty-five billion rows, which means finding one row in a billion-row table is four page reads, and the top two levels are permanently cached. That's not a percentage improvement, it's a change of complexity class from O(n) to O(log n), which is why adding an index turns four seconds into a fraction of a millisecond. The linked leaves are what makes range scans cheap — you descend once and walk — and the property people undervalue most is that the leaves are already sorted, so
ORDER BY indexed_col LIMIT 10reads ten entries and stops instead of sorting the table. A lot of index wins are ordering wins, not filtering wins.
Q: Postgres is ignoring my index. Why?
First I'd assume it's right, because it usually is, and check what it estimated versus what it got in
EXPLAIN ANALYZE. The most common real causes are: a function or a cast wrapping the column, so the predicate isn't sargable —date(created_at) = '...'instead of a half-open range; a leading wildcard in aLIKE, which no prefix-sorted structure can serve; low selectivity, where the index would do seven hundred thousand random page fetches and a sequential scan with readahead is genuinely faster; a small table, where the whole thing is a few cached pages; stale statistics after a bulk load, where the planner thinks the table has a thousand rows; and the leftmost-prefix rule on a composite index. The subtle one worth mentioning is correlated columns — filtering on city and country, Postgres assumes independence and multiplies selectivities, estimates almost no rows, and picks a nested loop that runs hundreds of thousands of times. No amount of indexing fixes that;CREATE STATISTICSdoes. And I'd useSET enable_seqscan = offas a probe to price the index plan, never as a fix — if the forced plan is slower, the planner was right and I stop.
Q: What does an index cost?
Four things. Every insert writes every index, with its own WAL, so roughly five to ten percent per index and worse for random keys like UUID v4 where each insert lands on a different page. Disk and cache, since an index on a wide key can be bigger than the table and competes for the same shared buffers. Vacuum time, because every index is scanned. And the one people miss — heap-only tuple updates. Postgres can update a row in place on the same page without touching any index, but only if no indexed column changed. So putting an index on
updated_at, which changes on every single update, disables HOT for the entire table and makes every update write every index. People add that index for a report they run twice a day and pay for it on every write forever. So the question isn't "would an index help this query" — it almost always would — it's "does this query matter enough to tax every write".
Q: How do you add an index to a 100-million-row table in production?
CREATE INDEX CONCURRENTLY, because the plain form takes a lock that blocks all writes for the entire build, which on that size is minutes of failed checkouts. Concurrently does two table passes and waits for old transactions, so it's two or three times slower, and it can't run inside a transaction block — in Alembic that means anautocommit_block. The part people get caught by is that if it fails, it leaves an invalid index behind: queries won't use it, but every write still maintains it, so you get the cost with none of the benefit and nothing tells you. So the runbook includes checkingpg_indexforNOT indisvalidafterwards and dropping concurrently before retrying. I'd also check whether a long-running transaction is going to block it indefinitely before starting.
Q: Are foreign keys indexed automatically?
Primary keys are, foreign keys aren't — and that asymmetry surprises people. The referencing column has no index unless you create one, which means every delete or key update on the parent has to scan the entire child table looking for references, while holding locks. On a big child table that turns a routine delete into a multi-second operation that blocks other work, and it's the sort of thing that only shows up when someone deletes a user for the first time in production. There's a catalogue query over
pg_constraintandpg_indexthat lists every unindexed FK in the database, and I'd run it as part of a schema review — most codebases have several.
Mini task
- Load 5M rows. Time
WHERE id = 4712with and without the primary key index (use a copy without one). Compute the ratio. - Compute the fanout for your own table:
pg_relation_size(index) / 8192pages, and the tree height frompgstattuple/pageinspect. Compare with the 400-per-page estimate. - Write a query where
ORDER BY x LIMIT 10uses the index. Then drop the index and compare — find theSortnode and itsSort Method. - Force an index-only scan and read
Heap FetchesinEXPLAIN ANALYZE. RunVACUUM, re-run, and watch it drop to zero. - Reproduce all eight ignore-reasons deliberately, one query each, and record the
EXPLAINline that proves it. - Fix the function case with an expression index, and confirm the plan changes.
- Create a
LIKE 'x%'query in a non-C collation, watch it seq-scan, and fix it withtext_pattern_ops. - Build the correlated-columns case (city/country), read the estimated vs actual rows, then add
CREATE STATISTICSand re-read the plan. - Bulk-load 10M rows, immediately run a query, then
ANALYZEand run it again. Diff the plans. - Measure insert throughput with 0, 3 and 6 indexes on the same table. Then add an index on
updated_atand measure update throughput before and after — that's HOT. - Run the unindexed-foreign-key catalogue query against your capstone. Index one and time a parent delete before and after.
- Start
CREATE INDEX CONCURRENTLYon a big table and kill it halfway. Find the invalid index inpg_index. Drop it concurrently.
Exit questions
Answer aloud, no notes.
- Why is a B+tree the right structure, and what do the linked leaves buy you?
- Do the fanout arithmetic aloud: how many page reads to find one row in a billion?
- Name the four questions an index answers, and the one people forget.
- What's an index-only scan, why can it still hit the heap, and what fixes that?
- When is a bitmap heap scan the right plan, and what does "lossy" mean in one?
- Define sargable. Give three non-sargable predicates and their rewrites.
- Why can't a B-tree serve
LIKE '%x', and what breaksLIKE 'x%'? - At roughly what selectivity does a seq scan win, and why is that correct rather than stupid?
- What does
n_mod_since_analyzetell you, and what doesCREATE STATISTICSfix that indexing can't? - State the leftmost-prefix rule with the phone-book analogy.
- What is
enable_seqscan = offfor, and what is it not for? - Give the four costs of an index, and explain HOT.
- Why is indexing
updated_atusually a bad trade? - What are the three hazards of
CREATE INDEX CONCURRENTLY? - Which constraint type gets an index for free, which doesn't, and what does that cost?
- What must you wait for before dropping an index with
idx_scan = 0?
Articulation drill
Two minutes: "Explain database indexes."
Don't start with "they make queries faster". Start with the structure and the arithmetic, because
that's what makes it real: a B+tree with about four hundred entries per page, four levels covering
twenty-five billion rows, so one row in a billion is four page reads and two of them are cached. Then
name what that structure can and can't answer, and make the point people miss — sorted leaves mean
ORDER BY ... LIMIT is free, so half of index wins are ordering wins. Then flip to the honest half:
when the planner ignores your index it's usually right, and go through the two most instructive
cases — a function on the column making the predicate non-sargable, and low selectivity where seven
hundred thousand random fetches lose to a sequential scan with readahead. Finish on cost, because
that's what shows you've run one of these in production: every insert writes every index, and
indexing updated_at disables heap-only-tuple updates for the whole table, so you pay on every write
for a report you run twice a day.
Previous: Day 188 · Tomorrow: Day 190 — Indexes II: composite column order derived properly, partial and covering indexes, and the five index types that aren't B-trees