Indexes in Practice — When They're Used (and Ignored)

An index exists — so why is the query still slow?

Having an index doesn't mean the optimizer uses it. The single most useful debugging skill here is knowing when an index applies and when it's silently skipped. The mental model: a B+tree index is a phone book sorted by its columns — you can only seek on a prefix of that sort order.

The leftmost-prefix rule (composite indexes)

An index on (a, b, c) can serve a, a AND b, and a AND b AND c — but never b or c alone, because the data is sorted by a first. Order the columns by what you filter on most (and put equality columns before range columns).

When an index is IGNORED

Covering index = index-only scan

If the index contains every column the query needs, the DB answers from the index alone and never touches the table (no bookmark hop). E.g. for SELECT total FROM orders WHERE user_id=?, an index on (user_id, total) is covering.

EXPLAIN SELECT total FROM orders WHERE user_id = 7;
--  Index Only Scan using orders_user_total on orders   <- covering, no table access
-- vs Index Scan  (then a heap fetch per row)  vs  Seq Scan  (no usable index)

Dense vs sparse: how many index entries per record?

One more axis worth having in your head when you picture "the index": does it carry an entry for every record, or one per block?

A dense index keeps one entry per record: to find key 37 you probe the index and follow the pointer straight to the row — direct hit, but the index is as long as the table. A sparse index keeps one entry per block (each block's first key): finding 37 means taking the largest entry ≤ 37 (here 30), jumping to that block, and scanning forward — 30, 33, 37 — so you trade a short in-block scan for an index smaller by a factor of rows-per-block, often the difference between an index that lives in RAM and one that doesn't. The catch is the precondition: sparse only works when the data file itself is sorted (clustered) on the indexed key, because both "largest entry ≤ k" and the forward scan rely on physical order — which is why a secondary index over an unsorted heap must be dense. A B+tree quietly contains both ideas: its leaf level is a dense index (every key appears once), while the internal levels are a sparse index over the leaves — one separator key per child page. That is the whole trick behind its shape: dense at the bottom for exact lookups, sparse above so the top levels stay small enough to cache.

Pitfalls

• Over-indexing: each index slows every write and costs storage. Index for real query patterns.
• Wrong column order in a composite index makes it useless for your actual filters.
• Trusting it works — always confirm with EXPLAIN (next page).

Takeaways

• Leftmost-prefix: (a,b,c) serves prefixes only; order columns by your filters (equality before range).
• Functions, leading wildcards, type casts, and low selectivity all defeat an index.
• A covering index answers from the index alone — the fastest read.

Re-authored for this guide; phone-book/leftmost-prefix diagram hand-authored as SVG. Follows Use The Index, Luke! (Markus Winand). See also: How Indexes Work, How a Query Executes.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Indexes in Practice — Used vs Ignored

Why this exists / the decision it encodes

Indexes are sorted phone books. The optimizer uses them only when a predicate matches a leftmost prefix and the cost model (stats-driven) believes seeking beats scanning. Creating an index is not a guarantee — EXPLAIN is. Equality-before-range column order is the design decision that makes or breaks composite indexes.

Worked example with numbers or traced SQL/FD

Index (last, first): seek last='Adams'; seek last+first; NOT first alone
Composite equality-before-range worked:
  Index (status, created_at) for WHERE status='open' AND created_at > '2026-01-01'
  status equality first → contiguous slice; then range on created_at
  Reversed (created_at, status): range on created_at first → status filter becomes residual
Ignored: LOWER(email)=?, LIKE '%son', type cast on column, status matching 90% rows
Covering (user_id, total): Index Only Scan for SELECT total WHERE user_id=?
Write cost: each index is O(1) extra B-tree maintenance per row change

When NOT / named alternative

Do not index every column "just in case" — write amplification and bloat. Do not fight a seq scan on 90% selective predicates — the planner is right. Prefer partial indexes (WHERE status='open') when a hot subset is small. Functional index when you must filter LOWER(email).

Failure mode / ops fingerprint / interview trap

Ops: INVALID index after crashed CREATE INDEX CONCURRENTLY — planner ignores it silently. Staging Index Scan vs prod Seq Scan from different stats. Interview: "I added an index so it must be used" — staff rejects.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: indexing is pure query/schema judgment under stats and write cost. K12: concurrent writes bloat MVCC index entries; monotonic keys hotspot the rightmost leaf. Connects to the staff drill ladder already on the page.

Hostile-panel drills (with model answers)

Q1. Leftmost-prefix rule for (a,b,c)?
Model answer: Serves a, (a,b), (a,b,c) predicates. Does not serve b alone or c alone as a seek prefix.

Q2. Why put equality columns before range columns in a composite index?
Model answer: Equality on the leading column selects a contiguous segment; a range on a leading column scatters subsequent equality filters into residual checks and weakens the seek.

Q3. Name four reasons an index is ignored.
Model answer: Function on column; leading wildcard LIKE; implicit type cast on column; low selectivity where seq scan is cheaper; also: wrong column order / not leftmost.