pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate index on function - why?
10+ messages / 9 participants
[nested] [flat]
* create index on function - why?
@ 2002-02-15 16:25 Wei Weng <wweng@kencast.com>
0 siblings, 5 replies; 10+ messages in thread
From: Wei Weng @ 2002-02-15 16:25 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-15 16:33 Nick Fankhauser <nickf@ontko.com>
parent: Wei Weng <wweng@kencast.com>
4 siblings, 0 replies; 10+ messages in thread
From: Nick Fankhauser @ 2002-02-15 16:33 UTC (permalink / raw)
To: wweng@kencast.com; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-15 16:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wei Weng <wweng@kencast.com>
4 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2002-02-15 16:46 UTC (permalink / raw)
To: wweng@kencast.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-15 17:01 Bruno Wolff III <bruno@wolff.to>
parent: Wei Weng <wweng@kencast.com>
4 siblings, 1 reply; 10+ messages in thread
From: Bruno Wolff III @ 2002-02-15 17:01 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-15 17:22 Luis Sousa <llsousa@ualg.pt>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 10+ messages in thread
From: Luis Sousa @ 2002-02-15 17:22 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Wei Weng <wweng@kencast.com>; pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-15 18:01 David Huselid <dhuselid@pepperdash.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: David Huselid @ 2002-02-15 18:01 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; wweng@kencast.com; +Cc: 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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-15 18:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Huselid <dhuselid@pepperdash.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2002-02-15 18:15 UTC (permalink / raw)
To: David Huselid <dhuselid@pepperdash.com>; +Cc: wweng@kencast.com; pgsql-sql
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-17 02:49 Bruce Momjian <root@candle.pha.pa.us>
parent: Wei Weng <wweng@kencast.com>
4 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2002-02-17 02:49 UTC (permalink / raw)
To: wweng@kencast.com; +Cc: pgsql-sql
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-02-22 03:13 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Bruce Momjian <root@candle.pha.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Christopher Kings-Lynne @ 2002-02-22 03:13 UTC (permalink / raw)
To: Bruce Momjian <root@candle.pha.pa.us>; wweng@kencast.com; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create index on function - why?
@ 2002-12-22 10:18 Philip Warner <pjw@rhyme.com.au>
parent: Wei Weng <wweng@kencast.com>
4 siblings, 0 replies; 10+ messages in thread
From: Philip Warner @ 2002-12-22 10:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; wweng@kencast.com; +Cc: pgsql-sql
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 |/
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-12-22 10:18 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-15 16:25 create index on function - why? Wei Weng <wweng@kencast.com>
2002-02-15 16:33 ` Nick Fankhauser <nickf@ontko.com>
2002-02-15 16:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-15 18:01 ` David Huselid <dhuselid@pepperdash.com>
2002-02-15 18:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-15 17:01 ` Bruno Wolff III <bruno@wolff.to>
2002-02-15 17:22 ` Luis Sousa <llsousa@ualg.pt>
2002-02-17 02:49 ` Bruce Momjian <root@candle.pha.pa.us>
2002-02-22 03:13 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-12-22 10:18 ` Philip Warner <pjw@rhyme.com.au>
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