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 树、哈希、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指定对于唯一索引，是否应该将空值视为相异的（不等于）。默认值是它们是相异的，以便唯一索引可以在一列中包含多个空值。
storage_parameter特定于索引方法的存储参数的名称。有关详细信息，请参见下面的 索引储存参数。
tablespace_name创建索引的表空间。如果未指定，default_tablespace 将被使用或者 temp_tablespaces 将被用于临时表的索引。
谓词部分索引的约束表达式。
可选的 WITH 子句为索引指定存储参数。每个索引方法有其自身的允许存储参数集。B-tree、哈希、GiST 和 SP-GiST 索引方法都接受此参数
fillfactor (integer) #索引的填充因子是一个百分比，用于确定索引方法将尝试使索引页填充的程度。对于 B-tree，在初始索引构建期间以及在向右扩展索引（添加新的最大键值）时，叶页将填充到此百分比。如果页随后变得完全已满，它们将被拆分，从而导致磁盘索引结构碎片。B-tree 使用 90 的默认填充因子，但是可以选择 10 到 100 之间的任何整数值。
预测大量插入和/或更新的表上的 B-tree 索引可以在 CREATE INDEX 时间从较低填充因子设置中受益（在批量加载到表中之后）。50 - 90 范围内的值可以有效地“平滑”B-tree 索引早期阶段的页面拆分速率（降低填充因子，甚至可以降低页面拆分的绝对数量，尽管该效果高度依赖于工作负载）。第 64.1.4.2 节 中描述的自底向上 B-tree 索引删除技术依赖于页面上有一些“额外”空间来存储“额外”元组版本，因此会受到填充因子的影响（尽管影响通常并不显著）。
在其他特定情况下，将填充因子在 CREATE INDEX 时间增加到 100 可能是增加空间利用率的一种有用的方法。只有当您完全确定表是静态表（即它永远不会受到插入或更新的影响）时，才应考虑这一点。否则，填充因子设置为 100 有可能损害性能：即使是一些更新或插入也会导致页面拆分的突然激增。
其他索引方法以不同但大致类似的方式使用填充因子；默认填充因子在不同方法之间有所不同。
B 树索引还接受以下参数
deduplicate_items (boolean) #控制对 第 64.1.4.3 节 中描述的 B 树重复数据删除技术的用法。设置为 ON 或 OFF 以启用或禁用此优化。（如下面的 第 19.1 节 中所述，允许使用 ON 和 OFF 的其他拼写选项。）默认设置为 ON。
通过 ALTER INDEX 关闭 deduplicate_items 可以防止将来的插入触发重复数据删除，但并不能使得现有的发布列表元组使用标准元组表示法。
GiST 索引还接受以下参数
buffering (enum) #决定使用 第 64.2.4.1 节 中描述的缓冲构建技术生成索引。使用 OFF 时禁用缓冲，使用 ON 时启用缓冲，而使用 AUTO 时一开始会禁用缓冲，但当索引大小达到 effective_cache_size 后会动态启用缓冲。默认设置为 AUTO。请注意，如果可以排序生成，则会使用排序生成而不是缓冲生成，除非指定 buffering=ON。
GIN 索引接受不同的参数
fastupdate (boolean) #此设置控制对 第 64.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) #定义构成表的每个条目的一个块范围的表块数BRIN索引（有关详细信息，请参见 第 64.5.1 节）。默认值为 128。
autosummarize (boolean) #定义在下一个插入检测到时，是否为过去的一页范围排队摘要运行。有关详细信息，请参见 第 64.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 树、GiST、GIN 和 BRIN 索引方法支持多键列索引。是否可以有多个键列独立于是否可以将 INCLUDE 列添加到索引。索引最多可以有 32 列，包括 INCLUDE 列。（构建 PostgreSQL 时可以更改此限制。）当前只有 B 树支持唯一索引。
可以为索引的每一列指定带有可选参数的操作符类。操作符类标识索引用于该列的操作符。例如，四字节整数上的 B 树索引将使用 int4_ops 类；此操作符类包括对四字节整数的比较函数。实际上，列的数据类型的默认操作符类通常就足够了。具有操作符类的主要目的是，对于某些数据类型，可能存在多个有意义的排序。例如，我们可能希望按绝对值或按实部对复数数据类型进行排序。我们可以在创建索引时定义数据类型的两个操作符类，然后选择适当的类。有关操作符类的更多信息，请参见 第 11.10 节 和 第 36.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）请求的排序顺序匹配的多列索引。如果需要支持““null 值低排序””行为（而不是默认的““null 值高排序””），则 NULLS 选项非常有用，用于依赖索引来避免排序步骤的查询。
系统会定期收集表中所有列的统计信息。新创建的非表达式索引可以直接使用这些统计信息来确定索引的实用性。对于新的表达式索引，有必要运行 ANALYZE 或等待 自动清理守护进程 分析表以生成这些索引的统计信息。
在运行 CREATE INDEX 时，search_path 将暂时更改为 pg_catalog, pg_temp。
对于大多数索引方法，创建索引的速度取决于 maintenance_work_mem 的设置。较大的值将缩短创建索引所需的时间，只要不使其大于实际可用的内存量，这会使机器进入交换。
PostgreSQL 可以利用多个 CPU 来构建索引，以便更快地处理表行。此特性称为并行索引构建。对于支持并行构建索引的索引方法（目前只有 B-tree），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 视图中报告其进度。有关详细信息，请参见 第 27.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");
要创建具有非默认排序顺序的索引，将 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 标准中没有针对索引的规定。