Rajesh Kumar Mallah wrote:

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.

ILIKE is the case insensitive comparison operator.

SELECT email_id from email_bank WHERE email ILIKE 'user@domain.org'

You might also want to create an using the lower function.

CREATE INDEX my_foo_index ON LOWER(email_bank(email));