Re: Possible solution for LIKE optimization

"Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
>>> Do we have to make 2 indexes for non_ASCII text field ?
>>
>> You would if you want to use indexscans for both LIKE and "x < 'FOO'"
>> (ie, locale-aware comparisons).

> And ORDER BY ?

Well, the assumption is that we'd make a second set of string comparison
operators that are defined as not-locale-aware. Names still to be
chosen, but let's suppose they're $=$, $<$, $<=$, etc. Then

SELECT ... ORDER BY foo;

would want to use a plain index (defined using locale-aware comparison),
whereas

SELECT ... ORDER BY foo USING $<$;

would want to use a non-locale-aware index. So you could use such an
index for sorting, as long as you were content to have non-locale-aware
output ordering.

> I'm not familiar with non_ASCII locale.
> Is 'ss' always guaranteed to be LIKE 's%' for example ?

I'd assume so, but I'd be interested to hear whether native speakers
of German think that that's appropriate in their locale ...

regards, tom lane