CREATE INDEX

CREATE INDEX
上一页	上一级	SQL 命令	起始页	下一页

CREATE INDEX — 定义一个新索引

大纲

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

描述

CREATE INDEX在指定关系的指定列上构建一个索引，该关系可以是一个表或者一个物化视图。索引主要被用来提升数据库性能（不过不当的使用会导致性能变差）。

索引的键域被指定为列名或者写在圆括号中的表达式。如果索引方法支持多列索引，可以指定多个域。

一个索引域可以是一个从表行的一列或者更多列值进行计算的表达式。这种特性可以被用来获得对基于基本数据某种变换的数据的快速访问。例如，一个在upper(col)上计算的索引可以允许子句 WHERE upper(col) = 'JIM'使用索引。

PostgreSQL提供了索引方法 B-树、哈希、GiST、SP-GiST、GIN 以及 BRIN。用户也可以定义自己的索引方法，但是相对较复杂。

当WHERE子句存在时，会创建一个 部分索引。部分索引只包含表中一部分行的项，通常索引这一部分会比表的其他部分更有用。例如，如果有一个表包含了已付和未付订单，其中未付订单占了整个表的一小部分并且是经常被使用的部分，可以通过只在这一部分上创建一个索引来改进性能。另一种可能的应用是使用带有UNIQUE的 WHERE在表的一个子集上强制唯一性。更多的讨论请见第 11.8 节。

WHERE子句中使用的表达式只能引用底层表的列，但它可以引用所有列而不仅仅是被索引的列。当前， WHERE中也禁止使用子查询和聚集表达式。同样的限制也适用于表达式索引中的表达式域。

所有在索引定义中使用的函数和操作符必须是“不可变的”，就是说它们的结果必须仅依赖于它们的参数而不受外在因素（例如另一个表的内容和当前的时间）的影响。这种限制确保了索引的行为是良定的。要在一个索引表达式或者WHERE子句中使用用户定义的函数，记住在创建函数时把它标记为不可变。

参数

UNIQUE: 导致系统在索引被创建时（如果数据已经存在）或者加入数据时检查重复值。会导致重复项的数据插入或者更新尝试将会产生一个错误。
CONCURRENTLY: 当使用了这个选项时，PostgreSQL在构建索引时不会取得任何会阻止该表上并发插入、更新或者删除的锁。而标准的索引构建将会把表锁住以阻止对表的写（但不阻塞读），这种锁定会持续到索引创建完毕。在使用这个选项时有多个需要注意的地方 — 请见并发构建索引。
IF NOT EXISTS: 如果一个同名关系已经存在则不要抛出错误。这种情况下会发出一个提示。注意着并不保证现有的索引与将要创建的索引有任何相似。当 IF NOT EXISTS被指定时，需要指定索引名。
name: 要创建的索引名称。这里不能包括模式名，因为索引总是被创建在其基表所在的模式中。如果索引名称被省略，PostgreSQL将基于基表名称和被索引列名称选择一个合适的名称。
table_name: 要被索引的表的名称（可以被模式限定）。
method: 要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、 gin以及brin。默认方法是btree。
column_name: 一个表列的名称。
expression: 一个基于一个或者更多个表列的表达式。如语法中所示，表达式通常必须被写在圆括号中。不过，如果该表达式是一个函数调用的形式，圆括号可以被省略。
collation: 要用于该索引的排序规则的名称。默认情况下，该索引使用被索引列的排序规则或者被索引表达式的结果排序规则。当查询涉及到使用非默认排序规则的表达式时，使用非默认排序规则的索引就能排上用场。
opclass: 一个操作符类的名称。详见下文。
ASC: 指定上升排序（默认）。
DESC: 指定下降排序。
NULLS FIRST: 指定把空值排序在非空值前面。在指定DESC时，这是默认行为。
NULLS LAST: 指定把空值排序在非空值后面。在没有指定DESC时，这是默认行为。
storage_parameter: 索引方法相关的存储参数的名称。详见索引存储参数。
tablespace_name: 在其中创建索引的表空间。如果没有指定，将会使用 default_tablespace。或者对临时表上的索引使用 temp_tablespaces。
predicate: 部分索引的约束表达式。

