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: | Raw Message | Whole Thread | 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? |