Re: What are functional indices good for?

"Patrick L. Nolan" <pln@razzle.Stanford.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