create_index(5)
CREATE INDEX() SQL Commands CREATE INDEX()
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 per-
formance (though inappropriate use can result in slower per-
formance).
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 transforma-
tion 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.
SQL - Language StatementLast change: 2008-01-03 1
CREATE INDEX() SQL Commands CREATE INDEX()
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 expres-
sion or WHERE clause, remember to mark the function immut-
able 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 sur-
rounding 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.
SQL - Language StatementLast change: 2008-01-03 2
CREATE INDEX() SQL Commands CREATE INDEX()
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 deter-
mines how full the index method will try to pack index
pages. For B-trees, leaf pages are filled to this per-
centage 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 fin-
ished. 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 CON-
CURRENTLY option of CREATE INDEX. When this option is used,
SQL - Language StatementLast change: 2008-01-03 3
CREATE INDEX() SQL Commands CREATE INDEX()
PostgreSQL must perform two scans of the table, and in addi-
tion it must wait for all existing transactions to ter-
minate. Thus this method requires more total work than a
standard index build and takes significantly longer to com-
plete. However, since it allows normal operations to con-
tinue 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 attrac-
tive.)
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 avail-
able for use, or even in cases where the index build eventu-
ally fails. Also, if a failure does occur in the second
scan, the ``invalid'' index continues to enforce its unique-
ness 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
SQL - Language StatementLast change: 2008-01-03 4
CREATE INDEX() SQL Commands CREATE INDEX()
default. (This limit can be altered when building Post-
greSQL.) 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 exam-
ple, 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 infor-
mation 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 sig-
nificant 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:
SQL - Language StatementLast change: 2008-01-03 5
CREATE INDEX() SQL Commands CREATE INDEX()
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)]
SQL - Language StatementLast change: 2008-01-03 6
Man(1) output converted with
man2html