Re: 7.3 no longer using indexes for LIKE queries

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(at)gmx(dot)net