CREATE INDEX

CREATE INDEX - 定义一个新索引

语法格式

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

说明

CREATE INDEX构造指定关系的指定列的索引，该索引可以是表或实例化视图。索引主要用于增强数据库性能（尽管不恰当的使用会导致性能降低）。

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

索引字段可以是根据表行的一列或多列的值计算的表达式。此功能可用于基于对基本数据的某些转换来快速访问数据。例如，在upper(col) 上计算的索引将允许子句 WHERE upper(col) = 'JIM' 使用索引。

Vastbase E100提供索引方法B-tree，hash，GiST，SP-GiST，GIN和BRIN。用户也可以定义自己的索引方法，但这相当复杂。

当 WHERE子句存在时，将创建部分索引。部分索引是一个索引，它只包含表的一部分的条目，通常是比索引的其余部分更有用的索引部分。例如，如果您的表包含已开票和未开单的订单，其中未开单的订单占据总表的一小部分，但这是一个经常使用的部分，您可以通过仅在该部分上创建索引来提高性能。另一个可能的应用是使用带有 UNIQUE 的 WHERE 来强制表的子集的唯一性。

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

索引定义中使用的所有函数和运算符必须是”不可变的”，也就是说，它们的结果必须仅依赖于它们的参数说明，而不是任何外部影响（例如另一个表的内容或当前时间）。此限制可确保明确定义索引的行为。要在索引表达式或WHERE 子句中使用用户定义的函数，请记住在创建函数时将该函数标记为不可变。

参数说明

UNIQUE

导致系统在索引被创建时（如果数据已经存在）或者加入数据时检查重复值。会导致重复项的数据插入或者更新尝试将会产生一个错误。当唯一索引被应用在分区边上时会有额外的限制，请参考CREATE TABLE。
CONCURRENTLY

当使用了这个选项时，Vastbase E100在构建索引时不会取得任何会阻止该表上并发插入、更新或者删除的锁。而标准的索引构建将会把表锁住以阻止对表的写（但不阻塞读），这种锁定会持续到索引创建完毕。在使用这个选项时有多个需要注意的地方 — 请见并发构建索引。
IF NOT EXISTS

如果一个同名关系已经存在则不要抛出错误。这种情况下会发出一个提示。注意着并不保证现有的索引与将要创建的索引有任何相似。当 IF NOT EXISTS被指定时，需要指定索引名。
INCLUDE

可选的INCLUDE子句指定一个列的列表，其中的列将被包括在索引中作为非键列。非键列不能作为索引扫描的条件，并且该索引所强制的任何唯一性或者排除约束都不会考虑它们。不过，只用索引的扫描可以返回非键列的内容而无需访问该索引的基表，因为在索引项中就能直接拿到它们。因此，非键列的增加允许查询使用只用索引的扫描，否则就无法使用。保守地向索引中增加非键列是明智的，特别是很宽的列。如果一个索引元组超过索引类型允许的最大尺寸，数据插入将会失败。在任何情况下，非键列都会重复来自索引基表的数据并且让索引的尺寸膨胀，因此可能会拖慢搜索。

INCLUDE子句中列出的列不需要合适的操作符类，甚至数据类型没有为给定的访问方法定义操作符类的列都可以包括在这个子句中。不支持把表达式作为被包括列，因为它们不能被用在只用索引的扫描中。当前，仅有B-树索引访问方法支持这一特性。在B-树索引中，INCLUDE子句中列出的列的值被包括在对应于堆元组的叶子元组中，但是不包括在用于树导航的上层索引项中。
name

要创建的索引名称。这里不能包括模式名，因为索引总是被创建在其基表所在的模式中。如果索引名称被省略，Vastbase E100将基于基表名称和被索引列名称选择一个合适的名称。
ONLY

如果该表是分区表，指示不要在分区上递归创建索引。默认会递归创建索引。
table_name

要被索引的表的名称（可以被模式限定）。
method

要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、 gin以及brin。默认方法是btree。
column_name

一个表列的名称。
expression

一个基于一个或者更多个表列的表达式。如语法中所示，表达式通常必须被写在圆括号中。不过，如果该表达式是一个函数调用的形式，圆括号可以被省略。
collation

要用于该索引的排序规则的名称。默认情况下，该索引使用被索引列的排序规则或者被索引表达式的结果排序规则。当查询涉及到使用非默认排序规则的表达式时，使用非默认排序规则的索引就能排上用场。
opclass

一个操作符类的名称。详见下文。
ASC

指定上升排序（默认）。
DESC

指定下降排序。
NULLS FIRST

指定把空值排序在非空值前面。在指定DESC时，这是默认行为。
NULLS LAST

指定把空值排序在非空值后面。在没有指定DESC时，这是默认行为。
storage_parameter

索引方法相关的存储参数的名称。详见索引存储参数。
tablespace_name

在其中创建索引的表空间。如果没有指定，将会使用 default_tablespace。或者对临时表上的索引使用 temp_tablespaces。
predicate

部分索引的约束表达式。

索引存储参数说明

可选的WITH子句为索引指定存储参数。每一种索引方法都有自己的存储参数集合。B-树、哈希、GiST以及SP-GiST索引方法都接受这个参数：

fillfactor

