| 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 |
— 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 — 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) && '(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> |