On Tue, Jan 17, 2006 at 03:44:30PM +0100, Magnus Hagander wrote:
> > > 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
> :-)
I was going to submit a patch on this, and the best way seems to be
adding a note to 'ILIKE', specifying that it will only work if there's a
prefix to the pattern that isn't letters. Is there a standard way to tag
a word indicating that there's a note?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461