Expression Indexes

~12 min · indexes, expression

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Index a computation, not just a column

An expression index indexes the result of an expression. CREATE INDEX ... ON users (LOWER(email)) indexes the lowercase form. Now WHERE LOWER(email) = 'x' can use the index — without the expression index, that query is doomed to a sequential scan.

Common applications

Case-insensitive lookup: LOWER(email), UPPER(sku).
JSONB field access: index on (payload->>'user_id').
Concatenations: index on (first_name || ' ' || last_name).
Date truncation: index on DATE(timestamp_col) for "by-day" queries.

Cost

The expression is evaluated on every INSERT and UPDATE that touches the relevant columns. Expensive expressions add insert overhead. Cheap ones (LOWER, json field access) are essentially free.

Code

Case-insensitive email lookup·sql

CREATE INDEX users_email_lower_idx ON users (LOWER(email));

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

-- Even better: store lowercase in a generated column + index that
ALTER TABLE users
ADD COLUMN email_lower TEXT GENERATED ALWAYS AS (LOWER(email)) STORED;

CREATE INDEX users_email_lower_gen_idx ON users (email_lower);

JSONB field index·sql

-- Index a single field inside a JSONB column
CREATE INDEX events_user_id_idx ON events ((data->>'user_id'));

-- Now this is fast
SELECT * FROM events WHERE data->>'user_id' = '42';

Concatenated full-name search·sql

CREATE INDEX users_full_name_idx
ON users ((first_name || ' ' || last_name));

SELECT * FROM users
WHERE  first_name || ' ' || last_name = 'Ada Lovelace';

External links

PostgreSQL Expression Indexes

Exercise

Find a query in your project that wraps a column in a function (LOWER, DATE, ->>) — these can never use a plain index. Add an expression index that matches; verify the speedup with EXPLAIN.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousUnique and Partial Indexes Next →GIN Indexes for JSONB and Full-Text

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.