PostgreSQL Expression Indexes Explained (With Examples)

When a query filters on LOWER(email), a truncated timestamp, or a field buried in JSONB, a plain btree index on the raw column cannot help. A PostgreSQL expression index solves this: it indexes the result of a function or expression instead of the column itself, so the planner can replace row-by-row computation with an index lookup.

TL;DR: A PostgreSQL expression index (also called a functional index) is built on an expression such as LOWER(email) or payload ->> 'status' rather than a bare column. Functions in the expression must be IMMUTABLE, and the query planner uses the index only when the query repeats the expression exactly. It is ideal for case-insensitive search, JSONB lookups, and time-series rollups.

An expression index is an index whose key is the value produced by evaluating an expression — a function call, an operator, or a combination of columns — for each row. PostgreSQL computes that value once at write time and stores it as the index key, exactly as it stores column values in a regular btree. The official documentation on indexes on expressions presents them as the generalization of ordinary column indexes.

How It Differs from a Standard Column Index

A standard index on email stores raw addresses; an expression index on LOWER(email) stores lowercased ones. That difference cuts both ways:

• WHERE LOWER(email) = '[email protected]' can use the expression index but degrades to a sequential scan against a plain column index.
• WHERE email = '[email protected]' matches the plain index, not the expression index. Each query lines up with the index that stores the same values.

Basic CREATE INDEX Syntax for Expressions

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

Two rules from the CREATE INDEX reference matter in practice:

• A simple function call needs no extra parentheses: ON users (LOWER(email)).
• Operator expressions need double parentheses, e.g. ON t ((a || b)). Any access method is allowed — btree by default, GIN for tsvector and JSONB expressions.

The planner uses an expression index only when the query expression matches the stored index expression exactly.

The IMMUTABLE Function Requirement

Every function in an index expression must be declared IMMUTABLE: the same arguments must always produce the same result, regardless of session settings, server time zone, or hidden state. The reason is structural — index keys are computed once at write time, so a function whose output could change later would leave stored keys silently wrong. PostgreSQL's function volatility documentation defines the categories in detail.

A frequent surprise: date_trunc() is immutable for timestamp but only stable for timestamptz, because the result depends on the server's time zone. You can wrap a stable function in a custom IMMUTABLE function to force an index, but if the hidden dependency ever shifts, index entries drift from the data. Treat that as a last resort.

How the Query Planner Matches Expressions

The planner matches expressions structurally; it never rewrites UPPER(x) into LOWER(x) or infers casts. In practice:

• The query must contain the identical expression: same function, same arguments, same order.
• Casts count. LOWER(email::text) does not match an index on LOWER(email) unless the cast is identical.
• The predicate must use an operator the access method supports (for btree, the btree operators of the expression's type).

Verifying Usage with EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

A matching plan (abridged) shows the index in use:

Index Scan using idx_users_email_lower on users
  Index Cond: (lower(email) = '[email protected]'::text)

If you see Seq Scan plus a Filter line, the expression did not match. On a tiny table a sequential scan can still be cheaper — that is the planner being right, not the index being broken.

Case-Insensitive Search with LOWER() and the citext Alternative

The classic postgres expression index looks like this:

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

SELECT * FROM users WHERE LOWER(email) = '[email protected]';

If nearly every lookup on a column is case-insensitive, the citext extension is often cleaner: it stores case-insensitive text so queries stop repeating LOWER(). Prefer an expression index when only some queries need case-insensitive matching. For anchored prefix searches such as LOWER(email) LIKE 'ali%', add a text_pattern_ops operator class to the indexed expression.

Indexing date_trunc() for Time-Series Rollups

Grouping by day is a common rollup, and the timestamptz gotcha surfaces immediately:

CREATE INDEX idx_events_day ON events (date_trunc('day', created_at));
-- ERROR: functions in index expression must be marked IMMUTABLE

Pin the time zone to make the expression immutable, and repeat the identical expression in the query:

CREATE INDEX idx_events_day
ON events (date_trunc('day', created_at AT TIME ZONE 'UTC'));

SELECT date_trunc('day', created_at AT TIME ZONE 'UTC') AS day, count(*)
FROM events
GROUP BY 1
ORDER BY 1;

Expression Indexes on JSONB Fields

For equality on one extracted field, index the ->> expression with a btree:

CREATE INDEX idx_orders_status
ON orders ((payload ->> 'status'));

SELECT * FROM orders WHERE payload ->> 'status' = 'shipped';

For containment checks against the whole document, a GIN index is the right tool:

CREATE INDEX idx_orders_payload
ON orders USING gin (payload jsonb_path_ops);

SELECT * FROM orders WHERE payload @> '{"status": "shipped"}';

Indexing Computed Values and Concatenations

Concatenations and full-text search follow the same rule — index exactly what the query evaluates:

CREATE INDEX idx_contacts_name
ON contacts ((first_name || ' ' || last_name));

CREATE INDEX idx_docs_search
ON documents USING gin (to_tsvector('english', body));

SELECT * FROM documents
WHERE to_tsvector('english', body) @@ 'planner';

The to_tsvector pattern comes straight from the PostgreSQL documentation; the query must repeat the identical call, including the configuration name.

These features solve different problems, and the comparison clarifies which fits your workload:

When an Expression Index Beats a Generated Column Plus Index

A generated column (GENERATED ALWAYS AS ... STORED, PostgreSQL 12 and later) materializes the expression as a real column you then index. Choose an expression index when you want no schema change, minimal table storage, and the value matters only to the planner. Choose a generated column when applications need the value in results, in many predicates, or when simpler query text is worth the storage.

Combining Expressions with Partial Index Predicates

The two features are orthogonal — a partial index can index an expression over a subset of rows:

CREATE INDEX idx_active_orders_net
ON orders ((total - discount))
WHERE status = 'active';

The planner uses it only when the query's conditions imply the predicate, for example WHERE status = 'active' AND total - discount > 100.

Write Amplification and Maintenance Overhead

Expression indexes are maintained automatically, but the cost lands on writes:

• Every INSERT, and every UPDATE that modifies a referenced column, re-evaluates the expression and maintains the index — extra CPU and I/O on write-heavy tables.
• Because the index depends on the underlying column, modifying that column prevents HOT updates for the row, even when the computed value is unchanged.
• Btree entries for frequently rewritten values contribute to index bloat; monitor it and use REINDEX CONCURRENTLY when needed.

Expression Mismatch: Why Your Index Is Ignored

When a postgres expression index is not used, check in this order:

1. Expression mismatch — UPPER() vs LOWER(), different argument order, an extra or missing cast, a different concatenation order.
2. Different semantics — the query needs raw values while the index stores transformed ones.
3. Stale statistics — run ANALYZE after bulk loads.
4. Cost-based refusal — on small tables or low-selectivity predicates, a sequential scan is genuinely cheaper.

Statistics, ANALYZE, and Planner Decisions

PostgreSQL gathers statistics for expression indexes just as it does for columns, so the planner can estimate how selective LOWER(email) = '...' is. After large data changes, ANALYZE users; refreshes those estimates. To judge whether an index earns its keep, watch pg_stat_user_indexes.idx_scan over a representative workload before keeping or dropping it.