Indexes on Expressions in PostgreSQL

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:

CREATE INDEX index_users_on_lower_username
ON users (LOWER(username));

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 the 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:

CREATE UNIQUE INDEX index_users_on_lower_username
ON users (LOWER(username));

and it won't allow to insert rows with values like 'andreigridnev' or 'Andreigridnev' in the username column into 'users' table if it already has a row with 'AndreiGridnev' value in that column.

More about indexes on expressions: