A PostgreSQL index key does not have to be a column copied directly from a table row. It can be the result of an expression computed from that row. The stored key then represents the transformed value, allowing a matching predicate to use ordinary indexed access instead of computing the expression across every candidate row.

Case-normalized text is a compact example. An application may preserve the original spelling of an email address while searching on a normalized form:

CREATE INDEX accounts_email_lower_idx
    ON accounts (lower(email));

A predicate using the same indexed expression can become an index condition:

SELECT id, email
FROM accounts
WHERE lower(email) = lower('Ada@Example.com');

The useful property is not that lower() receives special treatment. PostgreSQL indexes the expression result as a key. Other immutable expressions can serve the same role when their result matches the access pattern.

The expression becomes the index key

For a plain B-tree index on email, entries are ordered by values from the email column. For an index on lower(email), entries are ordered by the result of evaluating lower(email) for indexed rows.

That distinction matters to query shape. A condition on the base column is not equivalent to a condition on its transformed value:

WHERE email = 'ada@example.com'

and:

WHERE lower(email) = 'ada@example.com'

represent different indexed keys. A plain index on email directly supports the first form. The expression index directly supports the second.

The planner still decides whether an index scan is cheaper than other plans. An expression index makes indexed access available; it does not force PostgreSQL to choose it.

Stored computation moves work to writes

The expression is evaluated when PostgreSQL creates or maintains the index entry. Searches do not need to recompute the indexed expression for every row represented in the index because the derived key is already stored there.

That shifts part of the cost to writes. Inserts must compute the expression before adding the index entry. Updates that require index maintenance must keep the derived key synchronized with the row. The index also consumes storage like any other secondary index.

This cost model favors expressions that appear repeatedly in selective predicates. An expression used only in occasional reporting may not justify a persistent index structure, especially on a write-heavy table.

Index expressions require immutable behavior

PostgreSQL requires functions and operators used in index expressions to be immutable. An indexed key must remain a valid representation of its row without changing because of session state, clock time, table contents, or another external input.

Built-in functions differ in volatility classification, so an expression that is valid in a SELECT statement is not automatically valid in an index definition. PostgreSQL rejects index expressions containing functions that do not meet the immutability requirement.

This restriction protects the relationship between a stored index key and the row from which it was derived. If the same row could produce a different key later without a row change, index lookup could no longer rely on the stored value.

Unique expression indexes can enforce transformed identity

An expression index can also be declared unique. Case-insensitive uniqueness is a common example:

CREATE UNIQUE INDEX accounts_email_lower_uidx
    ON accounts (lower(email));

With this index, values such as Ada@Example.com and ada@example.com produce the same indexed key and cannot coexist when the expression results compare equal under the index semantics.

This is different from merely adding an expression index for search speed. UNIQUE turns the derived key into an enforced data rule. The chosen normalization therefore becomes part of the database model, not just a query optimization.

Multi-column keys can mix columns and expressions

Expression keys can participate in multi-column indexes. A tenant-scoped normalized identifier can combine a regular column with a derived value:

CREATE UNIQUE INDEX members_tenant_handle_uidx
    ON members (tenant_id, lower(handle));

The resulting key pairs each tenant_id with the normalized handle. Two tenants may contain the same normalized handle, while duplicates within one tenant are rejected.

The column order still follows normal B-tree considerations. Putting tenant_id first fits predicates that constrain a tenant and then search or enforce identity within that tenant.

Expression matching is part of index design

An expression index is tied to a particular computation. Small differences in query expressions can affect whether PostgreSQL can map a predicate to the indexed key. The safest design is to make the indexed representation explicit and keep query predicates consistent with it.

This also argues against indexing transformations speculatively. Each derived key adds write work and storage, and its value depends on real query shapes using that representation.

Expression indexes are most coherent when the transformed value has stable meaning in the data model: normalized identifiers, deterministic derived keys, or other immutable row-local computations. In those cases, storing the computation in the index can align lookup semantics, constraint semantics, and physical access around the same representation.

PostgreSQL Database Indexes SQL