When locale is enabled, we have always had a problem using an index
with:
col LIKE 'abc%'
We need to make this:
col LIKE 'abc%' ANDcol >= "abc" ANDcol < "abd"
but when locale is enabled, we can't be sure what letter is greater than
'c' in this case.
Why don't we just spin through all 255 locale values, and find the
lowest value that is greater than comparison target. It takes only 255
comparisons, which is certainly faster than not using an index with
LIKE.
It is so simple, I don't know why I didn't think of it before. If
performance is a problem, we can do it once in the backend or even in
the postmaster and keep the collation ordering in a 256-byte array. We
may even be able to handle multi-byte in this way, though with 256^2, we
would need to do it once on postmaster startup.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026