pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (18+ messages)

Message-ID: <3.0.6.32.20020111161313.007a2310@pop6.sympatico.ca>
Permalink:  ../3.0.6.32.20020111161313.007a2310@pop6.sympatico.ca/
Also on:    postgresql.org/message-id/3.0.6.32.20020111161313.007a2310@pop6.sympatico.ca

 · 

reply

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