Case Sensitivity and Indexes

From: chris(dot)gamble(at)CPBINC(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Case Sensitivity and Indexes
Date: 2002-07-11 17:31:34
Message-ID: 00CA54A79070D411A9E20090273CEF1C14F3CF@inet1.cpbinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a SELECT statement that needs to be able to act on the data in a case
insensitive manner. In order to do this, I am using functional indexes
UPPER(field)=UPPER(val). However, this only lets me use a 1 field index in
my search.

So, to my real world example, if I have

SELECT * from customers WHERE UPPER(city) LIKE 'PARIS%' AND UPPER(state)
LIKE 'Texas%'

the query parser will chose my UPPER(city) index. However, to get the
optimal performance from this query, I need to be able to create and use an
index that is more like (UPPER(city), UPPER(state)) -- because of course my
fictional PARIS city exists in more than one state. I've considered useing
an index that concatenates city and state in upper case, but that limits by
ability to run LIKE searches on both fields. Any ideas how to make this run
faster. (btw, the query is already fast, but I want to be able to show my
users the blazing speed I know is possible in psql.)

Thanks for listening

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Lockhart 2002-07-11 17:38:17 Re: workaround for lack of REPLACE() function
Previous Message Chris Albertson 2002-07-11 17:29:38 Re: Query Speed!!!