From: | greenreaper(at)hotmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #10051: Documentation should explain use of LOWER(X) text_pattern_ops indexes with LIKE to replace ILIKE |
Date: | 2014-04-16 17:19:20 |
Message-ID: | 20140416171920.17202.25766@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 10051
Logged by: Laurence Parry
Email address: greenreaper(at)hotmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | postgresql.org | 2014-04-16 17:54:36 | BUG #10052: COPY (...) TO 'file' doesn't create file |
Previous Message | fburgess | 2014-04-16 16:29:39 | Having trouble configuring a Master with multiple standby Servers in PostgreSQL 9.3.3 |