Thread

BUG #10051: Documentation should explain use of LOWER(X) text_pattern_ops indexes with LIKE to replace ILIKE

Laurence Parry <greenreaper@hotmail.com> — 2014-04-16T17:19:20Z

The following bug has been logged on the website:

Bug reference:      10051
Logged by:          Laurence Parry
Email address:      greenreaper@hotmail.com
PostgreSQL version: 9.3.4
Operating system:   Debain wheezy (Linux 3.2.0)
Description:        

Using ILIKE can be non-performant, e.g.
SELECT user_id FROM users WHERE username ILIKE 'Green%';
would result in a sequential scan on users.

http://www.postgresql.org/docs/current/static/indexes-types.html states that
it is not possible to accelerate a general ILIKE pattern with B-tree
indexes:
"It is also possible to use B-tree indexes for ILIKE and ~*, but only if the
pattern starts with non-alphabetic characters..."

However, it *is* possible to get general prefix-based case-insensitive
searches on B-tree-indexed text, varchar or bpchar columns by using
*_pattern_ops classes:

CREATE INDEX like_lowercase_usernames on users (LOWER(username)
text_pattern_ops);

SELECT user_id FROM users WHERE LOWER(username) LIKE LOWER('Green%');

This was ~200x faster than the ILIKE case for my workload.

I think this possibility should be mentioned in docs section 11.2 above, and
perhaps also at
http://www.postgresql.org/docs/current/static/indexes-opclass.html where
only LIKE is mentioned.

This trick comes up frequently in mailing lists, slides, guides, e.g.:
http://blog.2ndquadrant.com/text-search-strategies-in-postgresql/
http://postgresql.1045698.n5.nabble.com/GENERAL-indexes-for-ILIKE-td1857024.html
http://www.postgresql.org/message-id/16763.1277148967@sss.pgh.pa.us

Ideally ILIKE would not require this workaround - I expected LOWER indexes
to "just work" with ILIKE, as a variation of the support for LIKE. I don't
know how feasible this is, though (collation issues?). If implemented, this
should also be documented.