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