参数说明

• UNIQUE

  创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将导致一个错误。

  目前只有行存表B-tree及UBtree索引支持唯一索引。

• CONCURRENTLY

  以不阻塞DML的方式创建索引（加ShareUpdateExclusiveLock锁）。创建索引时，一般会阻塞其他语句对该索引所依赖表的访问。指定此关键字，可以实现创建过程中不阻塞DML。

  • 此选项只能指定一个索引的名称。
  • 普通CREATE INDEX命令可以在事务内执行，但是CREATE INDEX CONCURRENTLY不可以在事务内执行。
  • 列存表、分区表不支持CONCURRENTLY方式创建索引，对于临时表，支持使用CONCURRENTLY关键字创建索引，但是实际创建过程中，采用的是阻塞式的创建方式，因为没有其他会话会并发访问临时表，并且阻塞式创建成本更低。
  

  • 创建索引时指定此关键字，需要执行先后两次对该表的全表扫描来完成build，第一次扫描的时候创建索引，不阻塞读写操作；第二次扫描的时候合并更新第一次扫描到目前为止发生的变更。
  • 由于需要执行两次对表的扫描和build，而且必须等待现有的所有可能对该表执行修改的事务结束。这意味着该索引的创建比正常耗时更长，同时因此带来的CPU和I/O消耗对其他业务也会造成影响。
  • 如果在索引构建时发生失败，那会留下一个“不可用”的索引。这个索引会被查询忽略，但它仍消耗更新开销。这种情况推荐的恢复方法是通过DROP INDEX IF EXISTS语法删除该索引并尝试再次CONCURRENTLY建索引。
  • 由于在第二次扫描之后，索引构建必须等待任何持有早于第二次扫描拿的快照的事务终止，而且建索引时加的ShareUpdateExclusiveLock锁（4级）会和大于等于4级的锁冲突，在创建这类索引时，容易引发卡住（hang）或者死锁问题。例如：
    • 两个会话对同一个表创建CONCURRENTLY索引，会引起死锁问题。
    • 两个会话，一个对表创建CONCURRENTLY索引，一个drop table，会引起死锁问题。
    • 三个会话，会话1先对表a加锁，不提交，会话2接着对表b创建CONCURRENTLY索引，会话3接着对表a执行写入操作，在会话1事务未提交之前，会话2会一直被阻塞。
    • 创建CONCURRENTLY索引与同一个表的TRUNCATE操作并发，会引起死锁问题。
    • 将事务隔离级别设置成可重复读（默认为读已提交），起两个会话，会话1起事务对表a执行写入操作，不提交，会话2对表b创建CONCURRENTLY索引，在会话1事务未提交之前，会话2会一直被阻塞。
  • 索引构建过程中或者构建失败的情况下，需要确认索引进度或状态，可以通过查询函数gs_get_index_status('schema_name', 'index_name')来确认当前所有节点上索引的状态，其中入参为schema_name和index_name，分别用来指定索引的模式名称和索引名称，返回值为node_name，indisready和indisvalid，分别表示节点名称，索引在该节点上是否可插入，以及索引在该节点上是否可用，只有当所有节点indisready和indisvalid均为true的情况下，索引才是“可用的”，否则请等待索引创建完成，或者构建失败情况下，删除索引重新创建。
• schema_name

  模式的名称。

  取值范围：已存在模式名。

• index_name

  要创建的索引名，不能包含模式名，索引的模式与表相同。

  取值范围：字符串，要符合标识符的命名规范。

• table_name

  需要为其创建索引的表的名称，可以用模式修饰。

  取值范围：已存在的表名。

• USING method

  指定创建索引的方法。

  取值范围：

  • btree：B-tree索引使用一种类似于B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。
  • gin：GIN索引是倒排索引，可以处理包含多个键的值（比如数组）。
  • gist：Gist索引适用于几何和地理等多维数据类型和集合数据类型。
  • Psort：Psort索引。针对列存表进行局部排序索引。

  行存表支持的索引类型：btree（行存表缺省值）、gin、gist。列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。全局临时表不支持GIN索引和Gist索引。

• column_name

  表中需要创建索引的列的名称（字段名）。

  如果索引方式支持多字段索引，可以声明多个字段。全局索引最多可以声明31个字段，其他索引最多可以声明32个字段。

• expression

  创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括弧中。如果表达式有函数调用的形式，圆括弧可以省略。

  表达式索引可用于获取对基本数据的某种变形的快速访问。比如，一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。

  在创建表达式索引时，如果表达式中包含IS NULL子句，则这种索引是无效的。此时，建议用户尝试创建一个部分索引。

• COLLATE collation

  COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“select * from pg_collation”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。

• opclass

  操作符类的名称。对于索引的每一列可以指定一个操作符类，操作符类标识了索引那一列的使用的操作符。例如一个B-tree索引在一个四字节整数上可以使用int4_ops；这个操作符类包括四字节整数的比较函数。实际上对于列上的数据类型默认的操作符类是足够用的。操作符类主要用于一些有多种排序的数据。例如，用户想按照绝对值或者实数部分排序一个复数。能通过定义两个操作符类然后当建立索引时选择合适的类。

• ASC

  指定按升序排序 （默认）。

• DESC

  指定按降序排序。

• NULLS FIRST

  指定空值在排序中排在非空值之前，当指定DESC排序时，本选项为默认的。

• NULLS LAST

  指定空值在排序中排在非空值之后，未指定DESC排序时，本选项为默认的。

• WITH ( {storage_parameter = value} [, ... ] )

  指定索引方法的存储参数。

  取值范围：

  只有GIN索引支持FASTUPDATE，GIN_PENDING_LIST_LIMIT参数。GIN和Psort之外的索引都支持FILLFACTOR参数。

  • FILLFACTOR

    一个索引的填充因子（fillfactor）是一个介于10和100之间的百分数。

    取值范围：10~100

  • FASTUPDATE

    GIN索引是否使用快速更新。

    取值范围：ON，OFF

    默认值：ON

  • GIN_PENDING_LIST_LIMIT

    当GIN索引启用fastupdate时，设置该索引pending list容量的最大值。

    取值范围：64~2147483647‬，单位KB。

    默认值：gin_pending_list_limit的默认取决于GUC中gin_pending_list_limit的值（默认为4MB）

  • CROSSBUCKET

    索引是否使用跨hashbucket索引。仅支持B-Tree索引。

    取值范围：ON，OFF

    默认值：ON

• TABLESPACE tablespace_name

  指定索引的表空间，如果没有声明则使用默认的表空间。

  取值范围：已存在的表空间名。

• WHERE predicate

  创建一个部分索引。部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。例如，有一个表，表里包含已记账和未记账的订单，未记账的订单只占表的一小部分而且这部分是最常用的部分，此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。

  取值范围：predicate表达式只能引用表的字段，它可以使用所有字段，而不仅是被索引的字段。目前，子查询和聚集表达式不能出现在WHERE子句里。不建议使用int等数值类型作为predicate，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。

  对于分区表索引，当创建索引带GLOBAL/LOCAL关键字，或者最终创建的索引类型为GLOBAL索引时，不支持带WHERE子句创建索引。

• PARTITION index_partition_name

  索引分区的名称。

  取值范围：字符串，要符合标识符的命名规范。

• TABLESPACE index_partition_tablespace

  索引分区的表空间。

  取值范围：如果没有声明，将使用分区表索引的表空间index_tablespace。