Received: from tangent.outreach.olemiss.edu (tangent.outreach.olemiss.edu [130.74.31.3]) by postgresql.org (Postfix) with ESMTP id AE53147594B for ; Fri, 19 Apr 2002 07:23:34 -0400 (EDT) Received: from olemiss.edu (adsl-156-167-170.jan.bellsouth.net [66.156.167.170]) by tangent.outreach.olemiss.edu (8.9.3/linuxconf) with ESMTP id GAA24446; Fri, 19 Apr 2002 06:23:28 -0500 Message-ID: <3CBFFE2F.7000707@olemiss.edu> Date: Fri, 19 Apr 2002 06:23:27 -0500 From: Thomas Swan User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0rc1) Gecko/20020417 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rajesh Kumar Mallah Cc: PostgResql SQL Mailing List Subject: Re: how to get index scan at work? References: <3CBFFB30.C9F29632@trade-india.com> Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/244 X-Sequence-Number: 7508 Rajesh Kumar Mallah wrote:
Hi folks,

can any one help me with this.

tradein_clients=> explain SELECT   email_id from email_bank where lower(email) = 'mallah@grex.org' ;
NOTICE:  QUERY PLAN:

Seq Scan on email_bank  (cost=0.00..25223.02 rows=9385 width=4)

EXPLAIN
tradein_clients=> explain SELECT   email_id from email_bank where email = 'mallah@grex.org' ;
NOTICE:  QUERY PLAN:

Index Scan using email_bank_email_key on email_bank  (cost=0.00..4.83 rows=1 width=4)

EXPLAIN

i want to query the data in case insensetive manner , but if i put lower(email)
index is not being used  any workarounds??
 

regds
Mallah.

ILIKE is the case insensitive comparison operator.  

SELECT   email_id from email_bank WHERE email ILIKE 'user@domain.org'

You might also want to create an using the lower function.

CREATE INDEX my_foo_index ON LOWER(email_bank(email));