<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.63 2007/06/03 17:05:53 tgl 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>
<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>
<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>
<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
</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 might be incomplete;
</para>
<para>
- Currently, only the B-tree and GiST index methods support
+ 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
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
+ 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
</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>
Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
to remove an index.
</para>
OSDN Git Service
