描述

CREATE INDEX 在指定关系（可以是表或物化视图）的指定列上构建索引。索引主要用于提高数据库性能（尽管使用不当会导致性能下降）。

索引的键字段指定为列名，或者可选地用括号括起来的表达式。如果索引方法支持多列索引，则可以指定多个字段。

索引字段可以是根据表行的一列或多列的值计算得出的表达式。此功能可用于根据对基本数据的某种转换获得对数据的快速访问。例如，根据 upper(col) 计算的索引将允许子句 WHERE upper(col) = 'JIM' 使用索引。

PostgreSQL 提供了 B-tree、hash、GiST、SP-GiST、GIN 和 BRIN 索引方法。用户还可以定义自己的索引方法，但这相当复杂。

当存在 WHERE 子句时，将创建 部分索引。部分索引是仅包含表一部分的条目（通常是比表其余部分更适合索引的部分）的索引。例如，如果您有一个表包含已开具发票和未开具发票的订单，其中未开具发票的订单占表总量的很小一部分，但这是一个经常使用的部分，那么您可以通过在该部分上创建一个索引来提高性能。另一个可能的应用是将 WHERE 与 UNIQUE 一起使用来对表的子集强制唯一性。有关更多讨论，请参阅 第 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 索引方法支持多键列索引。是否可以有多个键列与是否可以将 INCLUDE 列添加到索引无关。索引最多可以有 32 列，包括 INCLUDE 列。（此限制可以在构建 PostgreSQL 时更改。）目前只有 B 树支持唯一索引。

每个索引列都可以指定一个具有可选参数的操作符类。操作符类标识索引用于该列的操作符。例如，一个四字节整数的 B 树索引将使用 int4_ops 类；该操作符类包括用于四字节整数的比较函数。在实践中，列数据类型的默认操作符类通常就足够了。操作符类存在的意义是，对于某些数据类型，可能存在多个有意义的排序方式。例如，我们可能希望根据绝对值或实部对复数数据类型进行排序。我们可以通过为数据类型定义两个操作符类，然后在创建索引时选择适当的类来实现这一点。有关操作符类的更多信息，请参见第 11.10 节和第 36.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 选项非常有用，在依赖索引以避免排序步骤的查询中，这一点很重要。

系统定期收集所有表列的统计信息。新创建的非表达式索引可以立即使用这些统计信息来确定索引是否有用。对于新的表达式索引，需要运行 ANALYZE 或等待 自动清理守护进程 分析表以生成这些索引的统计信息。

在 CREATE INDEX 运行期间，search_path 将临时更改为 pg_catalog, pg_temp。

对于大多数索引方法，创建索引的速度取决于 maintenance_work_mem 的设置。较大的值将减少创建索引所需的时间，只要你不将其设置得大于实际可用的内存量，否则会导致机器进行交换。

PostgreSQL 可以构建索引，同时利用多个 CPU 以更快地处理表行。此功能称为并行索引构建。对于支持并行构建索引的索引方法（目前只有 B 树），maintenance_work_mem 指定每个索引构建操作作为一个整体可以使用的最大内存量，而不管启动了多少个工作进程。通常，成本模型会自动确定应该请求多少个工作进程（如果有）。

并行索引构建可能会受益于增加 maintenance_work_mem，而在等效的串行索引构建中，几乎不会或根本不会看到任何好处。请注意，maintenance_work_mem 可能会影响请求的工作进程数量，因为并行工作进程必须至少有 32MB 的总 maintenance_work_mem 预算份额。领导者进程还必须剩余 32MB 的份额。增加 max_parallel_maintenance_workers 可能会允许使用更多工作进程，这将减少创建索引所需的时间，只要索引构建不已经是 I/O 绑定。当然，还应该有足够的 CPU 容量，否则会处于空闲状态。

通过 ALTER TABLE 为 parallel_workers 设置一个值将直接控制 CREATE INDEX 对表的请求的并行工作进程数量。这完全绕过了成本模型，并阻止了 maintenance_work_mem 影响请求的并行工作进程数量。通过 ALTER TABLE 将 parallel_workers 设置为 0 将在所有情况下禁用表上的并行索引构建。

虽然 CREATE INDEX 使用 CONCURRENTLY 选项支持并行构建，但没有特殊限制，但实际上只有第一个表扫描是并行执行的。

使用 DROP INDEX 删除索引。

与任何长时间运行的事务一样，对表的 CREATE INDEX 可能会影响并发 VACUUM 对任何其他表可以删除哪些元组。

早期版本的 PostgreSQL 还具有 R 树索引方法。该方法已被删除，因为它没有比 GiST 方法明显的优势。如果指定了 USING rtree，CREATE INDEX 将将其解释为 USING gist，以简化将旧数据库转换为 GiST 的操作。

每个运行 CREATE INDEX 的后端将在 pg_stat_progress_create_index 视图中报告其进度。有关详细信息，请参见第 27.4.4 节。

示例

在表 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 索引，以便我们可以有效地对转换函数的结果使用框操作符

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 标准中没有关于索引的规定。

另请参见