What are expression indexes in PostgreSQL?

An expression index (also called a functional index) is built on the result of a function or expression applied to a column, rather than the column value directly. Example: CREATE INDEX idx_lower_email ON users(LOWER(email)); allows case-insensitive lookups: WHERE LOWER(email) = LOWER('User@Example.COM') — PostgreSQL uses this index. CREATE INDEX idx_year ON orders(EXTRACT(YEAR FROM created_at)); for year-based queries. JSON field index: CREATE INDEX idx_city ON profiles((data->>'city'));. The query's WHERE clause must use the exact same expression as the index for the planner to use it. Expression indexes are invaluable for queries that transform data before comparing.