pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow 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=> explain SELECT 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: QUERY PLAN:</font></tt> </p>
<p><tt><font color="#000099">Seq Scan on email_bank (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=> explain </font></tt><tt><font
color="#000099">SELECT 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: QUERY PLAN:</font></tt> </p>
<p><tt><font color="#000099">Index Scan using email_bank_email_key on email_bank
(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 any workarounds?? <br>
</p>
<p>regds <br>
Mallah.<br>
</p>
</blockquote>
ILIKE is the case insensitive comparison operator. <br>
<br>
SELECT 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