索引存储参数

可选的WITH子句为索引指定存储参数。每一种索引方法都有自己的存储参数集合。B-树、哈希、GiST 和 SP-GiST 索引方法都接受这个参数：

fillfactor: 索引的填充因子是一个百分数，它决定索引方法将尝试填充索引页面的充满程度。对于 B-树，在初始的索引构建过程中，叶子页面会被填充至该百分数，当在索引右端扩展索引（增加新的最大键值）时也会这样处理。如果页面后来被完全填满，它们就会被分裂，导致索引的效率逐渐退化。B-树使用了默认的填充因子 90，但是也可以选择为 10 到 100 的任何整数值。如果表是静态的，那么填充因子 100 是最好的，因为它可以让索引的物理尺寸最小化。但是对于更新负荷很重的表，较小的填充因子有利于最小化对页面分裂的需求。其他索引方法以不同但是大致类似的方式使用填充因子，不同方法的默认填充因子也不相同。

GiST 还额外接受这个参数：

buffering: 决定是否用第 62.4.1 节中描述的缓冲构建技术来构建索引。OFF会禁用它，ON则启用该特性，如果设置为AUTO则初始会禁用它，但是一旦索引尺寸到达 effective_cache_size就会随时打开。默认值是 AUTO。

GIN 索引接受不同的参数：

fastupdate: 这个设置控制第 64.4.1 节中描述的快速更新技术的使用。它是一个布尔参数：ON启用快速更新， OFF禁用之（ON和OFF的其他写法在第 19.1 节中有介绍）。默认是 ON。
注意
通过ALTER INDEX关闭fastupdate 会阻止未来的更新进入到待处理索引项列表中，但它不会自己处理之前的待处理项。可以使用VACUUM或者调用 gin_clean_pending_list确保处理完待处理列表的项。

gin_pending_list_limit: 自定义gin_pending_list_limit参数。这个值要以千字节来指定。

BRIN索引接受不同的参数：

pages_per_range: 定义用于每一个BRIN索引项的块范围由多少个表块组成（详见第 65.1 节）。默认是128。
autosummarize: 定义每当在下一页检测到插入时，是否为前一页范围调用汇总运行。

并发构建索引

创建索引可能会干扰数据库的常规操作。通常 PostgreSQL会锁住要被索引的表，让它不能被写入，并且用该表上的一次扫描来执行整个索引的构建。其他事务仍然可以读取表，但是如果它们尝试在该表上进行插入、更新或者删除，它们会被阻塞直到索引构建完成。如果系统是一个生产数据库，这可能会导致严重的后果。索引非常大的表可能会需要很多个小时，而且即使是较小的表，在构建索引过程中阻塞写入者一段时间在生产系统中也是不能接受的。

PostgreSQL支持构建索引时不阻塞写入。这种方法通过指定CREATE INDEX的CONCURRENTLY选项实现。当使用这个选项时，PostgreSQL必须执行该表的两次扫描，此外它必须等待所有现有可能会修改或者使用该索引的事务终止。因此这种方法比起标准索引构建过程来说要做更多工作并且需要更多时间。不过，由于它允许在构建索引时继续普通操作，这种方式对于在生产环境中增加新索引很有用。当然，由索引创建带来的额外 CPU 和 I/O 开销可能会拖慢其他操作。

在并发索引构建中，索引实际上在一个事务中被录入到系统目录，然后在两个事务中发生两次表扫描。在每一次表扫描之前，索引构建必须等待已经修改了表的现有事务终止。在第二次扫描之后，索引构建必须等待任何持有早于第二次扫描的快照（见第 13 章）的事务终止。然后该索引最终能被标记为准备好使用，并且CREATE INDEX命令终止。不过即便那样，该索引也不是立刻可以用于查询：在最坏的情况下，只要早于索引构建开始时存在的事务存在，该索引就无法使用。

