You create an index on a column, but the database still reads the whole table for a query that filters on that column. What are the reasons an optimizer skips a usable index, and how would you narrow down which one applies?

## Two distinct failure modes When a plan shows a full table scan despite a matching index, exactly one of two things happened. 1. The index was **unusable** for that predicate: the engine could not turn the filter into a contiguous range of index keys. 2. The index was **usable but rejected**: the optimizer costed both access paths and judged the scan cheaper. Separating them is the whole diagnosis, because the fixes are opposite. Family one is fixed by reshaping the predicate or the index; family two is fixed by statistics, data distribution, or accepting the plan. ## Family one: the predicate is not searchable A B+Tree stores keys sorted by the column's own value. Any index access must reduce to *find the first key >= X, walk forward until > Y*. Predicates that break that shape are commonly called non-sargable: - **A function wraps the column**, for example lower(email) = '[email protected]' or price * 1.2 > 100. The index holds email, not lower(email); the sort order of raw values tells the engine nothing about the order of transformed values. - **An implicit cast lands on the column side.** Comparing an indexed text column to a number (or vice versa in some engines) makes the engine convert every row's value, which is a function call by another name. If the cast lands on the literal instead, the index still works. Which side gets converted is a per-engine type-precedence rule, so a comparison that is fine in one product silently disables the index in another. - **A leading wildcard** in a pattern match has no known prefix, so there is no entry point into the sorted keys. A trailing wildcard does have one. - **The column is not the leading column** of a composite index, so there is no single start point in key order. - **Negations** (not equal, NOT IN) describe everything except a narrow range, which almost always costs more than reading the table. ## Family two: usable, but costed away Optimizers are cost based. They estimate how many rows a predicate returns, translate that into page reads plus CPU, and pick the cheapest plan. An index loses when: - **Selectivity is poor.** Each matched row that is not already in the index costs a lookup into the table, typically a random page fetch. Once matches exceed a few percent of the table, the scan's cheap sequential reads win. - **The table is tiny.** A three-page table is read entirely for three I/Os; descending the index costs at least root plus leaf plus the row fetch. - **The estimate is wrong.** Stale, missing, or thinly sampled statistics; a value outside the histogram range after a bulk load; correlated predicates whose selectivities the optimizer multiplies as if independent. A ten-thousand-fold underestimate makes a nested-loop-with-index plan look free, and a large overestimate makes it look ruinous. - **Cost-model constants mismodel the hardware.** The assumed price of random versus sequential I/O and the assumed cache hit ratio are configuration. Defaults tuned for spinning disks systematically over-price index access on NVMe. ## A diagnosis workflow 1. Capture the plan with **actual** row counts and timings, not the estimate alone. 2. Compare estimated to actual rows at the filter. Orders of magnitude apart means an estimation problem; close together means the optimizer chose deliberately. 3. Read the predicate as the plan prints it. Engines usually show the cast or function they inserted, which exposes family one instantly. 4. Check table size and the fraction of rows matched. 5. Refresh statistics and re-plan. If the plan flips, you had a statistics problem, not an indexing problem. 6. Only then change the schema: an expression index for the wrapped column, a partial index for a narrow hot subset, or adding the projected column so the lookup disappears. 7. A hint is the last resort, not the first. ## What not to conclude "The index must be corrupt, rebuild it" is rarely correct. A rebuild often appears to fix the problem only because it refreshes statistics as a side effect. Equally, a full scan is not automatically a defect. For a query returning a third of a table, or for a lookup table of two hundred rows, the scan is the right plan and forcing the index makes it slower.