Databases
Indexes: multiple-choice review
Crux Multiple-choice synthesis across the indexes unit — leading-column rule, partial and covering indexes, index-only scans and the Visibility Map, index-type selection, and production failure modes.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. None is a definition to recite — each mirrors a design call or an incident you weigh under real query load, where the wrong index costs storage, write throughput, or a 3am page.
Goal
Confirm you can connect B-tree anatomy, the leading-column rule, partial and covering indexes, index-only scans, index-type selection, and production failure modes — the synthesis the seven lessons built toward.
Quiz
Completed
A multi-tenant dashboard has an index on (workspace_id, status, created_at). A new analytics query runs WHERE status = 'pending' ORDER BY created_at DESC with no workspace_id filter, and it does a sequential scan. What is the fix that respects the unit's design discipline?
Heads-up That breaks every existing tenant-scoped query that filters on workspace_id first. You design composites per dominant query, not by reshuffling one index to chase a new access pattern — add a second index instead.
Heads-up Stale stats cause wrong row estimates, not a structural inability to use the index. No prefix of (workspace_id, status, created_at) starts with status, so the index offers no entry point regardless of statistics.
Heads-up Postgres has no index hints, and the index does not cover a status-only filter — status is the second key column, unreachable without the leading workspace_id. The leading-column rule is structural, not a planner preference.
Quiz
Completed
A 100M-row tasks table is 80% status = 'done' (never queried by the dashboard). The dashboard only reads open and in_progress tasks. Which index strategy minimizes both size and write overhead?
Heads-up A full index covers all 100M rows including the 80M done rows the dashboard never reads — roughly 5× larger than needed, and every write to a done row still maintains it. The partial predicate eliminates that waste.
Heads-up 20% of 100M is 20M rows, but the dashboard filters further (per-workspace, per-project) to a tiny selective slice. A partial index on the hot subset is exactly what the planner wants there.
Heads-up BRIN stores min/max per block range and only helps when data is physically ordered by the indexed column. status is low-cardinality and unordered on disk; every block range spans all statuses, so BRIN gives no pruning.
Quiz
Completed
EXPLAIN (ANALYZE, BUFFERS) shows Index Only Scan with Heap Fetches: 12000 on an orders table, and the query is slow despite the right covering index. What is happening and what is the durable fix?
Heads-up If columns were missing the plan node would be Index Scan, not Index Only Scan. An index-only scan with heap fetches is a Visibility Map problem, not a covering problem.
Heads-up An INVALID index is invisible to the planner — the plan would show a Seq Scan, not an Index Only Scan. The scan type proves the index is valid and chosen.
Heads-up Row count does not decide heap fetches; the Visibility Map bit per page does. An append-mostly table returning millions of rows can still show Heap Fetches: 0 if VACUUM keeps the VM current.
Quiz
Completed
A DELETE FROM posts WHERE id = 42 with ON DELETE CASCADE takes 7 minutes and holds locks the whole time, on a posts/comments schema. What is the root cause?
Heads-up posts(id) is the primary key and is already indexed. The cascade scans the child table comments by post_id; it is the unindexed referencing column, not the referenced one, that forces the O(n) scan.
Heads-up Indexing comments(post_id) turns the cascade lookup into an index scan. The slowness is a missing index, not an intrinsic property of cascades.
Heads-up VACUUM reclaims dead tuples and helps visibility, but it does not give the cascade a way to find child rows by post_id. Only an index on comments(post_id) fixes the scan.
Quiz
Completed
orders.user_id is BIGINT with an index on orders(user_id). A query runs SELECT * FROM orders WHERE user_id = '42' (the literal is TEXT) and does a Seq Scan. Why, and what is the fix?
Heads-up Bloat slows an index but does not flip the plan to Seq Scan for a selective equality. The cause here is the cast disabling index use; EXPLAIN shows the coercion in the filter.
Heads-up B-tree handles BIGINT equality perfectly. The problem is the TEXT literal forcing a per-row cast of the column, not the index type.
Heads-up That works around the symptom by indexing the wrong thing. The correct fix is to send the right type so the existing index is used — don't add an index to compensate for a type bug in the query.
Quiz
Completed
A search feature runs WHERE title ILIKE '%invoice%' on a 50M-row table and takes 12 seconds. The query is keyword-style search over document text. What is the right index choice?
Heads-up A leading wildcard '%invoice%' has no known prefix, so a B-tree (which needs a prefix to navigate) cannot help — it is always O(n). That is exactly why the query is slow.
Heads-up HNSW does semantic similarity and needs an embedding pipeline per document and query. For exact keyword search it is the wrong tool and far more expensive; tsvector GIN is the default.
Heads-up BRIN summarizes min/max per block range for physically-ordered numeric or time data. It cannot answer substring or keyword containment on text at all.
Recap
The unit’s through-line is one design loop: a composite is usable only from a leading prefix, so design it around the always-present filter; partial predicates cut a hot-subset index to a fraction of full size and skip writes for cold rows; INCLUDE plus a current Visibility Map make an index-only scan skip the heap; the index type must match the data shape (B-tree for ordered values, GIN for text and JSONB, HNSW for semantic); and the recurring failures — missing FK index, type coercion, stale stats, leading-wildcard ILIKE — all resolve back to matching the index to the exact question the query asks, verified with EXPLAIN (ANALYZE, BUFFERS).