/sql-index-tuning

name: sql-index-tuning
description: Diagnose slow SQL queries and add the right indexes without over-indexing — B-tree mechanics, composite ordering (equality-before-range), the leftmost-prefix rule, covering/index-only scans, reading EXPLAIN ANALYZE, selectivity, and write-amplification costs. Worked SQL examples and a runnable index-suggester.
type: skill
version: 2.0.0
updated: 2026-06-29

SQL Index Tuning

Indexing is the highest-leverage performance work in most data-backed systems: the right index turns a multi-second sequential scan into a sub-millisecond lookup, and the wrong one quietly taxes every write forever. This skill is the deep reference for doing it deliberately — how B-tree indexes actually work, how to order composite columns, how to read a query plan, and when an index is the wrong answer. Heavy detail lives in `references/`; copy-paste DDL and plans in `examples/`; a runnable index-suggester in `scripts/`.

Mental model

An index is a **sorted, redundant copy** of one or more columns plus a pointer back to the row. The database maintains that sort order on every write so reads can binary-search instead of scanning. Two consequences follow directly and explain almost every tuning decision:

| Property | Why it matters | |---|---| | Indexes are **sorted** | They serve equality, range, prefix, `ORDER BY`, and `MIN/MAX` from order alone | | Indexes are **redundant** | Every `INSERT`/`UPDATE`/`DELETE` must also update them — pure write cost | | Indexes are **left-anchored** | A composite `(a, b, c)` can seek on `a`, `a,b`, `a,b,c` — never `b` or `c` alone |

Keep both halves in mind at once: you are buying read speed with write speed and storage. The job is to buy only the indexes that pay for themselves.

1. B-tree basics

The default index type everywhere (`CREATE INDEX` builds a B-tree unless you ask otherwise) is a balanced tree whose leaf nodes hold sorted key values. Lookups are `O(log n)` — a few page reads even for billions of rows. Because the leaves are sorted and linked, a B-tree efficiently serves:

• **Equality**: `WHERE status = 'open'`
• **Range**: `WHERE created_at > '2026-01-01'`, `BETWEEN`, `<`, `>`
• **Sorted output**: `ORDER BY created_at` with no separate sort step
• **Prefix matches**: `WHERE email LIKE 'jay%'` (but not `'%jay'` — a leading wildcard cannot seek)

It cannot help with operations that don't respect that ordering: `WHERE lower(email) = ...` (unless you index the expression), `!=`, or `%suffix` searches. Full mechanics, page splits, and why a low-cardinality boolean is a poor leading column are in [`references/btree-internals.md`](references/btree-internals.md).

2. Composite indexes & the leftmost-prefix rule

A composite index `(a, b, c)` is sorted by `a`, then `b` within equal `a`, then `c`. This is the single most misunderstood part of indexing. The index can satisfy a predicate only as a **left-anchored prefix**:

-- Index: (tenant_id, status, created_at)
WHERE tenant_id = 9 AND status = 'open'                  -- uses (tenant_id, status)      ✓
WHERE tenant_id = 9 AND status = 'open' AND created_at>… -- uses all three                ✓
WHERE tenant_id = 9                                      -- uses (tenant_id)              ✓
WHERE status = 'open'                                    -- CANNOT seek (skips tenant_id) ✗
WHERE tenant_id = 9 AND created_at > …                   -- seeks tenant_id, filters rest ◑

**Column ordering rule: equality columns first, then one range column, then columns needed only for sort.** A range predicate (`>`, `<`, `BETWEEN`) "uses up" the index — columns after the range column can no longer be used for seeking, only as a filter. So `(tenant_id, status, created_at)` is right for `tenant_id = ? AND status = ? AND created_at > ?`, but putting `created_at` before `status` would waste the `status` equality. Full reasoning, plus covering indexes and index-only scans, in [`references/composite-and-covering.md`](references/composite-and-covering.md).

3. Covering indexes & index-only scans

If an index contains **every column a query touches** (in `SELECT`, `WHERE`, and `ORDER BY`), the engine answers from the index alone and never visits the table heap — an **index-only scan**. This eliminates the random I/O of fetching rows and is often a 5–50x win on hot read paths.

-- Query reads only id, tenant_id, status:
SELECT id, status FROM orders WHERE tenant_id = 9 AND status = 'open';

-- Covering index (Postgres INCLUDE keeps non-key columns in the leaf):
CREATE INDEX ix_orders_cover ON orders (tenant_id, status) INCLUDE (id);

The trade-off: covering indexes are wider, so they cost more to write and store. Add them only for high-frequency queries you have measured. See `examples/index-ddl.sql` for `INCLUDE` vs composite forms.

4. Reading EXPLAIN / EXPLAIN ANALYZE

`EXPLAIN` shows the planner's chosen plan and **estimated** cost; `EXPLAIN ANALYZE` actually runs the query and reports **real** timings and row counts. Always tune against `ANALYZE` output. What to look for:

• **`Seq Scan` on a large table** under a selective `WHERE` — the prime candidate for an index.
• **`rows` estimate vs `actual`** wildly diverging — stale statistics; run `ANALYZE <table>`.
• **`Sort` / `Hash` nodes with high cost** — an ordered index may remove the sort.
• **`Index Scan` vs `Index Only Scan`** — the latter means your index is covering.
• **`Rows Removed by Filter`** — rows read then thrown away; a better index seeks past them.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders WHERE tenant_id = 9 AND created_at > now() - interval '7 days';

A full annotated walkthrough — before/after plans for the same query — is in `examples/explain-walkthrough.sql` and the node-by-node guide in [`references/reading-explain.md`](references/reading-explain.md).

5. Selectivity & cardinality

**Selectivity** is the fraction of rows a predicate keeps; **cardinality** is the number of distin