pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas Swan <tswan@olemiss.edu>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: PostgResql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: how to get index scan at work?
Date: Fri, 19 Apr 2002 06:23:27 -0500
Message-ID: <3CBFFE2F.7000707@olemiss.edu> (raw)
References: <3CBFFB30.C9F29632@trade-india.com>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body>
Rajesh Kumar Mallah wrote:<br>
<blockquote type="cite" cite="mid3CBFFB30.C9F29632@trade-india.com">  Hi
folks, 
  <p>can any one help me with this. </p>
  <p><tt><font color="#000099">tradein_clients=&gt; explain SELECT&nbsp;&nbsp; email_id
from email_bank where lower(email) = '<a class="moz-txt-link-abbreviated" href="mailto:mallah@grex.org">mallah@grex.org</a>' ;</font></tt> <br>
  <tt><font color="#000099">NOTICE:&nbsp; QUERY PLAN:</font></tt> </p>
  <p><tt><font color="#000099">Seq Scan on email_bank&nbsp; (cost=0.00..25223.02 
rows=9385 width=4)</font></tt> </p>
  <p><tt><font color="#000099">EXPLAIN</font></tt> <br>
  <tt><font color="#000099">tradein_clients=&gt; explain </font></tt><tt><font
 color="#000099">SELECT&nbsp;&nbsp; email_id from email_bank </font></tt><tt><font
 color="#000099">where email = '<a class="moz-txt-link-abbreviated" href="mailto:mallah@grex.org">mallah@grex.org</a>' ;</font></tt> <br>
  <tt><font color="#000099">NOTICE:&nbsp; QUERY PLAN:</font></tt> </p>
  <p><tt><font color="#000099">Index Scan using email_bank_email_key on email_bank&nbsp; 
(cost=0.00..4.83 rows=1 width=4)</font></tt> </p>
  <p><tt><font color="#000099">EXPLAIN</font></tt> </p>
  <p>i want to query the data in case insensetive manner , but if i put lower(email) 
  <br>
index is not being used&nbsp; any workarounds?? <br>
&nbsp; </p>
  <p>regds <br>
Mallah.<br>
  </p>
</blockquote>
ILIKE is the case insensitive comparison operator. &nbsp; <br>
<br>
SELECT&nbsp;&nbsp; email_id from email_bank WHERE email ILIKE '<a class="moz-txt-link-abbreviated" href="mailto:user@domain.org">user@domain.org</a>'<br>
<br>
You might also want to create an using the lower function.<br>
<br>
CREATE INDEX my_foo_index ON LOWER(email_bank(email));<br>
</body>
</html>




view thread (4+ messages)  latest in thread

Message-ID: <3CBFFE2F.7000707@olemiss.edu>
Permalink:  ../3CBFFE2F.7000707@olemiss.edu/
Also on:    postgresql.org/message-id/3CBFFE2F.7000707@olemiss.edu

 · 

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: tswan@olemiss.edu, mallah@trade-india.com
  Subject: Re: how to get index scan at work?
  In-Reply-To: <3CBFFE2F.7000707@olemiss.edu>

* 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