Обсуждение: BUG #10051: Documentation should explain use of LOWER(X) text_pattern_ops indexes with LIKE to replace ILIKE
BUG #10051: Documentation should explain use of LOWER(X) text_pattern_ops indexes with LIKE to replace ILIKE
От
greenreaper@hotmail.com
Дата:
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.