PostgreSQL CREATE INDEX

描述

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

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

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

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

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

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

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

参数

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

注解

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

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

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

当在一个分区表上调用CREATE INDEX时，默认的行为是递归到所有的分区上以确保它们都具有匹配的索引。每一个分区首先会被检查是否有一个等效的索引存在，如果有则该索引将被挂接为被创建索引的一个分区索引，而被创建的索引将成为其父索引。如果不存在匹配的索引，则会创建一个新的索引并且自动进行挂接。如果命令中没有指定索引名称，每个分区中的新索引的名称将被自动决定。如果指定了ONLY选项，则不会进行递归，并且该索引会被标记为无效（一旦所有的分区都得到该索引， ALTER INDEX ... ATTACH PARTITION可以把该索引标记为有效）。不过，要注意不管是否指定这一选项，未来使用CREATE TABLE ... PARTITION OF创建的任何分区将自动有一个匹配的索引，不管有没有指定ONLY。

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

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

PostgreSQL可以在构建索引时利用多个CPU以更快地处理表行。这种特性被称为并行索引构建。对于支持并行构建索引的索引方法（当前只有B-树），maintenance_work_mem指定每次索引构建操作整体可用的最大内存量，而不管启动了多少工作者进程。一般来说，一个代价模型（如果有）自动判断应该请求多少工作者进程。

增加maintenance_work_mem可以让并行索引构建受益，而等效的串行索引构建将无法受益或者得到很小的益处。注意maintenance_work_mem可能会影响请求的工作者进程的数量，因为并行工作者必须在总的maintenance_work_mem预算中占有至少32MB的份额。还必须有 32MB的份额留给领袖进程。增加max_parallel_maintenance_workers可以允许使用更多的工作者，这将降低索引创建所需的时间，只要索引构建不是I/O密集型的。当然，还需要有足够的CPU计算能力，否则工作者们会闲置。

通过ALTER TABLE为parallel_workers设置一个值直接控制着CREATE INDEX会对表请求多少并行工作者进程。这会完全绕过代价模型，并且防止maintenance_work_mem对请求多少并行工作者产生影响。通过 ALTER TABLE将parallel_workers设置为0将禁用所有情况下的并行索引构建。

虽然带有CONCURRENTLY选项的CREATE INDEX支持并行构建并且没有特殊的限制，但只有第一次表扫描会实际以并行方式执行。

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

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

示例

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

CREATE UNIQUE INDEX title_idx ON films (title);

要在表films的列title上创建一个唯一的B-树索引并且包括列director和rating：

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

要创建禁用重复数据删除的 B 树索引：

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

在表达式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 标准中 没有对于索引的规定。

另见