Postgresql 中文操作指南

CREATE INDEX

CREATE INDEX — 定义新索引

Synopsis

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

Description

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

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

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

PostgreSQL 提供 B 树、哈希、GiST、SP-GiST、GIN 和 BRIN 索引方法。用户还可以定义自己的索引方法，但这非常复杂。

当存在 WHERE 子句时，将创建 partial index 。部分索引是一个索引，其中仅包含表的某一部分的条目，通常是比表的其它部分更适合于编制索引的那一部分。例如，如果你有一个包含已开票和未开票订单的表，其中未开票订单占总表的很小一部分，但仍是经常使用的一部分，则可以通过仅在该部分上创建索引来提高性能。另一个可能的应用是使用 WHERE 和 UNIQUE 对表的子集强制唯一性。有关更多讨论，请参见 Section 11.8 。

WHERE 子句中使用的表达式只能引用基础表中的列，但它可以使用所有列，不仅仅是正在编制索引的列。目前，在 WHERE 中子查询和聚合表达式也被禁止。相同限制适用于作为表达式的索引字段。

在索引定义中所使用的所有函数和运算符都必须是“不变的”，即，其结果必须只取决于其参数，并且永远不会依赖任何外部影响（例如其他表的表内容或当前时间）。这个限制确保了索引的行为是明确定义的。要在索引表达或 WHERE 子句中使用用户定义的函数，请记住在创建函数时标记该函数为不变。

Parameters

  • UNIQUE

    • 当索引被创建（如果数据已经存在）以及每次添加数据时，系统会检查表中的重复值。尝试插入或更新将会生成重复输入的数据将产生错误。

    • 在将唯一索引应用于分区表时，会应用其他限制；请参见 CREATE TABLE 。

  • CONCURRENTLY

    • 当使用此选项时，PostgreSQL 将构建索引，而不会导致任何锁定，从而防止对表进行并发插入、更新或删除；而标准索引构建将在它完成之前锁定对表的写入（但不锁读）。在使用此选项时，有几个需要注意的注意事项，请参见下面的 Building Indexes Concurrently 。

    • 对于临时表， CREATE INDEX 始终是无并发的，因为没有其他会话可以访问它们，并且无并发索引创建更加便宜。

  • IF NOT EXISTS

    • 如果已经存在同名关系，则不要抛出错误。在这种情况下，会发出通知。请注意，无法保证现有索引与本应创建的索引类似。 IF NOT EXISTS 被指定时，需要索引名称。

  • INCLUDE

    • 可选项 INCLUDE 子句指定一列作为 non-key 列包含在索引中的列表中。非键列不能用在索引扫描搜索限定符中，并且不考虑任何由索引执行的唯一性或排除约束。但是，仅索引扫描可以返回非键列的内容，而无需访问索引的表，因为它们可以直接从索引条目中获得。因此，非键列的添加允许仅索引扫描用于否则无法使用它们的查询。

    • 最好保守地向索引添加非键列，尤其是宽列。如果索引元组超过了索引类型允许的最大大小，则数据插入将失败。在任何情况下，非关键列都会复制索引表中的数据，并增加索引的大小，从而可能导致搜索变慢。此外，对于具有非键列的索引，从不使用 B 树去重。

    • INCLUDE 子句中列出的列不需要适当的操作器类；该子句可以包括其数据类型没有为给定访问方法定义操作器类的列。

    • 表达式不受支持作为包含的列，因为它们不能用于仅索引扫描。

    • 目前，B 树、GiST 和 SP-GiST 索引访问方法支持此功能。在这些索引中， INCLUDE 子句中列出的列的值包含在叶元组中，这些叶元组对应于堆元组，但这些叶元组不包含用于树导航的上层索引项。

  • name

    • 要创建的索引的名称。不能在此处包含架构名称；索引始终在与其父表相同的架构中创建。索引的名称必须与其架构中的任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称不同。如果省略名称，则 PostgreSQL 会根据父表的名称和索引列名称选择一个合适的名称。

  • ONLY

    • 如果表已分区，则表示不递归创建分区索引。默认情况下是递归。

  • table_name

    • 要编制索引的表名称（可能会限定架构）。

  • method

    • 要使用的索引方法的名称。选择包括 btree 、 hash 、 gist 、 spgist 、 gin 、 brin 或像 bloom 这样的用户已安装的访问方法。默认方法是 btree 。

  • column_name

    • 表的列的名称。

  • expression

    • 基于表的一个或多个列的表达式。该表达式通常必须用括号括起来编写，如语法中所示。但是，如果表达式是函数调用的形式，则可以省略括号。

  • collation

    • 要在索引中使用的对照的名称。默认情况下，索引使用为要编制索引的列声明的对照或要编制索引的表达式的结果对照。使用非默认对照的索引对于涉及使用非默认对照的表达式的查询很有用。

  • opclass

    • 一个执行器类的名称。有关详细信息，请参阅下文。

  • opclass_parameter

    • 一个执行器类参数的名称。有关详细信息，请参阅下文。

  • ASC

    • 指定升序排列顺序（这是默认设置）。

  • DESC

    • Specifies descending sort order.

  • NULLS FIRST

    • 指定空值在非空值之前进行排列。当指定 DESC 时，这是默认设置。

  • NULLS LAST

    • 指定空值在非空值之后进行排列。当未指定 DESC 时，这是默认设置。

  • NULLS DISTINCT__NULLS NOT DISTINCT

    • 指定对于唯一索引，空值是否应被视为不同的（不相等的）。默认值为不同，因此，唯一索引可在列中包含多个空值。

  • storage_parameter

  • tablespace_name

  • predicate

    • 部分索引的约束表达式。

