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 结合使用，以强制实现表的一个子集上的唯一性。更多讨论请参见 Section 11.8。
WHERE 子句中使用的表达式只能引用底层表的列，但它可以使用所有列，而不仅仅是被索引的那些列。目前，WHERE 中还禁止使用子查询和聚集表达式。同样的限制也适用于作为表达式的索引字段。
索引定义中使用的所有函数和操作符都必须是“不可变”的，也就是说，它们的结果必须只依赖于它们的参数，而绝不依赖于任何外部影响（例如另一个表的内容或当前时间）。这一限制确保了索引的行为是良定义的。要在索引表达式或 WHERE 子句中使用用户定义的函数，请记住在创建该函数时将其标记为不可变。
UNIQUE使系统在创建索引时（如果数据已经存在）以及每次添加数据时检查表中是否有重复值。插入或更新会导致重复条目的数据的尝试将产生一个错误。
当唯一索引用于分区表时还有额外的限制；参见 CREATE TABLE。
CONCURRENTLY使用该选项时，PostgreSQL 将在不采取任何阻止并发插入、更新或删除表的锁的情况下构建索引；而标准的索引构建在完成之前会锁定表上的写入（但不锁定读取）。使用该选项时有几点注意事项 — 参见下文的 Building Indexes Concurrently。
对于临时表，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 指定空值排在非空值之前。当指定了 DESC 时这是默认行为。
NULLS LAST 指定空值排在非空值之后。当未指定 DESC 时这是默认行为。
NULLS DISTINCTNULLS NOT DISTINCT指定对于唯一索引，空值是否应被视为互不相同（不相等）。默认情况下它们被视为互不相同，因此唯一索引可以在一个列中包含多个空值。
storage_parameter一个索引方法特有的存储参数的名称。详情请参见下文的 Index Storage Parameters。
tablespace_name在其中创建索引的表空间。如果未指定，则会查阅 default_tablespace，对于临时表上的索引则会查阅 temp_tablespaces。
predicate部分索引的约束表达式。
可选的 WITH 子句为索引指定存储参数。每种索引方法都有自己的一组允许的存储参数。
B-tree、hash、GiST 和 SP-GiST 索引方法都接受这个参数：
fillfactor (integer)
#控制索引方法将尝试把索引页填满到什么程度。对于 B-tree，在初始索引构建期间以及在向右扩展索引（添加新的最大键值）时，叶子页会被填充到这个百分比。如果页随后变得完全填满，它们将被分裂，导致磁盘上索引结构的碎片化。B-tree 使用默认的 fillfactor 90，但可以选择 10 到 100 之间的任何整数值。
对于预计会有大量插入和/或更新的表上的 B-tree 索引，在 CREATE INDEX 时（在向表中批量加载之后）采用较低的 fillfactor 设置是有益的。50 - 90 范围内的值可以有效地“平滑”B-tree 索引生命周期早期页分裂的速率（这样降低 fillfactor 甚至可能降低页分裂的绝对数量，不过这种效果高度依赖于工作负载）。Section 65.1.4.2 中描述的 B-tree 自底向上索引删除技术依赖于页上有一些“额外”的空间来存储“额外的”元组版本，因此可能受 fillfactor 的影响（尽管这种影响通常不显著）。
在其他特定情况下，在 CREATE INDEX 时把 fillfactor 提高到 100 可能有效，这是一种最大化空间利用率的方式。只有当你完全确定该表是静态的（即它永远不会受插入或更新影响）时，才应该考虑这样做。否则，将 fillfactor 设置为 100 会有损害性能的风险：即使是少量的更新或插入也会导致页分裂的突然激增。
其他索引方法以不同但大致类似的方式使用 fillfactor；默认的 fillfactor 因方法而异。
B-tree 索引还额外接受这个参数：
deduplicate_items (boolean)
# 控制是否使用 Section 65.1.4.3 中描述的 B-tree 去重技术。设置为 ON 或 OFF 可以启用或禁用该优化。（ON 和 OFF 的其他写法是允许的，如 Section 19.1 中所述。）默认值为 ON。
把 deduplicate_items 通过 ALTER INDEX 关闭可以防止未来的插入触发去重，但它本身不会让现有的 posting list 元组改用标准的元组表示。
GiST 索引还额外接受这个参数：
buffering (enum)
# 控制是否使用 Section 65.2.4.1 中描述的缓冲构建技术来构建索引。OFF 表示禁用缓冲，ON 表示启用，AUTO 表示最初禁用，但一旦索引大小达到 effective_cache_size 就会即时开启。默认值为 AUTO。注意，如果可以进行排序构建，除非指定了 buffering=ON，否则将使用排序构建而不是缓冲构建。
GIN 索引接受这些参数：
fastupdate (boolean)
# 控制是否使用 Section 65.4.4.1 中描述的快速更新技术。ON 启用快速更新，OFF 禁用它。默认值为 ON。
把 fastupdate 通过 ALTER INDEX 关闭可以防止未来的插入进入待处理索引条目列表，但它本身不会刷新现有的条目。之后你可能希望对该表执行 VACUUM 或调用 gin_clean_pending_list 函数，以确保待处理列表被清空。
gin_pending_list_limit (integer)
#为该索引覆盖 gin_pending_list_limit 的全局设置。该值以千字节为单位指定。
BRIN 索引接受这些参数：
pages_per_range (integer)
# 为 BRIN 索引的每个条目定义组成一个块区间的表块数量（更多细节参见 Section 65.5.1）。默认值为 128。
autosummarize (boolean)
# 定义每当在下一个页面区间上检测到插入时，是否将针对上一个页面区间的汇总运行排入队列（更多细节参见 Section 65.5.1.1）。默认值为 off。
创建索引可能会干扰数据库的常规操作。通常，PostgreSQL 会锁定要被索引的表禁止写入，并通过一次表扫描完成整个索引构建。其他事务仍然可以读取该表，但如果它们尝试在表中插入、更新或删除行，它们将被阻塞，直到索引构建完成为止。如果系统是一个正在运行的生产数据库，这可能会产生严重影响。非常大的表可能需要许多小时才能完成索引，即使是较小的表，索引构建也可能在对于生产系统来说不可接受的长时间内阻止写入者。
PostgreSQL 支持在不锁定写入的情况下构建索引。通过指定 CONCURRENTLY（CREATE INDEX 的一个选项）可以调用这一方法。使用该选项时，PostgreSQL 必须对表执行两次扫描，此外还必须等待所有可能修改或使用该索引的现有事务终止。因此，这种方法比标准索引构建需要更多的总工作量，完成所需的时间也显著更长。不过，由于它允许在构建索引期间继续正常操作，这种方法对于在生产环境中添加新索引非常有用。当然，索引创建带来的额外 CPU 和 I/O 负载可能会减慢其他操作。
在并发索引构建中，该索引实际上会在一个事务中作为一个“无效”索引被输入到系统目录中，然后在另外两个事务中进行两次表扫描。在每次表扫描之前，索引构建必须等待已修改该表的现有事务终止。在第二次扫描之后，如果所涉及的索引是部分索引或者具有不是简单列引用的列，索引构建必须等待任何拥有早于第二次扫描的快照（参见 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 INDEX CONCURRENTLY 重建该索引。）
并发构建唯一索引时的另一个注意事项是，当第二次表扫描开始时，唯一性约束已经在针对其他事务实施了。这意味着在该索引变得可用之前，甚至在索引构建最终失败的情况下，其他查询中也可能报告约束冲突。此外，如果第二次扫描确实发生了失败，该“无效”索引之后仍会继续实施其唯一性约束。
支持并发构建表达式索引和部分索引。在求值这些表达式时发生的错误可能导致与上文针对唯一性冲突所描述的行为类似的行为。
常规索引构建允许在同一个表上同时进行其他常规索引构建，但一个表上一次只能进行一个并发索引构建。在这两种情况下，索引构建期间都不允许对表进行模式修改。另一个区别是，常规的 CREATE INDEX 命令可以在事务块内执行，但 CREATE INDEX CONCURRENTLY 不能。
目前不支持为分区表上的索引进行并发构建。不过，你可以分别在各个分区上并发构建索引，最后再以非并发方式创建分区索引，以缩短分区表的写入被锁定的时间。在这种情况下，构建分区索引只是一个仅涉及元数据的操作。
有关何时可以使用索引、何时不使用索引以及索引在哪些特定情况下有用，请参见 Chapter 11。
目前，只有 B-tree、GiST、GIN 和 BRIN 索引方法支持多键列索引。是否可以有多个键列与是否可以向索引添加 INCLUDE 包含列无关。索引最多可以有 32 个列，包括 INCLUDE 包含列在内。（这一限制可以在构建 PostgreSQL 时更改。）目前只有 B-tree 支持唯一索引。
可以为索引的每一列指定一个带有可选参数的操作符类。操作符类确定索引对该列将使用的操作符。例如，四字节整数上的 B-tree 索引会使用 int4_ops 类；这个操作符类包含针对四字节整数的比较函数。实践中，该列数据类型的默认操作符类通常就足够了。拥有操作符类的要点在于，对某些数据类型来说，可能存在不止一种有意义的排序方式。例如，我们可能希望按绝对值或按实部对复数数据类型排序。我们可以通过为该数据类型定义两个操作符类，然后在创建索引时选择合适的类来做到这一点。关于操作符类的更多信息见 Section 11.10 和 Section 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 选项在你需要支持“空值排在低位”行为而不是默认的“空值排在高位”行为时很有用。
系统会定期收集表中所有列的统计信息。新建的非表达式索引可以立即使用这些统计信息来判断索引的有用程度。对于新的表达式索引，需要运行 ANALYZE 或等待 autovacuum daemon 分析该表，以便为这些索引生成统计信息。
在 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 可能会影响请求的工作进程数量，因为并行工作进程必须至少拥有 32MB 的总 maintenance_work_mem 预算份额。还必须为领导进程保留 32MB 的份额。增加 max_parallel_maintenance_workers 可能允许使用更多的工作进程，这将减少创建索引所需的时间，前提是索引构建尚未受限于 I/O。当然，还应当有原本会闲置的充足 CPU 容量。
为 parallel_workers 设置一个值（通过 ALTER TABLE）可以直接控制针对该表的 CREATE INDEX 将请求多少个并行工作进程。这会完全绕过代价模型，并防止 maintenance_work_mem 影响请求的并行工作进程数量。将 parallel_workers 通过 ALTER TABLE 设置为 0 将在所有情况下禁用该表上的并行索引构建。
作为索引构建调优的一部分，在设置 parallel_workers 之后你可能希望将其重置。这可以避免对查询计划造成无意间的更改，因为 parallel_workers 会影响所有并行表扫描。
虽然 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 27.4.4。
在列 title（属于表 films）上创建一个唯一的 B-tree 索引：
CREATE UNIQUE INDEX title_idx ON films (title);
在列 title 上创建一个带有包含列 director 和 rating 的唯一 B-tree 索引，该索引属于表 films：
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);
在列 code 上创建一个索引，该列属于表 films，并让该索引位于表空间 indexspace 中：
CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;
在一个 point 属性上创建 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);
CREATE INDEX 是一个 PostgreSQL 语言扩展。SQL 标准中没有关于索引的规定。