Wei Weng <wweng(at)kencast(dot)com> writes:
> But what is the rationale behind creating index on a function?
To speed up searching.
For example, given
create table foo (f1 text);
create index fooi on foo (upper(f1));
the index can be used for queries like
select * from foo where upper(f1) = 'HELLO';
Without the index, there'd be no way to avoid a sequential scan --- not
to mention evaluation of the function at every row. With the index,
the above query actually performs zero evaluations of upper() --- the
work got pushed into row insertion, instead.
A functional index is sort of like a hidden, precomputed column added to
your table.
regards, tom lane