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); 
           age            
------------------------- 
 6 years 9 mons 14:00:00 
(1 row)

Functions such as age are not really suitable for indexing because their output is not constant. Time goes on and consequently the output of age will change too. PostgreSQL will explicitly prohibit functions that have the potential to change their result given the same input. The cos function is fine in this respect because the cosine of a value will still be the same in 1,000 years from now.

To test the index, I have written a simple query to show what will happen:

test=# EXPLAIN SELECT * FROM  t_random WHERE cos(id) = 10;
                            QUERY PLAN                                
-------------------------------------------------------------- 
 Index Scan using idx_cos on t_random (cost=0.43..8.45 rows=1 width=9) 
   Index Cond: (cos((id)::double precision) = '10'::double precision) 
(2 rows)

As expected, the functional index will be used just like any other index.