Note

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 ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

Description

CREATE INDEX在指定关系的指定列上构造索引，该索引可以是表或实例化视图。索引主要用于增强数据库性能(尽管使用不当会导致性能降低)。

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

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

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

当存在WHERE子句时，将创建一个* partial index *。部分索引是仅包含表一部分的条目的索引，通常一部分索引比表的其余部分更有用。例如，如果您有一个既包含开票订单又包含未开票订单的表，其中未开票订单占总表的一小部分，但这是经常使用的部分，则可以通过仅在该部分上创建索引来提高性能。另一个可能的应用是将WHERE与UNIQUE结合使用，以对表的子集实施唯一性。有关更多讨论，请参见Section 11.8。

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

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

Parameters

• UNIQUE

  • 使系统在创建索引(如果已经存在数据)并且每次添加数据时检查表中是否存在重复值。尝试插入或更新可能导致重复 Importing 的数据将产生错误。

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

• CONCURRENTLY

  • 当使用该选项时，PostgreSQL 将构建索引而不会采取任何防止在表上进行并发插入，更新或删除的锁。而标准索引构建会锁定对表的写入(但不会读取)，直到完成为止。使用此选项时需要注意一些注意事项-参见同时构建索引。

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

• IF NOT EXISTS

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

• INCLUDE

  • 可选的INCLUDE子句指定列列表，这些列将作为非键列包含在索引中。非关键列不能在索引扫描搜索限定条件中使用，并且出于索引强制实施的任何唯一性或排除约束的目的而将其忽略。但是，仅索引扫描可以返回非键列的内容，而不必访问索引表，因为它们可以直接从索引条目获得。因此，添加非关键列可以将仅索引扫描用于原本无法使用它们的查询。

在向索引添加非关键列(尤其是宽列)时要保守一些是明智的。如果索引 Tuples 超出了索引类型允许的最大大小，则数据插入将失败。在任何情况下，非键列都会复制索引表中的数据，并且会使索引的大小膨胀，从而有可能减慢搜索速度。

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

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

当前，只有 B 树索引访问方法支持此功能。在 B 树索引中，INCLUDE子句中列出的列的值包含在与堆 Tuples 相对应的叶 Tuples 中，但不包含在用于树导航的上级索引条目中。

• name

  • 要创建的索引的名称。此处不能包含模式名称；索引始终与其父表在同一架构中创建。如果省略该名称，则 PostgreSQL 根据父表的名称和索引列的名称选择合适的名称。

• ONLY

  • 表示如果表已分区，则不递归在分区上创建索引。默认为递归。

• table_name

  • 要构建索引的表的名称(可能是架构限定的)。

• method

  • 要使用的索引方法的名称。选择是btree，hash，gist，spgist，gin和brin。默认方法是btree。

• column_name

  • 表的一列的名称。

• expression

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

• collation

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

• opclass

  • 运算符类的名称。有关详情，请参见下文。

• ASC

  • 指定升序排序(默认)。

• DESC

  • 指定降序排序。

• NULLS FIRST

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

• NULLS LAST

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

• storage_parameter

  • 特定于索引方法的存储参数的名称。有关详情，请参见索引存储参数。

• tablespace_name

  • 在其中创建索引的表空间。如果未指定，则查询default_tablespace或temp_tablespaces来查询临时表的索引。

• predicate

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

索引存储参数

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

• fillfactor

  • 索引的填充因子是一个百分比，它确定索引方法将尝试打包索引页面的程度。对于 B 树，在初始索引构建期间以及在向右扩展索引时(添加新的最大键值)，叶子页将填充到该百分比。如果页面随后完全变满，它们将被分割，导致索引效率逐渐下降。 B 树使用默认填充因子 90，但是可以选择 10 到 100 之间的任何整数值。如果表是静态的，则 fillfactor 100 最好最大程度地减小索引的物理大小，但是对于大量更新的表，较小的 fillfactor 更好地最小化对页面拆分的需求。其他索引方法以不同但大致相似的方式使用 fillfactor；默认的填充因子因方法而异。

B 树索引另外接受此参数：

• vacuum_cleanup_index_scale_factor

  • vacuum_cleanup_index_scale_factor的按索引值。

GiST 索引另外接受此参数：

• buffering

  • 确定是否使用Section 64.4.1中描述的缓冲构建技术来构建索引。使用OFF时，它被禁用；使用ON时，它被禁用；使用AUTO时，它最初被禁用，但是一旦索引大小达到effective_cache_size，则立即打开。默认值为AUTO。

GIN 索引接受不同的参数：

• fastupdate

  • 此设置控制Section 66.4.1中描述的快速更新技术的使用。它是一个布尔参数：ON启用快速更新，OFF禁用它。 (如Section 19.1中所述，允许使用ON和OFF的替代拼写。)默认值为ON。

• gin_pending_list_limit

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

BRIN 索引接受不同的参数：

• pages_per_range

  • 定义表块的数量，这些表块构成 BRIN 索引的每个条目的一个块范围(有关更多详细信息，请参见Section 67.1)。默认值为128。

• autosummarize

  • 定义每当在下一页范围中检测到插入时，是否为上一页范围调用摘要运行。

同时构建索引

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

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

在并发索引构建中，实际上是通过一个事务将索引 Importing 到系统目录中，然后在另外两个事务中进行两次表扫描。在每次表扫描之前，索引构建必须 await 修改该表的现有事务终止。在第二次扫描之后，索引构建必须 await 在第二次扫描之前具有快照(请参见Chapter 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重建索引.但是，由于REINDEX不支持并发构建，因此此选项似乎不太有吸引力.)

同时构建唯一索引时的另一个警告是，当第二次表扫描开始时，已经对其他事务强制执行了唯一性约束。这意味着可以在索引可用之前甚至在索引构建最终失败的情况下，在其他查询中报告违反约束的情况。同样，如果在第二次扫描中确实发生了故障，则“无效”索引之后将 continue 执行其唯一性约束。

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

常规索引构建允许在同一表上同时进行其他常规索引构建，但是一次只能在一个表上进行一个并发索引构建。无论哪种情况，在构建索引时都不允许对表进行模式修改。另一个区别是，常规的CREATE INDEX命令可以在事务块中执行，而CREATE INDEX CONCURRENTLY则不能。

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

Notes

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

当前，仅 B 树，GiST，GIN 和 BRIN 索引方法支持多列索引。默认情况下，最多可以指定 32 个字段。 (在构建 PostgreSQL 时可以更改此限制.)当前只有 B 树支持唯一索引。

可以为索引的每一列指定一个* operator class *。 operator 类标识该列的索引要使用的运算符。例如，在四字节整数上的 B 树索引将使用int4_ops类。此运算符类包含用于四字节整数的比较函数。实际上，列数据类型的默认运算符类通常就足够了。拥有运算符类的要点是，对于某些数据类型，可能会有不止一种有意义的排序。例如，我们可能想按绝对值或实数对复数数据类型进行排序。为此，我们可以为数据类型定义两个运算符类，然后在创建索引时选择适当的类。有关运算符类的更多信息，请参见Section 11.10和Section 38.15。

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

对于支持排序扫描的索引方法(当前仅支持 B 树)，可以指定可选子句ASC，DESC，NULLS FIRST和/或NULLS LAST来修改索引的排序 Sequences。由于可以向前或向后扫描有序索引，因此创建单列DESC索引通常没有用-常规索引已经可以使用排序 Sequences。这些选项的值是可以创建与混合排序查询所请求的排序 Sequences 匹配的多列索引，例如SELECT ... ORDER BY x ASC, y DESC。如果您需要在依赖索引以避免排序步骤的查询中支持“空排序低”行为，而不是默认的“高排序空”行为，则NULLS选项很有用。

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

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

并行索引构建可能会受益于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影响请求的并行 Worker 数。通过ALTER TABLE将parallel_workers设置为 0 将在所有情况下禁用表上的并行索引构建。

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

使用DROP INDEX删除索引。

先前的 PostgreSQL 版本也有 R 树索引方法。该方法已被删除，因为它与 GiST 方法相比没有明显的优势。如果指定了USING rtree，则CREATE INDEX会将其解释为USING gist，以简化从旧数据库到 GiST 的转换。

Examples

要在表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);

要在表达式lower(title)上创建索引，允许有效的不区分大小写的搜索：

CREATE INDEX ON films ((lower(title)));

(在此示例中，我们选择了省略索引名称，因此系统将选择一个名称，通常为films_lower_idx.)

要使用非默认排序规则创建索引：

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

要创建具有非默认 null 排序 Sequences 的索引：

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

Compatibility

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

See Also

ALTER INDEX, DROP INDEX