Index Storage Parameters

可选的 WITH 子句为索引指定 storage parameters 。每个索引方法都有其自身的一组允许的存储参数。B 树、哈希、GiST 和 SP-GiST 索引方法都接受此参数：

  • fillfactor (integer) #

    • 索引的填充因子是一个百分比，它确定索引方法尝试为索引页打包多少容量。对于 B 树，在初始索引构建期间，叶页填充到此百分比，并在向右扩展索引（添加新的最大的键值）时填充到此百分比。如果页随后完全填满，则会将这些页拆分，从而导致磁盘索引结构的碎片。B 树使用 90 的默认填充因子，但可以选择 10 到 100 之间的任意整数。

    • 在预期的插入和/或更新较多的表上的 B 树索引可以在 CREATE INDEX 时受益于较低的填充因子设置（在批量加载表之后）。50 至 90 范围内的值可以有效地“平滑”B 树索引早期阶段的页面拆分（像这样降低填充因子甚至可以降低页面拆分的绝对数量，尽管此效果在很大程度上取决于工作负荷）。 Section 67.4.2 中描述的 B 树自下而上索引删除技术依赖于页面上存在一些“额外”空间以存储“额外”元组版本，因此会受到填充因子的影响（尽管影响通常并不严重）。

    • 在其他特定情况下，将 CREATE INDEX 时将填充因子增加到 100 可能是最大化空间利用率的一种有用的方式。你应仅在完全确信表是静态表（即它永远不会受到插入或更新的影响）时才考虑这种情况。否则，填充因子设置为 100 会对 harming 性能产生风险：即使只有少量的更新或插入也会导致页面拆分的突然激增。

    • 其他索引方法以不同但大致类似的方式使用填充因子；默认填充因子因方法而异。

B 树索引还接受此参数：

  • deduplicate_items (boolean) #

    • 控制使用 Section 67.4.3 中描述的 B 树去重技术。设置为 ON 或 OFF 以启用或禁用优化。（允许 ON 和 OFF 的替代拼写，如 Section 20.1 中所述。）默认值为 ON 。

Note

通过 ALTER INDEX 关闭 deduplicate_items 可防止将来插入触发去重，但本身不会使现有的发布列表元组使用标准元组表示形式。

GiST 索引还接受此参数：

  • buffering (enum) #

    • 确定是否使用 Section 68.4.1 中描述的缓冲构建技术来构建索引。使用 OFF 禁用缓冲，使用 ON 启用缓冲，使用 AUTO 初始禁用缓冲，但当索引大小达到 effective_cache_size 时会动态启用缓冲。默认值为 AUTO 。请注意，如果可能进行排序构建，则会使用排序构建，除非指定了 buffering=ON 。

GIN 索引接受不同的参数：

  • fastupdate (boolean) #

    • 该设置控制 Section 70.4.1 中描述的快速更新技术的用法。这是一个布尔参数： ON 启用快速更新， OFF 禁用快速更新。默认值为 ON 。

Note

通过 ALTER INDEX 关闭 fastupdate 会阻止将将来的插入操作添加到待处理索引项列表中，但本身不会刷新以前的项。你可能希望 VACUUM 表或调用 gin_clean_pending_list 函数，以确保已清空待处理列表。

  • gin_pending_list_limit (integer) #

BRIN 索引接受不同的参数：

  • pages_per_range (integer) #

    • 定义表块的数量，这些表块组成 BRIN 索引的每个项的一个块范围（有关更多详细信息，请参阅 Section 71.1 ）。默认值为 128 。

  • autosummarize (boolean) #

    • 定义对前一页面的范围排队汇总运行，无论是在下一页面检测到插入操作时。有关更多详细信息，请参阅 Section 71.1.1 。默认值为 off 。

Building Indexes Concurrently

创建索引可能会干扰数据库的常规操作。PostgreSQL 通常将要编制索引的表锁定，以防写入，并使用表的单次扫描执行整个索引构建。其他事务仍可以读取表，但如果它们尝试在表中插入、更新或删除行，它们将阻塞，直到索引构建完成。如果该系统是一个实时生产数据库，则可能会产生严重的影响。非常大的表可能需要很多小时才能编制索引，即使对于较小的表，索引构建也会将写入者锁定一段时期，而这对于生产系统来说是不可接受的。

PostgreSQL 支持在不锁定写入的情况下构建索引。此方法通过指定 CREATE INDEX 的 CONCURRENTLY 选项来调用。当使用此选项时，PostgreSQL 必须对表执行两次扫描，此外，它还必须等待所有可能修改或使用索引的现有事务终止。因此，此方法比标准索引构建需要更多总工作，并且需要更长的时间才能完成。但是，由于它允许在构建索引时继续进行正常操作，因此此方法可用于在生产环境中添加新索引。当然，索引创建所施加的额外 CPU 和 I/O 负载可能会减慢其他操作。

在并发索引构建中，索引实际上作为一个“无效”索引进入到一个事务中的系统目录中，然后在两个以上的其他事务中发生两次表扫描。在每次表扫描前，索引构建必须等待已修改该表的现有事务终止。在第二次扫描后，索引构建必须等待任何具有早于第二次扫描的快照（参阅 Chapter 13 ）的事务终止，包括由其他表上并发索引构建的任何阶段使用的交易，如果涉及索引是部分的或具有不是简单列引用的列。然后，最终索引可以被标记为“有效”并准备好使用，并且 CREATE INDEX 命令终止。然而，即使这样，索引可能也无法立即用于查询：在最坏的情况下，只要早于索引构建开始的事务存在，就无法使用它。

如果在扫描表时出现问题，例如死锁或唯一索引中的唯一性违规，则 CREATE INDEX 命令将失败，但会留下“无效”索引。此索引将因可能是 incomplete 而被忽略用于查询目的；但是，它仍会消耗更新开销。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 INDEX CONCURRENTLY 重新构建索引。）

在并发构建唯一索引时的另一个需要注意的地方是，当第二个表扫描开始时，唯一性约束已对其他事务强制执行。这意味着在索引可用于使用之前，其他查询中可能会报告约束违规，甚至是在索引构建最终失败的情况下。此外，如果在第二次扫描中确实发生故障，则“无效”索引会继续在之后强制执行其唯一性约束。

表达式索引和部分索引的并发构建得到支持。在计算这些表达式时发生的错误可能会导致类似于上面针对唯一约束违规描述的行为。

常规索引构建允许在同一表上对其他常规索引构建同时发生，但一次只允许在表上发生一个并发索引构建。在这两种情况下，在构建索引时都不允许修改表的模式。另一个区别在于，常规 CREATE INDEX 命令可以在事务块内执行，但 CREATE INDEX CONCURRENTLY 则不行。

当前不支持对分区表的索引进行并发构建。但是，你可以分别对各个分区并发构建索引，然后最终以非并发方式创建分区索引，以减少锁定对分区表写入的时间。在这种情况下，构建分区索引是仅元数据的操作。

