RE: Sigh, LIKE indexing is *still* broken in foreign locales

> -----Original Message-----
> From: Giles Lean
>
> On Thu, 08 Jun 2000 10:04:11 -0400 Tom Lane wrote:
>
> > The bounds do not have to be perfectly tight, in the sense of being
> > the least string >= or largest string <= the desired strings. It's
> > OK if we scan a few extra tuples in some cases. But we have to have
> > reasonably close bounds or we can't implement LIKE with an index.
>
> Determining the bounding (sub-)strings looks like a very hard problem.
>

[snip]

>
> In the absence of a way to do this "right" we need someone to see a
> "good enough" hack that happens to work everywhere, or else give up
> using indexes for LIKE which I doubt would please anyone. I suppose
> the mismatch comes about because LIKE is about pattern matching and
> not collation. :(
>

Currently optimizer doesn't choose index scan unless the bounds
are sufficiently restrictive.
However we may be able to choose index scan more often if we
could have various type(e.g. LIKE) of qualificactions in index qual.
Though we have to scan an index file entirely,we may be able to
avoid looking up for the heap relation for sufficiently many index
tuples.

This may also give us another possibity.
Index scan may be available for the queries like
select * from .. where .. LIKE '%abc%';

Comments ?

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp