An index column need not be just a column of the underlying
table, but can be a function or scalar expression computed from
one or more columns of the table. This feature is useful to
obtain fast access to tables based on the results of
computations.

For example, a common way to do case-insensitive comparisons
is to use the lower function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

This query can use an index if one has been defined on the
result of the lower(col1) function:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

If we were to declare this index UNIQUE, it would prevent creation of rows whose
col1 values differ only in case, as well
as rows whose col1 values are actually
identical. Thus, indexes on expressions can be used to enforce
constraints that are not definable as simple unique
constraints.

The syntax of the CREATE INDEX
command normally requires writing parentheses around index
expressions, as shown in the second example. The parentheses can
be omitted when the expression is just a function call, as in the
first example.

Index expressions are relatively expensive to maintain,
because the derived expression(s) must be computed for each row
upon insertion and whenever it is updated. However, the index
expressions are not
recomputed during an indexed search, since they are already
stored in the index. In both examples above, the system sees the
query as just WHERE indexedcolumn =
'constant' and so the speed of the search is equivalent to
any other simple index query. Thus, indexes on expressions are
useful when retrieval speed is more important than insertion and
update speed.