cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hugi Thordarson <h...@karlmenn.is>
Subject Re: ILIKE vs. upper/lower with PostgreSQL
Date Fri, 15 Dec 2017 18:49:03 GMT

> On 15 Dec 2017, at 16:08, Andrus Adamchik <andrus@objectstyle.org> wrote:
> 
>> On Dec 15, 2017, at 11:00 AM, Musall, Maik <maik@selbstdenker.ag> wrote:
>> 
>> ILIKE is certainly useful for full text searches with CLOB/TEXT values and in combination
with GiST/GIN indexes, but IMHO not for regular case-insensitive
>> queries using b-tree indexes. So I'm not sure that PostgresQualifierTranslator does
the right thing here.
>> 
>> How is everyone else indexing varchar columns for case-insensitive queries on PostgreSQL?
> 
> I'd be interested to hear from others as well. PostgreSQL docs simply say "The key word
ILIKE can be used instead of LIKE to make the match case-insensitive according to the active
locale." There is not a slightest hint at performance degradation and not using indexes.
> 
> Andrus

I am not a Postgres specialist, but some research on the topic seems reveals (a lot of) anecdotal
evidence that matching on UPPER is more performant both with and without indexes and is generally
preferred to ILIKE for the generic case. So I believe a modification of the standard behaviour
would be sensible.

- hugi
Mime
View raw message