patternsqlpostgresqlModerate
Expression indexes to index computed values and function results
Viewed 0 times
expression indexfunctional indexlower email indeximmutable functiondate_trunc index
Problem
Queries filtering on lower(email) = 'user@example.com' or date_trunc('day', created_at) = '2024-01-01' perform sequential scans because standard indexes on the raw column cannot be used for the transformed value.
Solution
Create an index on the expression, matching the query exactly:
-- Case-insensitive email lookup:
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'user@example.com';
-- Index on truncated date:
CREATE INDEX idx_events_day ON events (date_trunc('day', created_at));
SELECT * FROM events WHERE date_trunc('day', created_at) = '2024-03-15';
-- Index on JSONB extraction:
CREATE INDEX idx_orders_status ON orders ((metadata->>'status'));
SELECT * FROM orders WHERE metadata->>'status' = 'shipped';
-- Case-insensitive email lookup:
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'user@example.com';
-- Index on truncated date:
CREATE INDEX idx_events_day ON events (date_trunc('day', created_at));
SELECT * FROM events WHERE date_trunc('day', created_at) = '2024-03-15';
-- Index on JSONB extraction:
CREATE INDEX idx_orders_status ON orders ((metadata->>'status'));
SELECT * FROM orders WHERE metadata->>'status' = 'shipped';
Why
PostgreSQL stores the result of the expression in the index during writes. At query time, if the planner detects the WHERE clause expression matches the index expression exactly, it uses the index. The expression must be immutable (no side effects, deterministic output).
Gotchas
- The expression in the query must match the index expression character-for-character
- Non-immutable functions (now(), random()) cannot be used in expression indexes
- Expression indexes are rebuilt on every VACUUM FULL and pg_repack; rebuilding is slow for large tables
- citext extension provides a case-insensitive text type that avoids needing lower() expression indexes
Code Snippets
Verify expression index usage
-- Confirm expression index is used:
EXPLAIN SELECT * FROM users WHERE lower(email) = 'alice@example.com';
-- Should show: Index Scan using idx_users_lower_emailContext
Queries that apply transformations to columns in WHERE clauses
Revisions (0)
No revisions yet.