A query filters on a computed value — for example the lowercased form of an email column — and the plain index on that column is never used. Explain why, and how an expression (function-based) index fixes it.

## Why a plain index cannot help An index is a lookup structure over stored key values. A B+tree on an `email` column holds the email strings in sorted order. That order is only meaningful for predicates that compare `email` itself. Now consider a predicate comparing `lower(email)` to a value. Lowercasing is not order-preserving in the tree's terms: the stored ordering interleaves `Ann@x` and `ann@x` differently from how the lowercased forms would sort, and more fundamentally, the value being compared — the lowercased string — is not present in the index at all. To evaluate the predicate the engine must apply the function to every row's value, which means reading every row. The index is unusable, and you get a sequential scan with a filter. The general property is often called *sargability*: a predicate is usable by an index when the indexed key appears on its own on one side of the comparison. Wrapping the key in a function destroys that. Common examples: casting a column to another type, extracting a date part from a timestamp, concatenating two columns, applying a string transformation, or arithmetic on a numeric column. ## What an expression index does An expression index tells the engine: compute this expression for every row and index the results. The keys stored are the computed values. Structurally it is an ordinary index — same tree, same statistics machinery — just with derived keys. With the keys being lowercased emails, a predicate on the lowercased email is now a direct probe. Ranges and ordering on the computed value work too, because the derived keys are stored in order. ## The two rewrites and when to prefer which There are usually two ways out of a non-sargable predicate: 1. **Rewrite the query** so the column stands alone. A predicate extracting the year from a timestamp can become a half-open range between two timestamps; a predicate on a cast can sometimes compare against a correctly-typed literal instead. This is preferable when possible — it costs nothing at write time and uses the index you already have. 2. **Index the expression** when the transformation is genuinely part of the semantics: case-insensitive matching, normalising a phone number, indexing a value pulled from a document column, or a computed classification. If option 1 exists, take it. Reach for the expression index when the transformation cannot be inverted into a range or a plain comparison. ## Matching is the failure mode The planner uses an expression index only when it can recognise the query's expression as the indexed one. That recognition is largely syntactic after normalisation, so mismatches are common and silent: - A different but equivalent function, or the same function with different arguments. - Argument order or an extra cast introduced by parameter typing. - Collation or locale differences between how the index was built and how the query compares. - Applying the function to the *literal* instead of the column, which is a different expression entirely. Because nothing errors when the match fails — you just get a scan — the discipline is to verify the plan after creating the index, and to keep the application's expression identical to the index definition, ideally by generating both from one place. ## Cost side Every insert and every update of a column the expression depends on re-evaluates the expression. For cheap string functions this is negligible; for expensive ones it becomes a real write tax, and it lands on the hot path of the transaction rather than in the background. The expression must also be deterministic — the same input must always give the same output — because the stored key is computed once and trusted forever. That constraint deserves its own treatment. ## Relationship to generated columns An alternative is to materialise the computed value into a stored column maintained by the engine, then index that column normally. The two approaches carry the same evaluation cost; the differences are that a stored column consumes table space and can be selected directly, whereas an expression index keeps the derived value only inside the index, and can therefore let a query read it without touching the table when the index covers the query. Engine support for each varies, so it is a per-engine decision. ## Combining with a predicate Expression indexes and partial indexes compose: you can index a computed value over only a subset of rows, for example the normalised identifier of rows that are still active. That gives both the small-index benefit and the computed-key benefit in one structure — with the corresponding requirement that the query must both match the expression and imply the predicate. ## How to answer Name the mechanism (index stores raw values; a function produces values not stored), name the fix (index the expression's results), then immediately name the two caveats an interviewer is waiting for: the expression in the query must match the indexed expression, and the expression is evaluated on every write.