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--