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)

> 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.