Thread: how to get index scan at work?
Hi folks, <p>can any one help me with this. <p><tt><font color="#000099">tradein_clients=> explain SELECT email_idfrom email_bank where lower(email) = 'mallah@grex.org' ;</font></tt><br /><tt><font color="#000099">NOTICE: QUERYPLAN:</font></tt><tt><font color="#000099"></font></tt><p><tt><font color="#000099">Seq Scan on email_bank (cost=0.00..25223.02rows=9385 width=4)</font></tt><tt><font color="#000099"></font></tt><p><tt><font color="#000099">EXPLAIN</font></tt><br/><tt><font color="#000099">tradein_clients=> explain SELECT email_id from email_bankwhere email = 'mallah@grex.org' ;</font></tt><br /><tt><font color="#000099">NOTICE: QUERY PLAN:</font></tt><tt><fontcolor="#000099"></font></tt><p><tt><font color="#000099">Index Scan using email_bank_email_keyon email_bank (cost=0.00..4.83 rows=1 width=4)</font></tt><tt><font color="#000099"></font></tt><p><tt><fontcolor="#000099">EXPLAIN</font></tt><tt></tt><p>i want to query the data in case insensetivemanner , but if i put lower(email) <br />index is not being used any workarounds?? <br /> <p>regds <br />Mallah.<br /> <br /> <br />
Rajesh Kumar Mallah wrote:<br /><blockquote cite="mid3CBFFB30.C9F29632@trade-india.com" type="cite"> Hi folks, <p>can anyone help me with this. <p><tt><font color="#000099">tradein_clients=> explain SELECT email_id from email_bank wherelower(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><tt><font color="#000099">Seq Scan on email_bank (cost=0.00..25223.02 rows=9385 width=4)</font></tt><p><tt><font color="#000099">EXPLAIN</font></tt><br /><tt><fontcolor="#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><tt><fontcolor="#000099">Index Scan using email_bank_email_key on email_bank (cost=0.00..4.83 rows=1width=4)</font></tt><p><tt><font color="#000099">EXPLAIN</font></tt><p>i want to query the data in case insensetivemanner , but if i put lower(email) <br /> index is not being used any workarounds?? <br /> <p>regds <br />Mallah.<br /></blockquote> ILIKE is the case insensitive comparison operator. <br /><br /> SELECT email_id from email_bankWHERE 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/>
Hi Thomas, <p>it did not work for me! <p><tt><font color="#000099">tradein_clients=> explain SELECT email_id fromemail_bank where email ILIKE 'mallah@grex.org';</font></tt><br /><tt><font color="#000099">NOTICE: QUERY PLAN:</font></tt><br/><tt><font color="#000099">Seq Scan on email_bank (cost=0.00..22926.16 rows=1 width=4)</font></tt><br/><tt><font color="#000099">EXPLAIN</font></tt><br /><tt><font color="#000099">tradein_clients=>explain SELECT email_id from email_bank where email = 'mallah@grex.org';</font></tt><br/><tt><font color="#000099">NOTICE: QUERY PLAN:</font></tt><br /><tt><font color="#000099">IndexScan using email_bank_case_insen on email_bank (cost=0.00..4.83 rows=1 width=4)</font></tt><br /><tt><fontcolor="#000099">EXPLAIN</font></tt><br /><tt><font color="#000099">tradein_clients=></font></tt><br /> <p>btwi could not create the index the way you suggested <p>regds <p><tt>tradein_clients=> CREATE UNIQUE INDEX email_bank_case_insenon LOWER(email_bank(email));</tt><br /><b><tt><font color="#CC0000">ERROR: DefineIndex: relation "lower"not found</font></tt></b><br /><tt>tradein_clients=></tt><br />
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.