From: Frank Bax <fbax@sympatico.ca>
To: Wei Weng <wweng@kencast.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Resources
Date: Fri, 11 Jan 2002 16:13:13 -0500
Message-ID: <3.0.6.32.20020111161313.007a2310@pop6.sympatico.ca> (raw)
In-Reply-To: <20020111154258.706feccc.wweng@kencast.com>
References: <3.0.6.32.20020111141809.021c4eb0@pop6.sympatico.ca>
<NEBBLAAHGLEEPCGOBHDGEEOKECAA.nickf@ontko.com>
<3.0.6.32.20020111141809.021c4eb0@pop6.sympatico.ca>
At 03:42 PM 1/11/02 -0500, Wei Weng wrote:
>Can you index on a function?
>How exactly does that help performance of a query?
If a table "employee" contains a field "last" and you are not sure how the
last name might make use of capital letters, then to get all names starting
with 'MC', you might code a query like:
select * from employee where last ILIKE 'MC%';
which does not use an index on "last" because we used ILIKE instead of LIKE.
In this case, we would create an index using "lower" function, like:
create index employee_last on employee ( lower(last) );
then write the query as:
select * from employee where lower(last) LIKE 'mc%';
which does make use of the index (and a faster executing query).
Frank
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: fbax@sympatico.ca, wweng@kencast.com
Subject: Re: Resources
In-Reply-To: <3.0.6.32.20020111161313.007a2310@pop6.sympatico.ca>
* 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