From: David Huselid <dhuselid@pepperdash.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
To: 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> (raw)
In-Reply-To: <14409.1013791606@sss.pgh.pa.us>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dhuselid@pepperdash.com, tgl@sss.pgh.pa.us, wweng@kencast.com
Subject: Re: create index on function - why?
In-Reply-To: <004901c1b64a$c32e4870$9801a8c0@TELLURIUM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox