Re: Functional index performance question

Arguile <arguile(at)lucentstudios(dot)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