<!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.15 2000/09/12 20:52:08 momjian Exp $
-Postgres documentation
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.43 2003/09/22 00:16:57 petere Exp $
+PostgreSQL documentation
-->
<refentry id="SQL-CREATEINDEX">
<refmeta>
- <refentrytitle id="sql-createindex-title">
- CREATE INDEX
- </refentrytitle>
+ <refentrytitle id="sql-createindex-title">CREATE INDEX</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
+
<refnamediv>
- <refname>
- CREATE INDEX
- </refname>
- <refpurpose>
- Constructs a secondary index
- </refpurpose>
+ <refname>CREATE INDEX</refname>
+ <refpurpose>define a new index</refpurpose>
</refnamediv>
+
+ <indexterm zone="sql-createindex">
+ <primary>CREATE INDEX</primary>
+ </indexterm>
+
<refsynopsisdiv>
- <refsynopsisdivinfo>
- <date>1999-07-20</date>
- </refsynopsisdivinfo>
- <synopsis>
-CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable>
- [ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable> ] [, ...] )
-CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable>
- [ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) [ <replaceable class="parameter">ops_name</replaceable> ] )
- </synopsis>
-
- <refsect2 id="R2-SQL-CREATEINDEX-1">
- <refsect2info>
- <date>1998-09-09</date>
- </refsect2info>
- <title>
- Inputs
- </title>
- <para>
+<synopsis>
+CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
+ ( { <replaceable class="parameter">column</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] )
+ [ WHERE <replaceable class="parameter">predicate</replaceable> ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>CREATE INDEX</command> constructs an index <replaceable
+ class="parameter">index_name</replaceable> on the specified table.
+ Indexes are primarily used to enhance database performance (though
+ inappropriate use will result in slower performance).
+ </para>
+
+ <para>
+ The key field(s) for the index are specified as column names,
+ or alternatively as expressions written in parentheses.
+ Multiple fields can be specified if the index method supports
+ multicolumn indexes.
+ </para>
+
+ <para>
+ An index field can be an expression computed from the values of
+ one or more columns of the table row. This feature can be used
+ to obtain fast access to data based on some transformation of
+ the basic data. For example, an index computed on
+ <literal>upper(col)</> would allow the clause
+ <literal>WHERE upper(col) = 'JIM'</> to use an index.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> provides the index methods
+ B-tree, R-tree, hash, and GiST. The B-tree index method is an
+ implementation of Lehman-Yao high-concurrency B-trees. The R-tree
+ index method implements standard R-trees using Guttman's quadratic
+ split algorithm. The hash index method is an implementation of
+ Litwin's linear hashing. Users can also define their own index
+ methods, but that is fairly complicated.
+ </para>
+
+ <para>
+ When the <literal>WHERE</literal> clause is present, a
+ <firstterm>partial index</firstterm> is created.
+ A partial index is an index that contains entries for only a portion of
+ a table, usually a portion that is somehow more interesting than the
+ rest of the table. For example, if you have a table that contains both
+ billed and unbilled orders where the unbilled orders take up a small
+ fraction of the total table and yet that is an often used section, you
+ can improve performance by creating an index on just that portion.
+ Another possible application is to use <literal>WHERE</literal> with
+ <literal>UNIQUE</literal> to enforce uniqueness over a subset of a
+ table.
+ </para>
+
+ <para>
+ The expression used in the <literal>WHERE</literal> clause may refer
+ only to columns of the underlying table (but it can use all columns,
+ not only the one(s) being indexed). Presently, subqueries and
+ aggregate expressions are also forbidden in <literal>WHERE</literal>.
+ The same restrictions apply to index fields that are expressions.
+ </para>
+
+ <para>
+ All functions and operators used in an index definition must be
+ <quote>immutable</>, that is, their results must depend only on
+ their arguments and never on any outside influence (such as
+ the contents of another table or the current time). This restriction
+ ensures that the behavior of the index is well-defined. To use a
+ user-defined function in an index expression or <literal>WHERE</literal>
+ clause, remember to mark the function immutable when you create it.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
<variablelist>
<varlistentry>
- <term>UNIQUE</term>
+ <term><literal>UNIQUE</literal></term>
<listitem>
<para>
Causes the system to check for
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">index_name</replaceable></term>
+ <term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
- The name of the index to be created.
+ The name of the index to be created. No schema name can be included
+ here; the index is always created in the same schema as its parent
+ table.
</para>
</listitem>
</varlistentry>
<term><replaceable class="parameter">table</replaceable></term>
<listitem>
<para>
- The name of the table to be indexed.
+ The name (possibly schema-qualified) of the table to be indexed.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">acc_name</replaceable></term>
+ <term><replaceable class="parameter">method</replaceable></term>
<listitem>
<para>
- The name of the access method to be used for
- the index. The default access method is BTREE.
- Postgres provides three access methods for indexes:
-
- <variablelist>
- <varlistentry>
- <term>BTREE</term>
- <listitem>
- <para>
- an implementation of Lehman-Yao
- high-concurrency btrees.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term>RTREE</term>
- <listitem>
- <para>implements standard rtrees using Guttman's
- quadratic split algorithm.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term>HASH</term>
- <listitem>
- <para>
- an implementation of Litwin's linear hashing.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
+ The name of the method to be used for the index. Choices are
+ <literal>btree</literal>, <literal>hash</literal>,
+ <literal>rtree</literal>, and <literal>gist</literal>. The
+ default method is <literal>btree</literal>.
</para>
</listitem>
</varlistentry>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">ops_name</replaceable></term>
+ <term><replaceable class="parameter">expression</replaceable></term>
<listitem>
<para>
- An associated operator class. See below for details.
+ An expression based on one or more columns of the table. The
+ expression usually must be written with surrounding parentheses,
+ as shown in the syntax. However, the parentheses may be omitted
+ if the expression has the form of a function call.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">func_name</replaceable></term>
+ <term><replaceable class="parameter">opclass</replaceable></term>
<listitem>
<para>
- A function, which returns a value that can be indexed.
+ The name of an operator class. See below for details.
</para>
</listitem>
</varlistentry>
- </variablelist>
- </para>
- </refsect2>
-
- <refsect2 id="R2-SQL-CREATEINDEX-2">
- <refsect2info>
- <date>1998-09-09</date>
- </refsect2info>
- <title>
- Outputs
- </title>
- <para>
- <variablelist>
<varlistentry>
- <term><computeroutput>
-CREATE
- </computeroutput></term>
+ <term><replaceable class="parameter">predicate</replaceable></term>
<listitem>
<para>
- The message returned if the index is successfully created.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><computeroutput>
-ERROR: Cannot create index: 'index_name' already exists.
- </computeroutput></term>
- <listitem>
- <para>
- This error occurs if it is impossible to create the index.
+ The constraint expression for a partial index.
</para>
</listitem>
</varlistentry>
</variablelist>
- </para>
- </refsect2>
- </refsynopsisdiv>
+ </refsect1>
- <refsect1 id="R1-SQL-CREATEINDEX-1">
- <refsect1info>
- <date>1998-09-09</date>
- </refsect1info>
- <title>
- Description
- </title>
- <para>
- <command>CREATE INDEX</command> constructs an index
- <replaceable class="parameter">index_name</replaceable>
- on the specified <replaceable class="parameter">table</replaceable>.
-
- <tip>
- <para>
- Indexes are primarily used to enhance database performance.
- But inappropriate use will result in slower performance.
- </para>
- </tip>
- </para>
+ <refsect1>
+ <title>Notes</title>
<para>
- In the first syntax shown above, the key field(s) for the
- index are specified as column names.
- Multiple fields can be specified if the index access method supports
- multi-column indexes.
+ See <xref linkend="indexes"> for information about when indexes can
+ be used, when they are not used, and in which particular situations
+ can be useful.
</para>
<para>
- In the second syntax shown above, an index is defined
- on the result of a user-specified function
- <replaceable class="parameter">func_name</replaceable> applied
- to one or more attributes of a single class.
- These <firstterm>functional indices</firstterm>
- can be used to obtain fast access to data
- based on operators that would normally require some
- transformation to apply them to the base data.
+ Currently, only the B-tree and GiST index methods support
+ multicolumn indexes. Up to 32 fields may be specified by default.
+ (This limit can be altered when building
+ <productname>PostgreSQL</productname>.) Only B-tree currently
+ supports unique indexes.
</para>
- <para>
- Postgres provides btree, rtree and hash access methods for
- indices. The btree access method is an implementation of
- Lehman-Yao high-concurrency btrees. The rtree access method
- implements standard rtrees using Guttman's quadratic split algorithm.
- The hash access method is an implementation of Litwin's linear
- hashing. We mention the algorithms used solely to indicate that all
- of these access methods are fully dynamic and do not have to be
- optimized periodically (as is the case with, for example, static hash
- access methods).
- </para>
-
- <para>
- Use <xref linkend="sql-dropindex-title" endterm="sql-dropindex-title">
- to remove an index.
- </para>
-
- <refsect2 id="R2-SQL-CREATEINDEX-3">
- <refsect2info>
- <date>1998-09-09</date>
- </refsect2info>
- <title>
- Notes
- </title>
-
- <para>
- The <productname>Postgres</productname>
- query optimizer will consider using a btree index whenever
- an indexed attribute is involved in a comparison using one of:
-
- <simplelist type="inline">
- <member><</member>
- <member><=</member>
- <member>=</member>
- <member>>=</member>
- <member>></member>
- </simplelist>
- </para>
-
- <para>
- The <productname>Postgres</productname>
- query optimizer will consider using an rtree index whenever
- an indexed attribute is involved in a comparison using one of:
-
- <simplelist type="inline">
- <member><<</member>
- <member>&<</member>
- <member>&></member>
- <member>>></member>
- <member>@</member>
- <member>~=</member>
- <member>&&</member>
- </simplelist>
- </para>
-
- <para>
- The <productname>Postgres</productname>
- query optimizer will consider using a hash index whenever
- an indexed attribute is involved in a comparison using
- the <literal>=</literal> operator.
- </para>
-
- <para>
- Currently, only the btree access method supports multi-column
- indexes. Up to 16 keys may be specified by default (this limit
- can be altered when building Postgres).
- </para>
-
<para>
An <firstterm>operator class</firstterm> can be specified for each
- column of an index. The operator class identifies the operators to
- be used by the index for that column. For example, a btree index on
+ column of an index. The operator class identifies the operators to be
+ used by the index for that column. For example, a B-tree index on
four-byte integers would use the <literal>int4_ops</literal> class;
this operator class includes comparison functions for four-byte
- integers. In practice the default operator class for the field's
- data type is usually sufficient. The main point of having operator classes
+ integers. In practice the default operator class for the column's data
+ type is usually sufficient. The main point of having operator classes
is that for some data types, there could be more than one meaningful
- ordering. For example, we might want to sort a complex-number data type
- either by absolute value or by real part. We could do this by defining
- two operator classes for the data type and then selecting the proper
- class when making an index. There are also some operator classes with
- special purposes:
-
- <itemizedlist>
- <listitem>
- <para>
- The operator classes <literal>box_ops</literal> and
- <literal>bigbox_ops</literal> both support rtree indices on the
- <literal>box</literal> data type.
- The difference between them is that <literal>bigbox_ops</literal>
- scales box coordinates down, to avoid floating point exceptions from
- doing multiplication, addition, and subtraction on very large
- floating-point coordinates. If the field on which your rectangles lie
- is about 20,000 units square or larger, you should use
- <literal>bigbox_ops</literal>.
- </para>
- </listitem>
- </itemizedlist>
+ ordering. For example, we might want to sort a complex-number data
+ type either by absolute value or by real part. We could do this by
+ defining two operator classes for the data type and then selecting
+ the proper class when making an index. More information about
+ operator classes is in <xref linkend="indexes-opclass"> and in <xref
+ linkend="xindex">.
</para>
- <para>
- The following query shows all defined operator classes:
-
- <programlisting>
-SELECT am.amname AS acc_name,
- opc.opcname AS ops_name,
- opr.oprname AS ops_comp
- FROM pg_am am, pg_amop amop,
- pg_opclass opc, pg_operator opr
- WHERE amop.amopid = am.oid AND
- amop.amopclaid = opc.oid AND
- amop.amopopr = opr.oid
- ORDER BY acc_name, ops_name, ops_comp
- </programlisting>
- </para>
- </refsect2>
+ <para>
+ Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
+ to remove an index.
+ </para>
</refsect1>
- <refsect1 id="R1-SQL-CREATEINDEX-2">
- <title>
- Usage
- </title>
- <para>To create a btree index on the field <literal>title</literal>
- in the table <literal>films</literal>:
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To create a B-tree index on the column <literal>title</literal> in
+ the table <literal>films</literal>:
+<programlisting>
+CREATE UNIQUE INDEX title_idx ON films (title);
+</programlisting>
</para>
- <programlisting>
-CREATE UNIQUE INDEX title_idx
- ON films (title);
- </programlisting>
<!--
<comment>
Is this example correct?
</comment>
<para>
- To create a rtree index on a point attribute so that we
+ To create a R-tree index on a point attribute so that we
can efficiently use box operators on the result of the
conversion function:
</para>
</refsect1>
- <refsect1 id="R1-SQL-CREATEINDEX-3">
- <title>
- Compatibility
- </title>
-
- <refsect2 id="R2-SQL-CREATEINDEX-4">
- <refsect2info>
- <date>1998-09-09</date>
- </refsect2info>
- <title>
- SQL92
- </title>
- <para>
- CREATE INDEX is a <productname>Postgres</productname> language extension.
- </para>
- <para>
- There is no <command>CREATE INDEX</command> command in SQL92.
- </para>
- </refsect2>
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>CREATE INDEX</command> is a
+ <productname>PostgreSQL</productname> language extension. There
+ are no provisions for indexes in the SQL standard.
+ </para>
</refsect1>
</refentry>
projects / postgresql / blobdiff