Re: Functional index performance question

On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
[snip]
> 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).

See also:
http://developer.postgresql.org/docs/postgres/indexes-expressional.html
http://developer.postgresql.org/docs/postgres/sql-createfunction.html