Re: create index on function - why?

Hi All,

Following the advice given below, I am trying to create an index on the
function upper() when used on a lastname varchar(60) column.

PostgreSQL 7.0.2

Create table members (lastname varchar(60));

Then I am creating the index as:

Create index upper_lastname_idx on members (upper(lastname));

But I am getting the following error:

ERROR: DefineIndex: function 'upper(varchar)' does not exist

I use the upper() function frequently and it works just fine, I just
want the search to use the index instead of a seq scan.

Thanks in advance.

Dave

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Friday, February 15, 2002 11:47 AM
To: wweng(at)kencast(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] create index on function - why?

Wei Weng <wweng(at)kencast(dot)com> writes:
> But what is the rationale behind creating index on a function?

To speed up searching.

For example, given

create table foo (f1 text);

create index fooi on foo (upper(f1));

the index can be used for queries like

select * from foo where upper(f1) = 'HELLO';

Without the index, there'd be no way to avoid a sequential scan --- not
to mention evaluation of the function at every row. With the index, the
above query actually performs zero evaluations of upper() --- the work
got pushed into row insertion, instead.

A functional index is sort of like a hidden, precomputed column added to
your table.

regards, tom lane

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