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.10 2000/03/16 14:39:30 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">col</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 non-duplicate data will generate an
  50         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 which is to be used for
  78         the index. The default access method is BTREE.
  79         Postgres provides three access methods for secondary indexes:
  80
  81         <variablelist>
  82          <varlistentry>
  83           <term>BTREE</term>
  84           <listitem>
  85            <para>
  86             an implementation of the 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 user-defined function, which returns a value that can
 137         be indexed.
 138        </para>
 139       </listitem>
 140      </varlistentry>
 141     </variablelist>
 142    </para>
 143   </refsect2>
 144
 145   <refsect2 id="R2-SQL-CREATEINDEX-2">
 146    <refsect2info>
 147     <date>1998-09-09</date>
 148    </refsect2info>
 149    <title>
 150     Outputs
 151    </title>
 152    <para>
 153
 154     <variablelist>
 155      <varlistentry>
 156       <term><computeroutput>
 157 CREATE
 158        </computeroutput></term>
 159       <listitem>
 160        <para>
 161         The message returned if the index is successfully created.
 162        </para>
 163       </listitem>
 164      </varlistentry>
 165
 166      <varlistentry>
 167       <term><computeroutput>
 168 ERROR: Cannot create index: 'index_name' already exists.
 169        </computeroutput></term>
 170       <listitem>
 171        <para>
 172         This error occurs if it is impossible to create the index.
 173        </para>
 174       </listitem>
 175      </varlistentry>
 176     </variablelist>
 177    </para>
 178   </refsect2>
 179  </refsynopsisdiv>
 180
 181  <refsect1 id="R1-SQL-CREATEINDEX-1">
 182   <refsect1info>
 183    <date>1998-09-09</date>
 184   </refsect1info>
 185   <title>
 186    Description
 187   </title>
 188   <para>
 189    <command>CREATE INDEX</command> constructs an index
 190    <replaceable class="parameter">index_name</replaceable>
 191    on the specified <replaceable class="parameter">table</replaceable>.
 192
 193    <tip>
 194     <para>
 195      Indexes are primarily used to enhance database performance.
 196      But inappropriate use will result in slower performance.
 197     </para>
 198    </tip>
 199   </para>
 200
 201   <para>
 202    In the first syntax shown above, the key fields for the
 203    index are specified as column names; a column may also have
 204    an associated operator class. An operator class is used
 205    to specify the operators to be used for a particular
 206    index. For example, a btree index on four-byte integers
 207    would use the <literal>int4_ops</literal> class;
 208    this operator class includes
 209    comparison functions for four-byte integers. The default
 210    operator class is the appropriate operator class for that
 211    field type.
 212   </para>
 213
 214   <para>
 215    In the second syntax shown above, an index is defined
 216    on the result of a user-defined function
 217    <replaceable class="parameter">func_name</replaceable> applied
 218    to one or more attributes of a single class.
 219    These <firstterm>functional indices</firstterm>
 220    can be used to obtain fast access to data
 221    based on operators that would normally require some
 222    transformation to apply them to the base data.
 223   </para>
 224
 225   <para>
 226    Postgres provides btree, rtree and hash access methods for
 227    secondary indices.  The btree access method is an implementation of
 228    the Lehman-Yao high-concurrency btrees.  The rtree access method
 229    implements standard rtrees using Guttman's quadratic split algorithm.
 230    The hash access method is an implementation of Litwin's linear
 231    hashing.  We mention the algorithms used solely to indicate that all
 232    of these access methods are fully dynamic and do not have to be
 233    optimized periodically (as is the case with, for example, static hash
 234    access methods).
 235   </para>
 236
 237   <refsect2 id="R2-SQL-CREATEINDEX-3">
 238    <refsect2info>
 239     <date>1998-09-09</date>
 240    </refsect2info>
 241    <title>
 242     Notes
 243    </title>
 244
 245    <para>
 246     The Postgres query optimizer will consider using btree indices in a scan
 247     whenever an indexed attribute is involved in a comparison using one of:
 248
 249     <simplelist type="inline">
 250      <member>&lt;</member>
 251      <member>&lt;=</member>
 252      <member>=</member>
 253      <member>&gt;=</member>
 254      <member>&gt;</member>
 255     </simplelist>
 256    </para>
 257
 258    <para>
 259     Both box classes support indices on the <literal>box</literal> data
 260     type in <productname>Postgres</productname>.
 261     The difference between them is that <literal>bigbox_ops</literal>
 262     scales box coordinates down, to avoid floating point exceptions from
 263     doing multiplication, addition, and subtraction on very large
 264     floating-point coordinates.  If the field on which your rectangles lie
 265     is about 20,000 units square or larger, you should use
 266     <literal>bigbox_ops</literal>.
 267     The <literal>poly_ops</literal> operator class supports rtree
 268     indices on <literal>polygon</literal> data.
 269    </para>
 270
 271    <para>
 272     The <productname>Postgres</productname>
 273     query optimizer will consider using an rtree index whenever
 274     an indexed attribute is involved in a comparison using one of:
 275
 276     <simplelist type="inline">
 277      <member>&lt;&lt;</member>
 278      <member>&amp;&lt;</member>
 279      <member>&amp;&gt;</member>
 280      <member>&gt;&gt;</member>
 281      <member>@</member>
 282      <member>~=</member>
 283      <member>&amp;&amp;</member>
 284     </simplelist>
 285    </para>
 286
 287    <para>
 288     The <productname>Postgres</productname>
 289     query optimizer will consider using a hash index whenever
 290     an indexed attribute is involved in a comparison using
 291     the <literal>=</literal> operator.
 292    </para>
 293
 294    <para>
 295     Currently, only the BTREE access method supports multi-column
 296     indexes. Up to 7 keys may be specified.
 297    </para>
 298
 299    <para>
 300     Use <xref linkend="sql-dropindex-title" endterm="sql-dropindex-title">
 301     to remove an index.
 302    </para>
 303
 304    <para>
 305     The <literal>int24_ops</literal>
 306     operator class is useful for constructing indices on int2 data, and
 307     doing comparisons against int4 data in query qualifications.
 308     Similarly, <literal>int42_ops</literal>
 309     support indices on int4 data that is to be compared against int2 data
 310     in queries.
 311    </para>
 312
 313    <para>
 314     The following select list returns all ops_names:
 315
 316     <programlisting>
 317 SELECT am.amname AS acc_name,
 318        opc.opcname AS ops_name,
 319        opr.oprname AS ops_comp
 320     FROM pg_am am, pg_amop amop,
 321          pg_opclass opc, pg_operator opr
 322     WHERE amop.amopid = am.oid AND
 323           amop.amopclaid = opc.oid AND
 324           amop.amopopr = opr.oid
 325     ORDER BY acc_name, ops_name, ops_comp
 326     </programlisting>
 327    </para>
 328   </refsect2>
 329  </refsect1>
 330
 331  <refsect1 id="R1-SQL-CREATEINDEX-2">
 332   <title>
 333    Usage
 334   </title>
 335   <para>To create a btree index on the field <literal>title</literal>
 336    in the table <literal>films</literal>:
 337   </para>
 338   <programlisting>
 339 CREATE UNIQUE INDEX title_idx
 340     ON films (title);
 341   </programlisting>
 342
 343 <!--
 344 <comment>
 345 Is this example correct?
 346 </comment>
 347   <para>
 348    To create a rtree index on a point attribute so that we
 349    can efficiently use box operators on the result of the
 350    conversion function:
 351   </para>
 352   <programlisting>
 353 CREATE INDEX pointloc
 354     ON points USING RTREE (point2box(location) box_ops);
 355 SELECT * FROM points
 356     WHERE point2box(points.pointloc) = boxes.box;
 357   </programlisting>
 358 -->
 359
 360  </refsect1>
 361
 362  <refsect1 id="R1-SQL-CREATEINDEX-3">
 363   <title>
 364    Compatibility
 365   </title>
 366
 367   <refsect2 id="R2-SQL-CREATEINDEX-4">
 368    <refsect2info>
 369     <date>1998-09-09</date>
 370    </refsect2info>
 371    <title>
 372     SQL92
 373    </title>
 374    <para>
 375     CREATE INDEX is a <productname>Postgres</productname> language extension.
 376    </para>
 377    <para>
 378     There is no <command>CREATE INDEX</command> command in SQL92.
 379    </para>
 380   </refsect2>
 381  </refsect1>
 382 </refentry>
 383
 384 <!-- Keep this comment at the end of the file
 385 Local variables:
 386 mode: sgml
 387 sgml-omittag:nil
 388 sgml-shorttag:t
 389 sgml-minimize-attributes:nil
 390 sgml-always-quote-attributes:t
 391 sgml-indent-step:1
 392 sgml-indent-data:t
 393 sgml-parent-document:nil
 394 sgml-default-dtd-file:"../reference.ced"
 395 sgml-exposed-tags:nil
 396 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 397 sgml-local-ecat-files:nil
 398 End:
 399 -->