Re: Possible solution for LIKE optimization

"Hiroshi Inoue" <Inoue@tpf.co.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