索引的填充因子是一个百分数，它决定索引方法将尝试填充索引页面的充满程度。对于B-树，在初始的索引构建过程中，叶子页面会被填充至该百分数，当在索引右端扩展索引（增加新的最大键值）时也会这样处理。如果页面后来被完全填满，它们就会被分裂，导致索引的效率逐渐退化。B-树使用了默认的填充因子 90，但是也可以选择为 10 到 100 的任何整数值。如果表是静态的，那么填充因子 100 是最好的，因为它可以让索引的物理尺寸最小化。但是对于更新负荷很重的表，较小的填充因子有利于最小化对页面分裂的需求。其他索引方法以不同但是大致类似的方式使用填充因子，不同方法的默认填充因子也不相同。

B-树索引还额外接受这个参数：

vacuum_cleanup_index_scale_factor
vacuum_cleanup_index_scale_factor针对每个索引的值。

GiST还额外接受这个参数：

buffering

决定是否用缓冲构建技术来构建索引。OFF会禁用它，ON则启用该特性，如果设置为AUTO则初始会禁用它，但是一旦索引尺寸到达effective_cache_size就会随时打开。默认值是AUTO。

GIN索引接受不同的参数：

fastupdate

这个设置控制快速更新技术的使用。它是一个布尔参数：ON启用快速更新， OFF禁用。默认是 ON。
gin_pending_list_limit

自定义gin_pending_list_limit参数。这个值要以千字节来指定。

BRIN索引接受不同的参数：

pages_per_range

定义用于每一个BRIN索引项的块范围由多少个表块组成。默认是128。
autosummarize

定义是否只要在下一个页面上检测到插入就为前面的页面范围运行概要操作。

并发构建索引

创建索引可能会干扰数据库的常规操作。通常，Vastbase E100会锁定要针对写入进行索引的表，并通过对表的单次扫描来执行整个索引构建。其他事务仍然可以读取表，但如果它们尝试插入，更新或删除表中的行，它们将阻塞，直到索引构建完成。如果系统是实时生产环境数据库，这可能会产生严重影响。非常大的表可能需要很长时间才能被编入索引，即使对于较小的表，索引构建也可能会锁定编写器，这些时间段对于生产环境系统来说是不可接受的。

Vastbase E100支持构建索引而不会锁定写入。通过指定 CREATE INDEX 的CONCURRENTLY 选项来调用此方法。使用此选项时，Vastbase E100必须对表执行两次扫描，此外，它必须等待可能修改或使用索引终止的所有现有事务。因此，这种方法比标准索引构建需要更多的总工作，并且需要更长的时间来完成。但是，由于它允许在构建索引时继续正常操作，因此此方法对于在生产环境环境中添加新索引很有用。当然，索引创建带来的额外CPU和I /O负载可能会减慢其他操作。

在并发索引构建中，索引实际上在一个事务中输入到系统目录中，然后在另外两个事务中发生两个表扫描。在每次扫描表之前，索引构建必须等待已修改表的现有事务终止。在第二次扫描之后，索引构建必须等待在第二次扫描之前具有快照的任何事务终止。最后，索引可以标记为可以使用，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 。（另一种可能性是使用_1117557重建索引。但是，由于 REINDEX不支持并发构建，因此该选项看起来不太有吸引力。）

同时构建唯一索引时的另一个警告是，在第二个表扫描开始时，已经对其他事务强制执行唯一性约束。这意味着在索引可供使用之前，甚至在索引构建最终失败的情况下，可以在其他查询中报告约束违规。此外，如果在第二次扫描中确实发生了故障，则”无效”索引会继续强制执行其唯一性约束。

支持表达式索引和部分索引的并发构建。在评估这些表达式时发生的错误可能导致类似于上面针对唯一约束违规所描述的行为。

常规索引构建允许同一个表上的其他常规索引构建同时发生，但一次只能在一个表上发生一个并发索引构建。在任何一种情况下，在构建索引时都不允许对表进行模式修改。另一个区别是可以在事务块中执行常规CREATE INDEX 命令，但 CREATE INDEX CONCURRENTLY 不能。

注意事项

当前，只有 B-树、GiST、GIN 和 BRIN 索引方法支持多列索引。默认最多可以索引 32 个域。当前只有 B-树支持唯一索引。

为索引的每一列可以指定一个操作符类。该操作符类标识要被该索引用于该列的操作符。例如，一个四字节整数上的 B-树索引会使用int4_ops类。这个操作符类包括了用于四字节整数的比较函数。实际上，通常列数据类型的默认操作符类就足够了。对某些数据类型指定操作符类的主要原因是，可能会有多于一种有意义的顺序。例如，我们可能想用绝对值或者实数部分对复数类型排序。我们可以通过为该数据类型定义两个操作符类来做到，并且在创建索引时选择其中合适的类。

当在一个分区表上调用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选项就能派上用场，默认的行为是“空值排序高”。

对于大多数索引方法，索引的创建速度取决于 maintenance_work_mem的设置。较大的值将会减少索引创建所需的时间，当然不要把它设置得超过实际可用的内存量（那会迫使机器进行交换）。

Vastbase E100可以在构建索引时利用多个CPU以更快地处理表行。这种特性被称为并行索引构建。对于支持并行构建索引的索引方法（当前只有B-树），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可以移除一个索引。

以前的Vastbase E100发行也有一种 R-树索引方法。这种方法已经被移除，因为它比起 GiST 方法来说没有什么明显的优势。如果指定了USING rtree，CREATE INDEX 将会把它解释为USING gist，以便把旧的数据库转换成 GiST。

示例

要在表 films 中的 title 列上创建唯一的B树索引：

CREATE UNIQUE INDEX title_idx ON films (title);

要在列 title 上创建唯一的B树索引，并在表 films 中包含列 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);

VastbaseE100