CREATE INDEX — 定义一个新索引
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ]name] ON [ ONLY ]table_name[ USINGmethod] ( {column_name| (expression) } [ COLLATEcollation] [opclass] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE (column_name[, ...] ) ] [ 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结合使用， 以便在表的一个子集上强制唯一性。更多讨论请见 Section 11.8。
WHERE子句中使用的表达式只能引用底层表的列，但 它可以使用所有列，而不仅仅是被索引的列。当前， WHERE中也禁止使用子查询和聚合表达式。同样的 限制也适用于作为表达式的索引字段。
所有在索引定义中使用的函数和操作符都必须是“不可变的”， 也就是说，它们的结果只能依赖其参数，而不能受任何外部因素影响 （例如另一个表的内容或当前时间）。这种限制确保索引的行为定义明确。 要在索引表达式或WHERE子句中使用用户定义的函数， 记得在创建该函数时将其标记为不可变。
UNIQUE使系统在创建索引时（如果数据已经存在）以及每次添加数据时， 检查表中的重复值。任何会导致重复项的插入或更新操作都会报错。
当唯一索引用于分区表时，还会有额外限制；参见 CREATE TABLE。
CONCURRENTLY使用此选项时，PostgreSQL将在构建索引时不获取任何会阻止对表进行并发插入、更新或删除的锁；而标准索引构建会阻塞对表的写入（但不会阻塞读取），直到构建完成。使用此选项时有几个注意事项需要了解—请参见并发构建索引。
对于临时表，CREATE INDEX始终是非并发的， 因为没有其他会话可以访问它们，而且非并发创建索引的成本更低。
IF NOT EXISTS如果已存在同名关系，则不要抛出错误。此时会发出通知。请注意，无法保证现有索引与将要创建的索引有任何相似之处。指定IF NOT EXISTS时必须提供索引名称。
INCLUDE可选的INCLUDE子句指定要作为非键列包含在索引中的列列表。非键列不能用于索引扫描的搜索限定条件，索引强制执行的任何唯一性约束或排他约束也会忽略这些列。不过，仅索引扫描可以直接从索引条目取得非键列的内容，无需访问索引所对应的表。因此，添加非键列可以让原本无法使用仅索引扫描的查询使用这种扫描。
向索引添加非键列时，谨慎一些是明智的，尤其是宽列。如果索引元组超过该索引类型允许的最大大小，数据插入将失败。无论如何，非键列会复制索引所对应的表中的数据并使索引膨胀，从而可能降低搜索速度。
INCLUDE子句中列出的列不需要适用的操作符类；对于给定访问方法，如果某些列的数据类型没有定义操作符类，该子句仍可包含这些列。
表达式不支持作为包含列，因为它们不能用于仅索引扫描。
目前，B-树 和 GiST 索引访问方法支持此功能。在 B-树 和 GiST 索引中，INCLUDE子句中列出的列值会包含在对应堆元组的叶元组中，但不会包含在用于树导航的上层索引条目中。
name要创建的索引名称。此处不能包含模式名称；索引始终创建在其父表所在的同一模式中。如果省略名称，PostgreSQL会根据父表名称和被索引的列名选择合适的名称。
ONLY如果表已分区，则表示创建索引时不要递归到分区。默认情况下会递归。
table_name要建立索引的表名称（可能包含模式限定）。
method要使用的索引方法名称。可选项包括btree、hash、gist、spgist、gin、brin，以及用户安装的访问方法（例如bloom）。默认方法是btree。
column_name表的列名称。
expression基于表中一个或多个列的表达式。通常必须按照语法所示，在表达式两侧加上括号。不过，如果表达式采用函数调用的形式，则可以省略括号。
collation用于索引的排序规则名称。默认情况下，索引使用要建立索引的列所声明的排序规则，或要建立索引的表达式的结果排序规则。具有非默认排序规则的索引对于涉及使用非默认排序规则的表达式的查询可能很有用。
opclass操作符类的名称。详细信息见下文。
ASC指定升序排序（这是默认值）。
DESC指定降序排序。
NULLS FIRST指定将空值排在非空值之前。当指定DESC时，这是默认值。
NULLS LAST指定将空值排在非空值之后。当未指定DESC时，这是默认值。
storage_parameter索引方法专用存储参数的名称。有关详细信息，请参见索引存储参数。
tablespace_name要创建索引的表空间。如果未指定，则查询default_tablespace；对于临时表上的索引，则查询temp_tablespaces。
predicate部分索引的约束表达式。
可选的WITH子句为索引指定存储参数。每一种 索引方法都有其各自允许的存储参数集合。
fillfactor #索引的填充因子是一个百分比，用于确定索引方法将尝试把索引页填充到多满。对于 B-树，在初始构建索引期间，以及向右扩展索引（添加新的最大键值）时，叶页都会填充到该百分比。如果之后页面变得完全填满，就会进行拆分，导致索引效率逐渐下降。B-树 使用默认填充因子 90，但可以选择 10 到 100 之间的任意整数值。如果表是静态的，填充因子 100 最适合将索引的物理大小降至最低；但对于频繁更新的表，较小的填充因子更适合减少页面拆分的需要。其他索引方法以不同但大致类似的方式使用填充因子；默认填充因子因方法而异。
B-树、哈希、GiST 和 SP-GiST 索引方法都接受以下参数：
vacuum_cleanup_index_scale_factor #B-树索引还接受以下参数：
buffering #确定是否使用Section 64.4.1中描述的缓冲构建技术来构建索引。设置为OFF时禁用，设置为ON时启用，设置为AUTO时初始禁用，但一旦索引大小达到effective_cache_size，就会动态启用。默认值为AUTO。
GiST 索引还接受以下参数：
fastupdate #此设置控制Section 66.4.1中描述的快速更新技术的使用。这是一个布尔参数：ON启用快速更新，OFF禁用快速更新。（如Section 19.1中所述，允许使用ON和OFF的其他拼写形式。）默认值为ON。
通过ALTER INDEX关闭fastupdate 会阻止后续插入进入待处理索引项列表，但这本身不会刷新现有条目。 之后可能需要对该表执行VACUUM，或调用 gin_clean_pending_list函数，以确保待处理列表被清空。
gin_pending_list_limit #为该索引覆盖gin_pending_list_limit的全局设置。 该值以千字节为单位。
BRIN 索引接受以下参数：
pages_per_range #定义每个BRIN索引项对应的一个块范围由多少个表块组成 （详见Section 67.1）。默认值为128。
autosummarize #定义当在下一页范围检测到插入时，是否为前一页范围排队执行一次提要操作 （详见Section 67.1.1）。 默认值为off。
创建索引可能会干扰数据库的正常运行。通常 PostgreSQL会锁住要建立索引的表，阻止其写入， 并通过一次扫描完成整个索引构建。其他事务仍可读取该表，但如果它们试图 在表中插入、更新或删除行，就会阻塞直到索引构建完成。如果系统是在线生 产数据库，这可能产生严重影响。对非常大的表建立索引可能需要很多小时， 即便是较小的表，索引构建也可能在一段对生产系统而言不可接受的时间内阻 止写入者操作。
PostgreSQL支持在不阻止写入的情况下构建索引。 这种方法通过在CREATE INDEX中指定 CONCURRENTLY选项来启用。使用该选项时， 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-树、GiST、GIN 和 BRIN 索引方法支持多列索引。默认最多可指定 32 个字段。（构建PostgreSQL时可以更改此限制。）目前只有 B-树 支持唯一索引。
索引的每一列都可以指定一个操作符类。操作符类标识索引用于该列的操作符。例如，四字节整数上的 B-树 索引会使用int4_ops类；此操作符类包含四字节整数的比较函数。实际上，列数据类型的默认操作符类通常已足够。操作符类存在的主要原因是，某些数据类型可能有多个有意义的排序方式。例如，我们可能希望按绝对值或实部对复数数据类型排序。我们可以为该数据类型定义两个操作符类，并在创建索引时选择合适的类。有关操作符类的更多信息，请参见Section 11.10和Section 37.16。
当在分区表上调用CREATE INDEX时，默认行为是递归到 所有分区，以确保它们都拥有匹配的索引。系统首先检查每个分区是否已经存 在等效索引；如果存在，该索引会作为正在创建索引的分区索引附接到其上，而 被创建的索引将成为其父索引。若不存在匹配索引，则会创建一个新索引并自 动附着；每个分区中新索引的名称会按命令中未指定索引名时的规则确定。如 果指定ONLY选项，则不进行递归，且该索引会被标记为 无效。（一旦所有分区都获得匹配索引，ALTER INDEX ... ATTACH PARTITION会将该索引标记为有效。）不过请注意，将来使用 CREATE TABLE ... PARTITION OF创建的任何分区都会 自动拥有匹配索引，无论是否指定了ONLY。
对于支持有序扫描的索引方法（当前只有 B-树），可以指定可选子句 ASC、DESC、NULLS FIRST 和/或NULLS LAST来修改索引的排序顺序。由于有序索引可 以向前或向后扫描，因此创建单列DESC索引通常并无用处 — 常规索引已经提供了这种排序顺序。这些选项的价值在于可以创建与混合 排序查询所要求顺序相匹配的多列索引，例如 SELECT ... ORDER BY x ASC, y DESC。如果需要在依靠 索引避免排序步骤的查询中支持“空值排在低位”，而不是默认的 “空值排在高位”行为，那么NULLS选项就很有用。
系统定期收集表中所有列的统计信息。新创建的非表达式索引可以立即使用 这些统计数据来确定索引的有用性。对于新的表达式索引，需要运行 ANALYZE 或等待自动清理守护进程分析表以 生成这些索引的统计信息。
对于大多数索引方法，创建索引的速度取决于maintenance_work_mem的设置。较大的值会减少创建索引所需的时间，但前提是不能将它设得大于实际可用的内存量，否则机器会进入交换。
PostgreSQL可以在构建索引时利用多个 CPU， 以便更快处理表中的行。这项特性被称为并行索引构建。 对于支持并行构建索引的索引方法（当前为 B-树、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 的转换。
要在表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);
要在表达式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 索引，以便能够在转换函数的结果上高效地使用 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 标准中没有关于 索引的规定。