Notes

参阅 Chapter 11 ，了解有关何时可以使用索引、何时不使用索引以及在哪些特定情况下它们会有用的信息。

目前，只有 B 树、GiST、GIN 和 BRIN 索引方法支持多键列索引。是否可以有多个键列与是否可以将 INCLUDE 列添加到索引中无关。索引最多可以有 32 列，包括 INCLUDE 列。（在构建 PostgreSQL 时可以更改此限制。）目前只有 B 树支持唯一索引。

可以为索引的每一列指定带有可选参数的 operator class 。运算符类标识用于索引针对该列的运算符。例如，基于四字节整数的 B 树索引将使用 int4_ops 类；此运算符类包括用于四字节整数的比较函数。在实践中，列的数据类型的默认运算符类通常是足够的。具有运算符类的主要目的在于，对于某些数据类型，可能存在多种有意义的排序顺序。例如，我们可能希望按绝对值或实部两种方式对复数数据类型进行排序。我们可以通过为数据类型定义两个运算符类，然后在创建索引时选择适当的类来做到这一点。关于运算符类的更多信息在 Section 11.10 和 Section 38.16 中。

当对分区表调用 CREATE INDEX 时，默认 behavior 是递归遍历所有分区以确保它们都具有匹配的索引。首先检查每个分区以确定是否已存在等效索引，如果存在，该索引将作为分区索引附加到正在创建的索引中，该索引将成为它的父索引。如果不存在匹配的索引，则将创建一个新索引并自动附加；每个分区中新索引的名称将被确定，就好像命令中未指定索引名称一样。如果指定了 ONLY 选项，则不会执行递归，并且索引会被标记为无效。（一旦所有分区获取匹配的索引， ALTER INDEX …​ ATTACH PARTITION 将标记索引为有效。）但是，请注意，无论是否指定了 ONLY ，使用 CREATE TABLE …​ PARTITION OF 创建的任何未来的分区都会自动拥有匹配的索引。

对于支持有序扫描的索引方法（目前仅限 B-tree），可以指定可选子句 ASC 、 DESC 、 NULLS FIRST 和/或 NULLS LAST 来修改索引的排序顺序。由于有序索引可以在向前或向后扫描，因此通常无须创建单列 DESC 索引——常规索引中已经提供了该排序顺序。这些选项的价值在于，可以创建匹配混合排序查询（例如 SELECT …​ ORDER BY x ASC, y DESC ）所请求的排序顺序的多列索引。如果您需要支持“nulls sort low”行为（而不是在依赖索引以避免排序步骤的查询中的默认“nulls sort high”）， NULLS 选项将非常有用。

系统会定期收集表所有列的统计信息。新创建的非表达式索引可以立即使用这些统计信息来确定索引的有效性。对于新的表达式索引，有必要运行 ANALYZE 或等待 autovacuum daemon 分析表以生成这些索引的统计信息。

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

PostgreSQL 可以在利用多颗 CPU 处理表行时构建索引，以更快地处理表行。此功能称为 parallel index build 。对于支持并行构建索引的索引方法（目前仅限 B-tree）， 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 将在所有情况下禁用表上的并行索引构建。

Tip

您可能希望在将 parallel_workers 设置为调整索引构建的一部分之后对其进行重置。这样做可以避免查询计划的无意更改，因为 parallel_workers 会影响 all 并行表扫描。

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

使用 DROP INDEX 删除索引。

与任何长期运行的事务一样，针对某个表的 CREATE INDEX 可能会影响在任何其他表上由并发 VACUUM 可以删除哪些元组。

PostgreSQL 的早期版本也有一个 R-tree 索引方法。该方法已被删除，因为它相对于 GiST 方法没有重大优势。如果指定了 USING rtree ， CREATE INDEX 会将其解释为 USING gist ，以简化将旧数据库转换为 GiST 的过程。

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

Examples

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

CREATE UNIQUE INDEX title_idx ON films (title);

在表 films 中的列 title 上创建具有已包含列 director 和 rating 的唯一 B-tree 索引：

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

创建禁用重复消除的 B-Tree 索引：

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

Compatibility

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

See Also

ALTER INDEX 、 DROP INDEX 、 REINDEX 、 Section 28.4.4