> On 15 Dec 2017, at 16:08, Andrus Adamchik <[email protected]> wrote: > >> On Dec 15, 2017, at 11:00 AM, Musall, Maik <[email protected]> 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
