SQL Reference ยท Transactions and Performance
SQL sargable predicates
Concept guide to sargable SQL predicates: why wrapping a column in a function stops index use, and how to rewrite WHERE conditions so indexes help.
A predicate is sargable (from "search ARGument ABLE") when the database can use an index to jump straight to the matching rows instead of testing every row. The idea is simple: an index is sorted by the stored column value, so it can only help when the condition compares that raw, unchanged column to a value.
The moment you wrap the column in a function or do arithmetic on it, the database would have to compute the expression for every row before it could compare, and the index, which stores the original values, is no use. The fix is usually to move the work to the other side of the comparison, onto the constant.
-- Not sargable: the function hides the column WHERE strftime('%Y', ordered_at) = '2024' -- Sargable: the column is compared as it is stored WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'
Seeing the difference
The query plan tells you whether an index was used. SEARCH means the index narrowed the rows down; SCAN means every row was read. (The exact wording of the plan can vary slightly between SQLite versions.)
Press Run (or Ctrl + Enter) to see the result here.
The first plan says SCAN orders USING COVERING INDEX: SQLite reads the whole index from start to end (it is narrower than the table, but still every entry). The second says SEARCH ... (ordered_at>? AND ordered_at<?): it jumps into the index and reads only the range. Both queries return the same rows; only the second lets the index do the work. The half-open range (>= start, < next start) also handles timestamps such as '2024-12-31 23:59:59' correctly, which BETWEEN '2024-01-01' AND '2024-12-31' would miss.
Press Run (or Ctrl + Enter) to see the result here.
Common rewrites
| Not sargable | Sargable rewrite |
|---|---|
WHERE YEAR(created) = 2024 |
WHERE created >= '2024-01-01' AND created < '2025-01-01' |
WHERE LOWER(email) = '[email protected]' |
store emails normalized, or create an index on LOWER(email) |
WHERE price * 1.2 > 100 |
WHERE price > 100 / 1.2 |
WHERE name LIKE '%son' |
WHERE name LIKE 'son%' (prefix) or full-text search |
WHERE col + 0 = 5 |
WHERE col = 5 |
WHERE ISNULL(col, 0) = 0 |
WHERE col = 0 OR col IS NULL |
WHERE CAST(id AS TEXT) = '42' |
WHERE id = 42 |
A leading wildcard cannot use an ordinary index, because the index is sorted from the first character. A pattern with a fixed start can. In SQLite, LIKE only uses an index when the column has a matching collation or the case_sensitive_like setting is on; GLOB is easier to optimize.
Indexes on expressions
Sometimes you really need the function. Several databases can index the expression itself, and then the same expression in WHERE is sargable again.
Press Run (or Ctrl + Enter) to see the result here.
-- PostgreSQL, SQLite, Oracle (function-based index) CREATE INDEX idx_customers_lower_email ON customers (LOWER(email)); -- MySQL 8.0.13+ (functional key part needs extra parentheses) CREATE INDEX idx_customers_lower_email ON customers ((LOWER(email))); -- SQL Server: use a computed column, then index it ALTER TABLE customers ADD email_lower AS LOWER(email); CREATE INDEX idx_customers_lower_email ON customers (email_lower);
The expression in the query must match the indexed expression exactly.
Other things that defeat an index
- Implicit type conversion. Comparing a text column to a number (
WHERE phone = 5551234) can force a conversion of every stored value in some databases. Compare with the column's type. ORacross different columns.a = 1 OR b = 2needs one index per branch; without them the database scans.- Low selectivity. A condition that matches most rows (such as
status <> 'archived') is faster to scan; the optimizer is right to ignore the index. - Leading column of a composite index. An index on
(country, city)helpsWHERE country = 'DE', but notWHERE city = 'Berlin'alone. SELECT *. Not a sargability problem, but it stops an index from being "covering".
Dialect notes
The principle is identical in every database. What differs is how well each optimizer rewrites simple cases for you. Some can rewrite col + 0 = 5 or fold constants like 1000000 / 12, but none can undo a function such as YEAR(col) or CAST in general. SQL Server and Oracle report the problem in their plans as a scan or an index scan instead of a seek; PostgreSQL shows Seq Scan instead of Index Scan.
Usage notes
- Check the plan, not the theory. Run the query plan before and after a rewrite; see EXPLAIN.
- Keep columns bare. Put calculations, casts and function calls on the constant side of the comparison whenever you can.
- Use half-open date ranges instead of formatting or extracting date parts.
- Do not over-index. Every index slows writes and uses space; add one for queries that matter.
- Small tables scan fine. With a few hundred rows, a scan can be as fast as an index; the problem shows at scale.
Related
- EXPLAIN, CREATE INDEX, index types, WHERE, LIKE
- Lessons: Indexes, Reading query plans, Query optimization