如果在扫描表示出现问题，例如死锁或者唯一索引中的唯一性被违背， CREATE INDEX将会失败，但留下一个“不可用” 的索引。这个索引会被查询所忽略，因为它可能不完整。不过它仍将消耗更新开销。psql的\d命令将把这类索引报告为 INVALID：

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

这种情况下推荐的恢复方法是删除该索引并且尝试再次执行 CREATE INDEX CONCURRENTLY（另一种可能性是用 REINDEX重建该索引。不过，由于 REINDEX不支持并发构建，这种选择不那么有吸引力）。

并发构建一个唯一索引时需要注意的另一点是，当第二次表扫描开始时，唯一约束已经被强制在其他事务上。这意味着在该索引变得可用之前，其他查询中可能就会报告该约束被违背，或者甚至在索引构建最终失败的情况中也是这样。还有，如果在第二次扫描时发生失败，“无效的”索引也会继续强制它的唯一性约束。

表达式索引和部分索引的并发构建也被支持。在这些表达式计算过程中发生的错误可能导致和上述唯一约束违背类似的行为。

常规索引构建允许在同一个表上并行构建其他常规索引，但是在一个表上同时只能有一个并发索引构建发生。在两种情况下，同时都不允许在表上有其他类型的模式修改。另一个不同是，一个常规CREATE INDEX 命令可以在一个事务块中执行，但是 CREATE INDEX CONCURRENTLY不行。

注解

关于索引何时能被使用、何时不被使用以及什么情况下它们有用的信息请见第 11 章。

当前，只有 B-树、GiST、GIN 和 BRIN 索引方法支持多列索引。默认最多可以索引 32 个域（可以在构建 PostgreSQL修改这种限制）。当前只有 B-树支持唯一索引。

为索引的每一列可以指定一个操作符类。该操作符类标识要被该索引用于该列的操作符。例如，一个四字节整数上的 B-树索引会使用int4_ops类。这个操作符类包括了用于四字节整数的比较函数。实际上，通常列数据类型的默认操作符类就足够了。对某些数据类型指定操作符类的主要原因是，可能会有多于一种有意义的顺序。例如，我们可能想用绝对值或者实数部分对复数类型排序。我们可以通过为该数据类型定义两个操作符类来做到，并且在创建索引时选择其中合适的类。更多有关操作符类的信息请见第 11.9 节以及第 37.14 节。

对于支持有序扫描的索引方法（当前只有 B-树），可以指定可选子句ASC、 DESC、NULLS FIRST以及NULLS LAST 来修改索引的排序顺序。由于一个有序索引能前向或者反向扫描，通常创建一个单列DESC索引没什么用处 — 一个常规索引已经提供了排序顺序。这些选项的价值是可以创建多列索引，让它的排序顺序匹配有混合排序要求的查询，例如SELECT ... ORDER BY x ASC, y DESC。如果你想要在依靠索引避免排序步骤的查询中支持 “空值排序低”这种行为，NULLS选项就能派上用场，默认的行为是“空值排序高”。

对于大多数索引方法，索引的创建速度取决于 maintenance_work_mem的设置。较大的值将会减少索引创建所需的时间，当然不要把它设置得超过实际可用的内存量（那会迫使机器进行交换）。

使用DROP INDEX可以移除一个索引。

以前的PostgreSQL发行也有一种 R-树索引方法。这种方法已经被移除，因为它比起 GiST 方法来说没有什么明显的优势。如果指定了USING rtree，CREATE INDEX 将会把它解释为USING gist，以便把旧的数据库转换成 GiST。

示例

在表films中的列title上创建一个 B-树索引：

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");

创建一个具有非默认空值排序顺序的索引：

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);

兼容性

CREATE INDEX是一种 PostgreSQL的语言扩展。在 SQL 标准中没有对于索引的规定。

另见

ALTER INDEX, DROP INDEX