描述

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

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

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

PostgreSQL 提供了 B 树、哈希、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 可能会影响请求的工作进程的数量，因为并行工作进程必须至少拥有总 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 删除索引。

像任何长时间运行的事务一样，表上的 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 中创建包含列 director 和 rating 的列 title 的唯一 B 树索引

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

另请参阅