-<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>
+<!--
+$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>
+
+ <refnamediv>
+ <refname>CREATE INDEX</refname>
+ <refpurpose>define a new index</refpurpose>
</refnamediv>
- <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>
- </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>
- </para>
- </REFSECT2>
-
- <REFSECT2 ID="R2-SQL-CREATEINDEX-2">
- <REFSECT2INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT2INFO>
- <TITLE>
- Outputs
- </TITLE>
- <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.
+ <indexterm zone="sql-createindex">
+ <primary>CREATE INDEX</primary>
+ </indexterm>
+
+ <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> ] [ 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> ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <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>
+ 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, hash, GiST, and GIN. 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 more useful for indexing 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. See <xref linkend="indexes-partial"> for more discussion.
+ </para>
+
+ <para>
+ 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>.
+ 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><literal>CONCURRENTLY</literal></term>
+ <listitem>
+ <para>
+ When this option is used, <productname>PostgreSQL</> will build the
+ index without taking any locks that prevent concurrent inserts,
+ updates, or deletes on the table; whereas a standard index build
+ locks out writes (but not reads) on the table until it's done.
+ There are several caveats to be aware of when using this option
+ — see <xref linkend="SQL-CREATEINDEX-CONCURRENTLY"
+ endterm="SQL-CREATEINDEX-CONCURRENTLY-title">.
+ </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 index method to be used. Choices are
+ <literal>btree</literal>, <literal>hash</literal>,
+ <literal>gist</literal>, and <literal>gin</>. 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 can 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><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
+ <xref linkend="sql-createindex-storage-parameters" endterm="sql-createindex-storage-parameters-title">
+ for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The tablespace in which to create the index. If not specified,
+ <xref linkend="guc-default-tablespace"> is consulted, or
+ <xref linkend="guc-temp-tablespaces"> for indexes on temporary
+ tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">predicate</replaceable></term>
+ <listitem>
+ <para>
+ The constraint expression for a partial index.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
+
+ <refsect2 id="SQL-CREATEINDEX-storage-parameters">
+ <title id="SQL-CREATEINDEX-storage-parameters-title">Index Storage Parameters</title>
+
+ <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 <literal>B-tree</literal>, <literal>hash</literal> and
+ <literal>GiST</literal> built-in index methods all accept a single parameter:
</para>
- </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.
- </para>
- </tip>
- </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.
- </para>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>FILLFACTOR</></term>
+ <listitem>
+ <para>
+ The fillfactor for an index is a percentage that determines how full
+ the index method will try to pack index pages. For B-trees, leaf pages
+ are filled to this percentage during initial index build, and also
+ when extending the index at the right (largest key values). If pages
+ subsequently become completely full, they will be split, leading to
+ gradual degradation in the index's efficiency. B-trees use a default
+ fillfactor of 90, but any value from 10 to 100 can be selected.
+ If the table is static then fillfactor 100 is best to minimize the
+ index's physical size, but for heavily updated tables a smaller
+ fillfactor is better to minimize the need for page splits. The
+ other index methods use fillfactor in different but roughly analogous
+ ways; the default fillfactor varies between methods.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
+ </refsect2>
+
+ <refsect2 id="SQL-CREATEINDEX-CONCURRENTLY">
+ <title id="SQL-CREATEINDEX-CONCURRENTLY-title">Building Indexes Concurrently</title>
+
+ <indexterm zone="SQL-CREATEINDEX-CONCURRENTLY">
+ <primary>index</primary>
+ <secondary>building concurrently</secondary>
+ </indexterm>
+
+ <para>
+ Creating an index can interfere with regular operation of a database.
+ Normally <productname>PostgreSQL</> locks the table to be indexed against
+ writes and performs the entire index build with a single scan of the
+ 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. 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>
+
+ <para>
+ <productname>PostgreSQL</> supports building indexes without locking
+ out writes. This method is invoked by specifying the
+ <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 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 might slow other operations.
+ </para>
+
+ <para>
+ 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;
+ 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</>
+ does not support concurrent builds, this option is unlikely to seem
+ attractive.)
+ </para>
+
+ <para>
+ Another caveat when building a unique index concurrently is that the
+ uniqueness constraint is already being enforced against other transactions
+ when the second table scan begins. This means that constraint violations
+ could be reported in other queries prior to the index becoming available
+ for use, or even in cases where the index build eventually fails. Also,
+ if a failure does occur in the second scan, the <quote>invalid</> index
+ continues to enforce its uniqueness constraint afterwards.
+ </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.
+ Concurrent builds of expression indexes and partial indexes are supported.
+ Errors occurring in the evaluation of these expressions could cause
+ behavior similar to that described above for unique constraint violations.
</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.
+ Regular index builds permit other regular index builds on the
+ same table to occur in parallel, but only one concurrent index build
+ can occur on a table at a time. In both cases, no other types of schema
+ modification on the table are allowed meanwhile. Another difference
+ is that a regular <command>CREATE INDEX</> command can be performed within
+ a transaction block, but <command>CREATE INDEX CONCURRENTLY</> cannot.
</para>
- </REFSECT2>
+ </refsect2>
</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>:
- </PARA>
- <ProgramListing>
-CREATE UNIQUE INDEX title_idx
- ON films (title);
- </ProgramListing>
+ <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
+ they can be useful.
+ </para>
+
+ <para>
+ 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>
+ 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>
+ 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>
+ Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
+ to remove an index.
+ </para>
+
+ <para>
+ Prior releases of <productname>PostgreSQL</productname> also had an
+ R-tree index method. This method has been removed because
+ it had no significant advantages over the GiST method.
+ If <literal>USING rtree</> is specified, <command>CREATE INDEX</>
+ will interpret it as <literal>USING gist</>, to simplify conversion
+ of old databases to GiST.
+ </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>
+ To create an index on the expression <literal>lower(title)</>,
+ allowing efficient case-insensitive searches:
+<programlisting>
+CREATE INDEX lower_title_idx ON films ((lower(title)));
+</programlisting>
+ </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);
+</programlisting>
+ </para>
+
+ <para>
+ To create an index on the column <literal>code</> in the table
+ <literal>films</> and have the index reside in the tablespace
+ <literal>indexspace</>:
+<programlisting>
+CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
+</programlisting>
+ </para>
<!--
<comment>
Is this example correct?
</comment>
<para>
- To create a rtree index on a point attribute so that we
+ To create a GiST index on a point attribute so that we
can efficiently use box operators on the result of the
conversion function:
- </para>
<programlisting>
CREATE INDEX pointloc
- ON points USING RTREE (point2box(location) box_ops);
+ ON points USING GIST (point2box(location) box_ops);
SELECT * FROM points
WHERE point2box(points.pointloc) = boxes.box;
</programlisting>
+ </para>
-->
- </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>
+ <para>
+ To create an index without locking out writes to the table:
+<programlisting>
+CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
+</programlisting>
+ </para>
+
</refsect1>
-</REFENTRY>
-
-
-<!-- Keep this comment at the end of the file
-Local variables:
-mode: sgml
-sgml-omittag:t
-sgml-shorttag:t
-sgml-minimize-attributes:nil
-sgml-always-quote-attributes:t
-sgml-indent-step:1
-sgml-indent-data:t
-sgml-parent-document:nil
-sgml-default-dtd-file:"../reference.ced"
-sgml-exposed-tags:nil
-sgml-local-catalogs:"/usr/lib/sgml/catalog"
-sgml-local-ecat-files:nil
-End:
--->
+
+ <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>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-alterindex" endterm="sql-alterindex-title"></member>
+ <member><xref linkend="sql-dropindex" endterm="sql-dropindex-title"></member>
+ </simplelist>
+ </refsect1>
+</refentry>
OSDN Git Service
