doc/src/sgml/ref/create_index.sgml

   1 <!--
   2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.52 2005/11/07 17:36:44 tgl Exp $
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEINDEX">
   7  <refmeta>
   8   <refentrytitle id="sql-createindex-title">CREATE INDEX</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>CREATE INDEX</refname>
  14   <refpurpose>define a new index</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-createindex">
  18   <primary>CREATE INDEX</primary>
  19  </indexterm>
  20
  21  <refsynopsisdiv>
  22 <synopsis>
  23 CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
  24     ( { <replaceable class="parameter">column</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] )
  25     [ TABLESPACE <replaceable class="parameter">tablespace</replaceable> ]
  26     [ WHERE <replaceable class="parameter">predicate</replaceable> ]
  27 </synopsis>
  28  </refsynopsisdiv>
  29
  30  <refsect1>
  31   <title>Description</title>
  32
  33   <para>
  34    <command>CREATE INDEX</command> constructs an index <replaceable
  35    class="parameter">index_name</replaceable> on the specified table.
  36    Indexes are primarily used to enhance database performance (though
  37    inappropriate use can result in slower performance).
  38   </para>
  39
  40   <para>
  41    The key field(s) for the index are specified as column names,
  42    or alternatively as expressions written in parentheses.
  43    Multiple fields can be specified if the index method supports
  44    multicolumn indexes.
  45   </para>
  46
  47   <para>
  48    An index field can be an expression computed from the values of
  49    one or more columns of the table row.  This feature can be used
  50    to obtain fast access to data based on some transformation of
  51    the basic data. For example, an index computed on
  52    <literal>upper(col)</> would allow the clause
  53    <literal>WHERE upper(col) = 'JIM'</> to use an index.
  54   </para>
  55
  56   <para>
  57    <productname>PostgreSQL</productname> provides the index methods
  58    B-tree, hash, and GiST.  Users can also define their own index
  59    methods, but that is fairly complicated.
  60   </para>
  61
  62   <para>
  63     When the <literal>WHERE</literal> clause is present, a
  64     <firstterm>partial index</firstterm> is created.
  65     A partial index is an index that contains entries for only a portion of
  66     a table, usually a portion that is more useful for indexing than the
  67     rest of the table. For example, if you have a table that contains both
  68     billed and unbilled orders where the unbilled orders take up a small
  69     fraction of the total table and yet that is an often used section, you
  70     can improve performance by creating an index on just that portion.
  71     Another possible application is to use <literal>WHERE</literal> with
  72     <literal>UNIQUE</literal> to enforce uniqueness over a subset of a
  73     table.  See <xref linkend="indexes-partial"> for more discussion.
  74   </para>
  75
  76   <para>
  77     The expression used in the <literal>WHERE</literal> clause may refer
  78     only to columns of the underlying table, but it can use all columns,
  79     not just the ones being indexed.  Presently, subqueries and
  80     aggregate expressions are also forbidden in <literal>WHERE</literal>.
  81     The same restrictions apply to index fields that are expressions.
  82   </para>
  83
  84   <para>
  85    All functions and operators used in an index definition must be
  86    <quote>immutable</>, that is, their results must depend only on
  87    their arguments and never on any outside influence (such as
  88    the contents of another table or the current time).  This restriction
  89    ensures that the behavior of the index is well-defined.  To use a
  90    user-defined function in an index expression or <literal>WHERE</literal>
  91    clause, remember to mark the function immutable when you create it.
  92   </para>
  93  </refsect1>
  94
  95  <refsect1>
  96   <title>Parameters</title>
  97
  98     <variablelist>
  99      <varlistentry>
 100       <term><literal>UNIQUE</literal></term>
 101       <listitem>
 102        <para>
 103         Causes the system to check for
 104         duplicate values in the table when the index is created (if data
 105         already exist) and each time data is added. Attempts to
 106         insert or update data which would result in duplicate entries
 107         will generate an error.
 108        </para>
 109       </listitem>
 110      </varlistentry>
 111
 112      <varlistentry>
 113       <term><replaceable class="parameter">name</replaceable></term>
 114       <listitem>
 115        <para>
 116         The name of the index to be created.  No schema name can be included
 117         here; the index is always created in the same schema as its parent
 118         table.
 119        </para>
 120       </listitem>
 121      </varlistentry>
 122
 123      <varlistentry>
 124       <term><replaceable class="parameter">table</replaceable></term>
 125       <listitem>
 126        <para>
 127         The name (possibly schema-qualified) of the table to be indexed.
 128        </para>
 129       </listitem>
 130      </varlistentry>
 131
 132      <varlistentry>
 133       <term><replaceable class="parameter">method</replaceable></term>
 134       <listitem>
 135        <para>
 136         The name of the index method to be used.  Choices are
 137         <literal>btree</literal>, <literal>hash</literal>,
 138         and <literal>gist</literal>.  The
 139         default method is <literal>btree</literal>.
 140        </para>
 141       </listitem>
 142      </varlistentry>
 143
 144      <varlistentry>
 145       <term><replaceable class="parameter">column</replaceable></term>
 146       <listitem>
 147        <para>
 148         The name of a column of the table.
 149        </para>
 150       </listitem>
 151      </varlistentry>
 152
 153      <varlistentry>
 154       <term><replaceable class="parameter">expression</replaceable></term>
 155       <listitem>
 156        <para>
 157         An expression based on one or more columns of the table.  The
 158         expression usually must be written with surrounding parentheses,
 159         as shown in the syntax.  However, the parentheses may be omitted
 160         if the expression has the form of a function call.
 161        </para>
 162       </listitem>
 163      </varlistentry>
 164
 165      <varlistentry>
 166       <term><replaceable class="parameter">opclass</replaceable></term>
 167       <listitem>
 168        <para>
 169         The name of an operator class. See below for details.
 170        </para>
 171       </listitem>
 172      </varlistentry>
 173
 174      <varlistentry>
 175       <term><replaceable class="parameter">tablespace</replaceable></term>
 176       <listitem>
 177        <para>
 178         The tablespace in which to create the index.  If not specified,
 179         <xref linkend="guc-default-tablespace"> is used, or the database's
 180         default tablespace if <varname>default_tablespace</> is an empty
 181         string.
 182        </para>
 183       </listitem>
 184      </varlistentry>
 185
 186      <varlistentry>
 187       <term><replaceable class="parameter">predicate</replaceable></term>
 188       <listitem>
 189        <para>
 190         The constraint expression for a partial index.
 191        </para>
 192       </listitem>
 193      </varlistentry>
 194
 195     </variablelist>
 196  </refsect1>
 197
 198  <refsect1>
 199   <title>Notes</title>
 200
 201   <para>
 202    See <xref linkend="indexes"> for information about when indexes can
 203    be used, when they are not used, and in which particular situations
 204    they can be useful.
 205   </para>
 206
 207   <para>
 208    Currently, only the B-tree and GiST index methods support
 209    multicolumn indexes. Up to 32 fields may be specified by default.
 210    (This limit can be altered when building
 211    <productname>PostgreSQL</productname>.)  Only B-tree currently
 212    supports unique indexes.
 213   </para>
 214
 215   <para>
 216    An <firstterm>operator class</firstterm> can be specified for each
 217    column of an index. The operator class identifies the operators to be
 218    used by the index for that column. For example, a B-tree index on
 219    four-byte integers would use the <literal>int4_ops</literal> class;
 220    this operator class includes comparison functions for four-byte
 221    integers. In practice the default operator class for the column's data
 222    type is usually sufficient. The main point of having operator classes
 223    is that for some data types, there could be more than one meaningful
 224    ordering. For example, we might want to sort a complex-number data
 225    type either by absolute value or by real part. We could do this by
 226    defining two operator classes for the data type and then selecting
 227    the proper class when making an index.  More information about
 228    operator classes is in <xref linkend="indexes-opclass"> and in <xref
 229    linkend="xindex">.
 230   </para>
 231
 232   <para>
 233    Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
 234    to remove an index.
 235   </para>
 236
 237   <para>
 238    Indexes are not used for <literal>IS NULL</> clauses by default.
 239    The best way to use indexes in such cases is to create a partial index
 240    using an <literal>IS NULL</> predicate.
 241   </para>
 242
 243   <para>
 244    Prior releases of <productname>PostgreSQL</productname> also had an
 245    R-tree index method.  This method has been removed because
 246    it had no significant advantages over the GiST method.
 247    If <literal>USING rtree</> is specified, <command>CREATE INDEX</>
 248    will interpret it as <literal>USING gist</>, to simplify conversion
 249    of old databases to GiST.
 250   </para>
 251  </refsect1>
 252
 253  <refsect1>
 254   <title>Examples</title>
 255
 256   <para>
 257    To create a B-tree index on the column <literal>title</literal> in
 258    the table <literal>films</literal>:
 259 <programlisting>
 260 CREATE UNIQUE INDEX title_idx ON films (title);
 261 </programlisting>
 262   </para>
 263
 264   <para>
 265    To create an index on the column <literal>code</> in the table
 266    <literal>films</> and have the index reside in the tablespace
 267    <literal>indexspace</>:
 268 <programlisting>
 269 CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
 270 </programlisting>
 271   </para>
 272
 273 <!--
 274 <comment>
 275 Is this example correct?
 276 </comment>
 277   <para>
 278    To create a GiST index on a point attribute so that we
 279    can efficiently use box operators on the result of the
 280    conversion function:
 281   </para>
 282   <programlisting>
 283 CREATE INDEX pointloc
 284     ON points USING GIST (point2box(location) box_ops);
 285 SELECT * FROM points
 286     WHERE point2box(points.pointloc) = boxes.box;
 287   </programlisting>
 288 -->
 289
 290  </refsect1>
 291
 292  <refsect1>
 293   <title>Compatibility</title>
 294
 295   <para>
 296    <command>CREATE INDEX</command> is a
 297    <productname>PostgreSQL</productname> language extension.  There
 298    are no provisions for indexes in the SQL standard.
 299   </para>
 300  </refsect1>
 301
 302  <refsect1>
 303   <title>See Also</title>
 304
 305   <simplelist type="inline">
 306    <member><xref linkend="sql-alterindex" endterm="sql-alterindex-title"></member>
 307    <member><xref linkend="sql-dropindex" endterm="sql-dropindex-title"></member>
 308   </simplelist>
 309  </refsect1>
 310 </refentry>
 311
 312 <!-- Keep this comment at the end of the file
 313 Local variables:
 314 mode: sgml
 315 sgml-omittag:nil
 316 sgml-shorttag:t
 317 sgml-minimize-attributes:nil
 318 sgml-always-quote-attributes:t
 319 sgml-indent-step:1
 320 sgml-indent-data:t
 321 sgml-parent-document:nil
 322 sgml-default-dtd-file:"../reference.ced"
 323 sgml-exposed-tags:nil
 324 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 325 sgml-local-ecat-files:nil
 326 End:
 327 -->