What is an expression index, and how do you make WHERE lower(email) = 'x' fast?
An expression index stores the result of a function or expression instead of the raw column. Create an index on lower(email) and queries that use exactly lower(email) can use it; a plain index on email cannot help.
Explain it simply
A normal index is sorted by the column as written. If you search by lower(email), PostgreSQL would have to lowercase every row to compare, so a plain index on email is useless: it is sorted by "Asha@x.com", not "asha@x.com".
An expression index is sorted by the computed value. Think of a contact book sorted by lowercase names so that capital letters never split the order.
Example
CREATE INDEX users_email_lower_idx ON users (lower(email));
-- Can use the index: expression matches exactly
SELECT * FROM users WHERE lower(email) = 'asha@example.com';
-- Cannot use it
SELECT * FROM users WHERE email = 'Asha@example.com';
SELECT * FROM users WHERE upper(email) = 'ASHA@EXAMPLE.COM';
-- Case-insensitive uniqueness
CREATE UNIQUE INDEX users_email_unique ON users (lower(email));The function must be immutable, meaning the same input always gives the same output. Writes get a little slower because the expression is computed on every insert and update.
What to say in the interview
"An expression index stores a computed value, like lower(email). The query must use the same expression for the planner to pick it. I also use it for case-insensitive unique constraints. The function has to be immutable."
Common mistakes
- Indexing lower(email) but querying with ILIKE or upper().