<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.55 2005/11/07 17:36:44 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.56 2006/01/18 21:29:45 momjian Exp $ -->

<chapter id="indexes">

<title id="indexes-title">Indexes</title>

<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.

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>