Re: [HACKERS] Docs off on ILIKE indexing?

I have applied the following patch to clarify the current behavior.
Thanks.

---------------------------------------------------------------------------

Jim C. Nasby wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073