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 [EMAIL PROTECTED] 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
