Re: LIKE indexing proposal

Tom Lane writes:

> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > I would like to re-table my proposal from many moons ago to allow
> > pattern-matching operations to use indexes under any locale.
>
> Do you have better answers to the objections that were raised the last
> time?

The objection last time was that, according to the SQL standard, LIKE
patterns should use the locale-specific collation order for the fixed
parts (instead of character-by-character comparisons), and that under that
rule my proposed solution would fall down.

Time has shown that this objection is poor on four grounds:

1. LIKE does not work that way, and omitting optimizations now based on
possible future changes is a stupid approach.

2. If LIKE were to be changed that way, we can always take the
optimization out.

3. Even if LIKE were changed, certainly POSIX regexps would never be
changed, so you can still use the optimization there.

4. Nobody is seriously proposing to change LIKE that way. And if someone
did, I would object, because the established collation rules make getting
consistent results from this approach impossible.

--
Peter Eisentraut peter_e(at)gmx(dot)net