Expression Index
An expression index indexes the result of a function or expression — lower(email), date columns, JSON paths — so filtered or extracted queries can use an index instead of scanning.
Definition
An expression index stores the computed value of an immutable expression per row, keyed by that value. Queries filtering on the exact same expression can use the index. It solves case-insensitive lookups, JSON field queries, and computed-range filters where the raw column alone cannot serve the predicate.
Context and scope
Used for case-insensitive email and username matching, JSONB inner-field predicates, timezone-adjusted timestamps, and computed search keys.
Common misconceptions
- An index on the column covers case-insensitive queries — the predicate must match the indexed expression exactly
- Expression indexes work with any function — the expression must be immutable; timezone-dependent or locale-dependent functions are rejected for good reason
- The expression result is stored per row — it adds write cost and row duplication of the computed value, sized accordingly
Anti-patterns
- Querying with a slightly different expression than the indexed one and falling back to seq scans unknowingly
- Marking a mutable function immutable to force index creation and corrupting correctness across locale or timezone changes
- Indexing huge JSON expressions and bloating storage when a targeted path or generated column would suffice
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/indexes-expressions.html
What it is
A predicate on lower(email) cannot use an index on email — the engine would have to compute the function for every row to compare. An expression index precomputes it: the tree is keyed by function output, and the query that asks the same function gets an index seek.
How it works
The index stores expression results per row, updated on write. Immutability is mandatory: the planner must trust that the indexed value equals recomputing the expression later, which locale or clock-dependent functions violate. Generated columns are the sibling feature that makes the computed value a real column, queryable and constrainable.
When to use it
Case-insensitive identity lookups, JSONB field filters, trimmed or normalized codes, and functional-range queries like grouping by date from a timestamp. Prefer a generated column when the expression should appear in SELECT or serve many queries.
Pitfalls
Expression must match textually after planner normalization — one extra function wrapper silently disables the index. Very large expression values bloat entries. Collation changes on the database can invalidate assumptions behind text expression indexes, so review them during major upgrades.