Re: 7.3 no longer using indexes for LIKE queries

Peter Eisentraut wrote:
>
> 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.

For example, there could be case-insensitive collations.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/