Case Sensitivity and Indexes
От | chris.gamble@CPBINC.com |
---|---|
Тема | Case Sensitivity and Indexes |
Дата | |
Msg-id | 00CA54A79070D411A9E20090273CEF1C14F3CF@inet1.cpbinc.com обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: