Re: LIKE and Locale

>
> On Wed, 31 Mar 2004 pgsql(at)mohawksoft(dot)com wrote:
>
>> I'm a little frustrated
>>
>> select * from mytable where mystring = 'foo';
>>
>> Uses an index
>>
>> select * from mytable where mystring like 'foo';
>>
>> Does not use an index.
>>
>> I know Tom is not to excited about this, but I think it is a serious
>> problem. What really brings me to this is that I just installed 7.4.2.
>> It
>
> I agree with Tom mostly. It'd be nice for cases to be better optimized in
> general, but optimizing basically degenerate cases seems futile especially
> when there's a generally better workaround (see below)

I'm not convinced that one optimization must de-optimize something else.
Also, I am suspicious of "work arounds" being suggested as norms.

>
>> is my first real deployment of PostgreSQL in about a year and a half.
>> Unknown to me, the default for my latest DB was not type 'C' but
>> "en_US.iso885915" and thus no amount of work would have allowed a 'LIKE'
>> to use an index without surrounding the index and query with some
>
> What about making an index with the <whatever>_pattern_ops opclass which
> IIRC is supposed to allow index use on LIKE even for anchored searches
> in non-C locales.

At issue, would this require a change of the SQL query? If it requires
changing the query, then PostgreSQL places too much of a burden on the
application writer when it comes to supporting multiple databases.