From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200601182128.k0ILShj04902@candle.pha.pa.us>
Subject: Re: [HACKERS] Docs off on ILIKE indexing?
In-Reply-To: <20060117202639.GL67693@pervasive.com>
To: "Jim C. Nasby" <jnasby@pervasive.com>
Date: Wed, 18 Jan 2006 16:28:43 -0500 (EST)
CC: Magnus Hagander <mha@sollentuna.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=ELM1137619723-29870-0_
Content-Transfer-Encoding: 7bit


--ELM1137619723-29870-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


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@pervasive.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

--ELM1137619723-29870-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/bjm/diff"

Index: doc/src/sgml/indices.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v
retrieving revision 1.55
diff -c -c -r1.55 indices.sgml
*** doc/src/sgml/indices.sgml	7 Nov 2005 17:36:44 -0000	1.55
--- doc/src/sgml/indices.sgml	18 Jan 2006 21:24:06 -0000
***************
*** 141,157 ****
  
    <para>
     The optimizer can also use a B-tree index for queries involving the
!    pattern matching operators <literal>LIKE</>,
!    <literal>ILIKE</literal>, <literal>~</literal>, and
!    <literal>~*</literal>, <emphasis>if</emphasis> the pattern is a constant
!    and is anchored to the beginning of the string &mdash; for example,
!    <literal>col LIKE 'foo%'</literal> or <literal>col ~ '^foo'</literal>,
!    but not <literal>col LIKE '%bar'</literal>.  However, if your server does
!    not use the C locale you will need to create the index with a
!    special operator class to support indexing of pattern-matching queries.
!    See <xref linkend="indexes-opclass"> below.
    </para>
  
    <para>
     <indexterm>
      <primary>index</primary>
--- 141,161 ----
  
    <para>
     The optimizer can also use a B-tree index for queries involving the
!    pattern matching operators <literal>LIKE</> and <literal>~</literal>
!    <emphasis>if</emphasis> the pattern is a constant and is anchored to
!    the beginning of the string &mdash; for example, <literal>col LIKE
!    'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
!    <literal>col LIKE '%bar'</literal>. However, if your server does not
!    use the C locale you will need to create the index with a special
!    operator class to support indexing of pattern-matching queries. See
!    <xref linkend="indexes-opclass"> below. It is also possible to use
!    B-tree indexes for <literal>ILIKE</literal> and
!    <literal>~*</literal>, but only if the pattern starts with
!    non-alphabetic characters, i.e. characters that are not affected by
!    upper/lower case conversion.
    </para>
  
+ 
    <para>
     <indexterm>
      <primary>index</primary>

--ELM1137619723-29870-0_--