Debian Codegraph @

doc/src/sgml/ref/create_index.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_index.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createindex">
7 <indexterm zone="sql-createindex">
8 <primary>CREATE INDEX</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE INDEX</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE INDEX</refname>
19 <refpurpose>define a new index</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
25 ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> [ ( <replaceable class="parameter">opclass_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
26 [ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
27 [ NULLS [ NOT ] DISTINCT ]
28 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
29 [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
30 [ WHERE <replaceable class="parameter">predicate</replaceable> ]
31 </synopsis>
32 </refsynopsisdiv>
33
34 <refsect1>
35 <title>Description</title>
36
37 <para>
38 <command>CREATE INDEX</command> constructs an index on the specified column(s)
39 of the specified relation, which can be a table or a materialized view.
40 Indexes are primarily used to enhance database performance (though
41 inappropriate use can result in slower performance).
42 </para>
43
44 <para>
45 The key field(s) for the index are specified as column names,
46 or alternatively as expressions written in parentheses.
47 Multiple fields can be specified if the index method supports
48 multicolumn indexes.
49 </para>
50
51 <para>
52 An index field can be an expression computed from the values of
53 one or more columns of the table row. This feature can be used
54 to obtain fast access to data based on some transformation of
55 the basic data. For example, an index computed on
56 <literal>upper(col)</literal> would allow the clause
57 <literal>WHERE upper(col) = 'JIM'</literal> to use an index.
58 </para>
59
60 <para>
61 <productname>PostgreSQL</productname> provides the index methods
62 B-tree, hash, GiST, SP-GiST, GIN, and BRIN. Users can also define their own
63 index methods, but that is fairly complicated.
64 </para>
65
66 <para>
67 When the <literal>WHERE</literal> clause is present, a
68 <firstterm>partial index</firstterm> is created.
69 A partial index is an index that contains entries for only a portion of
70 a table, usually a portion that is more useful for indexing than the
71 rest of the table. For example, if you have a table that contains both
72 billed and unbilled orders where the unbilled orders take up a small
73 fraction of the total table and yet that is an often used section, you
74 can improve performance by creating an index on just that portion.
75 Another possible application is to use <literal>WHERE</literal> with
76 <literal>UNIQUE</literal> to enforce uniqueness over a subset of a
77 table. See <xref linkend="indexes-partial"/> for more discussion.
78 </para>
79
80 <para>
81 The expression used in the <literal>WHERE</literal> clause can refer
82 only to columns of the underlying table, but it can use all columns,
83 not just the ones being indexed. Presently, subqueries and
84 aggregate expressions are also forbidden in <literal>WHERE</literal>.
85 The same restrictions apply to index fields that are expressions.
86 </para>
87
88 <para>
89 All functions and operators used in an index definition must be
90 <quote>immutable</quote>, that is, their results must depend only on
91 their arguments and never on any outside influence (such as
92 the contents of another table or the current time). This restriction
93 ensures that the behavior of the index is well-defined. To use a
94 user-defined function in an index expression or <literal>WHERE</literal>
95 clause, remember to mark the function immutable when you create it.
96 </para>
97 </refsect1>
98
99 <refsect1>
100 <title>Parameters</title>
101
102 <variablelist>
103 <varlistentry>
104 <term><literal>UNIQUE</literal></term>
105 <listitem>
106 <para>
107 Causes the system to check for
108 duplicate values in the table when the index is created (if data
109 already exist) and each time data is added. Attempts to
110 insert or update data which would result in duplicate entries
111 will generate an error.
112 </para>
113
114 <para>
115 Additional restrictions apply when unique indexes are applied to
116 partitioned tables; see <xref linkend="sql-createtable" />.
117 </para>
118 </listitem>
119 </varlistentry>
120
121 <varlistentry>
122 <term><literal>CONCURRENTLY</literal></term>
123 <listitem>
124 <para>
125 When this option is used, <productname>PostgreSQL</productname> will build the
126 index without taking any locks that prevent concurrent inserts,
127 updates, or deletes on the table; whereas a standard index build
128 locks out writes (but not reads) on the table until it's done.
129 There are several caveats to be aware of when using this option
130 &mdash; see <xref linkend="sql-createindex-concurrently"/> below.
131 </para>
132 <para>
133 For temporary tables, <command>CREATE INDEX</command> is always
134 non-concurrent, as no other session can access them, and
135 non-concurrent index creation is cheaper.
136 </para>
137 </listitem>
138 </varlistentry>
139
140 <varlistentry>
141 <term><literal>IF NOT EXISTS</literal></term>
142 <listitem>
143 <para>
144 Do not throw an error if a relation with the same name already exists.
145 A notice is issued in this case. Note that there is no guarantee that
146 the existing index is anything like the one that would have been created.
147 Index name is required when <literal>IF NOT EXISTS</literal> is specified.
148 </para>
149 </listitem>
150 </varlistentry>
151
152 <varlistentry>
153 <term><literal>INCLUDE</literal></term>
154 <listitem>
155 <para>
156 The optional <literal>INCLUDE</literal> clause specifies a
157 list of columns which will be included in the index
158 as <firstterm>non-key</firstterm> columns. A non-key column cannot
159 be used in an index scan search qualification, and it is disregarded
160 for purposes of any uniqueness or exclusion constraint enforced by
161 the index. However, an index-only scan can return the contents of
162 non-key columns without having to visit the index's table, since
163 they are available directly from the index entry. Thus, addition of
164 non-key columns allows index-only scans to be used for queries that
165 otherwise could not use them.
166 </para>
167
168 <para>
169 It's wise to be conservative about adding non-key columns to an
170 index, especially wide columns. If an index tuple exceeds the
171 maximum size allowed for the index type, data insertion will fail.
172 In any case, non-key columns duplicate data from the index's table
173 and bloat the size of the index, thus potentially slowing searches.
174 Furthermore, B-tree deduplication is never used with indexes
175 that have a non-key column.
176 </para>
177
178 <para>
179 Columns listed in the <literal>INCLUDE</literal> clause don't need
180 appropriate operator classes; the clause can include
181 columns whose data types don't have operator classes defined for
182 a given access method.
183 </para>
184
185 <para>
186 Expressions are not supported as included columns since they cannot be
187 used in index-only scans.
188 </para>
189
190 <para>
191 Currently, the B-tree, GiST and SP-GiST index access methods support
192 this feature. In these indexes, the values of columns listed
193 in the <literal>INCLUDE</literal> clause are included in leaf tuples
194 which correspond to heap tuples, but are not included in upper-level
195 index entries used for tree navigation.
196 </para>
197 </listitem>
198 </varlistentry>
199
200 <varlistentry>
201 <term><replaceable class="parameter">name</replaceable></term>
202 <listitem>
203 <para>
204 The name of the index to be created. No schema name can be included
205 here; the index is always created in the same schema as its parent
206 table. The name of the index must be distinct from the name of any
207 other relation (table, sequence, index, view, materialized view, or
208 foreign table) in that schema.
209 If the name is omitted, <productname>PostgreSQL</productname> chooses a
210 suitable name based on the parent table's name and the indexed column
211 name(s).
212 </para>
213 </listitem>
214 </varlistentry>
215
216 <varlistentry>
217 <term><literal>ONLY</literal></term>
218 <listitem>
219 <para>
220 Indicates not to recurse creating indexes on partitions, if the
221 table is partitioned. The default is to recurse.
222 </para>
223 </listitem>
224 </varlistentry>
225
226 <varlistentry>
227 <term><replaceable class="parameter">table_name</replaceable></term>
228 <listitem>
229 <para>
230 The name (possibly schema-qualified) of the table to be indexed.
231 </para>
232 </listitem>
233 </varlistentry>
234
235 <varlistentry>
236 <term><replaceable class="parameter">method</replaceable></term>
237 <listitem>
238 <para>
239 The name of the index method to be used. Choices are
240 <literal>btree</literal>, <literal>hash</literal>,
241 <literal>gist</literal>, <literal>spgist</literal>, <literal>gin</literal>,
242 <literal>brin</literal>, or user-installed access methods like
243 <link linkend="bloom">bloom</link>.
244 The default method is <literal>btree</literal>.
245 </para>
246 </listitem>
247 </varlistentry>
248
249 <varlistentry>
250 <term><replaceable class="parameter">column_name</replaceable></term>
251 <listitem>
252 <para>
253 The name of a column of the table.
254 </para>
255 </listitem>
256 </varlistentry>
257
258 <varlistentry>
259 <term><replaceable class="parameter">expression</replaceable></term>
260 <listitem>
261 <para>
262 An expression based on one or more columns of the table. The
263 expression usually must be written with surrounding parentheses,
264 as shown in the syntax. However, the parentheses can be omitted
265 if the expression has the form of a function call.
266 </para>
267 </listitem>
268 </varlistentry>
269
270 <varlistentry>
271 <term><replaceable class="parameter">collation</replaceable></term>
272 <listitem>
273 <para>
274 The name of the collation to use for the index. By default,
275 the index uses the collation declared for the column to be
276 indexed or the result collation of the expression to be
277 indexed. Indexes with non-default collations can be useful for
278 queries that involve expressions using non-default collations.
279 </para>
280 </listitem>
281 </varlistentry>
282
283 <varlistentry>
284 <term><replaceable class="parameter">opclass</replaceable></term>
285 <listitem>
286 <para>
287 The name of an operator class. See below for details.
288 </para>
289 </listitem>
290 </varlistentry>
291
292 <varlistentry>
293 <term><replaceable class="parameter">opclass_parameter</replaceable></term>
294 <listitem>
295 <para>
296 The name of an operator class parameter. See below for details.
297 </para>
298 </listitem>
299 </varlistentry>
300
301 <varlistentry>
302 <term><literal>ASC</literal></term>
303 <listitem>
304 <para>
305 Specifies ascending sort order (which is the default).
306 </para>
307 </listitem>
308 </varlistentry>
309
310 <varlistentry>
311 <term><literal>DESC</literal></term>
312 <listitem>
313 <para>
314 Specifies descending sort order.
315 </para>
316 </listitem>
317 </varlistentry>
318
319 <varlistentry>
320 <term><literal>NULLS FIRST</literal></term>
321 <listitem>
322 <para>
323 Specifies that nulls sort before non-nulls. This is the default
324 when <literal>DESC</literal> is specified.
325 </para>
326 </listitem>
327 </varlistentry>
328
329 <varlistentry>
330 <term><literal>NULLS LAST</literal></term>
331 <listitem>
332 <para>
333 Specifies that nulls sort after non-nulls. This is the default
334 when <literal>DESC</literal> is not specified.
335 </para>
336 </listitem>
337 </varlistentry>
338
339 <varlistentry>
340 <term><literal>NULLS DISTINCT</literal></term>
341 <term><literal>NULLS NOT DISTINCT</literal></term>
342 <listitem>
343 <para>
344 Specifies whether for a unique index, null values should be considered
345 distinct (not equal). The default is that they are distinct, so that
346 a unique index could contain multiple null values in a column.
347 </para>
348 </listitem>
349 </varlistentry>
350
351 <varlistentry>
352 <term><replaceable class="parameter">storage_parameter</replaceable></term>
353 <listitem>
354 <para>
355 The name of an index-method-specific storage parameter. See
356 <xref linkend="sql-createindex-storage-parameters"/> below
357 for details.
358 </para>
359 </listitem>
360 </varlistentry>
361
362 <varlistentry>
363 <term><replaceable class="parameter">tablespace_name</replaceable></term>
364 <listitem>
365 <para>
366 The tablespace in which to create the index. If not specified,
367 <xref linkend="guc-default-tablespace"/> is consulted, or
368 <xref linkend="guc-temp-tablespaces"/> for indexes on temporary
369 tables.
370 </para>
371 </listitem>
372 </varlistentry>
373
374 <varlistentry>
375 <term><replaceable class="parameter">predicate</replaceable></term>
376 <listitem>
377 <para>
378 The constraint expression for a partial index.
379 </para>
380 </listitem>
381 </varlistentry>
382
383 </variablelist>
384
385 <refsect2 id="sql-createindex-storage-parameters" xreflabel="Index Storage Parameters">
386 <title>Index Storage Parameters</title>
387
388 <para>
389 The optional <literal>WITH</literal> clause specifies <firstterm>storage
390 parameters</firstterm> for the index. Each index method has its own set
391 of allowed storage parameters.
392 </para>
393
394 <para>
395 The B-tree, hash, GiST and SP-GiST index methods all accept this
396 parameter:
397 </para>
398
399 <variablelist>
400 <varlistentry id="index-reloption-fillfactor" xreflabel="fillfactor">
401 <term><literal>fillfactor</literal> (<type>integer</type>)
402 <indexterm>
403 <primary><varname>fillfactor</varname> storage parameter</primary>
404 </indexterm>
405 </term>
406 <listitem>
407 <para>
408 Controls how full
409 the index method will try to pack index pages. For B-trees, leaf pages
410 are filled to this percentage during initial index builds, and also
411 when extending the index at the right (adding new largest key values).
412 If pages
413 subsequently become completely full, they will be split, leading to
414 fragmentation of the on-disk index structure. B-trees use a default
415 fillfactor of 90, but any integer value from 10 to 100 can be selected.
416 </para>
417 <para>
418 B-tree indexes on tables where many inserts and/or updates are
419 anticipated can benefit from lower fillfactor settings at
420 <command>CREATE INDEX</command> time (following bulk loading into the
421 table). Values in the range of 50 - 90 can usefully <quote>smooth
422 out</quote> the <emphasis>rate</emphasis> of page splits during the
423 early life of the B-tree index (lowering fillfactor like this may even
424 lower the absolute number of page splits, though this effect is highly
425 workload dependent). The B-tree bottom-up index deletion technique
426 described in <xref linkend="btree-deletion"/> is dependent on having
427 some <quote>extra</quote> space on pages to store <quote>extra</quote>
428 tuple versions, and so can be affected by fillfactor (though the effect
429 is usually not significant).
430 </para>
431 <para>
432 In other specific cases it might be useful to increase fillfactor to
433 100 at <command>CREATE INDEX</command> time as a way of maximizing
434 space utilization. You should only consider this when you are
435 completely sure that the table is static (i.e. that it will never be
436 affected by either inserts or updates). A fillfactor setting of 100
437 otherwise risks <emphasis>harming</emphasis> performance: even a few
438 updates or inserts will cause a sudden flood of page splits.
439 </para>
440 <para>
441 The other index methods use fillfactor in different but roughly
442 analogous ways; the default fillfactor varies between methods.
443 </para>
444 </listitem>
445 </varlistentry>
446 </variablelist>
447
448 <para>
449 B-tree indexes additionally accept this parameter:
450 </para>
451
452 <variablelist>
453 <varlistentry id="index-reloption-deduplicate-items" xreflabel="deduplicate_items">
454 <term><literal>deduplicate_items</literal> (<type>boolean</type>)
455 <indexterm>
456 <primary><varname>deduplicate_items</varname> storage parameter</primary>
457 </indexterm>
458 </term>
459 <listitem>
460 <para>
461 Controls usage of the B-tree deduplication technique described
462 in <xref linkend="btree-deduplication"/>. Set to
463 <literal>ON</literal> or <literal>OFF</literal> to enable or
464 disable the optimization. (Alternative spellings of
465 <literal>ON</literal> and <literal>OFF</literal> are allowed as
466 described in <xref linkend="config-setting"/>.) The default is
467 <literal>ON</literal>.
468 </para>
469
470 <note>
471 <para>
472 Turning <literal>deduplicate_items</literal> off via
473 <command>ALTER INDEX</command> prevents future insertions from
474 triggering deduplication, but does not in itself make existing
475 posting list tuples use the standard tuple representation.
476 </para>
477 </note>
478 </listitem>
479 </varlistentry>
480 </variablelist>
481
482 <para>
483 GiST indexes additionally accept this parameter:
484 </para>
485
486 <variablelist>
487 <varlistentry id="index-reloption-buffering" xreflabel="buffering">
488 <term><literal>buffering</literal> (<type>enum</type>)
489 <indexterm>
490 <primary><varname>buffering</varname> storage parameter</primary>
491 </indexterm>
492 </term>
493 <listitem>
494 <para>
495 Controls whether the buffered build technique described in
496 <xref linkend="gist-buffering-build"/> is used to build the index. With
497 <literal>OFF</literal> buffering is disabled, with <literal>ON</literal>
498 it is enabled, and with <literal>AUTO</literal> it is initially disabled,
499 but is turned on on-the-fly once the index size reaches
500 <xref linkend="guc-effective-cache-size"/>. The default
501 is <literal>AUTO</literal>.
502 Note that if sorted build is possible, it will be used instead of
503 buffered build unless <literal>buffering=ON</literal> is specified.
504 </para>
505 </listitem>
506 </varlistentry>
507 </variablelist>
508
509 <para>
510 GIN indexes accept these parameters:
511 </para>
512
513 <variablelist>
514 <varlistentry id="index-reloption-fastupdate" xreflabel="fastupdate">
515 <term><literal>fastupdate</literal> (<type>boolean</type>)
516 <indexterm>
517 <primary><varname>fastupdate</varname> storage parameter</primary>
518 </indexterm>
519 </term>
520 <listitem>
521 <para>
522 Controls usage of the fast update technique described in
523 <xref linkend="gin-fast-update"/>.
524 <literal>ON</literal> enables fast update, <literal>OFF</literal> disables it.
525 The default is <literal>ON</literal>.
526 </para>
527
528 <note>
529 <para>
530 Turning <literal>fastupdate</literal> off via <command>ALTER INDEX</command> prevents
531 future insertions from going into the list of pending index entries,
532 but does not in itself flush existing entries. You might want to
533 <command>VACUUM</command> the table or call
534 the <function>gin_clean_pending_list</function>
535 function afterward to ensure the pending list is emptied.
536 </para>
537 </note>
538 </listitem>
539 </varlistentry>
540 </variablelist>
541
542 <variablelist>
543 <varlistentry id="index-reloption-gin-pending-list-limit" xreflabel="gin_pending_list_limit">
544 <term><literal>gin_pending_list_limit</literal> (<type>integer</type>)
545 <indexterm>
546 <primary><varname>gin_pending_list_limit</varname></primary>
547 <secondary>storage parameter</secondary>
548 </indexterm>
549 </term>
550 <listitem>
551 <para>
552 Overrides the global setting of
553 <xref linkend="guc-gin-pending-list-limit"/> for this index.
554 This value is specified in kilobytes.
555 </para>
556 </listitem>
557 </varlistentry>
558 </variablelist>
559
560 <para>
561 <acronym>BRIN</acronym> indexes accept these parameters:
562 </para>
563
564 <variablelist>
565 <varlistentry id="index-reloption-pages-per-range" xreflabel="pages_per_range">
566 <term><literal>pages_per_range</literal> (<type>integer</type>)
567 <indexterm>
568 <primary><varname>pages_per_range</varname> storage parameter</primary>
569 </indexterm>
570 </term>
571 <listitem>
572 <para>
573 Defines the number of table blocks that make up one block range for
574 each entry of a <acronym>BRIN</acronym> index (see <xref linkend="brin-intro"/>
575 for more details). The default is <literal>128</literal>.
576 </para>
577 </listitem>
578 </varlistentry>
579
580 <varlistentry id="index-reloption-autosummarize" xreflabel="autosummarize">
581 <term><literal>autosummarize</literal> (<type>boolean</type>)
582 <indexterm>
583 <primary><varname>autosummarize</varname> storage parameter</primary>
584 </indexterm>
585 </term>
586 <listitem>
587 <para>
588 Defines whether a summarization run is queued for the previous page
589 range whenever an insertion is detected on the next one
590 (see <xref linkend="brin-operation"/> for more details).
591 The default is <literal>off</literal>.
592 </para>
593 </listitem>
594 </varlistentry>
595 </variablelist>
596 </refsect2>
597
598 <refsect2 id="sql-createindex-concurrently" xreflabel="Building Indexes Concurrently">
599 <title>Building Indexes Concurrently</title>
600
601 <indexterm zone="sql-createindex-concurrently">
602 <primary>index</primary>
603 <secondary>building concurrently</secondary>
604 </indexterm>
605
606 <para>
607 Creating an index can interfere with regular operation of a database.
608 Normally <productname>PostgreSQL</productname> locks the table to be indexed against
609 writes and performs the entire index build with a single scan of the
610 table. Other transactions can still read the table, but if they try to
611 insert, update, or delete rows in the table they will block until the
612 index build is finished. This could have a severe effect if the system is
613 a live production database. Very large tables can take many hours to be
614 indexed, and even for smaller tables, an index build can lock out writers
615 for periods that are unacceptably long for a production system.
616 </para>
617
618 <para>
619 <productname>PostgreSQL</productname> supports building indexes without locking
620 out writes. This method is invoked by specifying the
621 <literal>CONCURRENTLY</literal> option of <command>CREATE INDEX</command>.
622 When this option is used,
623 <productname>PostgreSQL</productname> must perform two scans of the table, and in
624 addition it must wait for all existing transactions that could potentially
625 modify or use the index to terminate. Thus
626 this method requires more total work than a standard index build and takes
627 significantly longer to complete. However, since it allows normal
628 operations to continue while the index is built, this method is useful for
629 adding new indexes in a production environment. Of course, the extra CPU
630 and I/O load imposed by the index creation might slow other operations.
631 </para>
632
633 <para>
634 In a concurrent index build, the index is actually entered as an
635 <quote>invalid</quote> index into
636 the system catalogs in one transaction, then two table scans occur in
637 two more transactions. Before each table scan, the index build must
638 wait for existing transactions that have modified the table to terminate.
639 After the second scan, the index build must wait for any transactions
640 that have a snapshot (see <xref linkend="mvcc"/>) predating the second
641 scan to terminate, including transactions used by any phase of concurrent
642 index builds on other tables, if the indexes involved are partial or have
643 columns that are not simple column references.
644 Then finally the index can be marked <quote>valid</quote> and ready for use,
645 and the <command>CREATE INDEX</command> command terminates.
646 Even then, however, the index may not be immediately usable for queries:
647 in the worst case, it cannot be used as long as transactions exist that
648 predate the start of the index build.
649 </para>
650
651 <para>
652 If a problem arises while scanning the table, such as a deadlock or a
653 uniqueness violation in a unique index, the <command>CREATE INDEX</command>
654 command will fail but leave behind an <quote>invalid</quote> index. This index
655 will be ignored for querying purposes because it might be incomplete;
656 however it will still consume update overhead. The <application>psql</application>
657 <command>\d</command> command will report such an index as <literal>INVALID</literal>:
658
659 <programlisting>
660 postgres=# \d tab
661 Table "public.tab"
662 Column | Type | Collation | Nullable | Default
663 --------+---------+-----------+----------+---------
664 col | integer | | |
665 Indexes:
666 "idx" btree (col) INVALID
667 </programlisting>
668
669 The recommended recovery
670 method in such cases is to drop the index and try again to perform
671 <command>CREATE INDEX CONCURRENTLY</command>. (Another possibility is
672 to rebuild the index with <command>REINDEX INDEX CONCURRENTLY</command>).
673 </para>
674
675 <para>
676 Another caveat when building a unique index concurrently is that the
677 uniqueness constraint is already being enforced against other transactions
678 when the second table scan begins. This means that constraint violations
679 could be reported in other queries prior to the index becoming available
680 for use, or even in cases where the index build eventually fails. Also,
681 if a failure does occur in the second scan, the <quote>invalid</quote> index
682 continues to enforce its uniqueness constraint afterwards.
683 </para>
684
685 <para>
686 Concurrent builds of expression indexes and partial indexes are supported.
687 Errors occurring in the evaluation of these expressions could cause
688 behavior similar to that described above for unique constraint violations.
689 </para>
690
691 <para>
692 Regular index builds permit other regular index builds on the
693 same table to occur simultaneously, but only one concurrent index build
694 can occur on a table at a time. In either case, schema modification of the
695 table is not allowed while the index is being built. Another difference is
696 that a regular <command>CREATE INDEX</command> command can be performed
697 within a transaction block, but <command>CREATE INDEX CONCURRENTLY</command>
698 cannot.
699 </para>
700
701 <para>
702 Concurrent builds for indexes on partitioned tables are currently not
703 supported. However, you may concurrently build the index on each
704 partition individually and then finally create the partitioned index
705 non-concurrently in order to reduce the time where writes to the
706 partitioned table will be locked out. In this case, building the
707 partitioned index is a metadata only operation.
708 </para>
709
710 </refsect2>
711 </refsect1>
712
713 <refsect1>
714 <title>Notes</title>
715
716 <para>
717 See <xref linkend="indexes"/> for information about when indexes can
718 be used, when they are not used, and in which particular situations
719 they can be useful.
720 </para>
721
722 <para>
723 Currently, only the B-tree, GiST, GIN, and BRIN index methods support
724 multiple-key-column indexes. Whether there can be multiple key
725 columns is independent of whether <literal>INCLUDE</literal> columns
726 can be added to the index. Indexes can have up to 32 columns,
727 including <literal>INCLUDE</literal> columns.
728 (This limit can be altered when building
729 <productname>PostgreSQL</productname>.) Only B-tree currently
730 supports unique indexes.
731 </para>
732
733 <para>
734 An <firstterm>operator class</firstterm> with optional parameters
735 can be specified for each column of an index.
736 The operator class identifies the operators to be
737 used by the index for that column. For example, a B-tree index on
738 four-byte integers would use the <literal>int4_ops</literal> class;
739 this operator class includes comparison functions for four-byte
740 integers. In practice the default operator class for the column's data
741 type is usually sufficient. The main point of having operator classes
742 is that for some data types, there could be more than one meaningful
743 ordering. For example, we might want to sort a complex-number data
744 type either by absolute value or by real part. We could do this by
745 defining two operator classes for the data type and then selecting
746 the proper class when creating an index. More information about
747 operator classes is in <xref linkend="indexes-opclass"/> and in <xref
748 linkend="xindex"/>.
749 </para>
750
751 <para>
752 When <literal>CREATE INDEX</literal> is invoked on a partitioned
753 table, the default behavior is to recurse to all partitions to ensure
754 they all have matching indexes.
755 Each partition is first checked to determine whether an equivalent
756 index already exists, and if so, that index will become attached as a
757 partition index to the index being created, which will become its
758 parent index.
759 If no matching index exists, a new index will be created and
760 automatically attached; the name of the new index in each partition
761 will be determined as if no index name had been specified in the
762 command.
763 If the <literal>ONLY</literal> option is specified, no recursion
764 is done, and the index is marked invalid.
765 (<command>ALTER INDEX ... ATTACH PARTITION</command> marks the index
766 valid, once all partitions acquire matching indexes.) Note, however,
767 that any partition that is created in the future using
768 <command>CREATE TABLE ... PARTITION OF</command> will automatically
769 have a matching index, regardless of whether <literal>ONLY</literal> is
770 specified.
771 </para>
772
773 <para>
774 For index methods that support ordered scans (currently, only B-tree),
775 the optional clauses <literal>ASC</literal>, <literal>DESC</literal>, <literal>NULLS
776 FIRST</literal>, and/or <literal>NULLS LAST</literal> can be specified to modify
777 the sort ordering of the index. Since an ordered index can be
778 scanned either forward or backward, it is not normally useful to create a
779 single-column <literal>DESC</literal> index &mdash; that sort ordering is already
780 available with a regular index. The value of these options is that
781 multicolumn indexes can be created that match the sort ordering requested
782 by a mixed-ordering query, such as <literal>SELECT ... ORDER BY x ASC, y
783 DESC</literal>. The <literal>NULLS</literal> options are useful if you need to support
784 <quote>nulls sort low</quote> behavior, rather than the default <quote>nulls
785 sort high</quote>, in queries that depend on indexes to avoid sorting steps.
786 </para>
787
788 <para>
789 The system regularly collects statistics on all of a table's
790 columns. Newly-created non-expression indexes can immediately
791 use these statistics to determine an index's usefulness.
792 For new expression indexes, it is necessary to run <link
793 linkend="sql-analyze"><command>ANALYZE</command></link> or wait for
794 the <link linkend="autovacuum">autovacuum daemon</link> to analyze
795 the table to generate statistics for these indexes.
796 </para>
797
798 <para>
799 While <command>CREATE INDEX</command> is running, the <xref
800 linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
801 pg_temp</literal>.
802 </para>
803
804 <para>
805 For most index methods, the speed of creating an index is
806 dependent on the setting of <xref linkend="guc-maintenance-work-mem"/>.
807 Larger values will reduce the time needed for index creation, so long
808 as you don't make it larger than the amount of memory really available,
809 which would drive the machine into swapping.
810 </para>
811
812 <para>
813 <productname>PostgreSQL</productname> can build indexes while
814 leveraging multiple CPUs in order to process the table rows faster.
815 This feature is known as <firstterm>parallel index
816 build</firstterm>. For index methods that support building indexes
817 in parallel (currently, B-tree, GIN, and BRIN),
818 <varname>maintenance_work_mem</varname> specifies the maximum
819 amount of memory that can be used by each index build operation as
820 a whole, regardless of how many worker processes were started.
821 Generally, a cost model automatically determines how many worker
822 processes should be requested, if any.
823 </para>
824
825 <para>
826 Parallel index builds may benefit from increasing
827 <varname>maintenance_work_mem</varname> where an equivalent serial
828 index build will see little or no benefit. Note that
829 <varname>maintenance_work_mem</varname> may influence the number of
830 worker processes requested, since parallel workers must have at
831 least a <literal>32MB</literal> share of the total
832 <varname>maintenance_work_mem</varname> budget. There must also be
833 a remaining <literal>32MB</literal> share for the leader process.
834 Increasing <xref linkend="guc-max-parallel-maintenance-workers"/>
835 may allow more workers to be used, which will reduce the time
836 needed for index creation, so long as the index build is not
837 already I/O bound. Of course, there should also be sufficient
838 CPU capacity that would otherwise lie idle.
839 </para>
840
841 <para>
842 Setting a value for <literal>parallel_workers</literal> via <link
843 linkend="sql-altertable"><command>ALTER TABLE</command></link> directly controls how many parallel
844 worker processes will be requested by a <command>CREATE
845 INDEX</command> against the table. This bypasses the cost model
846 completely, and prevents <varname>maintenance_work_mem</varname>
847 from affecting how many parallel workers are requested. Setting
848 <literal>parallel_workers</literal> to 0 via <command>ALTER
849 TABLE</command> will disable parallel index builds on the table in
850 all cases.
851 </para>
852
853 <tip>
854 <para>
855 You might want to reset <literal>parallel_workers</literal> after
856 setting it as part of tuning an index build. This avoids
857 inadvertent changes to query plans, since
858 <literal>parallel_workers</literal> affects
859 <emphasis>all</emphasis> parallel table scans.
860 </para>
861 </tip>
862
863 <para>
864 While <command>CREATE INDEX</command> with the
865 <literal>CONCURRENTLY</literal> option supports parallel builds
866 without special restrictions, only the first table scan is actually
867 performed in parallel.
868 </para>
869
870 <para>
871 Use <link linkend="sql-dropindex"><command>DROP INDEX</command></link>
872 to remove an index.
873 </para>
874
875 <para>
876 Like any long-running transaction, <command>CREATE INDEX</command> on a
877 table can affect which tuples can be removed by concurrent
878 <command>VACUUM</command> on any other table.
879 </para>
880
881 <para>
882 Prior releases of <productname>PostgreSQL</productname> also had an
883 R-tree index method. This method has been removed because
884 it had no significant advantages over the GiST method.
885 If <literal>USING rtree</literal> is specified, <command>CREATE INDEX</command>
886 will interpret it as <literal>USING gist</literal>, to simplify conversion
887 of old databases to GiST.
888 </para>
889
890 <para>
891 Each backend running <command>CREATE INDEX</command> will report its
892 progress in the <structname>pg_stat_progress_create_index</structname>
893 view. See <xref linkend="create-index-progress-reporting"/> for details.
894 </para>
895 </refsect1>
896
897 <refsect1>
898 <title>Examples</title>
899
900 <para>
901 To create a unique B-tree index on the column <literal>title</literal> in
902 the table <literal>films</literal>:
903 <programlisting>
904 CREATE UNIQUE INDEX title_idx ON films (title);
905 </programlisting>
906 </para>
907
908 <para>
909 To create a unique B-tree index on the column <literal>title</literal>
910 with included columns <literal>director</literal>
911 and <literal>rating</literal> in the table <literal>films</literal>:
912 <programlisting>
913 CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);
914 </programlisting>
915 </para>
916
917 <para>
918 To create a B-Tree index with deduplication disabled:
919 <programlisting>
920 CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);
921 </programlisting>
922 </para>
923
924 <para>
925 To create an index on the expression <literal>lower(title)</literal>,
926 allowing efficient case-insensitive searches:
927 <programlisting>
928 CREATE INDEX ON films ((lower(title)));
929 </programlisting>
930 (In this example we have chosen to omit the index name, so the system
931 will choose a name, typically <literal>films_lower_idx</literal>.)
932 </para>
933
934 <para>
935 To create an index with non-default collation:
936 <programlisting>
937 CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");
938 </programlisting>
939 </para>
940
941 <para>
942 To create an index with non-default sort ordering of nulls:
943 <programlisting>
944 CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
945 </programlisting>
946 </para>
947
948 <para>
949 To create an index with non-default fill factor:
950 <programlisting>
951 CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
952 </programlisting>
953 </para>
954
955 <para>
956 To create a <acronym>GIN</acronym> index with fast updates disabled:
957 <programlisting>
958 CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);
959 </programlisting>
960 </para>
961
962 <para>
963 To create an index on the column <literal>code</literal> in the table
964 <literal>films</literal> and have the index reside in the tablespace
965 <literal>indexspace</literal>:
966 <programlisting>
967 CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;
968 </programlisting>
969 </para>
970
971 <para>
972 To create a GiST index on a point attribute so that we
973 can efficiently use box operators on the result of the
974 conversion function:
975 <programlisting>
976 CREATE INDEX pointloc
977 ON points USING gist (box(location,location));
978 SELECT * FROM points
979 WHERE box(location,location) &amp;&amp; '(0,0),(1,1)'::box;
980 </programlisting>
981 </para>
982
983 <para>
984 To create an index without locking out writes to the table:
985 <programlisting>
986 CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
987 </programlisting></para>
988
989 </refsect1>
990
991 <refsect1>
992 <title>Compatibility</title>
993
994 <para>
995 <command>CREATE INDEX</command> is a
996 <productname>PostgreSQL</productname> language extension. There
997 are no provisions for indexes in the SQL standard.
998 </para>
999 </refsect1>
1000
1001 <refsect1>
1002 <title>See Also</title>
1003
1004 <simplelist type="inline">
1005 <member><xref linkend="sql-alterindex"/></member>
1006 <member><xref linkend="sql-dropindex"/></member>
1007 <member><xref linkend="sql-reindex"/></member>
1008 <member><xref linkend="create-index-progress-reporting"/></member>
1009 </simplelist>
1010 </refsect1>
1011 </refentry>