Thread: Functional index performance question

Let's assume I have a table like so:

CREATE TABLE employees (
 employeeid text not null,
 name text not null
);

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?

Mike Mascari
mascarm@mascari.com

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

Arguile <arguile@lucentstudios.com> writes:
> On Tue, 2003-09-30 at 07:06, Mike Mascari wrote:
>> SELECT lower(name)
>> FROM employees
>> WHERE lower(name) = 'mike'
>>
>> will PostgreSQL re-evaluate lower(name)? Is it necessary?

> No, it won't re-evaluate.

I think he's asking whether the lower(name) appearing in the output list
will be separately evaluated.  Which it will be.  There's not presently
any code that looks for common subexpressions.

            regards, tom lane

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@mascari.com

On Tue, 2003-09-30 at 09:54, Mike Mascari wrote:
> 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.
>
> I think it will.

You're correct, I misunderstood to which clause you were referring to: I
thought you were wondering about the lower(name) in the where clause.
Sorry for the confusion.

Arguile <arguile@lucentstudios.com> writes:

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

It doesn't have to reevaluate it for every record to see if it matches,
however it *does* reevaluate for each record it returns for the select list.
If it wasn't listed in the select list it wouldn't have to reevaluate it.

It could maybe do some constant propogation to remove calculations from the
select list, but it doesn't currently, and it doesn't (at least for this case)
in 7.4 either.


eg:

db=> create table a (a integer);
CREATE TABLE

db=> create sequence b;
CREATE SEQUENCE

db=> create or replace function a(integer) returns integer as 'select a from (select $1 as a, nextval(''b'') as b) as
x'language sql immutable; 
CREATE FUNCTION

db=> create index i on a(a(a));
CREATE INDEX

db=> insert into a (a) (select tab_id from tab);
INSERT 0 11907

db=> select currval('b');
 currval
---------
   11907
(1 row)

db=> explain analyze  select a(a) from a where a(a)=3;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using i on a  (cost=0.00..5.89 rows=5 width=4) (actual time=0.54..0.56 rows=1 loops=1)
   Index Cond: (a(a) = 3)
 Total runtime: 0.68 msec
(3 rows)

db=> select currval('b');
 currval
---------
   11908
(1 row)

db=>   select a(a) from a where a(a)=3;
 a
---
 3
(1 row)

db=> select currval('b');
 currval
---------
   11909
(1 row)

db=>   select 1 from a where a(a)=3;
 ?column?
----------
        1
(1 row)

db=> select currval('b');
 currval
---------
   11909
(1 row)



--
greg