<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.58 2006/09/16 00:30:17 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.70 2009/02/02 19:31:38 alvherre Exp $
PostgreSQL documentation
-->
<refentry id="SQL-CREATEINDEX">
<refmeta>
<refentrytitle id="sql-createindex-title">CREATE INDEX</refentrytitle>
+ <manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refsynopsisdiv>
<synopsis>
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] <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> ] [, ...] )
+ ( { <replaceable class="parameter">column</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ]
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
<title>Description</title>
<para>
- <command>CREATE INDEX</command> constructs an index <replaceable
- class="parameter">index_name</replaceable> on the specified table.
+ <command>CREATE INDEX</command> constructs an index named <replaceable
+ class="parameter">name</replaceable> on the specified table.
Indexes are primarily used to enhance database performance (though
inappropriate use can result in slower performance).
</para>
</para>
<para>
- The expression used in the <literal>WHERE</literal> clause may refer
+ The expression used in the <literal>WHERE</literal> clause can refer
only to columns of the underlying table, but it can use all columns,
not just the ones being indexed. Presently, subqueries and
aggregate expressions are also forbidden in <literal>WHERE</literal>.
<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
+ as shown in the syntax. However, the parentheses can be omitted
if the expression has the form of a function call.
</para>
</listitem>
</varlistentry>
<varlistentry>
+ <term><literal>ASC</></term>
+ <listitem>
+ <para>
+ Specifies ascending sort order (which is the default).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DESC</></term>
+ <listitem>
+ <para>
+ Specifies descending sort order.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>NULLS FIRST</></term>
+ <listitem>
+ <para>
+ Specifies that nulls sort before non-nulls. This is the default
+ when <literal>DESC</> is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>NULLS LAST</></term>
+ <listitem>
+ <para>
+ Specifies that nulls sort after non-nulls. This is the default
+ when <literal>DESC</> is not specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">storage_parameter</replaceable></term>
<listitem>
<para>
The name of an index-method-specific storage parameter. See
- below for details.
+ <xref linkend="sql-createindex-storage-parameters" endterm="sql-createindex-storage-parameters-title">
+ for details.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
The tablespace in which to create the index. If not specified,
- <xref linkend="guc-default-tablespace"> is used, or the database's
- default tablespace if <varname>default_tablespace</> is an empty
- string.
+ <xref linkend="guc-default-tablespace"> is consulted, or
+ <xref linkend="guc-temp-tablespaces"> for indexes on temporary
+ tables.
</para>
</listitem>
</varlistentry>
<para>
The <literal>WITH</> clause can specify <firstterm>storage parameters</>
for indexes. Each index method can have its own set of allowed storage
- parameters. The built-in index methods all accept a single parameter:
+ parameters. The <literal>B-tree</literal>, <literal>hash</literal> and
+ <literal>GiST</literal> built-in index methods all accept a single parameter:
</para>
<variablelist>
table. Other transactions can still read the table, but if they try to
insert, update, or delete rows in the table they will block until the
index build is finished. This could have a severe effect if the system is
- a live production database. Large tables can take many hours to be
+ a live production database. Very large tables can take many hours to be
indexed, and even for smaller tables, an index build can lock out writers
for periods that are unacceptably long for a production system.
</para>
<literal>CONCURRENTLY</> option of <command>CREATE INDEX</>.
When this option is used,
<productname>PostgreSQL</> must perform two scans of the table, and in
- addition it must wait for all existing transactions to terminate. Thus
+ addition it must wait for all existing transactions that could potentially
+ use the index to terminate. Thus
this method requires more total work than a standard index build and takes
significantly longer to complete. However, since it allows normal
operations to continue while the index is built, this method is useful for
adding new indexes in a production environment. Of course, the extra CPU
- and I/O load imposed by the index creation may slow other operations.
+ and I/O load imposed by the index creation might slow other operations.
</para>
<para>
- If a problem arises during the second scan of the table, such as a
+ In a concurrent index build, the index is actually entered into the
+ system catalogs in one transaction, then the two table scans occur in a
+ second and third transaction.
+ If a problem arises while scanning the table, such as a
uniqueness violation in a unique index, the <command>CREATE INDEX</>
command will fail but leave behind an <quote>invalid</> index. This index
- will be ignored for querying purposes because it may be incomplete;
- however it will still consume update overhead. The recommended recovery
+ will be ignored for querying purposes because it might be incomplete;
+ however it will still consume update overhead. The <application>psql</>
+ <command>\d</> command will mark such an index as <literal>INVALID</>:
+
+<programlisting>
+postgres=# \d tab
+ Table "public.tab"
+ Column | Type | Modifiers
+--------+---------+-----------
+ col | integer |
+Indexes:
+ "idx" btree (col) INVALID
+</programlisting>
+
+ The recommended recovery
method in such cases is to drop the index and try again to perform
<command>CREATE INDEX CONCURRENTLY</>. (Another possibility is to rebuild
the index with <command>REINDEX</>. However, since <command>REINDEX</>
</para>
<para>
- Currently, only the B-tree and GiST index methods support
- multicolumn indexes. Up to 32 fields may be specified by default.
+ Currently, only the B-tree, GiST and GIN index methods support
+ multicolumn indexes. Up to 32 fields can be specified by default.
(This limit can be altered when building
<productname>PostgreSQL</productname>.) Only B-tree currently
supports unique indexes.
</para>
<para>
- Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
- to remove an index.
+ For index methods that support ordered scans (currently, only B-tree),
+ the optional clauses <literal>ASC</>, <literal>DESC</>, <literal>NULLS
+ FIRST</>, and/or <literal>NULLS LAST</> can be specified to reverse
+ the normal sort direction of the index. Since an ordered index can be
+ scanned either forward or backward, it is not normally useful to create a
+ single-column <literal>DESC</> index — that sort ordering is already
+ available with a regular index. The value of these options is that
+ multicolumn indexes can be created that match the sort ordering requested
+ by a mixed-ordering query, such as <literal>SELECT ... ORDER BY x ASC, y
+ DESC</>. The <literal>NULLS</> options are useful if you need to support
+ <quote>nulls sort low</> behavior, rather than the default <quote>nulls
+ sort high</>, in queries that depend on indexes to avoid sorting steps.
+ </para>
+
+ <para>
+ For most index methods, the speed of creating an index is
+ dependent on the setting of <xref linkend="guc-maintenance-work-mem">.
+ Larger values will reduce the time needed for index creation, so long
+ as you don't make it larger than the amount of memory really available,
+ which would drive the machine into swapping. For hash indexes, the
+ value of <xref linkend="guc-effective-cache-size"> is also relevant to
+ index creation time: <productname>PostgreSQL</productname> will use one
+ of two different hash index creation methods depending on whether the
+ estimated index size is more or less than <varname>effective_cache_size</>.
+ For best results, make sure that this parameter is also set to something
+ reflective of available memory, and be careful that the sum of
+ <varname>maintenance_work_mem</> and <varname>effective_cache_size</> is
+ less than the machine's RAM less whatever space is needed by other
+ programs.
</para>
<para>
- Indexes are not used for <literal>IS NULL</> clauses by default.
- The best way to use indexes in such cases is to create a partial index
- using an <literal>IS NULL</> predicate.
+ Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
+ to remove an index.
</para>
<para>
</para>
<para>
+ To create an index with non-default sort ordering of nulls:
+<programlisting>
+CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
+</programlisting>
+ </para>
+
+ <para>
To create an index with non-default fill factor:
<programlisting>
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
OSDN Git Service
