From mallah@trade-india.com Fri Apr 19 11:07:30 2002 Received: from mail-relay.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 93ABD47594B for ; Fri, 19 Apr 2002 07:07:30 -0400 (EDT) Received: (qmail 2634 invoked from network); 19 Apr 2002 11:12:27 -0000 Received: from system11.trade-india-local.com (HELO trade-india.com) (192.168.0.11) by ptil-236-129-del.primus-india.net with SMTP; 19 Apr 2002 11:12:27 -0000 Message-ID: <3CBFFB30.C9F29632@trade-india.com> Date: Fri, 19 Apr 2002 16:40:40 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: PostgResql SQL Mailing List Subject: how to get index scan at work? Content-Type: multipart/alternative; boundary="------------DAB72C44731EC6F8A6EE938A" X-Archive-Number: 200204/243 X-Sequence-Number: 7507 --------------DAB72C44731EC6F8A6EE938A Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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. --------------DAB72C44731EC6F8A6EE938A Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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.
 
 
  --------------DAB72C44731EC6F8A6EE938A-- From tswan@olemiss.edu Fri Apr 19 11:23:34 2002 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));
From mallah@trade-india.com Fri Apr 19 16:29:57 2002 Received: from mail-relay.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 5034D47594D for ; Fri, 19 Apr 2002 12:29:57 -0400 (EDT) Received: (qmail 4710 invoked from network); 19 Apr 2002 16:35:08 -0000 Received: from system11.trade-india-local.com (HELO trade-india.com) (192.168.0.11) by ptil-236-129-del.primus-india.net with SMTP; 19 Apr 2002 16:35:08 -0000 Message-ID: <3CC046D4.B7F972A7@trade-india.com> Date: Fri, 19 Apr 2002 22:03:24 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: Thomas Swan Cc: PostgResql SQL Mailing List Subject: Re: how to get index scan at work? References: <3CBFFB30.C9F29632@trade-india.com> <3CBFFE2F.7000707@olemiss.edu> Content-Type: multipart/alternative; boundary="------------4F79A864DCD28DFB6517FBAD" X-Archive-Number: 200204/249 X-Sequence-Number: 7513 --------------4F79A864DCD28DFB6517FBAD Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi Thomas, it did not work for me! tradein_clients=> explain SELECT email_id from email_bank where email ILIKE 'mallah@grex.org'; NOTICE: QUERY PLAN: Seq Scan on email_bank (cost=0.00..22926.16 rows=1 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_case_insen on email_bank (cost=0.00..4.83 rows=1 width=4) EXPLAIN tradein_clients=> btw i could not create the index the way you suggested regds tradein_clients=> CREATE UNIQUE INDEX email_bank_case_insen on LOWER(email_bank(email)); ERROR: DefineIndex: relation "lower" not found tradein_clients=> --------------4F79A864DCD28DFB6517FBAD Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi Thomas,

it  did not  work  for me!

tradein_clients=> explain SELECT  email_id from email_bank where email ILIKE 'mallah@grex.org';
NOTICE:  QUERY PLAN:
Seq Scan on email_bank  (cost=0.00..22926.16 rows=1 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_case_insen on email_bank  (cost=0.00..4.83 rows=1 width=4)
EXPLAIN
tradein_clients=>
 

btw i could not create the index the way you suggested

regds

tradein_clients=> CREATE UNIQUE  INDEX  email_bank_case_insen on LOWER(email_bank(email));
ERROR:  DefineIndex: relation "lower" not found
tradein_clients=>
  --------------4F79A864DCD28DFB6517FBAD-- From sszabo@megazone23.bigpanda.com Fri Apr 19 17:12:13 2002 Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 3467A47510B for ; Fri, 19 Apr 2002 13:12:13 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 5BEEED5F7; Fri, 19 Apr 2002 10:12:04 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 51A4D5C01; Fri, 19 Apr 2002 10:12:04 -0700 (PDT) Date: Fri, 19 Apr 2002 10:12:04 -0700 (PDT) From: Stephan Szabo To: Rajesh Kumar Mallah Cc: Thomas Swan , PostgResql SQL Mailing List Subject: Re: how to get index scan at work? In-Reply-To: <3CC046D4.B7F972A7@trade-india.com> Message-ID: <20020419100955.A15437-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200204/250 X-Sequence-Number: 7514 On Fri, 19 Apr 2002, Rajesh Kumar Mallah wrote: > btw i could not create the index the way you suggested > > regds > > tradein_clients=> CREATE UNIQUE INDEX email_bank_case_insen on > LOWER(email_bank(email)); > ERROR: DefineIndex: relation "lower" not found > tradein_clients=> It actually should be: create index email_bank_case_insen on email_bank(lower(email)); And that should allow lower(email)='' to be indexable.