doc/src/sgml/ref/create_index.sgml

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