Re: Functional index performance question

Arguile wrote:

> On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
>
>>CREATE INDEX i_employees ON employees(lower(name));
>>
>>Let's also assume that the lower() function is computationally
>>expensive. Now if I have a query like:
>>
>>SELECT lower(name)
>>FROM employees
>>WHERE lower(name) = 'mike'
>>
>>will PostgreSQL re-evaluate lower(name)? Is it necessary?
>
> No, it won't re-evaluate. Which is why functional indexes work and why
> you can only declare a functional index on a referentially transparent
> function (see IMMUTABLE flag in CREATE FUNCTION).

I think it will.

Create a function that lies about its IMMUTABLE state and internally
modifies some global variable and execute the query more than once. It
appears that the evaluation of the predicate will not invoke the
function again, but the evaluation of the expression in the attribute
list of the SELECT will.

My point was that re-evaluation of the expression might be avoidable...

Mike Mascari
mascarm(at)mascari(dot)com