PostgreSQL 教程

Appearance

CREATE INDEX ​

CREATE INDEX — 定义新索引

语法 ​

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 ]

说明 ​

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

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

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

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

当WHERE子句存在时，将创建一个部分索引。部分索引是一个索引，其中仅包含表中一部分的条目，通常是比表中其他部分更适合于索引的部分。例如，如果你有一个包含已开具发票和未开具发票的订单的表，其中未开具发票的订单只占总表的很少一部分，但这是一个经常使用的部分，你可以通过仅对此部分创建索引来提高性能。另一个可能的应用程序是将WHERE与UNIQUE结合使用，以强制对表的子集进行唯一性。有关更多讨论，请参见第 11.8 节。

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

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

参数 ​

UNIQUE

在创建索引时（如果数据已经存在）和每次添加数据时，导致系统检查表中是否存在重复值。尝试插入或更新会导致重复条目的数据将生成错误。

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

CONCURRENTLY

当使用此选项时，PostgreSQL 将构建索引，而不会获取任何阻止对表进行并发插入、更新或删除的锁；而标准索引构建会锁定表上的写入（但不读取），直到完成。使用此选项时需要注意一些注意事项——请参见下面的并发构建索引。

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

IF NOT EXISTS

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

包含

可选的 INCLUDE 子句指定将作为 非键 列包含在索引中的列列表。非键列不能用于索引扫描搜索限定条件中，并且在索引强制执行的任何唯一性或排除约束方面被忽略。但是，仅索引扫描可以返回非键列的内容，而无需访问索引的表，因为它们直接从索引条目中获取。因此，添加非键列允许对原本无法使用它们进行查询使用仅索引扫描。

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

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

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

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

名称

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

仅

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

表名

要编制索引的表（可能符合模式限定）的名称。

方法

要使用的索引方法的名称。选项为 btree、hash、gist、spgist、gin、brin 或用户安装的访问方法，如 bloom。默认方法为 btree。

column_name

表中一列的名称。

expression

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

collation

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

opclass

运算符类的名称。有关详细信息，请参见下文。

opclass_parameter

运算符类参数的名称。有关详细信息，请参见下文。

ASC

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

DESC

指定降序排列顺序。

NULLS FIRST

指定 NULL 排在非 NULL 之前。在指定 DESC 时，这是默认设置。

NULLS LAST

指定 NULL 排在非 NULL 之后。在未指定 DESC 时，这是默认设置。

NULLS DISTINCT
NULLS NOT DISTINCT

指定对于唯一索引，是否应将 NULL 值视为不同（不等于）。默认情况下，它们是不同的，因此唯一索引可以在一列中包含多个 NULL 值。

storage_parameter

特定于索引方法的存储参数的名称。有关详细信息，请参见下文的 索引存储参数。

tablespace_name

创建索引的表空间。如果未指定，则会查询 default_tablespace，或者对于临时表上的索引，则会查询 temp_tablespaces。

predicate

部分索引的约束表达式。

索引存储参数 ​

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

fillfactor (integer) #

索引的填充因子是一个百分比，它决定了索引方法将尝试填充索引页面的程度。对于 B 树，在初始索引构建期间以及在右侧扩展索引（添加新的最大键值）时，叶页面将填充到此百分比。如果页面随后变得完全已满，它们将被拆分，导致磁盘索引结构的碎片化。B 树使用 90 的默认填充因子，但可以选择 10 到 100 之间的任何整数值。

在预期有许多插入和/或更新的表上的 B 树索引可以在 CREATE INDEX 时间（在批量加载到表中之后）受益于较低的填充因子设置。50 - 90 范围内的值可以有效地““平滑”” B 树索引早期生命中的页面拆分率（像这样降低填充因子甚至可以降低页面拆分的绝对数量，尽管这种效果高度依赖于工作负载）。第 67.4.2 节中描述的 B 树自底向上索引删除技术依赖于页面上有一些““额外””空间来存储““额外””元组版本，因此可能会受到填充因子的影响（尽管影响通常不显着）。

在其他特定情况下，在 CREATE INDEX 时间将填充因子增加到 100 可能有助于最大化空间利用率。只有在您完全确定表是静态的（即它永远不会受到插入或更新的影响）时，才应考虑这一点。填充因子设置为 100 否则会损害性能：即使是少数更新或插入也会导致页面拆分的突然激增。

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

B 树索引还接受此参数

deduplicate_items (boolean) #

控制在 第 67.4.3 节中描述的 B 树重复数据删除技术的用法。设置为 ON 或 OFF 以启用或禁用优化。（ON 和 OFF 的替代拼写形式允许，如 第 20.1 节中所述。）默认值为 ON。

注意

通过 ALTER INDEX 关闭 deduplicate_items 可防止将来的插入触发重复数据删除，但本身并不会使现有的发布列表元组使用标准元组表示法。

GiST 索引还接受此参数

buffering (enum) #

确定是否使用 第 68.4.1 节 中描述的缓冲构建技术构建索引。使用 OFF 禁用缓冲，使用 ON 启用缓冲，使用 AUTO 初始禁用缓冲，但一旦索引大小达到 effective_cache_size，就会动态启用缓冲。默认值为 AUTO。请注意，如果可以进行排序构建，则将使用排序构建来代替缓冲构建，除非指定 buffering=ON。

GIN 索引接受不同的参数

fastupdate (boolean) #

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

注意

通过 ALTER INDEX 关闭 fastupdate 可防止将来的插入进入待处理索引项列表，但本身并不会刷新先前的项。您可能希望之后 VACUUM 表或调用 gin_clean_pending_list 函数以确保清空待处理列表。

gin_pending_list_limit (integer) #

自定义 gin_pending_list_limit 参数。此值以千字节为单位指定。

BRIN索引接受不同的参数

pages_per_range (integer) #

定义构成 索引的每个项的一个块范围的表块数（有关更多详细信息，请参阅 第 71.1 节）。默认值为 128。

autosummarize (boolean) #

定义是否在下一个页面范围上检测到插入时为上一个页面范围排队一个汇总运行。有关更多详细信息，请参阅 第 71.1.1 节。默认值为 off。

并发构建索引 ​

创建索引可能会干扰数据库的正常运行。通常情况下，PostgreSQL会锁定要编入索引的表以防止写入，并通过对表进行单次扫描来执行整个索引构建。其他事务仍然可以读取该表，但如果它们尝试在表中插入、更新或删除行，则它们将阻塞，直到索引构建完成。如果系统是一个实时生产数据库，这可能会产生严重影响。非常大的表可能需要花费很多小时才能编入索引，即使对于较小的表，索引构建也可能将写入程序锁定在生产系统无法接受的较长时间内。

PostgreSQL支持在不锁定写入的情况下构建索引。此方法通过指定CONCURRENTLY选项的CREATE INDEX来调用。当使用此选项时，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 INDEX CONCURRENTLY重新构建索引）。

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

支持表达式索引和部分索引的并发构建。在这些表达式的求值中发生的错误可能会导致类似于上面针对唯一约束冲突所述的行为。

常规索引构建允许同时在同一张表上执行其他常规索引构建，但一次只能在一张表上执行一个并发索引构建。在任何情况下，在构建索引时都不允许对表进行架构修改。另一个区别是可以在事务块内执行常规CREATE INDEX命令，但不能执行CREATE INDEX CONCURRENTLY命令。

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

备注 ​

有关何时可以使用索引、何时不使用索引以及在哪些特定情况下它们可能是有用的信息，请参见第 11 章。

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

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

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

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

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

对于大多数索引方法，创建索引的速度取决于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 将在所有情况下禁用表上的并行索引构建。

提示

在将parallel_workers作为索引构建调整的一部分进行设置后，你可能希望重置它。这避免了对查询计划的无意更改，因为parallel_workers影响所有并行表扫描。

虽然带有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视图中报告其进度。有关详细信息，请参见第 28.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 标准中没有索引的规定。

另请参见 ​

ALTER INDEX，DROP INDEX，REINDEX，第 28.4.4 节