2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.34 2002/06/21 19:06:44 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATEINDEX">
8 <refentrytitle id="sql-createindex-title">CREATE INDEX</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
21 <date>2001-07-15</date>
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> ]
32 <refsect2 id="R2-SQL-CREATEINDEX-1">
34 <date>1998-09-09</date>
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.
56 <term><replaceable class="parameter">index_name</replaceable></term>
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
67 <term><replaceable class="parameter">table</replaceable></term>
70 The name (possibly schema-qualified) of the table to be indexed.
76 <term><replaceable class="parameter">acc_method</replaceable></term>
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
86 <term><literal>BTREE</></term>
89 an implementation of Lehman-Yao
90 high-concurrency B-trees.
96 <term><literal>RTREE</></term>
98 <para>implements standard R-trees using Guttman's
99 quadratic split algorithm.
105 <term><literal>HASH</></term>
108 an implementation of Litwin's linear hashing.
114 <term><literal>GIST</></term>
117 Generalized Index Search Trees.
127 <term><replaceable class="parameter">column</replaceable></term>
130 The name of a column of the table.
136 <term><replaceable class="parameter">ops_name</replaceable></term>
139 An associated operator class. See below for details.
145 <term><replaceable class="parameter">func_name</replaceable></term>
148 A function, which returns a value that can be indexed.
154 <term><replaceable class="parameter">predicate</replaceable></term>
157 Defines the constraint expression for a partial index.
165 <refsect2 id="R2-SQL-CREATEINDEX-2">
167 <date>1998-09-09</date>
176 <term><computeroutput>
178 </computeroutput></term>
181 The message returned if the index is successfully created.
187 <term><computeroutput>
188 ERROR: Cannot create index: 'index_name' already exists.
189 </computeroutput></term>
192 This error occurs if it is impossible to create the index.
201 <refsect1 id="R1-SQL-CREATEINDEX-1">
203 <date>1998-09-09</date>
209 <command>CREATE INDEX</command> constructs an index
210 <replaceable class="parameter">index_name</replaceable>
211 on the specified <replaceable class="parameter">table</replaceable>.
215 Indexes are primarily used to enhance database performance.
216 But inappropriate use will result in slower performance.
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
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.
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).
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
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>.
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
284 Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title">
288 <refsect2 id="R2-SQL-CREATEINDEX-3">
290 <date>1998-09-09</date>
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:
301 <simplelist type="inline">
302 <member><</member>
303 <member><=</member>
305 <member>>=</member>
306 <member>></member>
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:
315 <simplelist type="inline">
316 <member><<</member>
317 <member>&<</member>
318 <member>&></member>
319 <member>>></member>
322 <member>&&</member>
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.
333 Testing has shown PostgreSQL's hash indexes to be similar or slower
334 than btree 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
341 Currently, only the B-tree and gist access methods support multicolumn
342 indexes. Up to 16 keys may be specified by default (this limit
343 can be altered when building
344 <application>PostgreSQL</application>). Only B-tree currently supports
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:
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.)
381 The following query shows all defined operator classes:
384 SELECT am.amname AS acc_method,
385 opc.opcname AS ops_name,
386 opr.oprname AS ops_comp
387 FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
388 WHERE opc.opcamid = am.oid AND
389 amop.amopclaid = opc.oid AND
390 amop.amopopr = opr.oid
391 ORDER BY acc_method, ops_name, ops_comp;
397 <refsect1 id="R1-SQL-CREATEINDEX-2">
401 <para>To create a B-tree index on the field <literal>title</literal>
402 in the table <literal>films</literal>:
405 CREATE UNIQUE INDEX title_idx
411 Is this example correct?
414 To create a R-tree index on a point attribute so that we
415 can efficiently use box operators on the result of the
419 CREATE INDEX pointloc
420 ON points USING RTREE (point2box(location) box_ops);
422 WHERE point2box(points.pointloc) = boxes.box;
428 <refsect1 id="R1-SQL-CREATEINDEX-3">
433 <refsect2 id="R2-SQL-CREATEINDEX-4">
435 <date>1998-09-09</date>
441 CREATE INDEX is a <productname>PostgreSQL</productname> language extension.
444 There is no <command>CREATE INDEX</command> command in SQL92.
450 <!-- Keep this comment at the end of the file
455 sgml-minimize-attributes:nil
456 sgml-always-quote-attributes:t
459 sgml-parent-document:nil
460 sgml-default-dtd-file:"../reference.ced"
461 sgml-exposed-tags:nil
462 sgml-local-catalogs:"/usr/lib/sgml/catalog"
463 sgml-local-ecat-files:nil