-<REFENTRY ID="SQL-CREATEINDEX">
- <REFMETA>
- <REFENTRYTITLE>
- CREATE INDEX
- </REFENTRYTITLE>
- <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
- </REFMETA>
- <REFNAMEDIV>
- <REFNAME>
- CREATE INDEX
- </REFNAME>
- <REFPURPOSE>
- Constructs a secondary index
- </REFPURPOSE>
- <REFSYNOPSISDIV>
- <REFSYNOPSISDIVINFO>
- <DATE>1998-09-09</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>
- <VARIABLELIST>
- <VARLISTENTRY>
- <TERM>
- UNIQUE
- </TERM>
- <LISTITEM>
- <PARA>
- Causes the system to check for
- duplicate values in the table when the index is created (if data
- already exist) and each time data is added. Attempts to
- insert or update non-duplicate data will generate an
- error.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">index_name</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- The name of the index to be created.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">table</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- The name of the table to be indexed.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">acc_name</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- the name of the access method which is to be used for
- the index. The default access method is BTREE.
- Postgres provides three access methods for secondary indexes:
- <variablelist>
- <varlistentry>
- <term>BTREE</term>
- <listitem>
- <para>
- an implementation of the 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>
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">column</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- The name of a column of the table.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">ops_name</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- An associated operator class.
- The following select list returns all ops_names:
-
-<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>
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.43 2003/09/22 00:16:57 petere Exp $
+PostgreSQL documentation
+-->
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">func_name</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- A user-defined function, which returns a value that can
- be indexed.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- </variablelist>
-
- </REFSECT2>
-
- <REFSECT2 ID="R2-SQL-CREATEINDEX-2">
- <REFSECT2INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT2INFO>
- <TITLE>
- Outputs
- </TITLE>
- <PARA>
- </PARA>
- <VARIABLELIST>
- <VARLISTENTRY>
- <TERM>
-<replaceable>status</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- <VARIABLELIST>
- <VARLISTENTRY>
- <TERM>
- <ReturnValue>CREATE</ReturnValue>
- </TERM>
- <LISTITEM>
- <PARA>
- The message returned if the index is successfully created.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <ReturnValue>ERROR: Cannot create index: 'index_name' already exists.</ReturnValue>
- </TERM>
- <LISTITEM>
- <PARA>
- This error occurs if it is impossible to create the index.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- </variablelist>
- </LISTITEM>
- </VARLISTENTRY>
- </VARIABLELIST>
- </REFSECT2>
- </REFSYNOPSISDIV>
-
- <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.
-</tip>
+<refentry id="SQL-CREATEINDEX">
+ <refmeta>
+ <refentrytitle id="sql-createindex-title">CREATE INDEX</refentrytitle>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE INDEX</refname>
+ <refpurpose>define a new index</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-createindex">
+ <primary>CREATE INDEX</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<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>
- In the first syntax shown above, the key fields for the
- index are specified as column names; a column may also have
- an associated operator class. An operator class is used
- to specify the operators to be used for a particular
- index. For example, a btree index on four-byte integers
- would use the <literal>int4_ops</literal> class;
- this operator class includes
- comparison functions for four-byte integers. The default
- operator class is the appropriate operator class for that
- field type.
+ 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><literal>UNIQUE</literal></term>
+ <listitem>
+ <para>
+ Causes the system to check for
+ duplicate values in the table when the index is created (if data
+ already exist) and each time data is added. Attempts to
+ insert or update data which would result in duplicate entries
+ will generate an error.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ 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>
+
+ <varlistentry>
+ <term><replaceable class="parameter">table</replaceable></term>
+ <listitem>
+ <para>
+ The name (possibly schema-qualified) of the table to be indexed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">method</replaceable></term>
+ <listitem>
+ <para>
+ 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>
+ <term><replaceable class="parameter">column</replaceable></term>
+ <listitem>
+ <para>
+ The name of a column of the table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">expression</replaceable></term>
+ <listitem>
+ <para>
+ 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">opclass</replaceable></term>
+ <listitem>
+ <para>
+ The name of an operator class. See below for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">predicate</replaceable></term>
+ <listitem>
+ <para>
+ The constraint expression for a partial index.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ 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>
+ 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>
+ 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 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 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. More information about
+ operator classes is in <xref linkend="indexes-opclass"> and in <xref
+ linkend="xindex">.
+ </para>
+
+ <para>
+ Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
+ to remove an index.
+ </para>
+ </refsect1>
+
+ <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>
- <para>
- In the second syntax, an index is defined
- on the result of a user-defined function
- <replaceable class="parameter">func_name</replaceable> applied
- to one or more attributes of a single class. These functional
- indexes 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.
- </para>
-
- <REFSECT2 ID="R2-SQL-CREATEINDEX-3">
- <REFSECT2INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT2INFO>
- <TITLE>
- Notes
- </TITLE>
- <PARA>
- Currently, only the BTREE access method supports multi-column
- indexes. Up to 7 keys may be specified.
- </PARA>
- <para>
- Use <command>DROP INDEX</command>
- to remove an index.
- </para>
- </REFSECT2>
-
- <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>:
- </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>
</programlisting>
-->
- </REFSECT1>
-
- <REFSECT1 ID="R1-SQL-CREATEINDEX-3">
- <TITLE>
- Compatibility
- </TITLE>
- <PARA>
- </PARA>
-
- <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>
-</REFENTRY>
+
+ <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>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
-sgml-omittag:t
+sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
projects / postgresql / blobdiff