Indexes & Query Plans

This cover names the most counterintuitive fact about indexing: the majority of index problems aren't a missing index, they're an existing index the planner won't use. The index is right there, perfectly built, and the query still does a full scan because of one small mistake in how the query or schema is written.

Post five is the field guide to those traps. Each one survives code review because the query looks correct and even returns the right answer — it's just secretly slow. These are the bugs that surface as a 3am page months after they shipped.

The first and most common trap: wrapping the indexed column in a function or expression. An index on email stores the raw email values in sorted order. The moment you write WHERE LOWER(email) = '...', the engine can no longer use that sorted order, because it would have to compute LOWER on every stored value to compare — which means reading every row, a full scan.

The same trap appears with date arithmetic (WHERE created_at + interval '1 day' > now()), type casts, and string concatenation on the column. The rule: keep the indexed column bare on one side of the comparison, and move any transformation to the other side or into the index definition.

This snippet shows the trap and two fixes. The bad query applies LOWER to email, disabling the plain index. Fix A creates an expression index on LOWER(email) — now the engine has a sorted structure of lowercased emails to seek into, and the query is fast again. Fix B avoids the problem entirely by storing emails already normalized to lowercase, so no function is ever needed at query time.

Which fix to choose depends on your data. If you sometimes need the original casing, an expression index is right. If email should always be lowercase, normalizing on write is cleaner and removes a whole class of bugs. Both restore index usage; neither requires touching the query's logic.

The second trap is the leading-column rule for composite indexes. An index on (a, b) is sorted by a first, then by b within each a. That structure can seek efficiently for queries that filter on a, or on a and b together. But a query filtering only on b cannot use it — there's no way to seek to a b value without first knowing a, just as you can't find everyone named 'James' in a phone book sorted by last name.

This catches people who create one composite index and assume it helps all queries touching either column. It doesn't. You design composite indexes around the actual leading-column access patterns, and you may need a separate index on b alone if you frequently filter on b by itself.

The decision tree makes the leading-column rule unambiguous. If the query filters on a and also on b, the (a, b) index is fully used. If it filters on a only, the index still helps — it seeks on a, which is most of the benefit. But if it filters on b without a, the index is unusable and you fall back to a full scan.

Trace your real queries through this tree before creating a composite index. The branch that bites people is the bottom one: a perfectly good (a, b) index that does nothing for the team's most common query because that query only knows b. The tree turns an abstract rule into a quick yes/no check.

The third trap is over-indexing — the belief that more indexes are always safer. Every index must be updated on every insert, update, and delete to the indexed columns, so each one adds write overhead. An index the planner never chooses is pure cost: it slows writes and consumes storage while delivering zero read benefit.

The healthy practice is to index for observed query patterns, then periodically audit for indexes that are never used and drop them. Indexes accumulate over a project's life as features come and go; the ones left behind from removed features quietly tax every write. Pruning them is real, easy performance work.

This query is the audit tool for the previous slide. Postgres tracks how many times each index has been used in pg_stat_user_indexes; idx_scan is that counter. An index with idx_scan = 0 has never been chosen by the planner since stats were last reset — a strong candidate to drop.

Use it with judgment: confirm the counter has accumulated over a representative period (a full traffic cycle, not five minutes after a restart), and watch for indexes backing constraints you still need. But for a mature system, this one query reliably surfaces dead weight that's been silently slowing your writes.

The fourth trap is indexing low-selectivity columns — columns with few distinct values, like a status of 'active' or 'inactive', or a boolean flag. Selectivity is the fraction of rows a typical value narrows to. An index pays off when a value matches a small slice of the table; if a value matches half the rows, seeking the index and then fetching all those rows is more work than just scanning.

The planner knows this and will correctly refuse the index, leaving you with a full scan and the write cost of an index that does nothing. Reserve indexes for high-selectivity columns — IDs, emails, timestamps — where a lookup genuinely isolates a handful of rows.

The bar chart ranks index benefit by selectivity. A unique email column scores high — any value isolates one row, so the index is invaluable. A country column is middling: it narrows the set, but a popular country still matches many rows, so the planner uses it only sometimes. A boolean is_active flag scores near zero — it splits the table into two huge halves, so an index on it is almost always useless.

The takeaway is to estimate selectivity before creating an index. Ask: for a typical value, what fraction of rows match? If it's a large fraction, skip the index. Selectivity, not intuition, decides whether an index will ever be chosen.

The fifth trap is trusting plain EXPLAIN. EXPLAIN shows the plan the planner intends to use along with its estimated costs and row counts — but those are predictions, not measurements. A plan can look perfectly reasonable on paper while hiding a row-count mis-estimate that only manifests when the query actually runs.

EXPLAIN ANALYZE removes the doubt by executing the query and reporting actual times and row counts beside the estimates. The discipline is to never declare a query 'fixed' based on EXPLAIN alone; run ANALYZE and confirm the actual numbers match what you expected. Estimates lie just often enough to matter.

This final snippet shows the verification habit. EXPLAIN (ANALYZE, BUFFERS) runs the query and adds buffer statistics so you can see how many pages were read — a direct measure of I/O. You compare the estimated rows in the plan to the actual rows reported; a large gap is the signature of stale statistics.

When you see that gap, ANALYZE on the table refreshes the planner's statistics from the current data, which usually realigns its estimates and lets it choose better plans. This pair — diagnose the estimate gap with EXPLAIN ANALYZE, fix it with ANALYZE — resolves a large share of 'I have the right index but it's still slow' mysteries.

This recap turns the five traps into a checklist: no functions on indexed columns, composite indexes need their leading column, drop indexes the planner never uses, skip low-selectivity columns, and verify with EXPLAIN ANALYZE rather than plain EXPLAIN. Run a slow query through this list and you'll find the cause more often than not.

These five mistakes account for the bulk of real-world index problems precisely because each leaves a correct-but-slow query that passes review. Knowing them turns vague 'the database is slow' reports into specific, fixable diagnoses.

That completes the deep dive on indexes and query plans — from the B-tree and the planner, through real tuning, to the traps that defeat good indexes. Day 84 opens a new chapter: transactions, locking, and concurrency control — how the database keeps your data correct when many users write at once.