Re: create index on function - why? - Mailing list pgsql-sql

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@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, February 15, 2002 11:47 AM
To: wweng@kencast.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] create index on function - why? 


Wei Weng <wweng@kencast.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@postgresql.org)