performance - LOWER LIKE vs iLIKE

Question

How does the performance of the following two query components compare?

LOWER LIKE

... LOWER(description) LIKE '%abcde%' ...

iLIKE

... description iLIKE '%abcde%' ...

Answer 1

The answer depends on many factors like Postgres version, encoding and locale - LC_COLLATE in particular.

The bare expression lower(description) LIKE '%abc%' is typically a bit faster than description ILIKE '%abc%', and either is a bit faster than the equivalent regular expression: description ~* 'abc'. This matters for sequential scans where the expression has to be evaluated for every tested row.

But for big tables like you demonstrate in your answer one would certainly use an index. For arbitrary patterns (not only left-anchored) I suggest a trigram index using the additional module pg_trgm. Then we talk about milliseconds instead of seconds and the difference between the above expressions is nullified.

GIN and GiST indexes (using the gin_trgm_ops or gist_trgm_ops operator classes) support LIKE (~~), ILIKE (~~*), ~, ~* (and some more variants) alike. With a trigram GIN index on description (typically bigger than GiST, but faster for reads), your query would use description ILIKE 'case_insensitive_pattern'.

Related:

• PostgreSQL LIKE query performance variations
• Similar UTF-8 strings for autocomplete field

Basics for pattern matching in Postgres:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

When working with said trigram index it's typically more practical to work with:

description ILIKE '%abc%'

Or with the case-insensitive regexp operator (without % wildcards):

description ~* 'abc'

An index on (description) does not support queries on lower(description) like:

lower(description) LIKE '%abc%'

And vice versa.

With predicates on lower(description) exclusively, the expression index is the slightly better option.

In all other cases, an index on (description) is preferable as it supports both case-sensitive and -insensitive predicates.