sql-index-tuninglisted

# 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