©
本文档使用
php.cn手册 发布
CREATE INDEX
Name
CREATE INDEX -- 定义一个新索引Synopsis
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
描述
CREATE INDEX在指定的表上创建一个索引。 索引主要用来提高数据库性能。但是如果不恰当的使用将导致性能的下降。
索引的键字字段是以字段名的方式声明的，或者是可选的写在一个圆括弧 里面的表达式。如果索引方式支持多字段索引，那么也可以声明多个字段。
索引字段可以是一个使用一个或多个字段值进行计算的表达式。 这个特性可用于获取对基本数据的某种变形的快速访问。 比如，一个在upper(col)上的函数索引将允许 WHERE upper(col) = 'JIM'子句使用索引。
PostgreSQL为从索引提供B-tree, hash, GiST, GIN 索引方法。用户也可以定义它们自己的索引方法，但这个工作相当复杂。
如果出现了WHERE子句，则创建一个部分索引 。部分索引是一个只包含表的一部分记录的索引， 通常是该表中比其它部分数据更有用的部分。比如，如果你有一个表， 里面包含已记账和未记账的定单，未记账的定单只占表的一小部分而且这部分 是最常用的部分，那么你就可以通过只在未记账部分创建一个索引来改善性能。 另外一个可能的用途是使用带有UNIQUE的WHERE 强制一个表的某个子集的唯一性。 参阅Section 11.8获取更多信息。
WHERE子句里的表达式只能引用下层表的字段， 它可以使用所有字段，而不仅仅是被索引的字段。目前，子查询和 聚集表达式也不能出现在WHERE子句里。
索引定义里的所有函数和操作符都必须是"immutable"(不变的)， 也就是说，它们的结果必须只能依赖于它们的输入参数， 而不能依赖任何外部的影响(比如另外一个表的内容或者当前时间)。 这个限制可以确保该索引的行为是定义良好的。 要在一个索引上或WHERE中使用用户定义函数， 请把它标记为immutable函数。
参数
- UNIQUE
令系统在创建索引时(如果数据已经存在)和每次添加数据时检测表中 是否有重复值。 如果插入或更新的值会导致重复的记录时将生成一个错误。
- CONCURRENTLY
使用该选项后，PostgreSQL将在创建索引的过程中不在表上持有任何防止插入、 更改、删除的写入锁；否则将持有写入锁直到创建完成才释放。使用这个选项时有几个注意点，— 参见Building Indexes Concurrently并行创建索引。
- name
要创建的索引名，不能包含模式名。索引总是在同一个模式中作为父表创建的。 如果名称被忽略，PostgreSQL会基于父表的名称和索引列 名称来选择合适的名称。
- table
要索引的表名(可能有模式修饰)
- method
要使用的索引方法的名字。可选的名字是btree(缺省)， hash，gist和gin
- column
表的列/字段名
- expression
一个基于该表的一个或多个字段的表达式。这个表达式通常必须带着圆括 弧包围写出，如语法中显示那样。不过，如果表达式有函数调用的形式， 那么圆括弧可以省略。
- opclass
一个关联的操作符类。参阅下文获取细节。
- ASC
指定按升序排序（这是默认的）。
- DESC
指定按降序排序。
- NULLS FIRST
指定空值在排序中排在非空值之前。当DESC声明后这些是默认的。
- NULLS LAST
指定空值在排序中排在非空值之后。当DESC未声明时这是 默认的。
- storage_parameter
索引方法特定的存储参数的名字。 参阅索引存储参数获取细节。
- tablespace
-
在表空间中可创建索引。若未声明，需参考default_tablespace 或者参考temp_tablespaces获取临时表索引方面的信息。
创建索引的表空间。如果没有声明，则使用default_tablespace， 或者temp_tablespaces临时索引表。
- predicate
为一个部分索引定义约束表达式
索引存储参数
可选的WITH子句为索引声明storage parameters。 每个索引方法有自己一套允许的存储参数。B-tree，hash和GiST索引方法都接受 下面这个单独的参数：
- FILLFACTOR
一个索引的填充因子(fillfactor)是一个百分比， 它表示创建索引时每个索引页的数据填充率。对于B-trees来说， 意味着在创建索引时叶子页将按照此百分比填充数据， 在右侧(添加新的最大的键值)扩展索引时同样也按照此百分比填充数据。如果后来某个页被完全填满， 那么该页将被分割，从而导致索引性能退化。B-trees默认的填充因子是90 ， 但是有效的整数取值范围是10到100 。对于静态的不会发生改变的表， 最佳值100可以让索引的物理体积最小，但是对于不断增长的表，较小的填充因子更合适， 因为这将尽可能减少对页的分割。其它索引方法对填充因子的理解与此类似， 但是其默认值各不相同。
GIN索引接受一个不同的参数：
- FASTUPDATE
-
这个设置控制Section 53.3.1中描述的快速更新技术。 是一个布尔型参数：ON启用快速更新，OFF禁用它。 （ON和OFF的可选拼法允许像Section 18.1 中描述的一样。）默认是ON。
Note: 通过ALTER INDEX关闭FASTUPDATE可以阻止未来的 插入进入到挂起索引条目列表但自身不会划掉先前的条目。你可能想要 VACUUM然后确保挂起列表为空。
Building Indexes Concurrently并行创建索引
创建索引的过程会对数据库的常规操作性能有不利影响。通常， 在创建索引的时候PostgreSQL会锁定表以防止写入， 然后对表做一次完整扫描以完成索引的创建。在此过程中其他事务仍然可以读取表， 但是插入、更新、删除将被一直阻塞到索引创建完毕。 这样做对于处于活跃状态的数据库可能会产生严重的性能影响。 因为某些很大的表可能需要数个小时的时间来建立索引， 而且即使对于生产中正在使用的较小的表，这种阻塞通常也是不可接受的。
PostgreSQL支持在没有锁定出写道的情况下建立索引。 通过声明CREATE INDEX的CONCURRENTLY选项来调用 该方法。当该选项被使用，PostgreSQL必须执行表的两次扫描， 另外它必须等待所有可能会试用索引来中止的现有事务。因此该方法需要比需要长一些 时间来完成建立和使用的索引需要更多的总工作量。然而，尽管允许常规操作在索引 建立时继续下去，这个方法对于在生产环境添加新索引是非常有效的。索引创建强加的 额外CPU和I/O负载可能放慢其他操作。
PostgreSQL允许在CREATE INDEX创建索引时使用CONCURRENTLY选项指定不锁定表。 这样PostgreSQL就必须对表扫描两次，并且必须等待所有潜在 使用索引终止正在执行的事务完成。 这种方法增加了总体工作量并且可能需要非常长的时间才能完成索引的创建。 然而由于这种方法允许在创建索引的同时进行常规操作， 因此这种方法适合于在运行过程中添加新索引。当然， 创建索引所需要的额外CPU和I/O开销仍然会对其它操作有不利影响。
在一个并发索引构建中，该指数实际上是进入一个事务中的系统目录，然后这 两个表扫描就会发生不再第二和第三个表中。 如果在对表进行第二次扫描的时候出现了问题，比如在唯一索引字段上出现了重复值， CREATE INDEX命令将会失败并遗留下一个"非法"的索引。 这个"非法"索引将被忽略，因为它可能是不完整的， 然而它还是会消耗更新开销。psql\d 命令将会报告这样一个索引作为INVALID:
postgres=# \d tab
Table "public.tab"
Column | Type | Modifiers
--------+---------+-----------
col | integer |
Indexes:
"idx" btree (col) INVALID
在这种情况下推荐删除该索引然后尝试重新执行CREATE INDEX CONCURRENTLY命令， 另一种可能的方法是使用REINDEX重建索引。 由于REINDEX不支持并行创建索引，因此可能不是一个好方法。
对于并行创建唯一索引还有一个警告：在开始对表进行第二次扫描的时候， 已经在其他事务上强制进行唯一约束了。 这意味着在索引创建完毕之前，如果有其它违反唯一约束的行为那么将会报错， 甚至在索引最终创建失败的情况下也是如此。同样，如果在第二次扫描的过程中发生错误， 生成的"非法"索引仍将在随后强制执行唯一约束的检查。
并行创建表达式索引和部分索引也是可以的。 在表达式求值过程中发生的错误同样也会在唯一约束索引上导致类似前面描述过的 行为。
在创建普通索引的同时还允许在同一张表上创建其他普通索引， 但是在一张表上只能进行一个并行索引的创建。在此两种情况下， 都不允许同时对表所在的模式进行修改。另一个差异是CREATE INDEX可以放在一个事务块中执行， 但CREATE INDEX CONCURRENTLY不可以。
注意
参阅Chapter 11获取有关何时使用索引、何时不使用索引， 以及索引在哪种情况下是有用的信息。
目前，只有B-tree和GiST索引方法支持多字段索引。 缺省时最多可以声明32个键字(可以在编译PostgreSQL时修改)。 目前只有B-tree支持唯一索引。
可以为索引的每个列/字段声明一个操作符类标识将要被该索引用于该列/字段的操作符。 例如，一个四字节整数的B-tree索引将使用int4_ops表； 这个操作符类包括四字节整数的比较函数。实际上，该域的数据类型的缺省操作符类一般就足够了。 某些数据类型有操作符类的原因是它们可能有多个有意义的顺序。 例如，复数类型可能以绝对值或者实部排序。可以通过为该数据类型定义两个操作符类， 然后在建立索引的时候选择合适的表来实现。有关操作符类更多的信息在Section 11.9和Section 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 的设置。更大的值将减少创建索引所需的时间，只要您不把它设为大于真正可用的 内存数量，这会驱动机器进入交换。对于哈希索引， effective_cache_size还与索引创建时间有关系： PostgreSQL将会使用两个不同哈希创建方法中的一个， 这取决与估计索引大小是大于还是小于effective_cache_size。 为获得最佳效果，确保这个参数也被设置为可用内存的反射，并且要注意 maintenance_work_mem和effective_cache_size的和 是小于机器的RAM，无论其他程序需要多少空间。
使用DROP INDEX删除一个索引。
早先的PostgreSQL版本还有一个R-tree索引方法。 因为它并不比GiST方法优秀， 因此现在已经被删除了。如果指定了USING rtree的话， CREATE INDEX将把它当作USING gist看待，并将老的 索引转化为GiST索引。
例子
在表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_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索引，所以,我们能够有效地使用箱操作符， 以转换函数为结果：
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);
兼容性
CREATE INDEX是PostgreSQL 语言扩展。在SQL标准中没有这个命令。