Re: how to get index scan at work?

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