Expression Index

ELI5 — The Vibe Check

An expression index indexes the result of a function or calculation, not just a raw column. Want to search by lowercase email? Index LOWER(email). Need to query by year from a timestamp? Index EXTRACT(YEAR FROM created_at). The database pre-computes and indexes the expression.

Real Talk

An expression index (functional index) indexes the result of an expression or function applied to one or more columns. PostgreSQL evaluates the expression at index time and stores the result in the B-Tree. The query planner uses the index when the query's WHERE clause matches the indexed expression exactly. Useful for case-insensitive searches, computed values, and JSON field extraction.

Show Me The Code

-- Index for case-insensitive email search
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- This query uses the index
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Index on JSON field
CREATE INDEX idx_meta_country ON users ((metadata->>'country'));

When You'll Hear This

"Create an expression index on LOWER(email) so case-insensitive searches use the index." / "Expression indexes let you index computed values like date parts or JSON fields."