Re: 7.3 no longer using indexes for LIKE queries
От | Peter Eisentraut |
---|---|
Тема | Re: 7.3 no longer using indexes for LIKE queries |
Дата | |
Msg-id | Pine.LNX.4.44.0212090056030.25355-100000@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: 7.3 no longer using indexes for LIKE queries (Greg Stark <gsstark@mit.edu>) |
Список | pgsql-general |
Greg Stark writes: > It seems like there's an obvious easy fix for this. Allow indexes to be > created a simple non-locale dependent lexical sort order. They wouldn't be > useful for sorting in the locale sort order but they would be useful for the > case at hand. There has already been a proposed implementation of that idea, but it has been rejected because of some interpretational problems with how exactly the LIKE operator should respond to locale settings. According to the SQL standard, constant strings that are part of a pattern should be compared using the relevant collation order. If this were implemented (which it currently isn't), then an index based on strxfrm() should be used. The current implementation should use an index based on a binary comparison opclass. We need to figure out which exactly we want to proceed with. I will point out that I believe that an implemenation following the SQL standard model won't be particularly practical. First of all, strings that are not binary equivalents won't be compare as equal under any reasonable collation. Second, even if that were the case, it would certainly not be appropriate to use for the LIKE operator. Third, some of the rules that underly many collations would cause the LIKE operator to have pretty bizarre results. For example, sometimes the strings are compared backwards from the end of the string to the start, and it's not clear how that should behave when faced with a wildcard pattern anchored to the start. -- Peter Eisentraut peter_e@gmx.net
В списке pgsql-general по дате отправления: