doc/src/sgml/ref/create_index.sgml

   1 <!--
   2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.24 2001/09/14 08:05:55 ishii Exp $
   3 Postgres documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEINDEX">
   7  <refmeta>
   8   <refentrytitle id="sql-createindex-title">
   9    CREATE INDEX
  10   </refentrytitle>
  11   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  12  </refmeta>
  13  <refnamediv>
  14   <refname>
  15    CREATE INDEX
  16   </refname>
  17   <refpurpose>
  18    define a new index
  19   </refpurpose>
  20  </refnamediv>
  21  <refsynopsisdiv>
  22   <refsynopsisdivinfo>
  23    <date>2001-07-15</date>
  24   </refsynopsisdivinfo>
  25   <synopsis>
  26 CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable>
  27     [ USING <replaceable class="parameter">acc_method</replaceable> ] ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable> ] [, ...] )
  28     [ WHERE <replaceable class="parameter">predicate</replaceable> ]
  29 CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable>
  30     [ USING <replaceable class="parameter">acc_method</replaceable> ] ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) [ <replaceable class="parameter">ops_name</replaceable> ] )
  31     [ WHERE <replaceable class="parameter">predicate</replaceable> ]
  32   </synopsis>
  33
  34   <refsect2 id="R2-SQL-CREATEINDEX-1">
  35    <refsect2info>
  36     <date>1998-09-09</date>
  37    </refsect2info>
  38    <title>
  39     Inputs
  40    </title>
  41    <para>
  42
  43     <variablelist>
  44      <varlistentry>
  45       <term>UNIQUE</term>
  46       <listitem>
  47        <para>
  48         Causes the system to check for
  49         duplicate values in the table when the index is created (if data
  50         already exist) and each time data is added. Attempts to
  51         insert or update data which would result in duplicate entries
  52         will generate an error.
  53        </para>
  54       </listitem>
  55      </varlistentry>
  56
  57      <varlistentry>
  58       <term><replaceable class="parameter">index_name</replaceable></term>
  59       <listitem>
  60        <para>
  61         The name of the index to be created.
  62        </para>
  63       </listitem>
  64      </varlistentry>
  65
  66      <varlistentry>
  67       <term><replaceable class="parameter">table</replaceable></term>
  68       <listitem>
  69        <para>
  70         The name of the table to be indexed.
  71        </para>
  72       </listitem>
  73      </varlistentry>
  74
  75      <varlistentry>
  76       <term><replaceable class="parameter">acc_method</replaceable></term>
  77       <listitem>
  78        <para>
  79         The name of the access method to be used for
  80         the index. The default access method is BTREE.
  81         Postgres provides four access methods for indexes:
  82
  83         <variablelist>
  84          <varlistentry>
  85           <term>BTREE</term>
  86           <listitem>
  87            <para>
  88             an implementation of Lehman-Yao
  89             high-concurrency btrees.
  90            </para>
  91           </listitem>
  92          </varlistentry>
  93
  94          <varlistentry>
  95           <term>RTREE</term>
  96           <listitem>
  97            <para>implements standard rtrees using Guttman's
  98             quadratic split algorithm.
  99            </para>
 100           </listitem>
 101          </varlistentry>
 102
 103          <varlistentry>
 104           <term>HASH</term>
 105           <listitem>
 106            <para>
 107             an implementation of Litwin's linear hashing.
 108            </para>
 109           </listitem>
 110          </varlistentry>
 111
 112          <varlistentry>
 113           <term>GIST</term>
 114           <listitem>
 115            <para>
 116             Generalized Index Search Trees.
 117            </para>
 118           </listitem>
 119          </varlistentry>
 120         </variablelist>
 121        </para>
 122       </listitem>
 123      </varlistentry>
 124
 125      <varlistentry>
 126       <term><replaceable class="parameter">column</replaceable></term>
 127       <listitem>
 128        <para>
 129         The name of a column of the table.
 130        </para>
 131       </listitem>
 132      </varlistentry>
 133
 134      <varlistentry>
 135       <term><replaceable class="parameter">ops_name</replaceable></term>
 136       <listitem>
 137        <para>
 138         An associated operator class. See below for details.
 139        </para>
 140       </listitem>
 141      </varlistentry>
 142
 143      <varlistentry>
 144       <term><replaceable class="parameter">func_name</replaceable></term>
 145       <listitem>
 146        <para>
 147         A function, which returns a value that can be indexed.
 148        </para>
 149       </listitem>
 150      </varlistentry>
 151
 152      <varlistentry>
 153       <term><replaceable class="parameter">predicate</replaceable></term>
 154       <listitem>
 155        <para>
 156         Defines the constraint expression for a partial index.
 157        </para>
 158       </listitem>
 159      </varlistentry>
 160     </variablelist>
 161    </para>
 162   </refsect2>
 163
 164   <refsect2 id="R2-SQL-CREATEINDEX-2">
 165    <refsect2info>
 166     <date>1998-09-09</date>
 167    </refsect2info>
 168    <title>
 169     Outputs
 170    </title>
 171    <para>
 172
 173     <variablelist>
 174      <varlistentry>
 175       <term><computeroutput>
 176 CREATE
 177        </computeroutput></term>
 178       <listitem>
 179        <para>
 180         The message returned if the index is successfully created.
 181        </para>
 182       </listitem>
 183      </varlistentry>
 184
 185      <varlistentry>
 186       <term><computeroutput>
 187 ERROR: Cannot create index: 'index_name' already exists.
 188        </computeroutput></term>
 189       <listitem>
 190        <para>
 191         This error occurs if it is impossible to create the index.
 192        </para>
 193       </listitem>
 194      </varlistentry>
 195     </variablelist>
 196    </para>
 197   </refsect2>
 198  </refsynopsisdiv>
 199
 200  <refsect1 id="R1-SQL-CREATEINDEX-1">
 201   <refsect1info>
 202    <date>1998-09-09</date>
 203   </refsect1info>
 204   <title>
 205    Description
 206   </title>
 207   <para>
 208    <command>CREATE INDEX</command> constructs an index
 209    <replaceable class="parameter">index_name</replaceable>
 210    on the specified <replaceable class="parameter">table</replaceable>.
 211
 212    <tip>
 213     <para>
 214      Indexes are primarily used to enhance database performance.
 215      But inappropriate use will result in slower performance.
 216     </para>
 217    </tip>
 218   </para>
 219
 220   <para>
 221    In the first syntax shown above, the key field(s) for the
 222    index are specified as column names.
 223    Multiple fields can be specified if the index access method supports
 224    multi-column indexes.
 225   </para>
 226
 227   <para>
 228    In the second syntax shown above, an index is defined
 229    on the result of a user-specified function
 230    <replaceable class="parameter">func_name</replaceable> applied
 231    to one or more columns of a single table.
 232    These <firstterm>functional indexes</firstterm>
 233    can be used to obtain fast access to data
 234    based on operators that would normally require some
 235    transformation to apply them to the base data.
 236   </para>
 237
 238   <para>
 239    Postgres provides btree, rtree, hash, and GiST access methods for
 240    indexes.  The btree access method is an implementation of
 241    Lehman-Yao high-concurrency btrees.  The rtree access method
 242    implements standard rtrees using Guttman's quadratic split algorithm.
 243    The hash access method is an implementation of Litwin's linear
 244    hashing.  We mention the algorithms used solely to indicate that all
 245    of these access methods are fully dynamic and do not have to be
 246    optimized periodically (as is the case with, for example, static hash
 247    access methods).
 248   </para>
 249
 250   <para>
 251     When the <command>WHERE</command> clause is present, a
 252     <firstterm>partial index</firstterm> is created.
 253     A partial index is an index that contains entries for only a portion of
 254     a table, usually a portion that is somehow more interesting than the
 255     rest of the table. For example, if you have a table that contains both
 256     billed and unbilled orders where the unbilled orders take up a small
 257     fraction of the total table and yet that is an often used section, you
 258     can improve performance by creating an index on just that portion.
 259     Another possible application is to use <command>WHERE</command> with
 260     <command>UNIQUE</command> to enforce uniqueness over a subset of a
 261     table.
 262   </para>
 263
 264   <para>
 265     The expression used in the <command>WHERE</command> clause may refer
 266     only to columns of the underlying table (but it can use all columns,
 267     not only the one(s) being indexed).  Presently, sub-SELECTs and
 268     aggregate expressions are also forbidden in <command>WHERE</command>.
 269   </para>
 270
 271   <para>
 272    All functions and operators used in an index definition must be
 273    <firstterm>cachable</>, that is, their results must depend only on
 274    their input arguments and never on any outside influence (such as
 275    the contents of another table or the current time).  This restriction
 276    ensures that the behavior of the index is well-defined.  To use a
 277    user-defined function in an index, remember to mark the function cachable
 278    when you create it.
 279   </para>
 280
 281   <para>
 282    Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
 283    to remove an index.
 284   </para>
 285
 286   <refsect2 id="R2-SQL-CREATEINDEX-3">
 287    <refsect2info>
 288     <date>1998-09-09</date>
 289    </refsect2info>
 290    <title>
 291     Notes
 292    </title>
 293
 294    <para>
 295     The <productname>Postgres</productname>
 296     query optimizer will consider using a btree index whenever
 297     an indexed attribute is involved in a comparison using one of:
 298
 299     <simplelist type="inline">
 300      <member>&lt;</member>
 301      <member>&lt;=</member>
 302      <member>=</member>
 303      <member>&gt;=</member>
 304      <member>&gt;</member>
 305     </simplelist>
 306    </para>
 307
 308    <para>
 309     The <productname>Postgres</productname>
 310     query optimizer will consider using an rtree index whenever
 311     an indexed attribute is involved in a comparison using one of:
 312
 313     <simplelist type="inline">
 314      <member>&lt;&lt;</member>
 315      <member>&amp;&lt;</member>
 316      <member>&amp;&gt;</member>
 317      <member>&gt;&gt;</member>
 318      <member>@</member>
 319      <member>~=</member>
 320      <member>&amp;&amp;</member>
 321     </simplelist>
 322    </para>
 323
 324    <para>
 325     The <productname>Postgres</productname>
 326     query optimizer will consider using a hash index whenever
 327     an indexed attribute is involved in a comparison using
 328     the <literal>=</literal> operator.
 329    </para>
 330
 331    <para>
 332     Currently, only the btree and gist access methods support multi-column
 333     indexes. Up to 16 keys may be specified by default (this limit
 334     can be altered when building Postgres).  Only btree currently supports
 335     unique indexes.
 336    </para>
 337
 338   <para>
 339    An <firstterm>operator class</firstterm> can be specified for each
 340    column of an index.  The operator class identifies the operators to
 341    be used by the index for that column.  For example, a btree index on
 342    four-byte integers would use the <literal>int4_ops</literal> class;
 343    this operator class includes comparison functions for four-byte
 344    integers.  In practice the default operator class for the field's
 345    data type is usually sufficient.  The main point of having operator classes
 346    is that for some data types, there could be more than one meaningful
 347    ordering.  For example, we might want to sort a complex-number data type
 348    either by absolute value or by real part.  We could do this by defining
 349    two operator classes for the data type and then selecting the proper
 350    class when making an index.  There are also some operator classes with
 351    special purposes:
 352
 353    <itemizedlist>
 354     <listitem>
 355      <para>
 356       The operator classes <literal>box_ops</literal> and
 357       <literal>bigbox_ops</literal> both support rtree indexes on the
 358       <literal>box</literal> data type.
 359       The difference between them is that <literal>bigbox_ops</literal>
 360       scales box coordinates down, to avoid floating-point exceptions from
 361       doing multiplication, addition, and subtraction on very large
 362       floating-point coordinates.  (Note: this was true some time ago,
 363       but currently the two operator classes both use floating point
 364       and are effectively identical.)
 365      </para>
 366     </listitem>
 367    </itemizedlist>
 368   </para>
 369
 370    <para>
 371     The following query shows all defined operator classes:
 372
 373     <programlisting>
 374 SELECT am.amname AS acc_method,
 375        opc.opcname AS ops_name,
 376        opr.oprname AS ops_comp
 377     FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
 378     WHERE opc.opcamid = am.oid AND
 379           amop.amopclaid = opc.oid AND
 380           amop.amopopr = opr.oid
 381     ORDER BY acc_method, ops_name, ops_comp;
 382     </programlisting>
 383    </para>
 384   </refsect2>
 385  </refsect1>
 386
 387  <refsect1 id="R1-SQL-CREATEINDEX-2">
 388   <title>
 389    Usage
 390   </title>
 391   <para>To create a btree index on the field <literal>title</literal>
 392    in the table <literal>films</literal>:
 393   </para>
 394   <programlisting>
 395 CREATE UNIQUE INDEX title_idx
 396     ON films (title);
 397   </programlisting>
 398
 399 <!--
 400 <comment>
 401 Is this example correct?
 402 </comment>
 403   <para>
 404    To create a rtree index on a point attribute so that we
 405    can efficiently use box operators on the result of the
 406    conversion function:
 407   </para>
 408   <programlisting>
 409 CREATE INDEX pointloc
 410     ON points USING RTREE (point2box(location) box_ops);
 411 SELECT * FROM points
 412     WHERE point2box(points.pointloc) = boxes.box;
 413   </programlisting>
 414 -->
 415
 416  </refsect1>
 417
 418  <refsect1 id="R1-SQL-CREATEINDEX-3">
 419   <title>
 420    Compatibility
 421   </title>
 422
 423   <refsect2 id="R2-SQL-CREATEINDEX-4">
 424    <refsect2info>
 425     <date>1998-09-09</date>
 426    </refsect2info>
 427    <title>
 428     SQL92
 429    </title>
 430    <para>
 431     CREATE INDEX is a <productname>Postgres</productname> language extension.
 432    </para>
 433    <para>
 434     There is no <command>CREATE INDEX</command> command in SQL92.
 435    </para>
 436   </refsect2>
 437  </refsect1>
 438 </refentry>
 439
 440 <!-- Keep this comment at the end of the file
 441 Local variables:
 442 mode: sgml
 443 sgml-omittag:nil
 444 sgml-shorttag:t
 445 sgml-minimize-attributes:nil
 446 sgml-always-quote-attributes:t
 447 sgml-indent-step:1
 448 sgml-indent-data:t
 449 sgml-parent-document:nil
 450 sgml-default-dtd-file:"../reference.ced"
 451 sgml-exposed-tags:nil
 452 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 453 sgml-local-ecat-files:nil
 454 End:
 455 -->