Mastering PostgreSQL 9.6

Adding functional indexes

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 inside 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 INDEX test=# ANALYZE ; ANALYZE

All you have to do is put the function inside 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);  ...