Обсуждение: Docs off on ILIKE indexing?

http://www.postgresql.org/docs/8.1/static/indexes-types.html

says:
The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE, ILIKE, ~, and ~*, if the pattern is a
constant and is anchored to the beginning of the string - for example,
col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.


But really, does it use indexes for ILIKE? (And I assume the same holds
for case insensitive regexp matching)

(If it does, can someone enlighten me on what I have to do - I have a
system with C locale that refuses to do it for ILIKE, but works just
fine for LIKE. My workaronud for now is to create an index on lower(foo)
and then use WHERE lower(foo) LIKE 'bar%' which works fine - but it does
require an extra index..)

So. Am I off, or are the docs? Or is it just me who can't read ;-)

//Magnus

"Magnus Hagander" <mha@sollentuna.net> writes:
> http://www.postgresql.org/docs/8.1/static/indexes-types.html
> says:
> The optimizer can also use a B-tree index for queries involving the
> pattern matching operators LIKE, ILIKE, ~, and ~*, if the pattern is a
> constant and is anchored to the beginning of the string - for example,
> col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. 

> But really, does it use indexes for ILIKE?

That's pretty poorly phrased.  For ILIKE it'll only work if there's a
prefix of the pattern that's not letters (and hence is unaffected by
the case-folding issue).
        regards, tom lane

> > http://www.postgresql.org/docs/8.1/static/indexes-types.html
> > says:
> > The optimizer can also use a B-tree index for queries involving the
> > pattern matching operators LIKE, ILIKE, ~, and ~*, if the
> pattern is a
> > constant and is anchored to the beginning of the string -
> for example,
> > col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.
>
> > But really, does it use indexes for ILIKE?
>
> That's pretty poorly phrased.  For ILIKE it'll only work if
> there's a prefix of the pattern that's not letters (and hence
> is unaffected by the case-folding issue).

Ahh. That explains it. Perfectly logical.
And yes, that's pretty poorly phrased - at least I didn't understand it
:-)

//Magnus