Re: LIKE and Locale

On Wed, 31 Mar 2004 pgsql(at)mohawksoft(dot)com wrote:

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

But, given limited developer resources, optimizing degenerate sql is
probably not the best use unless someone feels strongly enough about it to
do it themselves.

> Also, I am suspicious of "work arounds" being suggested as norms.

The workaround in this case is to make an index that works with LIKE even
in non "C" locales. I qualified it as a workaround because potentially you
might need two indexes on the field. However, given that it's not limited
to non-wildcard containing strings, it's also more generally useful.

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

No, it involves making an index using the built-in <whatever>_pattern_ops
operator class (which is mentioned in the operator class part of the index
documentation I think, but probably needs better mention)

Something like:
CREATE INDEX indblah on tab(col text_pattern_ops)