On Fri, 2017-12-15 at 18:49 +0000, Hugi Thordarson wrote: > [EXTERNAL SOURCE] > > > > > 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
https://www.postgresql.org/docs/9.6/static/indexes-types.html The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.9 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion. https://dba.stackexchange.com/questions/4521/how-to-create-an-index-to-speed-up-an-aggregate-like-query-on-an-expression There is no index support for LIKE / ILIKE in PostgreSQL 8.4 - except for left anchored search terms. PostgreSQL 9.1 or later provides new features in the extension pg_trgm that enable index support for LIKE / ILIKE expressions (or simple regular expressions, operator ~ & friends) of any form with a GIN or GiST index using the provided operator classes. Install the extension once per database: CREATE EXTENSION pg_trgm; Example GIN index: CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops); More info and links in this related answer: How is LIKE implemented? Overview on pattern matching and appropriate indices: Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
