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.13 2000/05/02 20:02:03 thomas 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    Constructs a secondary index
  19   </refpurpose>
  20  </refnamediv>
  21  <refsynopsisdiv>
  22   <refsynopsisdivinfo>
  23    <date>1999-07-20</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_name</replaceable> ] ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable> ] [, ...] )
  28 CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable>
  29     [ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) [ <replaceable class="parameter">ops_name</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.
  60        </para>
  61       </listitem>
  62      </varlistentry>
  63
  64      <varlistentry>
  65       <term><replaceable class="parameter">table</replaceable></term>
  66       <listitem>
  67        <para>
  68         The name of the table to be indexed.
  69        </para>
  70       </listitem>
  71      </varlistentry>
  72
  73      <varlistentry>
  74       <term><replaceable class="parameter">acc_name</replaceable></term>
  75       <listitem>
  76        <para>
  77         The name of the access method to be used for
  78         the index. The default access method is BTREE.
  79         Postgres provides three access methods for indexes:
  80
  81         <variablelist>
  82          <varlistentry>
  83           <term>BTREE</term>
  84           <listitem>
  85            <para>
  86             an implementation of Lehman-Yao
  87             high-concurrency btrees.
  88            </para>
  89           </listitem>
  90          </varlistentry>
  91
  92          <varlistentry>
  93           <term>RTREE</term>
  94           <listitem>
  95            <para>implements standard rtrees using Guttman's
  96             quadratic split algorithm.
  97            </para>
  98           </listitem>
  99          </varlistentry>
 100
 101          <varlistentry>
 102           <term>HASH</term>
 103           <listitem>
 104            <para>
 105             an implementation of Litwin's linear hashing.
 106            </para>
 107           </listitem>
 108          </varlistentry>
 109         </variablelist>
 110        </para>
 111       </listitem>
 112      </varlistentry>
 113
 114      <varlistentry>
 115       <term><replaceable class="parameter">column</replaceable></term>
 116       <listitem>
 117        <para>
 118         The name of a column of the table.
 119        </para>
 120       </listitem>
 121      </varlistentry>
 122
 123      <varlistentry>
 124       <term><replaceable class="parameter">ops_name</replaceable></term>
 125       <listitem>
 126        <para>
 127         An associated operator class. See below for details.
 128        </para>
 129       </listitem>
 130      </varlistentry>
 131
 132      <varlistentry>
 133       <term><replaceable class="parameter">func_name</replaceable></term>
 134       <listitem>
 135        <para>
 136         A function, which returns a value that can be indexed.
 137        </para>
 138       </listitem>
 139      </varlistentry>
 140     </variablelist>
 141    </para>
 142   </refsect2>
 143
 144   <refsect2 id="R2-SQL-CREATEINDEX-2">
 145    <refsect2info>
 146     <date>1998-09-09</date>
 147    </refsect2info>
 148    <title>
 149     Outputs
 150    </title>
 151    <para>
 152
 153     <variablelist>
 154      <varlistentry>
 155       <term><computeroutput>
 156 CREATE
 157        </computeroutput></term>
 158       <listitem>
 159        <para>
 160         The message returned if the index is successfully created.
 161        </para>
 162       </listitem>
 163      </varlistentry>
 164
 165      <varlistentry>
 166       <term><computeroutput>
 167 ERROR: Cannot create index: 'index_name' already exists.
 168        </computeroutput></term>
 169       <listitem>
 170        <para>
 171         This error occurs if it is impossible to create the index.
 172        </para>
 173       </listitem>
 174      </varlistentry>
 175     </variablelist>
 176    </para>
 177   </refsect2>
 178  </refsynopsisdiv>
 179
 180  <refsect1 id="R1-SQL-CREATEINDEX-1">
 181   <refsect1info>
 182    <date>1998-09-09</date>
 183   </refsect1info>
 184   <title>
 185    Description
 186   </title>
 187   <para>
 188    <command>CREATE INDEX</command> constructs an index
 189    <replaceable class="parameter">index_name</replaceable>
 190    on the specified <replaceable class="parameter">table</replaceable>.
 191
 192    <tip>
 193     <para>
 194      Indexes are primarily used to enhance database performance.
 195      But inappropriate use will result in slower performance.
 196     </para>
 197    </tip>
 198   </para>
 199
 200   <para>
 201    In the first syntax shown above, the key field(s) for the
 202    index are specified as column names.
 203    Multiple fields can be specified if the index access method supports
 204    multi-column indexes.
 205   </para>
 206
 207   <para>
 208    In the second syntax shown above, an index is defined
 209    on the result of a user-specified function
 210    <replaceable class="parameter">func_name</replaceable> applied
 211    to one or more attributes of a single class.
 212    These <firstterm>functional indices</firstterm>
 213    can be used to obtain fast access to data
 214    based on operators that would normally require some
 215    transformation to apply them to the base data.
 216   </para>
 217
 218   <para>
 219    Postgres provides btree, rtree and hash access methods for
 220    indices.  The btree access method is an implementation of
 221    Lehman-Yao high-concurrency btrees.  The rtree access method
 222    implements standard rtrees using Guttman's quadratic split algorithm.
 223    The hash access method is an implementation of Litwin's linear
 224    hashing.  We mention the algorithms used solely to indicate that all
 225    of these access methods are fully dynamic and do not have to be
 226    optimized periodically (as is the case with, for example, static hash
 227    access methods).
 228   </para>
 229
 230   <para>
 231    Use <xref linkend="sql-dropindex-title" endterm="sql-dropindex-title">
 232    to remove an index.
 233   </para>
 234
 235   <refsect2 id="R2-SQL-CREATEINDEX-3">
 236    <refsect2info>
 237     <date>1998-09-09</date>
 238    </refsect2info>
 239    <title>
 240     Notes
 241    </title>
 242
 243    <para>
 244     The <productname>Postgres</productname>
 245     query optimizer will consider using a btree index whenever
 246     an indexed attribute is involved in a comparison using one of:
 247
 248     <simplelist type="inline">
 249      <member>&lt;</member>
 250      <member>&lt;=</member>
 251      <member>=</member>
 252      <member>&gt;=</member>
 253      <member>&gt;</member>
 254     </simplelist>
 255    </para>
 256
 257    <para>
 258     The <productname>Postgres</productname>
 259     query optimizer will consider using an rtree index whenever
 260     an indexed attribute is involved in a comparison using one of:
 261
 262     <simplelist type="inline">
 263      <member>&lt;&lt;</member>
 264      <member>&amp;&lt;</member>
 265      <member>&amp;&gt;</member>
 266      <member>&gt;&gt;</member>
 267      <member>@</member>
 268      <member>~=</member>
 269      <member>&amp;&amp;</member>
 270     </simplelist>
 271    </para>
 272
 273    <para>
 274     The <productname>Postgres</productname>
 275     query optimizer will consider using a hash index whenever
 276     an indexed attribute is involved in a comparison using
 277     the <literal>=</literal> operator.
 278    </para>
 279
 280    <para>
 281     Currently, only the btree access method supports multi-column
 282     indexes. Up to 16 keys may be specified by default (this limit
 283     can be altered when building Postgres).
 284    </para>
 285
 286   <para>
 287    An <firstterm>operator class</firstterm> can be specified for each
 288    column of an index.  The operator class identifies the operators to
 289    be used by the index for that column.  For example, a btree index on
 290    four-byte integers would use the <literal>int4_ops</literal> class;
 291    this operator class includes comparison functions for four-byte
 292    integers.  In practice the default operator class for the field's
 293    datatype is usually sufficient.  The main point of having operator classes
 294    is that for some datatypes, there could be more than one meaningful
 295    ordering.  For example, we might want to sort a complex-number datatype
 296    either by absolute value or by real part.  We could do this by defining
 297    two operator classes for the datatype and then selecting the proper
 298    class when making an index.  There are also some operator classes with
 299    special purposes:
 300
 301    <itemizedlist>
 302     <listitem>
 303      <para>
 304       The operator classes <literal>box_ops</literal> and
 305       <literal>bigbox_ops</literal> both support rtree indices on the
 306       <literal>box</literal> datatype.
 307       The difference between them is that <literal>bigbox_ops</literal>
 308       scales box coordinates down, to avoid floating point exceptions from
 309       doing multiplication, addition, and subtraction on very large
 310       floating-point coordinates.  If the field on which your rectangles lie
 311       is about 20,000 units square or larger, you should use
 312       <literal>bigbox_ops</literal>.
 313      </para>
 314     </listitem>
 315
 316     <listitem>
 317      <para>
 318       The <literal>int24_ops</literal>
 319       operator class is useful for constructing indices on int2 data, and
 320       doing comparisons against int4 data in query qualifications.
 321       Similarly, <literal>int42_ops</literal>
 322       support indices on int4 data that is to be compared against int2 data
 323       in queries.
 324      </para>
 325     </listitem>
 326    </itemizedlist>
 327   </para>
 328
 329    <para>
 330     The following query shows all defined operator classes:
 331
 332     <programlisting>
 333 SELECT am.amname AS acc_name,
 334        opc.opcname AS ops_name,
 335        opr.oprname AS ops_comp
 336     FROM pg_am am, pg_amop amop,
 337          pg_opclass opc, pg_operator opr
 338     WHERE amop.amopid = am.oid AND
 339           amop.amopclaid = opc.oid AND
 340           amop.amopopr = opr.oid
 341     ORDER BY acc_name, ops_name, ops_comp
 342     </programlisting>
 343    </para>
 344   </refsect2>
 345  </refsect1>
 346
 347  <refsect1 id="R1-SQL-CREATEINDEX-2">
 348   <title>
 349    Usage
 350   </title>
 351   <para>To create a btree index on the field <literal>title</literal>
 352    in the table <literal>films</literal>:
 353   </para>
 354   <programlisting>
 355 CREATE UNIQUE INDEX title_idx
 356     ON films (title);
 357   </programlisting>
 358
 359 <!--
 360 <comment>
 361 Is this example correct?
 362 </comment>
 363   <para>
 364    To create a rtree index on a point attribute so that we
 365    can efficiently use box operators on the result of the
 366    conversion function:
 367   </para>
 368   <programlisting>
 369 CREATE INDEX pointloc
 370     ON points USING RTREE (point2box(location) box_ops);
 371 SELECT * FROM points
 372     WHERE point2box(points.pointloc) = boxes.box;
 373   </programlisting>
 374 -->
 375
 376  </refsect1>
 377
 378  <refsect1 id="R1-SQL-CREATEINDEX-3">
 379   <title>
 380    Compatibility
 381   </title>
 382
 383   <refsect2 id="R2-SQL-CREATEINDEX-4">
 384    <refsect2info>
 385     <date>1998-09-09</date>
 386    </refsect2info>
 387    <title>
 388     SQL92
 389    </title>
 390    <para>
 391     CREATE INDEX is a <productname>Postgres</productname> language extension.
 392    </para>
 393    <para>
 394     There is no <command>CREATE INDEX</command> command in SQL92.
 395    </para>
 396   </refsect2>
 397  </refsect1>
 398 </refentry>
 399
 400 <!-- Keep this comment at the end of the file
 401 Local variables:
 402 mode: sgml
 403 sgml-omittag:nil
 404 sgml-shorttag:t
 405 sgml-minimize-attributes:nil
 406 sgml-always-quote-attributes:t
 407 sgml-indent-step:1
 408 sgml-indent-data:t
 409 sgml-parent-document:nil
 410 sgml-default-dtd-file:"../reference.ced"
 411 sgml-exposed-tags:nil
 412 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 413 sgml-local-ecat-files:nil
 414 End:
 415 -->