diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createindex.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createindex.html | 580 |
1 files changed, 580 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createindex.html b/doc/src/sgml/html/sql-createindex.html new file mode 100644 index 0000000..80d3c1f --- /dev/null +++ b/doc/src/sgml/html/sql-createindex.html @@ -0,0 +1,580 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE INDEX</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-creategroup.html" title="CREATE GROUP" /><link rel="next" href="sql-createlanguage.html" title="CREATE LANGUAGE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE INDEX</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-creategroup.html" title="CREATE GROUP">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createlanguage.html" title="CREATE LANGUAGE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEINDEX"><div class="titlepage"></div><a id="id-1.9.3.69.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE INDEX</span></h2><p>CREATE INDEX — define a new index</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <em class="replaceable"><code>name</code></em> ] ON [ ONLY ] <em class="replaceable"><code>table_name</code></em> [ USING <em class="replaceable"><code>method</code></em> ] + ( { <em class="replaceable"><code>column_name</code></em> | ( <em class="replaceable"><code>expression</code></em> ) } [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>opclass</code></em> [ ( <em class="replaceable"><code>opclass_parameter</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) + [ INCLUDE ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] + [ NULLS [ NOT ] DISTINCT ] + [ WITH ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ] + [ TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> ] + [ WHERE <em class="replaceable"><code>predicate</code></em> ] +</pre></div><div class="refsect1" id="id-1.9.3.69.5"><h2>Description</h2><p> + <code class="command">CREATE INDEX</code> constructs an index on the specified column(s) + of the specified relation, which can be a table or a materialized view. + Indexes are primarily used to enhance database performance (though + inappropriate use can result in slower performance). + </p><p> + The key field(s) for the index are specified as column names, + or alternatively as expressions written in parentheses. + Multiple fields can be specified if the index method supports + multicolumn indexes. + </p><p> + An index field can be an expression computed from the values of + one or more columns of the table row. This feature can be used + to obtain fast access to data based on some transformation of + the basic data. For example, an index computed on + <code class="literal">upper(col)</code> would allow the clause + <code class="literal">WHERE upper(col) = 'JIM'</code> to use an index. + </p><p> + <span class="productname">PostgreSQL</span> provides the index methods + B-tree, hash, GiST, SP-GiST, GIN, and BRIN. Users can also define their own + index methods, but that is fairly complicated. + </p><p> + When the <code class="literal">WHERE</code> clause is present, a + <em class="firstterm">partial index</em> is created. + A partial index is an index that contains entries for only a portion of + a table, usually a portion that is more useful for indexing than the + rest of the table. For example, if you have a table that contains both + billed and unbilled orders where the unbilled orders take up a small + fraction of the total table and yet that is an often used section, you + can improve performance by creating an index on just that portion. + Another possible application is to use <code class="literal">WHERE</code> with + <code class="literal">UNIQUE</code> to enforce uniqueness over a subset of a + table. See <a class="xref" href="indexes-partial.html" title="11.8. Partial Indexes">Section 11.8</a> for more discussion. + </p><p> + The expression used in the <code class="literal">WHERE</code> clause can refer + only to columns of the underlying table, but it can use all columns, + not just the ones being indexed. Presently, subqueries and + aggregate expressions are also forbidden in <code class="literal">WHERE</code>. + The same restrictions apply to index fields that are expressions. + </p><p> + All functions and operators used in an index definition must be + <span class="quote">“<span class="quote">immutable</span>”</span>, that is, their results must depend only on + their arguments and never on any outside influence (such as + the contents of another table or the current time). This restriction + ensures that the behavior of the index is well-defined. To use a + user-defined function in an index expression or <code class="literal">WHERE</code> + clause, remember to mark the function immutable when you create it. + </p></div><div class="refsect1" id="id-1.9.3.69.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">UNIQUE</code></span></dt><dd><p> + Causes the system to check for + duplicate values in the table when the index is created (if data + already exist) and each time data is added. Attempts to + insert or update data which would result in duplicate entries + will generate an error. + </p><p> + Additional restrictions apply when unique indexes are applied to + partitioned tables; see <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>. + </p></dd><dt><span class="term"><code class="literal">CONCURRENTLY</code></span></dt><dd><p> + When this option is used, <span class="productname">PostgreSQL</span> will build the + index without taking any locks that prevent concurrent inserts, + updates, or deletes on the table; whereas a standard index build + locks out writes (but not reads) on the table until it's done. + There are several caveats to be aware of when using this option + — see <a class="xref" href="sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY" title="Building Indexes Concurrently">Building Indexes Concurrently</a> below. + </p><p> + For temporary tables, <code class="command">CREATE INDEX</code> is always + non-concurrent, as no other session can access them, and + non-concurrent index creation is cheaper. + </p></dd><dt><span class="term"><code class="literal">IF NOT EXISTS</code></span></dt><dd><p> + Do not throw an error if a relation with the same name already exists. + A notice is issued in this case. Note that there is no guarantee that + the existing index is anything like the one that would have been created. + Index name is required when <code class="literal">IF NOT EXISTS</code> is specified. + </p></dd><dt><span class="term"><code class="literal">INCLUDE</code></span></dt><dd><p> + The optional <code class="literal">INCLUDE</code> clause specifies a + list of columns which will be included in the index + as <em class="firstterm">non-key</em> columns. A non-key column cannot + be used in an index scan search qualification, and it is disregarded + for purposes of any uniqueness or exclusion constraint enforced by + the index. However, an index-only scan can return the contents of + non-key columns without having to visit the index's table, since + they are available directly from the index entry. Thus, addition of + non-key columns allows index-only scans to be used for queries that + otherwise could not use them. + </p><p> + It's wise to be conservative about adding non-key columns to an + index, especially wide columns. If an index tuple exceeds the + maximum size allowed for the index type, data insertion will fail. + In any case, non-key columns duplicate data from the index's table + and bloat the size of the index, thus potentially slowing searches. + Furthermore, B-tree deduplication is never used with indexes + that have a non-key column. + </p><p> + Columns listed in the <code class="literal">INCLUDE</code> clause don't need + appropriate operator classes; the clause can include + columns whose data types don't have operator classes defined for + a given access method. + </p><p> + Expressions are not supported as included columns since they cannot be + used in index-only scans. + </p><p> + Currently, the B-tree, GiST and SP-GiST index access methods support + this feature. In these indexes, the values of columns listed + in the <code class="literal">INCLUDE</code> clause are included in leaf tuples + which correspond to heap tuples, but are not included in upper-level + index entries used for tree navigation. + </p></dd><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p> + The name of the index to be created. No schema name can be included + here; the index is always created in the same schema as its parent + table. The name of the index must be distinct from the name of any + other relation (table, sequence, index, view, materialized view, or + foreign table) in that schema. + If the name is omitted, <span class="productname">PostgreSQL</span> chooses a + suitable name based on the parent table's name and the indexed column + name(s). + </p></dd><dt><span class="term"><code class="literal">ONLY</code></span></dt><dd><p> + Indicates not to recurse creating indexes on partitions, if the + table is partitioned. The default is to recurse. + </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p> + The name (possibly schema-qualified) of the table to be indexed. + </p></dd><dt><span class="term"><em class="replaceable"><code>method</code></em></span></dt><dd><p> + The name of the index method to be used. Choices are + <code class="literal">btree</code>, <code class="literal">hash</code>, + <code class="literal">gist</code>, <code class="literal">spgist</code>, <code class="literal">gin</code>, + <code class="literal">brin</code>, or user-installed access methods like + <a class="link" href="bloom.html" title="F.7. bloom">bloom</a>. + The default method is <code class="literal">btree</code>. + </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p> + The name of a column of the table. + </p></dd><dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt><dd><p> + An expression based on one or more columns of the table. The + expression usually must be written with surrounding parentheses, + as shown in the syntax. However, the parentheses can be omitted + if the expression has the form of a function call. + </p></dd><dt><span class="term"><em class="replaceable"><code>collation</code></em></span></dt><dd><p> + The name of the collation to use for the index. By default, + the index uses the collation declared for the column to be + indexed or the result collation of the expression to be + indexed. Indexes with non-default collations can be useful for + queries that involve expressions using non-default collations. + </p></dd><dt><span class="term"><em class="replaceable"><code>opclass</code></em></span></dt><dd><p> + The name of an operator class. See below for details. + </p></dd><dt><span class="term"><em class="replaceable"><code>opclass_parameter</code></em></span></dt><dd><p> + The name of an operator class parameter. See below for details. + </p></dd><dt><span class="term"><code class="literal">ASC</code></span></dt><dd><p> + Specifies ascending sort order (which is the default). + </p></dd><dt><span class="term"><code class="literal">DESC</code></span></dt><dd><p> + Specifies descending sort order. + </p></dd><dt><span class="term"><code class="literal">NULLS FIRST</code></span></dt><dd><p> + Specifies that nulls sort before non-nulls. This is the default + when <code class="literal">DESC</code> is specified. + </p></dd><dt><span class="term"><code class="literal">NULLS LAST</code></span></dt><dd><p> + Specifies that nulls sort after non-nulls. This is the default + when <code class="literal">DESC</code> is not specified. + </p></dd><dt><span class="term"><code class="literal">NULLS DISTINCT</code><br /></span><span class="term"><code class="literal">NULLS NOT DISTINCT</code></span></dt><dd><p> + Specifies whether for a unique index, null values should be considered + distinct (not equal). The default is that they are distinct, so that + a unique index could contain multiple null values in a column. + </p></dd><dt><span class="term"><em class="replaceable"><code>storage_parameter</code></em></span></dt><dd><p> + The name of an index-method-specific storage parameter. See + <a class="xref" href="sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS" title="Index Storage Parameters">Index Storage Parameters</a> below + for details. + </p></dd><dt><span class="term"><em class="replaceable"><code>tablespace_name</code></em></span></dt><dd><p> + The tablespace in which to create the index. If not specified, + <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TABLESPACE">default_tablespace</a> is consulted, or + <a class="xref" href="runtime-config-client.html#GUC-TEMP-TABLESPACES">temp_tablespaces</a> for indexes on temporary + tables. + </p></dd><dt><span class="term"><em class="replaceable"><code>predicate</code></em></span></dt><dd><p> + The constraint expression for a partial index. + </p></dd></dl></div><div class="refsect2" id="SQL-CREATEINDEX-STORAGE-PARAMETERS"><h3>Index Storage Parameters</h3><p> + The optional <code class="literal">WITH</code> clause specifies <em class="firstterm">storage + parameters</em> for the index. Each index method has its own set of allowed + storage parameters. The B-tree, hash, GiST and SP-GiST index methods all + accept this parameter: + </p><div class="variablelist"><dl class="variablelist"><dt id="INDEX-RELOPTION-FILLFACTOR"><span class="term"><code class="literal">fillfactor</code> (<code class="type">integer</code>) + <a id="id-1.9.3.69.6.3.3.1.1.3" class="indexterm"></a> + </span></dt><dd><p> + The fillfactor for an index is a percentage that determines how full + the index method will try to pack index pages. For B-trees, leaf pages + are filled to this percentage during initial index builds, and also + when extending the index at the right (adding new largest key values). + If pages + subsequently become completely full, they will be split, leading to + fragmentation of the on-disk index structure. B-trees use a default + fillfactor of 90, but any integer value from 10 to 100 can be selected. + </p><p> + B-tree indexes on tables where many inserts and/or updates are + anticipated can benefit from lower fillfactor settings at + <code class="command">CREATE INDEX</code> time (following bulk loading into the + table). Values in the range of 50 - 90 can usefully <span class="quote">“<span class="quote">smooth + out</span>”</span> the <span class="emphasis"><em>rate</em></span> of page splits during the + early life of the B-tree index (lowering fillfactor like this may even + lower the absolute number of page splits, though this effect is highly + workload dependent). The B-tree bottom-up index deletion technique + described in <a class="xref" href="btree-implementation.html#BTREE-DELETION" title="67.4.2. Bottom-up Index Deletion">Section 67.4.2</a> is dependent on having + some <span class="quote">“<span class="quote">extra</span>”</span> space on pages to store <span class="quote">“<span class="quote">extra</span>”</span> + tuple versions, and so can be affected by fillfactor (though the effect + is usually not significant). + </p><p> + In other specific cases it might be useful to increase fillfactor to + 100 at <code class="command">CREATE INDEX</code> time as a way of maximizing + space utilization. You should only consider this when you are + completely sure that the table is static (i.e. that it will never be + affected by either inserts or updates). A fillfactor setting of 100 + otherwise risks <span class="emphasis"><em>harming</em></span> performance: even a few + updates or inserts will cause a sudden flood of page splits. + </p><p> + The other index methods use fillfactor in different but roughly + analogous ways; the default fillfactor varies between methods. + </p></dd></dl></div><p> + B-tree indexes additionally accept this parameter: + </p><div class="variablelist"><dl class="variablelist"><dt id="INDEX-RELOPTION-DEDUPLICATE-ITEMS"><span class="term"><code class="literal">deduplicate_items</code> (<code class="type">boolean</code>) + <a id="id-1.9.3.69.6.3.5.1.1.3" class="indexterm"></a> + </span></dt><dd><p> + Controls usage of the B-tree deduplication technique described + in <a class="xref" href="btree-implementation.html#BTREE-DEDUPLICATION" title="67.4.3. Deduplication">Section 67.4.3</a>. Set to + <code class="literal">ON</code> or <code class="literal">OFF</code> to enable or + disable the optimization. (Alternative spellings of + <code class="literal">ON</code> and <code class="literal">OFF</code> are allowed as + described in <a class="xref" href="config-setting.html" title="20.1. Setting Parameters">Section 20.1</a>.) The default is + <code class="literal">ON</code>. + </p><div class="note"><h3 class="title">Note</h3><p> + Turning <code class="literal">deduplicate_items</code> off via + <code class="command">ALTER INDEX</code> prevents future insertions from + triggering deduplication, but does not in itself make existing + posting list tuples use the standard tuple representation. + </p></div></dd></dl></div><p> + GiST indexes additionally accept this parameter: + </p><div class="variablelist"><dl class="variablelist"><dt id="INDEX-RELOPTION-BUFFERING"><span class="term"><code class="literal">buffering</code> (<code class="type">enum</code>) + <a id="id-1.9.3.69.6.3.7.1.1.3" class="indexterm"></a> + </span></dt><dd><p> + Determines whether the buffered build technique described in + <a class="xref" href="gist-implementation.html#GIST-BUFFERING-BUILD" title="68.4.1. GiST Index Build Methods">Section 68.4.1</a> is used to build the index. With + <code class="literal">OFF</code> buffering is disabled, with <code class="literal">ON</code> + it is enabled, and with <code class="literal">AUTO</code> it is initially disabled, + but is turned on on-the-fly once the index size reaches + <a class="xref" href="runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE">effective_cache_size</a>. The default + is <code class="literal">AUTO</code>. + Note that if sorted build is possible, it will be used instead of + buffered build unless <code class="literal">buffering=ON</code> is specified. + </p></dd></dl></div><p> + GIN indexes accept different parameters: + </p><div class="variablelist"><dl class="variablelist"><dt id="INDEX-RELOPTION-FASTUPDATE"><span class="term"><code class="literal">fastupdate</code> (<code class="type">boolean</code>) + <a id="id-1.9.3.69.6.3.9.1.1.3" class="indexterm"></a> + </span></dt><dd><p> + This setting controls usage of the fast update technique described in + <a class="xref" href="gin-implementation.html#GIN-FAST-UPDATE" title="70.4.1. GIN Fast Update Technique">Section 70.4.1</a>. It is a Boolean parameter: + <code class="literal">ON</code> enables fast update, <code class="literal">OFF</code> disables it. + The default is <code class="literal">ON</code>. + </p><div class="note"><h3 class="title">Note</h3><p> + Turning <code class="literal">fastupdate</code> off via <code class="command">ALTER INDEX</code> prevents + future insertions from going into the list of pending index entries, + but does not in itself flush previous entries. You might want to + <code class="command">VACUUM</code> the table or call <code class="function">gin_clean_pending_list</code> + function afterward to ensure the pending list is emptied. + </p></div></dd></dl></div><div class="variablelist"><dl class="variablelist"><dt id="INDEX-RELOPTION-GIN-PENDING-LIST-LIMIT"><span class="term"><code class="literal">gin_pending_list_limit</code> (<code class="type">integer</code>) + <a id="id-1.9.3.69.6.3.10.1.1.3" class="indexterm"></a> + </span></dt><dd><p> + Custom <a class="xref" href="runtime-config-client.html#GUC-GIN-PENDING-LIST-LIMIT">gin_pending_list_limit</a> parameter. + This value is specified in kilobytes. + </p></dd></dl></div><p> + <acronym class="acronym">BRIN</acronym> indexes accept different parameters: + </p><div class="variablelist"><dl class="variablelist"><dt id="INDEX-RELOPTION-PAGES-PER-RANGE"><span class="term"><code class="literal">pages_per_range</code> (<code class="type">integer</code>) + <a id="id-1.9.3.69.6.3.12.1.1.3" class="indexterm"></a> + </span></dt><dd><p> + Defines the number of table blocks that make up one block range for + each entry of a <acronym class="acronym">BRIN</acronym> index (see <a class="xref" href="brin-intro.html" title="71.1. Introduction">Section 71.1</a> + for more details). The default is <code class="literal">128</code>. + </p></dd><dt id="INDEX-RELOPTION-AUTOSUMMARIZE"><span class="term"><code class="literal">autosummarize</code> (<code class="type">boolean</code>) + <a id="id-1.9.3.69.6.3.12.2.1.3" class="indexterm"></a> + </span></dt><dd><p> + Defines whether a summarization run is queued for the previous page + range whenever an insertion is detected on the next one. + See <a class="xref" href="brin-intro.html#BRIN-OPERATION" title="71.1.1. Index Maintenance">Section 71.1.1</a> for more details. + The default is <code class="literal">off</code>. + </p></dd></dl></div></div><div class="refsect2" id="SQL-CREATEINDEX-CONCURRENTLY"><h3>Building Indexes Concurrently</h3><a id="id-1.9.3.69.6.4.2" class="indexterm"></a><p> + Creating an index can interfere with regular operation of a database. + Normally <span class="productname">PostgreSQL</span> locks the table to be indexed against + writes and performs the entire index build with a single scan of the + table. Other transactions can still read the table, but if they try to + insert, update, or delete rows in the table they will block until the + index build is finished. This could have a severe effect if the system is + a live production database. Very large tables can take many hours to be + indexed, and even for smaller tables, an index build can lock out writers + for periods that are unacceptably long for a production system. + </p><p> + <span class="productname">PostgreSQL</span> supports building indexes without locking + out writes. This method is invoked by specifying the + <code class="literal">CONCURRENTLY</code> option of <code class="command">CREATE INDEX</code>. + When this option is used, + <span class="productname">PostgreSQL</span> must perform two scans of the table, and in + addition it must wait for all existing transactions that could potentially + modify or use the index to terminate. Thus + this method requires more total work than a standard index build and takes + significantly longer to complete. However, since it allows normal + operations to continue while the index is built, this method is useful for + adding new indexes in a production environment. Of course, the extra CPU + and I/O load imposed by the index creation might slow other operations. + </p><p> + In a concurrent index build, the index is actually entered as an + <span class="quote">“<span class="quote">invalid</span>”</span> index into + the system catalogs in one transaction, then two table scans occur in + two more transactions. Before each table scan, the index build must + wait for existing transactions that have modified the table to terminate. + After the second scan, the index build must wait for any transactions + that have a snapshot (see <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>) predating the second + scan to terminate, including transactions used by any phase of concurrent + index builds on other tables, if the indexes involved are partial or have + columns that are not simple column references. + Then finally the index can be marked <span class="quote">“<span class="quote">valid</span>”</span> and ready for use, + and the <code class="command">CREATE INDEX</code> command terminates. + Even then, however, the index may not be immediately usable for queries: + in the worst case, it cannot be used as long as transactions exist that + predate the start of the index build. + </p><p> + If a problem arises while scanning the table, such as a deadlock or a + uniqueness violation in a unique index, the <code class="command">CREATE INDEX</code> + command will fail but leave behind an <span class="quote">“<span class="quote">invalid</span>”</span> index. This index + will be ignored for querying purposes because it might be incomplete; + however it will still consume update overhead. The <span class="application">psql</span> + <code class="command">\d</code> command will report such an index as <code class="literal">INVALID</code>: + +</p><pre class="programlisting"> +postgres=# \d tab + Table "public.tab" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + col | integer | | | +Indexes: + "idx" btree (col) INVALID +</pre><p> + + The recommended recovery + method in such cases is to drop the index and try again to perform + <code class="command">CREATE INDEX CONCURRENTLY</code>. (Another possibility is + to rebuild the index with <code class="command">REINDEX INDEX CONCURRENTLY</code>). + </p><p> + Another caveat when building a unique index concurrently is that the + uniqueness constraint is already being enforced against other transactions + when the second table scan begins. This means that constraint violations + could be reported in other queries prior to the index becoming available + for use, or even in cases where the index build eventually fails. Also, + if a failure does occur in the second scan, the <span class="quote">“<span class="quote">invalid</span>”</span> index + continues to enforce its uniqueness constraint afterwards. + </p><p> + Concurrent builds of expression indexes and partial indexes are supported. + Errors occurring in the evaluation of these expressions could cause + behavior similar to that described above for unique constraint violations. + </p><p> + Regular index builds permit other regular index builds on the + same table to occur simultaneously, but only one concurrent index build + can occur on a table at a time. In either case, schema modification of the + table is not allowed while the index is being built. Another difference is + that a regular <code class="command">CREATE INDEX</code> command can be performed + within a transaction block, but <code class="command">CREATE INDEX CONCURRENTLY</code> + cannot. + </p><p> + Concurrent builds for indexes on partitioned tables are currently not + supported. However, you may concurrently build the index on each + partition individually and then finally create the partitioned index + non-concurrently in order to reduce the time where writes to the + partitioned table will be locked out. In this case, building the + partitioned index is a metadata only operation. + </p></div></div><div class="refsect1" id="id-1.9.3.69.7"><h2>Notes</h2><p> + See <a class="xref" href="indexes.html" title="Chapter 11. Indexes">Chapter 11</a> for information about when indexes can + be used, when they are not used, and in which particular situations + they can be useful. + </p><p> + Currently, only the B-tree, GiST, GIN, and BRIN index methods support + multiple-key-column indexes. Whether there can be multiple key + columns is independent of whether <code class="literal">INCLUDE</code> columns + can be added to the index. Indexes can have up to 32 columns, + including <code class="literal">INCLUDE</code> columns. + (This limit can be altered when building + <span class="productname">PostgreSQL</span>.) Only B-tree currently + supports unique indexes. + </p><p> + An <em class="firstterm">operator class</em> with optional parameters + can be specified for each column of an index. + The operator class identifies the operators to be + used by the index for that column. For example, a B-tree index on + four-byte integers would use the <code class="literal">int4_ops</code> class; + this operator class includes comparison functions for four-byte + integers. In practice the default operator class for the column's data + type is usually sufficient. The main point of having operator classes + is that for some data types, there could be more than one meaningful + ordering. For example, we might want to sort a complex-number data + type either by absolute value or by real part. We could do this by + defining two operator classes for the data type and then selecting + the proper class when creating an index. More information about + operator classes is in <a class="xref" href="indexes-opclass.html" title="11.10. Operator Classes and Operator Families">Section 11.10</a> and in <a class="xref" href="xindex.html" title="38.16. Interfacing Extensions to Indexes">Section 38.16</a>. + </p><p> + When <code class="literal">CREATE INDEX</code> is invoked on a partitioned + table, the default behavior is to recurse to all partitions to ensure + they all have matching indexes. + Each partition is first checked to determine whether an equivalent + index already exists, and if so, that index will become attached as a + partition index to the index being created, which will become its + parent index. + If no matching index exists, a new index will be created and + automatically attached; the name of the new index in each partition + will be determined as if no index name had been specified in the + command. + If the <code class="literal">ONLY</code> option is specified, no recursion + is done, and the index is marked invalid. + (<code class="command">ALTER INDEX ... ATTACH PARTITION</code> marks the index + valid, once all partitions acquire matching indexes.) Note, however, + that any partition that is created in the future using + <code class="command">CREATE TABLE ... PARTITION OF</code> will automatically + have a matching index, regardless of whether <code class="literal">ONLY</code> is + specified. + </p><p> + For index methods that support ordered scans (currently, only B-tree), + the optional clauses <code class="literal">ASC</code>, <code class="literal">DESC</code>, <code class="literal">NULLS + FIRST</code>, and/or <code class="literal">NULLS LAST</code> can be specified to modify + the sort ordering of the index. Since an ordered index can be + scanned either forward or backward, it is not normally useful to create a + single-column <code class="literal">DESC</code> index — that sort ordering is already + available with a regular index. The value of these options is that + multicolumn indexes can be created that match the sort ordering requested + by a mixed-ordering query, such as <code class="literal">SELECT ... ORDER BY x ASC, y + DESC</code>. The <code class="literal">NULLS</code> options are useful if you need to support + <span class="quote">“<span class="quote">nulls sort low</span>”</span> behavior, rather than the default <span class="quote">“<span class="quote">nulls + sort high</span>”</span>, in queries that depend on indexes to avoid sorting steps. + </p><p> + The system regularly collects statistics on all of a table's + columns. Newly-created non-expression indexes can immediately + use these statistics to determine an index's usefulness. + For new expression indexes, it is necessary to run <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> or wait for + the <a class="link" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">autovacuum daemon</a> to analyze + the table to generate statistics for these indexes. + </p><p> + For most index methods, the speed of creating an index is + dependent on the setting of <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a>. + Larger values will reduce the time needed for index creation, so long + as you don't make it larger than the amount of memory really available, + which would drive the machine into swapping. + </p><p> + <span class="productname">PostgreSQL</span> can build indexes while + leveraging multiple CPUs in order to process the table rows faster. + This feature is known as <em class="firstterm">parallel index + build</em>. For index methods that support building indexes + in parallel (currently, only B-tree), + <code class="varname">maintenance_work_mem</code> specifies the maximum + amount of memory that can be used by each index build operation as + a whole, regardless of how many worker processes were started. + Generally, a cost model automatically determines how many worker + processes should be requested, if any. + </p><p> + Parallel index builds may benefit from increasing + <code class="varname">maintenance_work_mem</code> where an equivalent serial + index build will see little or no benefit. Note that + <code class="varname">maintenance_work_mem</code> may influence the number of + worker processes requested, since parallel workers must have at + least a <code class="literal">32MB</code> share of the total + <code class="varname">maintenance_work_mem</code> budget. There must also be + a remaining <code class="literal">32MB</code> share for the leader process. + Increasing <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a> + may allow more workers to be used, which will reduce the time + needed for index creation, so long as the index build is not + already I/O bound. Of course, there should also be sufficient + CPU capacity that would otherwise lie idle. + </p><p> + Setting a value for <code class="literal">parallel_workers</code> via <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a> directly controls how many parallel + worker processes will be requested by a <code class="command">CREATE + INDEX</code> against the table. This bypasses the cost model + completely, and prevents <code class="varname">maintenance_work_mem</code> + from affecting how many parallel workers are requested. Setting + <code class="literal">parallel_workers</code> to 0 via <code class="command">ALTER + TABLE</code> will disable parallel index builds on the table in + all cases. + </p><div class="tip"><h3 class="title">Tip</h3><p> + You might want to reset <code class="literal">parallel_workers</code> after + setting it as part of tuning an index build. This avoids + inadvertent changes to query plans, since + <code class="literal">parallel_workers</code> affects + <span class="emphasis"><em>all</em></span> parallel table scans. + </p></div><p> + While <code class="command">CREATE INDEX</code> with the + <code class="literal">CONCURRENTLY</code> option supports parallel builds + without special restrictions, only the first table scan is actually + performed in parallel. + </p><p> + Use <a class="link" href="sql-dropindex.html" title="DROP INDEX"><code class="command">DROP INDEX</code></a> + to remove an index. + </p><p> + Like any long-running transaction, <code class="command">CREATE INDEX</code> on a + table can affect which tuples can be removed by concurrent + <code class="command">VACUUM</code> on any other table. + </p><p> + Prior releases of <span class="productname">PostgreSQL</span> also had an + R-tree index method. This method has been removed because + it had no significant advantages over the GiST method. + If <code class="literal">USING rtree</code> is specified, <code class="command">CREATE INDEX</code> + will interpret it as <code class="literal">USING gist</code>, to simplify conversion + of old databases to GiST. + </p><p> + Each backend running <code class="command">CREATE INDEX</code> will report its + progress in the <code class="structname">pg_stat_progress_create_index</code> + view. See <a class="xref" href="progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING" title="28.4.2. CREATE INDEX Progress Reporting">Section 28.4.2</a> for details. + </p></div><div class="refsect1" id="id-1.9.3.69.8"><h2>Examples</h2><p> + To create a unique B-tree index on the column <code class="literal">title</code> in + the table <code class="literal">films</code>: +</p><pre class="programlisting"> +CREATE UNIQUE INDEX title_idx ON films (title); +</pre><p> + </p><p> + To create a unique B-tree index on the column <code class="literal">title</code> + with included columns <code class="literal">director</code> + and <code class="literal">rating</code> in the table <code class="literal">films</code>: +</p><pre class="programlisting"> +CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating); +</pre><p> + </p><p> + To create a B-Tree index with deduplication disabled: +</p><pre class="programlisting"> +CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off); +</pre><p> + </p><p> + To create an index on the expression <code class="literal">lower(title)</code>, + allowing efficient case-insensitive searches: +</p><pre class="programlisting"> +CREATE INDEX ON films ((lower(title))); +</pre><p> + (In this example we have chosen to omit the index name, so the system + will choose a name, typically <code class="literal">films_lower_idx</code>.) + </p><p> + To create an index with non-default collation: +</p><pre class="programlisting"> +CREATE INDEX title_idx_german ON films (title COLLATE "de_DE"); +</pre><p> + </p><p> + To create an index with non-default sort ordering of nulls: +</p><pre class="programlisting"> +CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST); +</pre><p> + </p><p> + To create an index with non-default fill factor: +</p><pre class="programlisting"> +CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70); +</pre><p> + </p><p> + To create a <acronym class="acronym">GIN</acronym> index with fast updates disabled: +</p><pre class="programlisting"> +CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off); +</pre><p> + </p><p> + To create an index on the column <code class="literal">code</code> in the table + <code class="literal">films</code> and have the index reside in the tablespace + <code class="literal">indexspace</code>: +</p><pre class="programlisting"> +CREATE INDEX code_idx ON films (code) TABLESPACE indexspace; +</pre><p> + </p><p> + To create a GiST index on a point attribute so that we + can efficiently use box operators on the result of the + conversion function: +</p><pre class="programlisting"> +CREATE INDEX pointloc + ON points USING gist (box(location,location)); +SELECT * FROM points + WHERE box(location,location) && '(0,0),(1,1)'::box; +</pre><p> + </p><p> + To create an index without locking out writes to the table: +</p><pre class="programlisting"> +CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity); +</pre></div><div class="refsect1" id="id-1.9.3.69.9"><h2>Compatibility</h2><p> + <code class="command">CREATE INDEX</code> is a + <span class="productname">PostgreSQL</span> language extension. There + are no provisions for indexes in the SQL standard. + </p></div><div class="refsect1" id="id-1.9.3.69.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterindex.html" title="ALTER INDEX"><span class="refentrytitle">ALTER INDEX</span></a>, <a class="xref" href="sql-dropindex.html" title="DROP INDEX"><span class="refentrytitle">DROP INDEX</span></a>, <a class="xref" href="sql-reindex.html" title="REINDEX"><span class="refentrytitle">REINDEX</span></a>, <a class="xref" href="progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING" title="28.4.2. CREATE INDEX Progress Reporting">Section 28.4.2</a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-creategroup.html" title="CREATE GROUP">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createlanguage.html" title="CREATE LANGUAGE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE GROUP </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE LANGUAGE</td></tr></table></div></body></html>
\ No newline at end of file |