What makes a WHERE predicate sargable, and what shape must the filtered column take?

## What the word means "Sargable" is a contraction of **search argument able**, terminology inherited from early relational-engine work. A *search argument* is something the engine can hand to an index to position itself: a starting point and a stopping condition. A predicate is sargable when it can be reduced to that shape. Anything else is a **filter**: the engine must first produce the row by some other means, then evaluate the expression on it and throw the row away if it fails. This is a property of how you *wrote the predicate*, not of the data and not of the index. It is the one performance lever that lives entirely in the text of the query. ## The shape rule A sargable predicate looks like: ```sql col = 42 col > 100 col BETWEEN 10 AND 20 col IN (1, 2, 3) col LIKE 'abc%' col >= CURRENT_DATE - INTERVAL '7' DAY ``` A non-sargable predicate looks like: ```sql YEAR(col) = 2024 CAST(col AS DATE) = DATE '2024-03-01' UPPER(col) = 'SMITH' col * 100 > 500 col + INTERVAL '30' DAY < CURRENT_DATE SUBSTRING(col FROM 1 FOR 3) = 'ABC' COALESCE(col, 0) > 10 ``` The test is mechanical: **if the column sits inside a function call or an arithmetic expression, the predicate is not a range over that column.** Note that the *other* side may be as complicated as you like — `CURRENT_DATE - INTERVAL '7' DAY` is fine, because it is computed once, before any row is touched, and does not mention the column. Two related shapes fail for the same underlying reason. `WHERE col_a > col_b` mentions two columns of the same row, so neither index gets a constant boundary. And a comparison that forces the *column* to be converted to another type — a numeric literal against a character column, say — is the engine wrapping your column in a cast for you; that is the type-mismatch case, and the cure is the same rule: keep the column bare and put the conversion on the literal. ## Why the engine cannot see through the function An index on `col` stores `col`'s values in sorted order. It does not store `UPPER(col)` or `YEAR(col)`. To use the index for `YEAR(col) = 2024` the engine would have to *invert* the function — work out which stored values map to 2024 — and there is no general way to invert an arbitrary expression. So the planner treats `f(col)` as an opaque value it can only compute row by row. Some engines special-case a handful of functions, and some let you index the expression itself, but nothing about that is portable: assume no engine sees through the wrapper unless you have proved otherwise on yours. ## What it actually costs Correctness is untouched — the non-sargable form returns exactly the same rows. What changes is the access path. On a fifty-million-row table it is the difference between reading a handful of index pages and reading every page of the table while calling a function on each row. There is a second, quieter cost: the optimizer normally keeps statistics on *columns*, not on expressions, so it falls back to a default guess for `f(col) = x`. A bad row estimate propagates into join order and memory sizing, so the damage can extend well past the one table you filtered. ## The rewrite catalogue Almost every non-sargable predicate falls into one of four repairs: 1. **Truncation → half-open range.** `CAST(ts AS DATE) = DATE '2024-03-01'` becomes `ts >= DATE '2024-03-01' AND ts < DATE '2024-03-02'`. 2. **Arithmetic → move it to the constant side.** `price * 1.2 > 120` becomes `price > 120 / 1.2`; `d + INTERVAL '30' DAY < CURRENT_DATE` becomes `d < CURRENT_DATE - INTERVAL '30' DAY`. 3. **Prefix extraction → prefix match.** `SUBSTRING(code FROM 1 FOR 3) = 'ABC'` becomes `code LIKE 'ABC%'`. 4. **Not invertible at all** (`UPPER`, `COALESCE`, a hash) → normalize the data on write and compare the bare column, or move the fix into the schema with an indexed expression or generated column. ## Sargable is necessary, not sufficient A sargable predicate is not a promise of an index seek. There must actually be an index whose leading column is the one you filtered, and the predicate must be selective enough that seeking beats scanning; a filter matching a third of a large table is often served faster by reading the table straight through, and that is a legitimate plan. The point of sargability is narrower and worth stating precisely: it keeps the *option* open. A non-sargable predicate takes the choice away from the engine before it has made it.