| From: | eric soroos <eric-psql(at)soroos(dot)net> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Index use for case insensitive query |
| Date: | 2002-10-01 18:48:29 |
| Message-ID: | 46780784.1178649587@[4.42.179.151] |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
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 because of that:
Is there a way to do a case insensitive index and appropriate query so that when searching for an email address, I get the benefit 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(at)bar(dot)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(at)bar(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2002-10-01 20:19:10 | Re: How do I use the Binary AND operator in a select? |
| Previous Message | Chris Pizzo | 2002-10-01 18:41:36 | How do I use the Binary AND operator in a select? |