Indexing The Result
An expression index, also called a functional index, indexes the output of an expression applied to one or more columns, not the columns themselves. Examples include the lowercased form of an email, the year extracted from a timestamp, or a value pulled out of a JSON document.
Why It Exists
A plain index on a column cannot help a query that filters on a transformed version of it, because wrapping the column in a function hides its sort order. An expression index stores the computed values sorted, so a query using the same expression can seek directly.
- Filter on the lowercased email, index the lowercased email, get a seek.
- Filter on the extracted year, index that extraction, get a seek.
Two Rules To Remember
- The query must use the identical expression the index was built on, or the planner will not match it.
- The expression must be deterministic, always returning the same output for the same input, since the stored value must stay valid. A function depending on the current time cannot be indexed.
Key idea
An expression index stores the sorted output of a deterministic expression over columns, so a query using that same expression can seek instead of scanning, which a plain column index cannot do.