CREATE INDEX
CREATE INDEX — Define a new index
Synopsis
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON
[ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass
[ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
Description
CREATE INDEX 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).
The key field(s) for the index are specified as column names, or as expressions written in parentheses. If the index method supports multicolumn indexes, multiple fields can be specified. 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 base data. For example, an index computed on upper(col) can allow the clause WHERE upper(col) = 'JIM' to use the index.
Halo provides the B-tree, hash, GiST, SP-GiST, GIN, and BRIN index methods. Users can also define their own index methods, but this is relatively complex.
When the WHERE clause is present, a partial index is created. A partial index contains entries for only a portion of a table, usually the part that is more useful for indexing than the rest of the table. For example, if you have a table that contains both paid and unpaid orders, where the unpaid orders constitute a small fraction of the total table but are frequently accessed, you can improve performance by creating an index only on that portion. Another possible use is to use WHERE with UNIQUE to enforce uniqueness on a subset of a table.
The expression used in the WHERE clause can only reference columns of the underlying table, but it can reference all columns, not just the indexed ones. Currently, subqueries and aggregate expressions are also prohibited in WHERE. The same restrictions apply to expression fields in expression indexes.
All functions and operators used in an index definition must be "immutable", that is, their results must depend only on their arguments and not on external factors (such as the contents of another table or the current time). This restriction ensures that the index behavior is well-defined. To use a user-defined function in an index expression or WHERE clause, remember to mark it as immutable when creating the function.
Parameters
UNIQUE
Causes the system to check for duplicate values when the index is created (if data already exists) and when data is added. Inserts or updates that would result in duplicate entries will produce an error.
There are additional restrictions when unique indexes are applied to partitioned tables; see CREATE TABLE.
CONCURRENTLY
When this option is used, the system will not acquire any locks that prevent concurrent inserts, updates, or deletes on the table while building the index. In contrast, standard index builds will lock the table against writes (but not reads), and this lock will be held until the index creation is complete. There are multiple caveats to be aware of when using this option — see Building Indexes Concurrently.
For temporary tables, CREATE INDEX is always non-concurrent, since no other session can access them, and creating a non-concurrent index is less expensive.
IF NOT EXISTS
Do not throw an error if a relation with the same name already exists. A notice will be issued in this case. Note that there is no guarantee that the existing index is at all similar to the one that would be created. When IF NOT EXISTS is specified, the index name must be specified.
INCLUDE
The optional INCLUDE clause specifies a list of columns that will be included in the index as non-key columns. Non-key columns cannot be used as index scan conditions, and any uniqueness or exclusion constraints enforced by the index will not consider them. However, index-only scans can return the contents of non-key columns without accessing the index's base table, since they are directly available in the index entries. Therefore, adding non-key columns can allow queries to use index-only scans that would otherwise not be possible.
It is wise to be conservative about adding non-key columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed by the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's base table and increase the index size, which may slow down searches. Additionally, B-tree deduplication is never used with indexes that have non-key columns.
Columns listed in the INCLUDE clause do not need a suitable operator class; even columns whose data types have no operator class defined for the given access method can be included in this clause.
Using expressions as included columns is not supported because they cannot be used in index-only scans.
Currently, the B-tree and GiST index access methods support this feature. In B-tree and GiST indexes, the values of columns listed in the INCLUDE clause are included in the leaf tuples corresponding to heap tuples, but not in the upper-level index entries used for tree navigation.
name
The name of the index to be created. A schema name cannot be included here, because indexes are always created in the same schema as their base table. If the index name is omitted, the system will choose a suitable name based on the base table name and the indexed column names.
ONLY
If the table is a partitioned table, this indicates that the index should not be created recursively on partitions. By default, indexes are created recursively.
table_name
The name of the table to be indexed (can be schema-qualified).
method
The name of the index method to be used. Choices are btree, hash, gist, spgist, gin, and brin. The default method is btree.
column_name
The name of a table column.
expression
An expression based on one or more columns of the table. As shown in the syntax, the expression must usually be written within parentheses. However, if the expression is in the form of a function call, the parentheses can be omitted.
collation
The name of the collation to use for the index. By default, the index uses the collation of the indexed column or the result collation of the indexed expression. An index with a non-default collation can be useful when queries involve expressions that use non-default collations.
opclass
The name of an operator class. See below for details.
opclass_parameter
The name of an operator class parameter. See below for details.
ASC
Specifies ascending sort order (default).
DESC
Specifies descending sort order.
NULLS FIRST
Specifies that nulls should be sorted before non-nulls. This is the default when DESC is specified.
NULLS LAST
Specifies that nulls should be sorted after non-nulls. This is the default when DESC is not specified.
storage_parameter
The name of an index method-specific storage parameter. See Index Storage Parameters for details.
tablespace_name
The tablespace in which to create the index. If not specified, default_tablespace is used, or temp_tablespaces for indexes on temporary tables.
predicate
The constraint expression for a partial index.
Index Storage Parameters
The optional WITH clause specifies storage parameters for the index. Each index method has its own set of storage parameters. The B-tree, hash, GiST, and SP-GiST index methods all accept this parameter:
fillfactor (integer)
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 the initial index build, and also when extending the index on the right end (adding new maximum key values). If pages are later completely filled, they will be split, leading to gradual degradation of index efficiency. B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected. If the table is static, a fillfactor of 100 is best because it minimizes the physical size of the index. However, for tables with heavy update loads, a smaller fillfactor helps minimize the need for page splits. Other index methods use fillfactor in different but conceptually similar ways, and the default fillfactor varies across methods.
B-tree indexes also accept these parameters:
deduplicate_items (boolean)
Controls the use of B-tree deduplication. Set to ON or OFF to enable or disable the optimization (alternative spellings for ON and OFF are described in Section 19.1). The default is ON.
vacuum_cleanup_index_scale_factor (floating point)
Per-index value for vacuum_cleanup_index_scale_factor.
GiST additionally accepts this parameter:
buffering (enum)
Controls the buffered build technique described for building the index. OFF disables it, ON enables the feature, and if set to AUTO, it is initially disabled but will be turned on at any point once the index size reaches effective_cache_size. The default is AUTO.
GIN indexes accept different parameters:
fastupdate (boolean)
This is a Boolean parameter: ON enables fast update, OFF disables it. The default is ON.
[TABLE]
gin_pending_list_limit (integer)
Customizes the gin_pending_list_limit parameter. This value should be specified in kilobytes.
BRIN indexes accept different parameters:
pages_per_range (integer)
Defines the number of table blocks that make up a block range for each BRIN index entry. The default is 128.
autosummarize (boolean)
Defines whether to run a summarization operation for the preceding page range as soon as an insert is detected on the next page.
Building Indexes Concurrently
Creating an index can interfere with normal database operations. Normally, the system 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 attempt to insert, update, or delete on the table, they will be blocked until the index build is complete. If this is a production database, this can have serious consequences. Indexing a very large table can take many hours, and even for smaller tables, blocking writers for a period of time during index construction may be unacceptable in a production system.
Halo supports building indexes without blocking writes. This method is implemented by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, the system must perform two scans of the table, and it must also wait for all existing transactions that might modify or use the index to terminate. Therefore, this method requires more work and more time than a standard index build. However, since it allows normal operations to continue while building the index, this approach is very useful for adding new indexes in a production environment. Of course, the extra CPU and I/O overhead from index creation may slow down other operations.
In a concurrent index build, the index is actually entered into the system catalog in one transaction, and then two table scans occur in two separate 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 holding snapshots older than the second scan to terminate. Then the index can finally be marked as ready for use, and the CREATE INDEX command terminates. However, even then, the index may not be immediately usable for queries: in the worst case, the index cannot be used as long as any transaction that existed before the index build began is still running.
If a problem occurs during the table scan, such as a deadlock or a uniqueness violation in a unique index, CREATE INDEX will fail but leave behind an "invalid" index. This index will be ignored by queries because it may be incomplete. However, it will still consume update overhead. The \d command in psql will report such an index as INVALID:
Halo0root=#\d tab
Table "public.tab"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
col | integer | | |
Indexes:
"idx" btree (col) INVALID
The recommended recovery method in this case is to drop the index and try executing CREATE INDEX CONCURRENTLY again.
(Another possibility is to rebuild the index using REINDEX INDEX CONCURRENTLY.)
Another point to note when concurrently building a unique index is that when the second table scan begins, the uniqueness constraint is already being enforced on other transactions. This means that other queries may report constraint violations even before the index becomes available, or even in cases where the index build ultimately fails. Additionally, if a failure occurs during the second scan, the "invalid" index will continue to enforce its uniqueness constraint.
Concurrent builds of expression indexes and partial indexes are also supported. Errors occurring during evaluation of these expressions may lead to behavior similar to the uniqueness constraint violation described above.
Regular index builds allow other regular indexes to be built concurrently on the same table, but only one concurrent index build can occur on a table at a time. In both cases, schema modifications to the table are not allowed while the index is being built. Another difference is that a regular CREATE INDEX command can be executed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.
Currently, concurrent index creation on partitioned tables is not supported. However, you can concurrently build indexes on each partition individually, and then ultimately create the partitioned index non-concurrently, which reduces the time that writes to the partitioned table are locked. In this case, creating the partitioned index is merely a metadata operation.
Notes
Currently, only the B-tree, GiST, GIN, and BRIN index methods support multicolumn indexes. Up to 32 columns can be indexed by default. Currently, only B-tree supports unique indexes.
An operator class with optional parameters can be specified for each column of the index. The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on a four-byte integer would use the int4_ops 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 reason for specifying an operator class for certain data types is that there may be more than one meaningful ordering. For example, we might want to sort a complex number type by its absolute value or its real part. We can achieve this by defining two operator classes for the data type and selecting the appropriate one when creating the index.
When CREATE INDEX 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 for an equivalent existing index; if one exists, it will be attached as a partition index of the index being created, and the created index will become its parent index. If no matching index exists, a new index will be created and automatically attached. If no index name is specified in the command, the name of the new index in each partition will be determined automatically. If the ONLY option is specified, no recursion will occur, and the index will be marked as invalid (once all partitions have the index, ALTER INDEX ... ATTACH PARTITION can mark the index as valid). However, note that regardless of whether this option is specified, any partitions created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, whether or not ONLY was specified.
For index methods that support ordered scans (currently only B-tree), the optional clauses ASC, DESC, NULLS FIRST, and NULLS LAST can be used to modify the sort order of the index. Since an ordered index can be scanned forward or backward, creating a single-column DESC index is usually not useful — a regular index already provides sort order. The value of these options is that they allow creating multicolumn indexes with a sort order that matches queries with mixed sort requirements, such as SELECT ... ORDER BY x ASC, y DESC. If you want to support "nulls sort low" behavior in queries that rely on indexes to avoid the sort step, the NULLS options can be useful; the default behavior is "nulls sort high".
For most index methods, the speed of index creation depends on the maintenance_work_mem setting. A larger value will reduce the time needed for index creation, but of course you should not set it beyond the actually available memory (which would force the machine to swap).
The system can utilize multiple CPUs to process table rows faster during index construction. This feature is known as parallel index builds. For index methods that support parallel index builds (currently only B-tree), maintenance_work_mem specifies the maximum total memory available per index build operation, regardless of how many worker processes are started. Generally, a cost model (if available) automatically determines how many worker processes should be requested.
Increasing maintenance_work_mem can benefit parallel index builds, while equivalent serial index builds will not benefit or will benefit very little. Note that maintenance_work_mem may affect the number of requested worker processes, because parallel workers must each claim at least 32MB of the total maintenance_work_mem budget. Additionally, 32MB must be reserved for the leader process. Increasing max_parallel_maintenance_workers allows the use of more workers, which will reduce the time needed for index creation, provided that the index build is not I/O-bound. Of course, sufficient CPU computing power is also required; otherwise, the workers will be idle.
Setting a value for parallel_workers via ALTER TABLE directly controls how many parallel worker processes CREATE INDEX will request for the table. This completely bypasses the cost model and prevents maintenance_work_mem from influencing how many parallel workers are requested. Setting parallel_workers to 0 via ALTER TABLE will disable parallel index builds in all cases.
Although CREATE INDEX with the CONCURRENTLY option supports parallel builds without special restrictions, only the first table scan is actually executed in parallel.
Use DROP INDEX to remove an index.
Examples
# Create a B-tree index on the column title in the table films:
CREATE UNIQUE INDEX title_idx ON films (title);
# Create a unique B-tree index on the column title in the table films and include the columns director and rating:
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);
# Create a B-tree index with deduplication disabled:
<a href='CREATE INDEX'>CREATE INDEX</a> title_idx ON films (title) WITH (deduplicate_items = off);
# Create an index on the expression lower(title) to allow efficient case-insensitive searches:
<a href='CREATE INDEX'>CREATE INDEX</a> ON films ((lower(title)));
# (In this example, we choose to omit the index name, so the system will choose a name, typically films_lower_idx).
# Create an index with a non-default collation:
<a href='CREATE INDEX'>CREATE INDEX</a> title_idx_german ON films (title COLLATE "de_DE");
# Create an index with a non-default null sort order:
<a href='CREATE INDEX'>CREATE INDEX</a> title_idx_nulls_low ON films (title NULLS FIRST);
# Create an index with a non-default fillfactor:
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
# Create a GIN index with fast update disabled:
<a href='CREATE INDEX'>CREATE INDEX</a> gin_idx ON documents_table USING GIN (locations) WITH (fastupdate =off);
# Create an index on the column code in the table films and place the index in the tablespace indexspace:
<a href='CREATE INDEX'>CREATE INDEX</a> code_idx ON films (code) TABLESPACE indexspace;
# Create a GiST index on a point attribute, so we can efficiently use box operators on the result of a conversion function:
<a href='CREATE INDEX'>CREATE INDEX</a> pointloc
ON points USING gist (box(location,location));
SELECT * FROM points
WHERE box(location,location) && '(0,0),(1,1)'::box;
# Create an index without blocking writes to the table:
<a href='CREATE INDEX'>CREATE INDEX</a> CONCURRENTLY sales_quantity_index ON sales_table (quantity);