 |
Index for
Section INDEX |
|
 |
Alphabetical
listing for C |
|
 |
Bottom of
page |
|
CREATE
NAME
CREATE INDEX - define a new index
SYNOPSIS
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
DESCRIPTION
CREATE INDEX constructs an index index_name on the specified table.
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
alternatively as expressions written in parentheses. Multiple fields can
be specified if the index method supports multicolumn indexes.
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 upper(col) would allow the clause WHERE upper(col) = 'JIM' to
use an index.
PostgreSQL provides the index methods B-tree, hash, GiST, and GIN. Users
can also define their own index methods, but that is fairly complicated.
When the WHERE clause is present, a partial index 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 WHERE with UNIQUE to enforce uniqueness over a subset
of a table. See in the documentation for more discussion.
The expression used in the WHERE clause may 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 WHERE. The same restrictions apply to index fields that are
expressions.
All functions and operators used in an index definition must be
``immutable'', 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 WHERE clause, remember to mark the function immutable when
you create it.
PARAMETERS
UNIQUE
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.
CONCURRENTLY
When this option is used, PostgreSQL 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 Building Indexes Concurrently
[create_index(5)].
name 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.
table
The name (possibly schema-qualified) of the table to be indexed.
method
The name of the index method to be used. Choices are btree, hash,
gist, and gin. The default method is btree.
column
The name of a column of the table.
expression
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 may be omitted if the
expression has the form of a function call.
opclass
The name of an operator class. See below for details.
storage_parameter
The name of an index-method-specific storage parameter. See below for
details.
tablespace
The tablespace in which to create the index. If not specified,
default_tablespace is used, or the database's default tablespace if
default_tablespace is an empty string.
predicate
The constraint expression for a partial index.
INDEX STORAGE PARAMETERS
The WITH clause can specify storage parameters for indexes. Each index
method can have its own set of allowed storage parameters. The built-in
index methods all accept a single parameter:
FILLFACTOR
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 build, and also
when extending the index at the right (largest key values). If pages
subsequently become completely full, they will be split, leading to
gradual degradation in the index's efficiency. B-trees use a default
fillfactor of 90, but any value from 10 to 100 can be selected. If
the table is static then fillfactor 100 is best to minimize the
index's physical size, but for heavily updated tables a smaller
fillfactor is better to minimize the need for page splits. The other
index methods use fillfactor in different but roughly analogous ways;
the default fillfactor varies between methods.
BUILDING INDEXES CONCURRENTLY
Creating an index can interfere with regular operation of a database.
Normally PostgreSQL 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. 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.
PostgreSQL supports building indexes without locking out writes. This
method is invoked by specifying the CONCURRENTLY option of CREATE INDEX.
When this option is used, PostgreSQL must perform two scans of the table,
and in addition it must wait for all existing transactions 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 may slow other operations.
If a problem arises during the second scan of the table, such as a
uniqueness violation in a unique index, the CREATE INDEX command will fail
but leave behind an ``invalid'' index. This index will be ignored for
querying purposes because it may be incomplete; however it will still
consume update overhead. The recommended recovery method in such cases is
to drop the index and try again to perform CREATE INDEX CONCURRENTLY.
(Another possibility is to rebuild the index with REINDEX. However, since
REINDEX does not support concurrent builds, this option is unlikely to seem
attractive.)
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 ``invalid'' index continues to
enforce its uniqueness constraint afterwards.
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.
Regular index builds permit other regular index builds on the same table to
occur in parallel, but only one concurrent index build can occur on a table
at a time. In both cases, no other types of schema modification on the
table are allowed meanwhile. Another difference is that a regular CREATE
INDEX command can be performed within a transaction block, but CREATE INDEX
CONCURRENTLY cannot.
NOTES
See in the documentation for information about when indexes can be used,
when they are not used, and in which particular situations they can be
useful.
Currently, only the B-tree and GiST index methods support multicolumn
indexes. Up to 32 fields may be specified by default. (This limit can be
altered when building PostgreSQL.) Only B-tree currently supports unique
indexes.
An operator class 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
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 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
making an index. More information about operator classes is in in the
documentation and in in the documentation.
Use DROP INDEX [drop_index(5)] to remove an index.
Indexes are not used for IS NULL clauses by default. The best way to use
indexes in such cases is to create a partial index using an IS NULL
predicate.
Prior releases of PostgreSQL also had an R-tree index method. This method
has been removed because it had no significant advantages over the GiST
method. If USING rtree is specified, CREATE INDEX will interpret it as
USING gist, to simplify conversion of old databases to GiST.
EXAMPLES
To create a B-tree index on the column title in the table films:
CREATE UNIQUE INDEX title_idx ON films (title);
To create an index on the expression lower(title), allowing efficient
case-insensitive searches:
CREATE INDEX lower_title_idx ON films ((lower(title)));
To create an index with non-default fill factor:
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
To create an index on the column code in the table films and have the index
reside in the tablespace indexspace:
CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
To create an index without locking out writes to the table:
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
COMPATIBILITY
CREATE INDEX is a PostgreSQL language extension. There are no provisions
for indexes in the SQL standard.
SEE ALSO
ALTER INDEX [alter_index(5)], DROP INDEX [drop_index(l)]
|
Index for
Section INDEX |
|
|
Alphabetical
listing for C |
|
 |
Top of
page |
|