CREATE INDEX

描述

CREATE INDEX 在一个指定表或者物化视图的指定列上创建一个索引,索引主要用来提高数据库的效率(尽管不合理的使用将导致较慢的效率)

索引的键字是用列名来声明的,或者在括号里面写一个表达式 .如果索引支持多列索引,我们也可以指定多个字段.

一个索引域可以是一个使用表的一个或多个字段进行计算的表达式。 这个特性可以快速访问一些基础数据的变形.例如,一个在upper(col)上计算的索引允许WHERE upper(col) = 'JIM'字句使用这个索引.

PostgreSQL提供的索引类型包括B-tree, hash, GiST, SP-GiST, 和 GIN.用户也可以自己定义索引类型,但这是相当复杂的.

当使用WHERE字句,将会创建一个partial index.一个部分索引是仅包括表的一部分记录的索引,通常是比表中其他部分更有用的记录. 例如,如果你有一个包含了记账和不记账的订单,不记帐的订单只占了表的一小部分,并且是经常使用的部分.你可以在这部分上建立一个索引来提高效率.另一个应用是使用WHERE 和UNIQUE 来强制保证表的一个子集的唯一性.更多的信息见:第 11.8 节

WHERE 字句的表达式可以使用底层表的列,并且可以是用它的所有列,而不仅仅建立索引的列.目前子查询和聚合表达式时禁止出现在WHERE字句的,这个限制同样适用于索引.

在索引定义中使用的所有函数和操作符必须是"immutable",即他们的结果依赖与他们的参数,而不受任何外部的数据的影响(例如另一个表的内容,或当前时间).这个限制保证了索引的定义完整性.在一个索引或WHERE子句中使用用户自定义的函数时,确保在定义这些函数时标记他们是不可变的.

Parameters

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Modifiers 
--------+---------+-----------
 col    | integer | 
Indexes:
    "idx" btree (col) INVALID

Notes

在第 11 章中描述了什么时候会使用索引，什么时候不使用索引，以及在什么情况下索引是有用的．

现在只有B-tree, GiST 和 GIN支持多列索引．默认支持最多32列．（当编译PostgreSQL时可以改变这个限制）．现在只有B-tree支持唯一索引．

对于索引的每一列可以指定一个operator class．operator class标识了索引那一列的使用的操作符．例如一个B-tree索引在一个四字节整数上可以使用int4_ops；这个操作符类包括四字节整数的比较函数．实际上对于列上的数据类型默认的操作符类时足够用的．操作符类主要用于一些有多种排序的数据．例如，我们想按照绝对值或者实数部分排序一个复数．我们能通过定义两个操作符类然后当建立索引时选择合适的类．详细的操作符类信息请参考：第 11.9 节和第 35.14 节.

索引（现在只有B-tree）中支持有序扫描的字句中可以制定ASC, DESC, NULLS FIRST, 和 NULLS LAST修改索引的排序方式．因为一个有序的索引能被从前向后或者从后向前扫描．建立一个单列的DESC索引是没有意义的．因为在一个标准的索引是已经排序的．在创建多列索引时使用这些选项值来匹配一些复合查询的排序请求，例如SELECT ... ORDER BY x ASC, y DESC.如果你在查询中需要支持 "nulls sort low"特性NULLS选项是很有用的，而不是默认的"nulls sort high",

对于大多数索引，创建索引的速度依赖于maintenance_work_mem的设置．只要你没有使他超过可得到内存而进入swap分区，较大的值将会减少索引创建的时间．对于创建哈希索引的时间与effective_cache_size值相关,PostgreSQL将使用两种不同的哈希索引创建方法，主要取决于索引的大小比effective_cache_size大还是小．为了得到较好的结果，确保这个参数的设置考虑到可用内存，并且注意maintenance_work_mem和effective_cache_size的和小于其他程序所需要的内存空间．

使用 DROP INDEX删除一个索引．

PostgreSQL之前的版本有一个R-tree索引方法．这个方法已经被删除因为它对于GiST索引没有明显的优势．如果指定USING rtree，CREATE INDEX会将他翻译为USING gist，去转换老的数据库版本到GiST.

Examples

在表films的title列上建立B-tree索引：

CREATE UNIQUE INDEX title_idx ON films (title);

允许大小写无关查询在 lower(title)表达式上建立索引：

CREATE INDEX ON films ((lower(title)));

(在这个例子中我们可以选在忽略索引名字，系统将给出一个名字，典型的是films_lower_idx.)

创建一个使用非默认排序方式的索引：

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

创建一个null值的非默认排序的索引：

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

创建一个非默认填充因子的索引：

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

创建一个将快速更新关闭的GIN索引：

CREATE INDEX gin_idx ON documents_table USING gin (locations) WITH (fastupdate = off);

创建一个在films的code列的索引，并且使索引建立在indexspace表空间中：

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

在一个点属性上创建GiST索引，让我们在转换函数的结果上高效的使用box操作符：

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

创建一个在表上不加写锁的索引：

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

Compatibility

CREATE INDEX是PostgreSQL语言的扩展．在SQL标准中没有索引的规定．

See Also