Обсуждение: Index use for case insensitive query

I've got a table where one of the main lookup access is by email address, which is generally a case insensitive lookup.


With the constraints that: some of the entries aren't reall email addresses and I can't change the case of all the data
becauseof that: 

Is there a way to do a case insensitive index and appropriate query so that when searching for an email address, I get
thebenefit of an index? 


In the following queries, there's an index on dl_profile(_email).

test=# explain analyze select _donorNum from dl_profile where _email~'^foo@bar.org$' ;
NOTICE:  QUERY PLAN:

Index Scan using dl_profile_email on dl_profile  (cost=0.00..467.75 rows=1 width=4) (actual time=14.59..14.63 rows=1
loops=1)
Total runtime: 14.97 msec

EXPLAIN
test=# explain analyze select _donorNum from dl_profile where _email~*'^foo@bar.org$' ;
NOTICE:  QUERY PLAN:

Seq Scan on dl_profile  (cost=0.00..10607.28 rows=1 width=4) (actual time=4196.43..5078.86 rows=1 loops=1)
Total runtime: 5079.42 msec

thanks

eric

On Tue, Oct 01, 2002 at 11:48:29 -0700,
  eric soroos <eric-psql@soroos.net> wrote:
> I've got a table where one of the main lookup access is by email address, which is generally a case insensitive
lookup. 
>
> With the constraints that: some of the entries aren't reall email addresses and I can't change the case of all the
databecause of that: 
>
> Is there a way to do a case insensitive index and appropriate query so that when searching for an email address, I
getthe benefit of an index? 

Yes you can make an index on a function.
You can do something like:
create index index_name on table_name (lower(column_name));

Then selects like the following should use an index:
select * from table_name where lower(column_name) = 'constant';