pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how to get index scan at work?
4+ messages / 3 participants
[nested] [flat]

* how to get index scan at work?
@ 2002-04-19 11:10 Rajesh Kumar Mallah <mallah@trade-india.com>
  2002-04-19 11:23 ` Re: how to get index scan at work? Thomas Swan <tswan@olemiss.edu>
  0 siblings, 1 reply; 4+ messages in thread

From: Rajesh Kumar Mallah @ 2002-04-19 11:10 UTC (permalink / raw)
  To: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: how to get index scan at work?
  2002-04-19 11:10 how to get index scan at work? Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2002-04-19 11:23 ` Thomas Swan <tswan@olemiss.edu>
  2002-04-19 16:33   ` Re: how to get index scan at work? Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Thomas Swan @ 2002-04-19 11:23 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: how to get index scan at work?
  2002-04-19 11:10 how to get index scan at work? Rajesh Kumar Mallah <mallah@trade-india.com>
  2002-04-19 11:23 ` Re: how to get index scan at work? Thomas Swan <tswan@olemiss.edu>
@ 2002-04-19 16:33   ` Rajesh Kumar Mallah <mallah@trade-india.com>
  2002-04-19 17:12     ` Re: how to get index scan at work? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Rajesh Kumar Mallah @ 2002-04-19 16:33 UTC (permalink / raw)
  To: Thomas Swan <tswan@olemiss.edu>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: how to get index scan at work?
  2002-04-19 11:10 how to get index scan at work? Rajesh Kumar Mallah <mallah@trade-india.com>
  2002-04-19 11:23 ` Re: how to get index scan at work? Thomas Swan <tswan@olemiss.edu>
  2002-04-19 16:33   ` Re: how to get index scan at work? Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2002-04-19 17:12     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2002-04-19 17:12 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Thomas Swan <tswan@olemiss.edu>; pgsql-sql


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)='<string>'
to be indexable.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-04-19 17:12 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-19 11:10 how to get index scan at work? Rajesh Kumar Mallah <mallah@trade-india.com>
2002-04-19 11:23 ` Thomas Swan <tswan@olemiss.edu>
2002-04-19 16:33   ` Rajesh Kumar Mallah <mallah@trade-india.com>
2002-04-19 17:12     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

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