Skip site navigation (1) Skip section navigation (2)

Index use for case insensitive query

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] (view raw, whole thread or download thread mbox)
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




Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2002-10-01 20:19:10
Subject: Re: How do I use the Binary AND operator in a select?
Previous:From: Chris PizzoDate: 2002-10-01 18:41:36
Subject: How do I use the Binary AND operator in a select?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group