By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,179 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,179 IT Pros & Developers. It's quick & easy.

Functional index performance question

P: n/a
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
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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...ressional.html
http://developer.postgresql.org/docs...efunction.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

P: n/a
Arguile <ar*****@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

P: n/a
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
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #4

P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #5

P: n/a
Arguile <ar*****@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.