Places an index on a table.
CREATE [ UNIQUE ] INDEX index_name ON table [ USING method ] ( column [ op_class ] [, ...] ) CREATE [ UNIQUE ] INDEX index_name ON table [ USING method ] ( func_name ( column [, ... ] ) [ op_class ] )
UNIQUE
The optional UNIQUE
keyword. When used, this causes the database
to check for, and prevent, duplicate values within the column (or combined columns) it is
placed upon. This check will occur both when the index is created and each time data is
added to the table. PostgreSQL will then generate an error whenever an INSERT
or UPDATE
request is made that would place duplicate
data within the index, and the command will fail.
index_name
The name for the new index.
table
The name of the table you are placing the index on.
method
The type of indexing method you wish to use for the index. There are three methods
available to choose from, the default being btree
:
btree
The PostgreSQL implementation of Lehman-Yao high-concurrency B-trees.
rtree
The PostgreSQL implementation of standard R-trees using Guttman’s quadratic split algorithm.
hash
The PostgreSQL implementation of Litwin’s linear hashing.
column
The name of the column (or comma-delimited list of columns) on which to place the index.
op_class
The optionally specified associated operator class. For most users, this should not be specified.
func_name
The name of a function you wish CREATE INDEX
to use on the
specified columns (rather than indexing the data values literally in those columns). The
specified function must return a valid value that can be indexed (e.g., not a set of
values).
CREATE
The message returned when an index is created successfully.
ERROR: Cannot create index: '
index_name
' already exists
The error returned if an index with the name you specified already exists.
ERROR: DefineIndex: attribute "
column
" not found
The error returned if the specified column
does not exist in the specified table
to
index.
ERROR: DefineIndex: relation "
table
" not found
The error returned if the specified table
does not exist in the connected database.
Use CREATE INDEX
to build an optimization index on a specified table,
based on one or more of its columns. Remember that while indices are designed to improve the
performance and effectiveness of your database, using them on tables whose cost of index
maintenance outweighs the practical benefit actually decreases overall
performance.
You may create an index specifying a list of one or more table columns. This is the
“traditional” index type. It may be used by queries that directly reference the indexed
columns in a WHERE
clause. Note that R-tree and Hash indices may only
index one column, though B-tree indices can index up to sixteen columns.
An alternate type of index is one called a functional index. A
functional index is an index based on the returned value of a function applied to one or more
columns. Such an index is useful for queries that use the same function in a WHERE
clause frequently.
For example, if you have a query that always references upper(last_name)
in its WHERE
clause, you could optimized that
query by creating a functional index on upper(last_name)
.
The PostgreSQL query optimizer will use different indices for different operators used in a comparison. It will choose which type of index to used based on the pre-defined list shown in Table 14-1.
Table 14-1. Operator/index correspondence
Index |
Operator |
---|---|
B-tree |
<, <=, >=, > |
R-tree |
<<, &<, &>, >>, @, ~=, && |
Hash |
= |
You can optionally specify an operator class for each column on
which an index is placed. This is done by setting the optional op_class
parameter to the class of operator you intend to use. This option only exists because, in
some circumstances, there can be more than one meaningful way to order data. The default
indexing method is generally sufficient for most users, however, and this option is best left
unused unless you are creating your own custom types and operators.
The following example creates a unique index on the id
column of the
employees
table:
booktown=# CREATE UNIQUE INDEX employee_id_idx booktown-# ON employees (id); CREATE
The next example creates a functional index on the last_name
column of
the employees
table, using the upper( )
function:
booktown=# CREATE INDEX employee_upper_name_idx booktown-# ON employees (upper(last_name)); CREATE
3.16.76.138