Explain why applying a function to an indexed column, or comparing an indexed text column to a numeric value, stops a B+Tree index from being used - and what you can do about each case without changing what the query returns.
answer
- sargable = seekable key range
- transformation on column kills the seek
- move work to the constant side
- implicit cast: which side converts?
- expression index as last resort
basics
~20 sA B+Tree is sorted by the raw column value, so it can only seek ranges of that value. A function or an implicit cast on the column produces a different value per row, forcing the engine to compute it for every row. Move the transformation to the constant, or index the expression.
solid answer
~50 sAn index seek works by binary searching a structure sorted on the column's stored value. If the predicate compares a *derived* value - lower(name), a date truncation, a cast to another type - the sorted order of the stored values no longer bounds the answer, so the engine must evaluate the expression row by row. That means a scan. Type mismatches are the sneaky version: comparing an indexed text column to a number makes the engine convert one side, and per-engine type-precedence rules decide which. If the column is converted, the index dies silently; if the literal is converted, everything is fine. It also happens through ORM parameter binding when the driver sends a different type than the column. Fixes, in order of preference: rewrite so the column appears bare and the transformation applies to the constant; fix the parameter or column type so no cast is needed; and only if the transformation is genuinely required, create an index on that expression.
code
sql · 15 lines-- not sargable: function on the indexed column
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2026;
-- sargable: half-open range on the bare column
SELECT * FROM orders WHERE created_at >= DATE '2026-01-01'
AND created_at < DATE '2027-01-01';
-- not sargable: arithmetic on the indexed column
SELECT * FROM items WHERE price * 1.2 > 100;
-- sargable: arithmetic folded into the constant
SELECT * FROM items WHERE price > 100 / 1.2;
-- risky: account_no is VARCHAR, literal is numeric -> engine may cast the column
SELECT * FROM accounts WHERE account_no = 4711;
-- safe: compare like with like
SELECT * FROM accounts WHERE account_no = '4711';go deeper
Say that an index is sorted by the column's actual values, so changing the value with a function means the sort order no longer helps, and give the rewrite of moving the function to the constant.
Add the implicit-cast case, explain that type precedence decides which side is converted, and name expression indexes and normalized columns as the fallbacks.
Talk about detecting the inserted conversion in the plan, driver and ORM parameter typing as the usual production cause, and collation choices as a schema-level fix.
Position it as a schema-contract problem: types and collations agreed across application and database, plus a policy limiting near-duplicate expression indexes and their write amplification.
## What sargable means Sargable, from *search argument able*, describes a predicate the engine can convert into a starting point and a stopping point inside a sorted index. B+Tree leaves hold keys in sorted order with sibling links; a seek descends to the first key satisfying the lower bound and then walks. That only works if the thing being compared is the key itself. ## Functions on the column Consider a filter on lower(email). The index contains email values sorted case-sensitively: 'Alice@x', 'BOB@y', 'alice@x'. The sorted order of the raw values does not determine the order of their lowercased forms, so there is no contiguous run of leaf entries that answers the question. The engine's only correct option is to produce every row, compute lower(email), and test it - a full scan plus a per-row function call. The same reasoning covers arithmetic on the column (price * 1.2 > 100), date truncation (extracting a year from a timestamp), string concatenation, and JSON extraction. Note the asymmetry: price > 100 / 1.2 is sargable because the arithmetic is on the constant, which the engine folds once before the seek. Similarly, a timestamp range with explicit bounds is sargable where a year-extraction on the same column is not. ## Implicit casts SQL comparisons between different types force a conversion. Which operand gets converted is decided by type precedence rules that differ across engines, and the outcome decides whether your index survives: - Column is text, literal is numeric: several engines convert the *column* to a number, disabling the index and, worse, risking runtime conversion errors on rows containing non-numeric text. - Column is a 64-bit integer, literal is a string: engines usually convert the literal, which is harmless. - Fixed-length versus variable-length character types, or different collations on the two sides, can also force per-row work. This is a leading cause of "it was fast in the SQL console and slow from the application": the console sent a literal of the right type while the driver bound a parameter of a different one. The plan text is the evidence - engines print the conversion they inserted around the column. ## Leading wildcards and other shapes A pattern match anchored at the start ('abc%') is sargable because it defines the key range from 'abc' up to the next prefix. An unanchored pattern ('%abc') is not, because matching entries are scattered anywhere in key order. Negations behave similarly: not-equal describes the complement of a narrow range, and reading everything except a slice is normally cheaper as a scan. ## Remedies 1. **Rewrite to bare column.** Move arithmetic and formatting to the constant side, and express date filters as explicit half-open ranges. This is free and keeps a single, general-purpose index. 2. **Fix the types.** Align column type, parameter type, and collation so no conversion is generated. In an ORM this usually means correcting the entity field type or the explicit parameter type, not the SQL. 3. **Store a normalized column.** If the application always searches case-insensitively, keep a normalized column maintained by the database and index that. 4. **Index the expression.** Where the engine supports it, an index built on lower(email) makes that exact expression sargable. The cost is that the index only serves predicates written with the identical expression, and it adds write-time work because the expression is recomputed on every insert and update of the column. 5. **Case-insensitive collation.** Some engines let you declare the column or index with a collation that ignores case, which removes the need to wrap the column at all. ## Two cautions An expression index is not free: it is an extra index with its own maintenance cost, and it is easy to end up with several near-duplicate indexes for slight expression variations. And rewriting must preserve semantics - moving a cast to the other side of the comparison can change which rows match at the boundaries, particularly with rounding, time zones, and lossy conversions. Verify the row count is unchanged before and after any rewrite.
- A query filtering a timestamp column by calendar year does a full scan. How do you make it index-friendly?Replace the year extraction with a half-open range on the bare column: greater than or equal to the start of the year and strictly less than the start of the next year. That gives the B+Tree an explicit lower and upper bound, so it can seek and walk. The half-open form also avoids boundary bugs with sub-second precision that an inclusive upper bound introduces.
- When is an index on an expression the right answer rather than a rewrite?When the transformation is inherent to the business question and cannot be pushed to the constant - case-insensitive or accent-insensitive matching, a computed hash of a long value, or a JSON field extraction. Accept that the index only serves predicates written with the exact same expression, and that every write recomputes the expression. If several variants of the expression are queried, prefer normalizing into a stored column instead of stacking near-duplicate indexes.
saying these in an interview costs you the question
- Believing any WHERE clause mentioning an indexed column can use the index
- Not recognizing implicit type conversion as the cause of a sudden scan
- Assuming the engine will algebraically rearrange a function off the column for you
- Adding an expression index without noticing that the predicate must match the expression exactly
- Rewriting a predicate for performance without verifying the result set is identical