Jesse's Notes

Indexes and Query Planning

4 min read

The naive model is “an index makes the query fast.” The accurate model is that an index is a second sorted data structure you are suggesting to the query optimizer — and the optimizer is allowed to say no. If a full table scan looks cheaper given the table’s statistics, it will ignore your index entirely. The index is the suggestion; the execution plan is the decision. The gap between the two is where most “but I added an index!” confusion lives.

See the plan, don’t assume it

The only source of truth is the plan the database actually chose. EXPLAIN shows the estimated plan; EXPLAIN ANALYZE actually runs the query and reports real timings and row counts. Reasoning about indexes without reading the plan is guessing. Type coercion (comparing a string column to a numeric literal) and other plan-killers are invisible in code review and obvious in EXPLAIN.

The three-star model

A useful way to grade an index for a given query:

  • ★ Clustering — equality predicates let matching rows sit contiguously, so the database reads a tight range instead of scattered rows.
  • ★★ Sorting — the index order already matches the query’s ORDER BY, so no separate sort step is needed.
  • ★★★ Covering — every column the query needs is in the index, so the database never touches the table at all (Using index).

The stars compete. A range predicate consumes the index’s ordering, so you often trade the sort star for the selectivity star. There is no free three-star index; there’s the right trade-off for this query.

Leftmost prefix and column order

A composite index on (A, B, C) works like a phone book sorted by last name, then first name. It serves predicates on A, or A and B, or all three — but not B alone. You can’t efficiently find “everyone named Brandon” in a book sorted by surname.

The ordering rule that matters most: equality columns before range columns. (customer_id, created_at) crushes (created_at, customer_id) for “this customer, in this date window,” because the equality on customer_id narrows to a contiguous block that created_at is then already sorted within. The author’s phrase: shape beats cardinality — the order of columns matters more than which column is individually most selective.

Sargability — don’t hide the column

A predicate is sargable (Search ARGument-able) when the index can be used to satisfy it. Wrapping the indexed column in a function destroys that:

  • DATE(created_at) = '2024-06-15' — not sargable; the index on created_at is useless.
  • created_at >= '2024-06-15' AND created_at < '2024-06-16' — sargable; same result, uses the index.

Related limits: B-trees optimize prefix matches ('foo%') but not suffix or infix ('%foo', '%foo%') — those need full-text search. And a type mismatch between column and literal forces a per-row conversion that disables the index.

Selectivity is the currency

An index earns its keep by eliminating rows. A predicate that matches most of the table (say >80%) is low-selectivity — the optimizer will often scan instead, because using the index would mean reading most of it plus the table. What matters is the actual data distribution, not where a column ranks in your mental model.

Every index is a write tax

An index is a sorted structure the database must keep in sync on every insert, update, and delete. More indexes means slower writes — meaningfully so (a common benchmark shows ~5 indexes roughly doubling write cost). So index reactively, not speculatively. The antipattern is the “index shotgun”: adding one for every column anyone might someday filter on, creating redundant indexes that tax all writes to speed up queries that may never run. Add an index in response to a real, measured query, confirm with EXPLAIN that the plan improved, then keep it.

My read

The mental-model correction — index as suggestion, plan as decision — is the whole post for me, and it generalizes past any one database. It pairs almost exactly with “a callback is a suggestion, not a plan” from the same series: in both cases the thing you wrote is an input to a system that decides for itself, and you only learn what actually happened by inspecting the real behavior (EXPLAIN for queries, the bypass list for callbacks). Coming from ORMs, this is the reminder to drop to the plan rather than trust that “there’s an index on that.”

Further reading

Graph View

Backlinks