Every backend engineer has lived this: query is slow, add an index, query is still slow, EXPLAIN says Seq Scan, and the database appears to be trolling you. It isn’t. An index is a physical data structure — a B-tree of sorted key values with pointers to rows — and every rule about when it helps falls out of that physicality. Learn the structure once and index design stops being cargo cult.

What the Structure Can and Cannot Do

An index on (last_name, first_name) is entries sorted by last_name, ties broken by first_name — a phone book. The phone book answers some questions brilliantly and others not at all:

text
  WHERE last_name = 'Chen'                        ✓ seek to Chen block
  WHERE last_name = 'Chen' AND first_name = 'Li'  ✓ seek exactly
  WHERE last_name LIKE 'Ch%'                      ✓ range scan
  WHERE first_name = 'Li'                         ✗ Li's are everywhere
  WHERE UPPER(last_name) = 'CHEN'                 ✗ sorted by last_name,
                                                    not UPPER(last_name)

That’s the leftmost prefix rule and the function-blindness rule in one picture, and together they explain most “why is my index unused” tickets. Corollaries worth their weight: composite column order is a design decision (equality columns first, then the range/sort column); WHERE created_at + interval '1 day' > now() disables an index that WHERE created_at > now() - interval '1 day' uses; and expression indexes (CREATE INDEX ON users (lower(email))) exist precisely to make the phone book sorted by the thing you actually ask.

The other physical fact: the index stores pointers, and following them costs. A secondary-index lookup is two structures: descend the B-tree, then fetch the row from the heap/table — random I/O per row. Which sets up the two most consequential ideas in index economics:

  • Selectivity. If the predicate matches 30% of a big table, millions of random heap fetches lose to one sequential scan of everything — sequential bandwidth is that much cheaper than scattered reads. The planner knows this arithmetic, which is why it ignores your index on low-selectivity predicates (status = 'active' matching 90% of rows was never going to use that index). Rule of thumb: indexes earn their keep below a few percent selectivity, partial indexes (WHERE status = 'pending') rescue the skewed cases.
  • Covering. If every column the query touches lives in the index, the heap trip disappears — an index-only scan. INCLUDE columns (Postgres) / wide composites exist to buy this: turning SELECT user_id, total FROM orders WHERE user_id = ? into a pure index read is routinely a 10× on hot paths. (Postgres wrinkle: the visibility map must be current — a heavily-updated table degrades index-only scans back to heap checks until vacuum catches up.)

The Ledger Has a Debit Side

Indexes are a copy of data, maintained synchronously: every INSERT updates every index; every UPDATE that touches an indexed column does (Postgres’s HOT optimization dodges this only for non-indexed columns). Ten indexes on a hot table means write amplification you chose. Auditing is mechanical, not moral: pg_stat_user_indexes (or sys.dm_db_index_usage_stats, or the MySQL equivalent) shows scan counts — indexes with zero reads and millions of writes are pure debt. Duplicate-prefix indexes ((a) alongside (a,b)) are the most common finding; the shorter one is usually free to drop.

And when EXPLAIN still surprises you after all this: the planner runs on statistics, not on your table. Stale stats after a bulk load (ANALYZE), correlated columns the estimator multiplies independently (extended statistics exist for this), or a parameter it couldn’t sniff — the cure is reading EXPLAIN (ANALYZE, BUFFERS) and comparing estimated vs actual rows at each node. A 1000× row misestimate names the problem faster than any amount of index shuffling.

Takeaways

  • An index is a sorted structure: leftmost prefixes and un-transformed columns only — expression indexes when your predicate disagrees.
  • Composite order: equality columns first, range/sort last; (a,b) serves a alone, never b alone.
  • Secondary lookups pay a random heap fetch per row — low selectivity makes seq scans correct, and covering/INCLUDE indexes delete the fetch entirely.
  • Every index taxes every write; read usage stats and drop the freeloaders.
  • EXPLAIN ANALYZE’s estimated-vs-actual rows is the diagnostic; stale or naive statistics explain most planner “mistakes.”