doc/src/sgml/ref/create_index.sgml

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