Re: What are functional indices good for?

"Patrick L. Nolan" <pln(at)razzle(dot)Stanford(dot)EDU> writes:
> As a newbie, I find this not very illuminating. Suppose I do
> create index funcindex on mytable (func_name(column1, column2));
> It appears that I can't use the name funcindex in any sort of
> SELECT statement. They deal with column names, not index names.
> I can try
> select * from mytable where func_name(column1, column2) < 3.14159;

This is the correct approach.

> However, EXPLAIN tells me that it doesn't use funcindex for this.

Your question is just a special case of the FAQ "why doesn't Postgres
use an index for FOO". In this case, I believe that "< 3.14159" is not
considered a selective enough WHERE condition to justify using the
index. You would probably see the index used for an equality check
or range check (eg, func_name(column1, column2) < 3.14159 AND
func_name(column1, column2) > 1.0).

regards, tom lane