Thread: create index on function - why?
I can understand the rationale behind creating index on tables, it speeds up the searching. But what is the rationale behind creating index on a function? how does it help with the database performance? Thank you. -- Wei Weng Network Software Engineer KenCast Inc.
I don't know yet if it will help me. (I was doing empirical research when I ran into my problem.) My reasoning went like this: Since using a function in my "where" clause forces postgresql to do a table scan instead of using my index on the field, I was hoping to build an index on the function, which *would* get used. I'm pretty sure this will work, but I'll post my results to the list if I find that it doesn't work. -Nick > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Wei Weng > Sent: Friday, February 15, 2002 11:26 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] create index on function - why? > > > I can understand the rationale behind creating index on tables, it > speeds up the searching. > > But what is the rationale behind creating index on a function? how does > it help with the database performance? > > Thank you. > > -- > Wei Weng > Network Software Engineer > KenCast Inc. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
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
On Fri, Feb 15, 2002 at 11:25:40AM -0500, Wei Weng <wweng@kencast.com> wrote: > I can understand the rationale behind creating index on tables, it > speeds up the searching. > > But what is the rationale behind creating index on a function? how does > it help with the database performance? If a where clause includes a function reference, than an index on that function can speed up finding rows that satisfy the where clause.
Can you give an example of it ?? I have some problems of perfomance in my queries, probably if I use this idea I could increase performance. I already explore all the index in the tables that are used in the queries. Luis Sousa > >If a where clause includes a function reference, than an index on that >function can speed up finding rows that satisfy the where clause. > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > >
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)
"David Huselid" <dhuselid@pepperdash.com> writes:
> PostgreSQL 7.0.2
You need to update ...
> But I am getting the following error:
> ERROR: DefineIndex: function 'upper(varchar)' does not exist
7.0.* is quite picky about index functions; there has to be an exact
match in pg_proc, not just a binary-compatible match. You could work
around this by creating a pg_proc entry for upper(varchar) that matches
the one for upper(text) ... but I'd suggest updating instead.
regards, tom lane
Due to time constraints, I do not directly answer general PostgreSQL questions. For assistance, please join the appropriate mailing list and post your question: http://www.postgresql.org/users-lounge You can also try the #postgresql IRC channel. See the PostgreSQL FAQ for more information. --------------------------------------------------------------------------- Wei Weng wrote: > I can understand the rationale behind creating index on tables, it > speeds up the searching. > > But what is the rationale behind creating index on a function? how does > it help with the database performance? > > Thank you. > > -- > Wei Weng > Network Software Engineer > KenCast Inc. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- Bruce Momjian | http://candle.pha.pa.us root@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Wei Weng wrote: > > I can understand the rationale behind creating index on tables, it > > speeds up the searching. > > > > But what is the rationale behind creating index on a function? how does > > it help with the database performance? Say you always use a query like this: select * from mytable where upper(name) = 'ASDF'; Now, postgres CANNOT use its indices as it has to retrieve every single row, convert the 'name' field to upper case, and then compare it to 'ASDF'. Now, say you add an index like this: create index myindex on mytable (upper(name)); Now, postgres can just use the 'myindex' index instead of having to scan every row. Is that any clearer? Chris
At 11:46 AM 15/02/2002 -0500, Tom Lane wrote:
>A functional index is sort of like a hidden, precomputed column added to
>your table.
One of the things I keep running into is the desire for a mixed
functional/data index. I know the workaround is to build a function that
combines all fields, but would it be hard to allow mixed indexes:
create table users(id integer, last_name text); create index users_id_name on users(id,lower(name));
?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/