Re: What are functional indices good for?

On Thursday 07 March 2002 17:04, Tom Lane wrote:

> > select * from mytable where func_name(column1, column2) < 3.14159;

> 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).

It strikes me that the selectivity of the query ought to be balanced against
some threshold set based upon an estimate of the compute cost of running the
function itself... some functions might be so resource-intensive to evaluate
that it's worth using the index even for a query that's expected to return
99% of the rows!

ABS

--
Alaric B. Snell, Technical
abs(at)frontwire(dot)com