NAME
CREATE INDEX - 定义一个新索引
SYNOPSIS
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ WHERE predicate ]
DESCRIPTION 描述
CREATE INDEX 在指定的表上构造一个名为 index_name 的索引。索引主要用来提高数据库性能。但是如果不恰当的使用将导致性能的下降。
索引的键字字段是以字段名的方式声明的，或者是可选的写在一个圆括弧里面的表达式。 如果索引方式支持多个字段索引，那么我们也可以声明多个字段。
一个索引字段可以是一个使用表的行的一个或多个字段的数值进行计算的表达式。 整个特性可用于获取对基本数据某种变形的快速访问。 比如，一个在 upper(col) 上的函数索引将允许子句 WHERE upper(col) = 'JIM' 使用索引。
PostgreSQL 为从索引提供 B-tree，R-tree，hash（散列） 和 GiST 索引方法。 B-tree 索引方法是一个 Lehman-Yao 高并发 B-trees 的实 现。R-tree 索引方法用 Guttman 的二次分裂算法实现了标准的 R-trees。 hash（散列）索引方法是 Litwin 的线性散列的一个实现。 用户也可以定义它们自己的索引方法，但这个工作相当复杂。
如果出现了 WHERE 子句，则创建一个部分索引。 部分索引是一个只包含表的一部分记录的索引，通常是该表中最让人感兴趣的部分。 比如，如果你有一个表，里面包含已上账和未上账的定单， 未上账的定单只占表的一小部分而且这部分是最常用的部分， 那么你就可以通过只在这个部分创建一个索引来改善性能。 另外一个可能的用途是用 WHERE 和 UNIQUE 强制一个表的某个子集的***性。
在 WHERE 子句里用的表达式只能引用下层表的字段（但是它可以使用所有字段，而不仅仅是被索引的字段）。 目前，子查询和聚集表达式也不能出现在WHERE里。
索引定义里的所有函数和操作符都必须是immutable，（不变的）也就是说， 它们的结果必须只能依赖于它们的输入参数，而决不能依赖任何外部的影响（比如另外一个表的内容或者当前时间）。 这个约束确保该索引的行为是定义完整的。要在一个索引上使用用户定义函数，请记住在你创建它的时候把它标记为immutable的函数。
PARAMETERS 参数
- UNIQUE
令系统检测当索引创建时（如果数据已经存在）和每次添加数据时表中是否有重复值。 如果插入或更新的值会导致重复的记录时将生成一个错误。- name
要创建的索引名。这里不能包含模式名； 索引总是在同一个模式中作为其父表创建的。- table
要索引的表名（可能有模式修饰）。- method
用于索引的方法的名字。可选的名字是 btree， hash，rtree，和 gist。缺省方法是 btree。- column
表的列/字段名。- expression
一个基于该表的一个或多个字段的表达式。 这个表达式通常必须带着圆括弧包围写出，如语法中显示那样。 不过，如果表达式有函数调用的形式，那么圆括弧可以省略。- opclass
一个关联的操作符表。参阅下文获取细节。- predicate
为一个部分索引定义约束表达式。
NOTES 注意
参阅 ``Indexes'' 获取有关何时使用索引，何时不使用索引， 以及哪种情况下是有用的信息。
目前，只有 B-tree 和 gist 索引方法支持多字段索引。 缺省时最多可以声明 32 个键字（这个限制可以在制作 PostgreSQL 时修改）。 目前只有 B-tree 支持***索引。
可以为索引的每个列/字段声明一个 操作符表。 操作符表标识将要被该索引用于该列/字段的操作符。 例如， 一个四字节整数的 B-tree 索引将使用 int4_ops 表； 这个操作符表包括四字节整数的比较函数。 实际上，该域的数据类型的缺省操作符表一般就足够了。 某些数据类型有操作符表的原因是，它们可能有多于一个的有意义的顺序。 例如，我们对复数类型排序时有可能以绝对值或者以实部。 我们可以通过为该数据类型定义两个操作符表，然后在建立索引的时候选择合适的表来实现。 有关操作符表更多的信息在 ``Operator Classes'' 和 ``Interfacing Extensions to Indexes'' 里。
使用 DROP INDEX [drop_index(7)] 删除一个索引。
EXAMPLES 例子
在表films上的 title字段创建一个 B-tree 索引：
CREATE UNIQUE INDEX title_idx ON films (title);
#p#
NAME
CREATE INDEX - define a new index
SYNOPSIS
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ 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 will 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, R-tree, hash, and GiST. The B-tree index method is an implementation of Lehman-Yao high-concurrency B-trees. The R-tree index method implements standard R-trees using Guttman's quadratic split algorithm. The hash index method is an implementation of Litwin's linear hashing. 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 somehow more interesting 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.
The expression used in the WHERE clause may refer only to columns of the underlying table (but it can use all columns, not only the one(s) 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.
- 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 method to be used for the index. Choices are btree, hash, rtree, and gist. 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.
- predicate
- The constraint expression for a partial index.
NOTES
See the chapter called ``Indexes'' in the documentation for information about when indexes can be used, when they are not used, and in which particular situations 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 the sections called ``Operator Classes'' and ``Interfacing Extensions to Indexes'' in the documentation.
Use DROP INDEX [drop_index(7)] to remove an index.
EXAMPLES
To create a B-tree index on the column title in the table films:
CREATE UNIQUE INDEX title_idx ON films (title);
