granicus.if.org Git - postgresql/blob - doc/src/sgml/ref/create_index.sgml

   1 <!--
   2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.27 2002/03/22 19:20:38 petere Exp $
   3 PostgreSQL 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 <literal>BTREE</literal>.
  81         <application>PostgreSQL</application> provides four access methods for indexes:
  82
  83         <variablelist>
  84          <varlistentry>
  85           <term><literal>BTREE</></term>
  86           <listitem>
  87            <para>
  88             an implementation of Lehman-Yao
  89             high-concurrency B-trees.
  90            </para>
  91           </listitem>
  92          </varlistentry>
  93
  94          <varlistentry>
  95           <term><literal>RTREE</></term>
  96           <listitem>
  97            <para>implements standard R-trees using Guttman's
  98             quadratic split algorithm.
  99            </para>
 100           </listitem>
 101          </varlistentry>
 102
 103          <varlistentry>
 104           <term><literal>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><literal>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    multicolumn 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    <application>PostgreSQL</application> provides B-tree, R-tree, hash, and GiST access methods for
 240    indexes.  The B-tree access method is an implementation of
 241    Lehman-Yao high-concurrency B-trees.  The R-tree access method
 242    implements standard R-trees 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>cacheable</>, 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 cacheable
 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>PostgreSQL</productname>
 296     query optimizer will consider using a B-tree 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>PostgreSQL</productname>
 310     query optimizer will consider using an R-tree 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>PostgreSQL</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 B-tree and gist access methods support multicolumn
 333     indexes. Up to 16 keys may be specified by default (this limit
 334     can be altered when building
 335     <application>PostgreSQL</application>).  Only B-tree currently supports
 336     unique indexes.
 337    </para>
 338
 339   <para>
 340    An <firstterm>operator class</firstterm> can be specified for each
 341    column of an index.  The operator class identifies the operators to
 342    be used by the index for that column.  For example, a B-tree index on
 343    four-byte integers would use the <literal>int4_ops</literal> class;
 344    this operator class includes comparison functions for four-byte
 345    integers.  In practice the default operator class for the field's
 346    data type is usually sufficient.  The main point of having operator classes
 347    is that for some data types, there could be more than one meaningful
 348    ordering.  For example, we might want to sort a complex-number data type
 349    either by absolute value or by real part.  We could do this by defining
 350    two operator classes for the data type and then selecting the proper
 351    class when making an index.  There are also some operator classes with
 352    special purposes:
 353
 354    <itemizedlist>
 355     <listitem>
 356      <para>
 357       The operator classes <literal>box_ops</literal> and
 358       <literal>bigbox_ops</literal> both support R-tree indexes on the
 359       <literal>box</literal> data type.
 360       The difference between them is that <literal>bigbox_ops</literal>
 361       scales box coordinates down, to avoid floating-point exceptions from
 362       doing multiplication, addition, and subtraction on very large
 363       floating-point coordinates.  (Note: this was true some time ago,
 364       but currently the two operator classes both use floating point
 365       and are effectively identical.)
 366      </para>
 367     </listitem>
 368    </itemizedlist>
 369   </para>
 370
 371    <para>
 372     The following query shows all defined operator classes:
 373
 374     <programlisting>
 375 SELECT am.amname AS acc_method,
 376        opc.opcname AS ops_name,
 377        opr.oprname AS ops_comp
 378     FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
 379     WHERE opc.opcamid = am.oid AND
 380           amop.amopclaid = opc.oid AND
 381           amop.amopopr = opr.oid
 382     ORDER BY acc_method, ops_name, ops_comp;
 383     </programlisting>
 384    </para>
 385   </refsect2>
 386  </refsect1>
 387
 388  <refsect1 id="R1-SQL-CREATEINDEX-2">
 389   <title>
 390    Usage
 391   </title>
 392   <para>To create a B-tree index on the field <literal>title</literal>
 393    in the table <literal>films</literal>:
 394   </para>
 395   <programlisting>
 396 CREATE UNIQUE INDEX title_idx
 397     ON films (title);
 398   </programlisting>
 399
 400 <!--
 401 <comment>
 402 Is this example correct?
 403 </comment>
 404   <para>
 405    To create a R-tree index on a point attribute so that we
 406    can efficiently use box operators on the result of the
 407    conversion function:
 408   </para>
 409   <programlisting>
 410 CREATE INDEX pointloc
 411     ON points USING RTREE (point2box(location) box_ops);
 412 SELECT * FROM points
 413     WHERE point2box(points.pointloc) = boxes.box;
 414   </programlisting>
 415 -->
 416
 417  </refsect1>
 418
 419  <refsect1 id="R1-SQL-CREATEINDEX-3">
 420   <title>
 421    Compatibility
 422   </title>
 423
 424   <refsect2 id="R2-SQL-CREATEINDEX-4">
 425    <refsect2info>
 426     <date>1998-09-09</date>
 427    </refsect2info>
 428    <title>
 429     SQL92
 430    </title>
 431    <para>
 432     CREATE INDEX is a <productname>PostgreSQL</productname> language extension.
 433    </para>
 434    <para>
 435     There is no <command>CREATE INDEX</command> command in SQL92.
 436    </para>
 437   </refsect2>
 438  </refsect1>
 439 </refentry>
 440
 441 <!-- Keep this comment at the end of the file
 442 Local variables:
 443 mode: sgml
 444 sgml-omittag:nil
 445 sgml-shorttag:t
 446 sgml-minimize-attributes:nil
 447 sgml-always-quote-attributes:t
 448 sgml-indent-step:1
 449 sgml-indent-data:t
 450 sgml-parent-document:nil
 451 sgml-default-dtd-file:"../reference.ced"
 452 sgml-exposed-tags:nil
 453 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 454 sgml-local-ecat-files:nil
 455 End:
 456 -->