CREATE INDEX

CREATE INDEX在指定关系的指定列上构建一个索引，该关系可以是表或物化视图。索引主要用于提升数据库性能（但使用不当也可能导致性能下降）。

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

索引字段可以是根据表行中一个或多个列值计算得到的表达式。该特性可用于根据基础数据的某种变换来快速访问数据。例如，在upper(col)上计算的索引可让子句 WHERE upper(col) = 'JIM'使用索引。

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

当WHERE子句存在时，会创建一个部分索引。部分索引只包含表中一部分行的索引项，通常这一部分比表的其余部分更适合建立索引。例如，如果一个表同时包含已开票和未开票订单，而未开票订单只占整个表的一小部分，但这一部分又经常被访问，就可以只对这部分创建索引来提升性能。另一种可能的应用是将WHERE与UNIQUE结合使用，以便在表的一个子集上强制唯一性。更多讨论请见第 11.8 节。

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

所有在索引定义中使用的函数和操作符都必须是“不可变的”，也就是说，它们的结果只能依赖其参数，而不能受任何外部因素影响（例如另一个表的内容或当前时间）。这种限制确保索引的行为定义明确。要在索引表达式或WHERE子句中使用用户定义的函数，记得在创建该函数时将其标记为不可变。

关于索引何时能被使用、何时不被使用以及什么情况下它们有用的信息请见第 11 章。

当前，只有 B-树、GiST、GIN 和 BRIN 索引方法支持多键列索引。能否拥有多个键列，与能否向索引添加INCLUDE列无关。索引最多可以有 32 列，包括INCLUDE列。（可以在构建PostgreSQL时修改这个限制）。当前只有 B-树支持唯一索引。

可以为索引中的每一列指定一个带可选参数的操作符类。操作符类标识该索引在该列上要使用的操作符。例如，一个四字节整数上的 B-树索引会使用int4_ops类；这个操作符类包含用于四字节整数的比较函数。实际上，列数据类型的默认操作符类通常就足够了。引入操作符类的主要原因在于，对于某些数据类型，可能存在不止一种有意义的排序方式。例如，可能需要按绝对值或实部对复数数据类型排序。要做到这一点，可以为该数据类型定义两个操作符类，然后在创建索引时选择合适的类。关于操作符类的更多信息请见第 11.10 节以及第 36.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 或等待自动清理守护进程分析表以生成这些索引的统计信息。

在 CREATE INDEX 执行期间，search_path会被临时设置为 pg_catalog, pg_temp。

对于大多数索引方法，索引的创建速度取决于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，将在所有情况下禁用该表上的并行索引构建。

虽然带有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-树索引：

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

要创建一个具有非默认空值排序顺序的索引：

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 标准中没有关于索引的规定。