> Am 15.12.2017 um 19:49 schrieb Hugi Thordarson <[email protected]>: > >> 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.
I think so, too. From PostgreSQL's own documentation, I'm not sure what ILIKE would be good for anyway. It's certainly a nicer syntax than an UPPER() comparison, but internally the implementation is different, and it's not just syntactic sugar for UPPER(). It may have coverage for some use cases around non-alphabetic languages or something, but there isn't even a hint to that in the documentation that I can see. And even the pg manual explicitly recommends <https://www.postgresql.org/docs/10/static/indexes-expressional.html> using upper() or lower() to index case-insensitive queries: > An index column need not be just a column of the underlying table, but can be > a function or scalar expression computed from one or more columns of the > table. This feature is useful to obtain fast access to tables based on the > results of computations. > > For example, a common way to do case-insensitive comparisons is to use the > lower function: > > SELECT * FROM test1 WHERE lower(col1) = 'value'; > This query can use an index if one has been defined on the result of the > lower(col1) function: > > CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1)); So, I also think Cayenne should ditch ILIKE. And it should be configurable whether UPPER() or LOWER() is used, because while UPPER() is the de-facto standard for this that everyone in the industry used for decades, LOWER() works just as well, and LOWER() is what's mentioned in the documentation. And people might already have indexes in place using either. Maik
