How do functions or expressions on a column in WHERE affect index usage (sargability)?
Quick Answer
A predicate is **sargable** ("Search ARGument ABLE") if the optimizer can use an index to evaluate it directly. Wrapping an indexed column in a function or expression (`WHERE UPPER(email) = 'X'`, `WHERE price * 1.1 > 100`) usually makes the predicate non-sargable, because the index stores the *raw* column values, not the transformed result — forcing a full scan that computes the function on every row. The fix is either to rewrite the predicate so the raw column is compared directly, or to create a functional/expression index matching the transformation.
Detailed Answer
The problem
CREATE INDEX ix_users_email ON users(email);
-- Non-sargable: the index stores raw 'email' values, not UPPER(email),
-- so the engine can't use the index to jump to matching rows -- it must
-- compute UPPER(email) for every row and compare, i.e. a full scan.
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
Common non-sargable patterns:
WHERE UPPER(last_name) = 'SMITH' -- function wraps the column
WHERE price * 1.1 > 100 -- arithmetic on the column
WHERE YEAR(order_date) = 2024 -- function wraps the column
WHERE SUBSTRING(phone, 1, 3) = '555' -- function wraps the column
WHERE '%' || search_term || '%' LIKE name -- leading wildcard, effectively
Making them sargable
Rewrite to isolate the column — move the transformation to the constant side of the comparison instead:
-- Sargable: 'order_date' itself is compared directly, function only applies to constants
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
-- Instead of price * 1.1 > 100, isolate price:
WHERE price > 100 / 1.1
Or create a matching expression/functional index, if the transformed predicate is unavoidable (e.g., you genuinely need case-insensitive lookups everywhere):
-- PostgreSQL: expression index matches the exact expression used in the query
CREATE INDEX ix_users_email_upper ON users (UPPER(email));
-- Now this query CAN use the index, because the index itself stores UPPER(email)
SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
(SQL Server achieves the same effect with a computed column plus an index on that column; MySQL supports functional key parts directly since 8.0.13.)
Or normalize the data at write time instead of transforming at read time — e.g., store emails already lowercased in a dedicated column, and compare against that directly, avoiding the need for any function at query time.
Why this trips people up
The predicate looks like it's filtering on an indexed column, and many developers assume "there's an index on email, so this must be fast" without noticing the function wrapped around it defeats that index entirely. Always check EXPLAIN when a seemingly-indexed query is slow — a Seq Scan/full scan despite an apparently relevant index is the classic symptom of a non-sargable predicate.