# SQL Indexes Under the Hood: Why Your Query Ignores Yours

> An index is a sorted structure with physical properties, not a magic 'go faster' flag. Leftmost prefixes, covering indexes, selectivity, and the honest reasons the planner chose a sequential scan over the index you lovingly created.

URL: https://harshith.in/blog/sql-indexes-under-the-hood/
Author: Harshith Sunku (Staff Engineer) — https://harshith.in/about/
Tags: databases, performance, systems

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."