Indexing An Expression
A normal index stores raw column values. A function based index, also called an expression index, stores the result of applying a function or expression to one or more columns. The engine indexes the computed value instead of the original.
Why It Is Needed
A predicate that wraps a column in a function usually cannot use an ordinary index. Filtering on the lowercase form of a name, or on the year extracted from a date, transforms the value so it no longer matches the stored keys. The engine falls back to scanning and computing the function for every row.
A function based index on that exact expression restores index access. The same transformation appears in both the index definition and the query, so the planner can seek directly.
Rules And Cautions
- The query expression must match the indexed expression precisely.
- The function must be deterministic: the same input always yields the same output.
- Each write recomputes the expression, adding maintenance cost.
- It is a clean alternative to storing a redundant generated column.
Key idea
A function based index stores the result of a deterministic expression so predicates that transform a column can seek instead of scanning the whole table.