From: "David Huselid" <dhuselid@pepperdash.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>, <wweng@kencast.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: create index on function - why? 
Date: Fri, 15 Feb 2002 13:01:15 -0500
Message-ID: <004901c1b64a$c32e4870$9801a8c0@TELLURIUM>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
In-reply-to: <14409.1013791606@sss.pgh.pa.us>
Importance: Normal

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)