Re: create index on function - why?

Wei Weng <wweng@kencast.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