So far, you have seen how to index the content of a column as it is. However, this might not always be what you really want. Therefore, PostgreSQL allows the creation of functional indexes. The basic idea is very simple; instead of indexing a value, the output of a function is stored in the index.
The following example shows how the cosine of the id column can be indexed:
test=# CREATE INDEX idx_cos ON t_random (cos(id));CREATE INDEXtest=# ANALYZE; ANALYZE
All you have to do is put the function on the list of columns and you are done. Of course, this won't work for all kinds of functions. Functions can only be used if their output is immutable:
test=# SELECT age('2010-01-01 10:00:00'::timestamptz); age ------------------------- ...
