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.
