Indexes on Expressions in PostgreSQL

2016-04-01

PostgreSQL supports indexes over expressions computed over one or more columns. The expressions may include deterministic functions like UPPER(country_code) or DATE_TRUNC('day', created_at), immutable user-defined functions, expressions like +, -, ||, and so on, but cannot include non-deterministic functions, for example NOW().

Consider using indexes over expressions if the typical table queries have a common condition based on computation. For example, this index:

will be used by queries that have a condition on LOWER(username) in WHERE clause. For example:

SELECT *
FROM users
WHERE LOWER(username) = 'andreigridnev';

and

SELECT *
FROM users
WHERE LOWER(username) = 'andreigridnev' AND disabled_at IS NULL;

Without this index PostgreSQL has to scan all table rows and apply LOWER(...) function
to the value of username column in each row to evaluate WHERE condition.
That is way slower that scanning an index.

In addition to improving query performance, indexes over expressions can be used to ensure data integrity.
For example, the index in the example can be made unique: