CREATE INDEX — 定义一个新索引
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ]name] ON [ ONLY ]table_name[ USINGmethod] ( {column_name| (expression) } [ COLLATEcollation] [opclass[ (opclass_parameter=value[, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE (column_name[, ...] ) ] [ NULLS [ NOT ] DISTINCT ] [ WITH (storage_parameter[=value] [, ... ] ) ] [ TABLESPACEtablespace_name] [ WHEREpredicate]
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 子句中使用用户定义的函数，请记住在创建函数时将其标记为不可变。
UNIQUE当创建索引时（如果已有数据）以及每次添加数据时，都会导致系统检查表中是否存在重复值。尝试插入或更新会导致重复条目的数据将生成错误。
当唯一索引应用于分区表时，还有其他限制；请参阅 CREATE TABLE。
CONCURRENTLY使用此选项时，PostgreSQL 将在不获取任何阻止表上并发插入、更新或删除的锁的情况下构建索引；而标准的索引构建会锁定表直到完成（但允许读取）。使用此选项时有几个需要注意的注意事项——请参阅下面的 并发构建索引。
对于临时表，CREATE INDEX 始终是非并发的，因为没有其他会话可以访问它们，并且非并发索引创建成本更低。
IF NOT EXISTS如果同名的关联已存在，则不报错。在这种情况下会发出通知。请注意，不能保证现有索引与将要创建的索引有任何相似之处。当指定 IF NOT EXISTS 时，需要索引名。
INCLUDE可选的 INCLUDE 子句指定了一个列列表，这些列将被包含在索引中作为 非键 列。非键列不能用于索引扫描的查询条件，并且对于索引强制执行的任何唯一性或排除约束，它都会被忽略。但是，仅索引扫描可以在不访问索引表的情况下返回非键列的内容，因为它们可以直接从索引条目中获取。因此，添加非键列可以使本来无法使用索引的查询能够使用仅索引扫描。
在向索引添加非键列（尤其是宽列）时，应谨慎。如果索引元组超过了索引类型允许的最大大小，数据插入将失败。无论如何，非键列会复制索引表中的数据并导致索引大小膨胀，从而可能减慢搜索速度。此外，B-tree 的重复数据删除功能从未使用过带有非键列的索引。
INCLUDE 子句中列出的列不需要相应的操作符类；该子句可以包含数据类型没有为给定访问方法定义操作符类的列。
由于表达式不能用于仅索引扫描，因此它们不支持作为包含的列。
目前，B-tree、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指定降序。
NULLS FIRST指定 NULL 值排在非 NULL 值之前。当指定 DESC 时，这是默认值。
NULLS LAST指定 NULL 值排在非 NULL 值之后。当未指定 DESC 时，这是默认值。
NULLS DISTINCTNULLS NOT DISTINCT指定对于唯一索引，NULL 值是否被视为不同（不相等）。默认情况下，它们是不同的，因此唯一索引可以在列中包含多个 NULL 值。
storage_parameter特定于索引方法的存储参数的名称。有关详细信息，请参阅下面的 索引存储参数。
tablespace_name用于创建索引的表空间。如果未指定，则会查询 default_tablespace，对于临时表的索引，则查询 temp_tablespaces。
predicate部分索引的约束表达式。
可选的 WITH 子句指定了索引的 存储参数。每个索引方法都有自己的一组允许的存储参数。
B-tree、hash、GiST 和 SP-GiST 索引方法都接受此参数
fillfactor (integer) #控制索引方法在填充索引页时尝试填充的程度。对于 B-trees，叶子页在初始索引构建期间以及在右侧扩展索引（添加新的最大键值）时会填充到此百分比。如果页面随后完全填满，它们将被分割，导致磁盘上的索引结构碎片化。B-trees 使用默认的 fillfactor 90，但可以选择 10 到 100 之间的任何整数值。
对于预计会有大量插入和/或更新的表上的 B-tree 索引，在 CREATE INDEX 时（在将数据批量加载到表中后）使用较低的 fillfactor 设置可能是有益的。50 - 90 的值可以在 B-tree 索引早期生命周期中有效地“平滑”页面分割的 “速率”（较低的 fillfactor 甚至可能降低页面分割的绝对数量，尽管这种效果高度依赖于工作负载）。B-tree 自底向上删除技术（在 第 65.1.4.2 节 中描述）依赖于在页面上具有一些 “额外” 空间来存储 “额外” 的元组版本，因此可能会受到 fillfactor 的影响（尽管影响通常不显著）。
在其他特定情况下，可以在 CREATE INDEX 时将 fillfactor 提高到 100，以最大化空间利用率。只有当您完全确定表是静态的（即，它永远不会受到插入或更新的影响）时，才应考虑这一点。否则，100 的 fillfactor 设置可能会 损害 性能：即使是少量更新或插入也会导致页面分割突然激增。
其他索引方法使用 fillfactor 的方式不同但大致相似；默认 fillfactor 在不同方法之间有所不同。
B-tree 索引另外接受此参数
deduplicate_items (boolean) #控制 第 65.1.4.3 节 中描述的 B-tree 去重技术的用法。设置为 ON 或 OFF 来启用或禁用优化。（如 第 19.1 节 中所述，允许使用 ON 和 OFF 的替代拼写）。默认值为 ON。
通过 ALTER INDEX 关闭 deduplicate_items 可防止未来的插入触发去重，但本身并不会使现有的发布列表元组使用标准的元组表示。
GiST 索引另外接受此参数
buffering (enum) #控制是否使用 第 65.2.4.1 节 中描述的缓冲构建技术来构建索引。设置为 OFF 时禁用缓冲，设置为 ON 时启用缓冲，设置为 AUTO 时初始禁用缓冲，但一旦索引大小达到 effective_cache_size 就会动态启用。默认值为 AUTO。请注意，如果可能使用排序构建，它将代替缓冲构建，除非指定了 buffering=ON。
GIN 索引接受这些参数
fastupdate (boolean) #控制 第 65.4.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) #定义构成一个索引条目的一个块范围的表块数（有关更多详细信息，请参阅 第 65.5.1 节）。默认值为 128。BRIN索引（参见 第 65.5.1 节）的每个条目所构成的块范围的数量。
autosummarize (boolean) #定义当检测到下一个页面范围发生插入时，是否为前一个页面范围排队一个摘要运行（有关更多详细信息，请参阅 第 65.5.1.1 节）。默认值为 off。
创建索引可能会干扰数据库的常规操作。通常，PostgreSQL 会锁定要索引的表以防止写入，并通过一次扫描表来完成整个索引构建。其他事务仍然可以读取表，但如果它们尝试插入、更新或删除表中的行，它们将被阻塞直到索引构建完成。这可能会对实时生产数据库产生严重影响。非常大的表可能需要数小时才能建立索引，即使对于较小的表，索引构建也可能在对生产系统来说过长的时间内阻止写入者。这可能会对生产系统产生严重影响。
PostgreSQL 支持在不阻止写入的情况下构建索引。通过指定 CREATE INDEX 的 CONCURRENTLY 选项来调用此方法。使用此选项时，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-tree、GiST、GIN 和 BRIN 索引方法支持多键列索引。是否存在多个键列独立于是否可以将 INCLUDE 列添加到索引中。索引最多可以有 32 列，包括 INCLUDE 列。（构建 PostgreSQL 时可以更改此限制）。目前只有 B-tree 支持唯一索引。
每个索引列都可以指定一个可选参数的操作符类。操作符类标识索引将为该列使用的操作符。例如，一个四字节整数上的 B-tree 索引将使用 int4_ops 类；这个操作符类包括四字节整数的比较函数。实际上，数据类型的默认操作符类通常就足够了。拥有操作符类的主要好处是，对于某些数据类型，可能存在多个有意义的排序。例如，我们可能想按绝对值或实部对复数数据类型进行排序。我们可以通过为该数据类型定义两个操作符类，然后在创建索引时选择正确的类来实现。有关操作符类的更多信息，请参阅 第 11.10 节 和 第 36.16 节。
当在分区表上调用 CREATE INDEX 时，默认行为是递归到所有分区以确保它们都具有匹配的索引。首先检查每个分区以确定是否已存在等效索引，如果存在，该索引将被附加为创建索引的分区索引，后者将成为其父索引。如果不存在匹配的索引，将创建一个新索引并自动附加；每个分区中新索引的名称将根据命令中是否指定了索引名称来确定。如果指定了 ONLY 选项，则不进行递归，并且索引将被标记为无效。（ALTER INDEX ... ATTACH PARTITION 在所有分区都获得匹配索引后，会将索引标记为有效。）但请注意，使用 CREATE TABLE ... PARTITION OF 创建的任何分区都将自动获得匹配的索引，无论是否指定了 ONLY。
对于支持有序扫描的索引方法（目前只有 B-tree），可以指定可选子句 ASC、DESC、NULLS FIRST 和/或 NULLS LAST 来修改索引的排序顺序。由于有序索引可以向前或向后扫描，因此通常没有必要创建一个单列 DESC 索引——这种排序顺序已经可以通过常规索引获得。这些选项的价值在于可以创建多列索引，以匹配混合排序查询所需的排序顺序，例如 SELECT ... ORDER BY x ASC, y DESC。NULLS 选项在您需要支持查询（依赖索引避免排序步骤）的 “nulls sort low” 行为，而不是默认的 “nulls sort high” 时很有用。
系统定期收集表中所有列的统计信息。新创建的非表达式索引可以立即使用这些统计信息来确定索引的有用性。对于新的表达式索引，需要运行 ANALYZE 或等待 autovacuum 守护进程 分析表以生成这些索引的统计信息。
在 CREATE INDEX 运行时，search_path 会临时更改为 pg_catalog, pg_temp。
对于大多数索引方法，创建索引的速度取决于 maintenance_work_mem 的设置。较大的值将减少索引创建所需的时间，只要您不将其设置得大于实际可用的内存量，否则会导致机器发生交换。
PostgreSQL 可以利用多个 CPU 来构建索引，以更快地处理表行。此功能称为 并行索引构建。对于支持并行构建索引的索引方法（目前是 B-tree、GIN 和 BRIN），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 将在所有情况下禁用该表的并行索引构建。
您可能希望在设置 parallel_workers 后将其重置，作为调整索引构建的一部分。这可以避免对查询计划造成意外更改，因为 parallel_workers 会影响 所有 并行表扫描。
虽然带 CONCURRENTLY 选项的 CREATE INDEX 支持并行构建而没有特殊限制，但只有第一次表扫描实际上是并行执行的。
使用 DROP INDEX 删除索引。
与任何长时间运行的事务一样，在表上运行 CREATE INDEX 可能会影响并发 VACUUM 在任何其他表上可以删除哪些元组。
PostgreSQL 的早期版本还有一个 R-tree 索引方法。此方法已被删除，因为它与 GiST 方法相比没有显著优势。如果指定 USING rtree，CREATE INDEX 将将其解释为 USING gist，以简化旧数据库到 GiST 的转换。
每个运行 CREATE INDEX 的后端都会在其 pg_stat_progress_create_index 视图中报告其进度。有关详细信息，请参阅 第 27.4.4 节。
要在表 films 的 title 列上创建唯一的 B-tree 索引
CREATE UNIQUE INDEX title_idx ON films (title);
要在表 films 的 title 列上创建唯一的 B-tree 索引，并包含 director 和 rating 列
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");
使用非默认 NULL 值排序顺序创建索引
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 标准中没有为索引提供任何规定。