CREATE INDEX

功能描述

在指定的表上创建索引。

索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引：

经常执行查询的字段。
在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引。例如，select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b，可以在t1表上的a，b字段上建立组合索引。
where子句的过滤条件字段上（尤其是范围条件）。
在经常出现在order by、group by和distinct后的字段。

在分区表上创建索引与在普通表上创建索引的语法不太一样，使用时请注意，如分区表上不支持并行创建索引、不支持创建部分索引。

注意事项

索引自身也占用存储空间、消耗计算资源，创建过多的索引将对数据库性能造成负面影响（尤其影响数据导入的性能，建议在数据导入后再建索引）。因此，仅在必要时创建索引。
索引定义里的所有函数和操作符都必须是immutable类型的，即它们的结果必须只能依赖于它们的输入参数，而不受任何外部的影响（如另外一个表的内容或者当前时间）。这个限制可以确保该索引的行为是定义良好的。要在一个索引上或WHERE中使用用户定义函数，请把它标记为immutable类型函数。
分区表索引分为LOCAL索引与GLOBAL索引，LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。
列存表支持的PSORT和B-tree索引都不支持创建表达式索引、部分索引，PSORT不支持创建唯一索引，B-tree支持创建唯一索引。
列存表支持的GIN索引支持创建表达式索引，但表达式不能包含空分词、空列和多列，不支持创建部分索引和唯一索引。
HASH索引目前仅限于行存表索引、临时表索引和分区表LOCAL索引，且不支持创建多字段索引。
被授予CREATE ANY INDEX权限的用户，可以在public模式和用户模式下创建索引。
BRIN 和 BLOOM 索引目前只支持行存表。

语法格式

在表上创建索引。

CreateIndex ::= CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name'.']index_name ] ON 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 ]
      [ PARALLEL num ];

在分区表上创建索引。

CreateIndex ::= CREATE [ UNIQUE ] INDEX [ [schema_name'.']index_name ] ON table_name [ USING method ]
    ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
    [ LOCAL [ ( { PARTITION index_partition_name | SUBPARTITION index_subpartition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
    [ INCLUDE ( column_name [, ...] )]
    [ WITH ( { storage_parameter = value } [, ...] ) ]
    [ TABLESPACE tablespace_name ]
    [ PARALLEL num ];

参数说明

UNIQUE

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

目前只有B-tree索引支持唯一索引。
CONCURRENTLY

以不阻塞DML的方式创建索引（加ShareUpdateExclusiveLock锁）。创建索引时，一般会阻塞其他语句对该索引所依赖表的访问。指定此关键字，可以实现创建过程中不阻塞DML。
- 此选项只能指定一个索引的名称。
- 普通CREATE INDEX命令可以在事务内执行，但是CREATE INDEX CONCURRENTLY不可以在事务内执行。
- 列存表和临时表不支持CONCURRENTLY方式创建索引。
- 分区表支持以CONCURRENTLY方式创建全局索引，但不支持创建局部索引。
说明：
- 创建索引时指定此关键字，需要执行先后两次对该表的全表扫描来完成build，第一次扫描的时候创建索引，不阻塞读写操作；第二次扫描的时候合并更新第一次扫描到目前为止发生的变更。
- 由于需要执行两次对表的扫描和build，而且必须等待现有的所有可能对该表执行修改的事务结束。这意味着该索引的创建比正常耗时更长，同时因此带来的CPU和I/O消耗对其他业务也会造成影响。
- 如果在索引构建时发生失败，那会留下一个“不可用”的索引。这个索引会被查询忽略，但它仍消耗更新开销。这种情况推荐的恢复方法是删除该索引并尝试再次CONCURRENTLY建索引。
- 由于在第二次扫描之后，索引构建必须等待任何持有早于第二次扫描拿的快照的事务终止，而且建索引时加的ShareUpdateExclusiveLock锁（4级）会和大于等于4级的锁冲突，在创建这类索引时，容易引发卡住（hang）或者死锁问题。例如：
  - 两个会话对同一个表创建CONCURRENTLY索引，会引起死锁问题；
  - 两个会话，一个对表创建CONCURRENTLY索引，一个drop table，会引起死锁问题；
  - 三个会话，会话1先对表a加锁，不提交，会话2接着对表b创建CONCURRENTLY索引，会话3接着对表a执行写入操作，在会话1事务未提交之前，会话2会一直被阻塞；
  - 将事务隔离级别设置成可重复读（默认为读已提交），起两个会话，会话1起事务对表a执行写入操作，不提交，会话2对表b创建CONCURRENTLY索引，在会话1事务未提交之前，会话2会一直被阻塞。
schema_name

模式的名称。

取值范围: 已存在模式名。
index_name

要创建的索引名，索引的模式与表相同。

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

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

取值范围: 已存在的表名。
USING method

指定创建索引的方法。

取值范围:
- btree：B-tree索引使用一种类似于B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。
- hash：Hash索引使用Hash函数对索引的关键字进行散列。只能处理简单等值比较，比较适合在索引值较长的情况下使用。
- gin：GIN索引是倒排索引，可以处理包含多个键的值（比如数组）。
- gist：Gist索引适用于几何和地理等多维数据类型和集合数据类型。目前支持的数据类型有box、point、poly、circle、tsvector、tsquery、range。
- Psort：Psort索引。针对列存表进行局部排序索引。
- ubtree：仅供ustore表使用的多版本B-tree索引，索引页面上包含事务信息，能并自主回收页面。
- brin：block range index，索引占用空间非常小，适用于表数据量非常大的场景。
- bloom：基于 bloom filter 实现，可以快速过滤不符合查询条件的数据，适用于查询条件的组合比较随机的场景。
行存表（ASTORE存储引擎）支持的索引类型：btree（行存表缺省值）、hash、gin、gist、brin、bloom。行存表（USTORE存储引擎）支持的索引类型：ubtree。列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。全局临时表不支持GIN索引和Gist索引。

说明： 列存表对GIN索引支持仅限于对于tsvector类型的支持，即创建列存GIN索引入参需要为to_tsvector函数（的返回值）。此方法为GIN索引比较普遍的使用方式。
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排序时，本选项为默认的。
LOCAL

指定创建的分区索引为LOCAL索引。
GLOBAL

指定创建的分区索引为GLOBAL索引，当不指定LOCAL、GLOBAL关键字时，默认创建GLOBAL索引。
INCLUDE ( column_name [, ...] )

可选的 INCLUDE 子句指定将一些非键列（non-key columns）包含在索引中。非键列不能用于作为索引扫描的加速搜索条件，同时在检查索引的唯一性约束时会忽略它们。

仅索引扫描 (Index Only Scan) 可以直接返回非键列中的内容，而不必去访问索引所对应的堆表。

将非键列添加为 INCLUDE 列需要保守一些，尤其是对于宽列。如果索引元组超过索引类型允许的最大大小，数据将插入失败。需要注意的是，任何情况下为索引添加非键列都会增加索引的空间占用，从而可能减慢搜索速度。

目前只有ubtree索引访问方式支持该特性。非键列会被保存在与堆元组对应的索引叶子元组中，不会包含在索引上层页面的元组中。
WITH ( {storage_parameter = value} [, ...] )

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

取值范围:

只有GIN索引支持FASTUPDATE，GIN_PENDING_LIST_LIMIT参数。GIN和Psort之外的索引都支持FILLFACTOR参数。只有UBTREE索引支持INDEXSPLIT参数。
- FILLFACTOR
  
  一个索引的填充因子（fillfactor）是一个介于10和100之间的百分数。
  
  取值范围: 10~100
- FASTUPDATE
  
  GIN索引是否使用快速更新。
  
  取值范围: ON，OFF
  
  默认值：ON
- GIN_PENDING_LIST_LIMIT
  
  当GIN索引启用fastupdate时，设置该索引pending list容量的最大值。
  
  取值范围: 64~INT_MAX，单位KB。
  
  默认值：gin_pending_list_limit的默认取决于GUC中gin_pending_list_limit的值（默认为4MB）
- INDEXSPLIT
  
  UBTREE索引选择采取哪种分裂策略。其中DEFAULT策略指的是与BTREE相同的分裂策略。INSERTPT策略能在某些场景下显著降低索引空间占用。
  
  取值范围：INSERTPT，DEAFAULT
  
  默认值：INSERTPT
- pages_per_range
  
  BRIN索引中用于表示一条索引数据包含多少个数据页的摘要信息。通常这个值设置得越小，索引占用的空间越大（因为需要存储更多的索引项），同时索引中摘要信息也越精确，在使用索引扫描时可以跳过更多数据页。
  
  取值范围：1~131072
  
  默认值：128
- autosummarize
  
  BRIN索引中当新增的数据页范围超出当前索引中的数据页范围时，是否为这些新增的数据页自动增加索引项的开关。
  
  取值范围：布尔值
  
  默认值：off
- length
  
  BLOOM索引中指定一条索引生成的位图使用多少bit表示，取值范围是1~4096，默认值是80。
- col1-col32
  
  BLOOM索引中指定索引列使用多少bit表示。默认值是2，最小值是1，最大值是4095。
TABLESPACE tablespace_name

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

取值范围: 已存在的表空间名。
WHERE predicate

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

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

索引分区的名称。

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

索引二级分区的名称。

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

索引分区的表空间。

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

索引参数，设置索引压缩算法。1代表pglz算法，2代表zstd算法，默认不压缩。（仅支持B-TREE索引）

取值范围：0~2，默认值为0。
COMPRESS_LEVEL

索引参数，设置索引压缩算法等级，仅当COMPRESS_TYPE为2时生效。压缩等级越高，索引的压缩效果越好，索引的访问速度越慢。（仅支持B-TREE索引）

取值范围：-31~31，默认值为0。
COMPRESS_CHUNK_SIZE

索引参数，设置索引压缩chunk块大小。chunk数据块越小，预期能达到的压缩效果越好，同时数据越离散，影响索引的访问速度。（仅支持B-TREE索引）

取值范围：与页面大小有关。在页面大小为8k场景，取值范围为：512、1024、2048、4096。

默认值：4096
COMPRESS_PREALLOC_CHUNKS

索引参数，设置索引压缩chunk块预分配数量。预分配数量越大，索引的压缩率相对越差，离散度越小，访问性能越好。（仅支持B-TREE索引）

取值范围：0~7，默认值为0。
- 当COMPRESS_CHUNK_SIZE为512和1024时，支持预分配设置最大为7。
- 当COMPRESS_CHUNK_SIZE为2048时，支持预分配设置最大为3。
- 当COMPRESS_CHUNK_SIZE为4096时，支持预分配设置最大为1。
COMPRESS_BYTE_CONVERT

索引参数，设置索引压缩字节转换预处理。在一些场景下可以提升压缩效果，同时会导致一定性能劣化。

取值范围：布尔值，默认关闭。
COMPRESS_DIFF_CONVERT

索引参数，设置索引压缩字节差分预处理。只能与compress_byte_convert一起使用。在一些场景下可以提升压缩效果，同时会导致一定性能劣化。

取值范围：布尔值，默认关闭。
PARALLEL num

在索引创建语句中直接指定并行度，根据对当前资源环境以及自身需求来指定并行度，可以更加有效地利用资源，同时提高使用灵活性。

取值范围：1-32

最终并行度由下列几个要素决定：

1、表的parallel_workers属性（必要因素）

当parallel_workers=0时，PARALLEL num无效

当parallel_workers=[1-32]时，PARALLEL num应当<=parallel_workers

如何设置/修改/查询表的parallel_workers属性：
```
MogDB=# create table t_1(aid integer);
CREATE TABLE

--查看表的并行度parallel_workers
MogDB=# select reloptions from pg_class where relname='t_1';
reloptions            
----------------------------------
{orientation=row,compression=no}
(1 row)

--设置表的并行度parallel_workers=8
MogDB=# alter table t_1 set (parallel_workers=8);
ALTER TABLE
MogDB=# select reloptions from pg_class where relname='t_1';
reloptions                      
-----------------------------------------------------
{orientation=row,compression=no,parallel_workers=8}
(1 row)

--设置表的并行度parallel_workers=1
MogDB=# alter table t_1 set (parallel_workers=1);
ALTER TABLE
MogDB=# select reloptions from pg_class where relname='t_1';

reloptions                      
-----------------------------------------------------

 {orientation=row,compression=no,parallel_workers=1}
(1 row)
```
2、指定值

我们在创建索引中指定的值parallel num，num取值范围是1-32，超过该范围会报错。

3、目标表类型

i. 如果目标表是一个普通表，那么并行度就按照上述计算结果。

ii. 如果目标表是一个分区表并且创建索引的类型为全局索引，那么并行度取分区个数和上述结果中较小的值作为最终的并行度。

iii. 如果目标表为分区表，并且是local的index，对于每一个分区按照上述指定值进行并行创建。

注意：仅有btree类型索引支持并行，因此该语法也仅支持btree索引。

示例1

--创建表ship_mode_t1。
MogDB=# CREATE TABLE ship_mode_t1
(
    SM_SHIP_MODE_SK           INTEGER               NOT NULL,
    SM_SHIP_MODE_ID           CHAR(16)              NOT NULL,
    SM_TYPE                   CHAR(30)                      ,
    SM_CODE                   CHAR(10)                      ,
    SM_CARRIER                CHAR(20)                      ,
    SM_CONTRACT               CHAR(20)
)
;

--在表ship_mode_t1上的SM_SHIP_MODE_SK字段上创建普通的唯一索引。
MogDB=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON ship_mode_t1(SM_SHIP_MODE_SK);

--在表ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引。
MogDB=# CREATE INDEX ds_ship_mode_t1_index4 ON ship_mode_t1 USING btree(SM_SHIP_MODE_SK);

--在表ship_mode_t1上SM_CODE字段上创建表达式索引。
MogDB=# CREATE INDEX ds_ship_mode_t1_index2 ON ship_mode_t1(SUBSTR(SM_CODE,1 ,4));

--在表ship_mode_t1上的SM_SHIP_MODE_SK字段上创建SM_SHIP_MODE_SK大于10的部分索引。
MogDB=# CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;

--重命名一个现有的索引。
MogDB=# ALTER INDEX ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;

--设置索引不可用。
MogDB=# ALTER INDEX ds_ship_mode_t1_index2 UNUSABLE;

--重建索引。
MogDB=# ALTER INDEX ds_ship_mode_t1_index2 REBUILD;

--删除一个现有的索引。
MogDB=# DROP INDEX ds_ship_mode_t1_index2;

--删除表。
MogDB=# DROP TABLE ship_mode_t1;

--创建表空间。
MogDB=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
MogDB=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
MogDB=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
MogDB=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
--创建表customer_address_p1。
MogDB=# CREATE TABLE customer_address_p1
(
    CA_ADDRESS_SK             INTEGER               NOT NULL,
    CA_ADDRESS_ID             CHAR(16)              NOT NULL,
    CA_STREET_NUMBER          CHAR(10)                      ,
    CA_STREET_NAME            VARCHAR(60)                   ,
    CA_STREET_TYPE            CHAR(15)                      ,
    CA_SUITE_NUMBER           CHAR(10)                      ,
    CA_CITY                   VARCHAR(60)                   ,
    CA_COUNTY                 VARCHAR(30)                   ,
    CA_STATE                  CHAR(2)                       ,
    CA_ZIP                    CHAR(10)                      ,
    CA_COUNTRY                VARCHAR(20)                   ,
    CA_GMT_OFFSET             DECIMAL(5,2)                  ,
    CA_LOCATION_TYPE          CHAR(20)
)
TABLESPACE example1
PARTITION BY RANGE(CA_ADDRESS_SK)
(
   PARTITION p1 VALUES LESS THAN (3000),
   PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
   PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
--创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名称。
MogDB=# CREATE INDEX ds_customer_address_p1_index1 ON customer_address_p1(CA_ADDRESS_SK) LOCAL;
--创建分区表索引ds_customer_address_p1_index2，并指定索引分区的名称。
MogDB=# CREATE INDEX ds_customer_address_p1_index2 ON customer_address_p1(CA_ADDRESS_SK) LOCAL
(
    PARTITION CA_ADDRESS_SK_index1,
    PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
    PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
)
TABLESPACE example2;

--创建GLOBAL分区索引
MogDB=# CREATE INDEX ds_customer_address_p1_index3 ON customer_address_p1(CA_ADDRESS_ID) GLOBAL;

--不指定关键字，默认创建GLOBAL分区索引
MogDB=# CREATE INDEX ds_customer_address_p1_index4 ON customer_address_p1(CA_ADDRESS_ID);

--修改分区表索引CA_ADDRESS_SK_index2的表空间为example1。
MogDB=# ALTER INDEX ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index2 TABLESPACE example1;

--修改分区表索引CA_ADDRESS_SK_index3的表空间为example2。
MogDB=# ALTER INDEX ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index3 TABLESPACE example2;

--重命名分区表索引。
MogDB=# ALTER INDEX ds_customer_address_p1_index2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;

--删除索引和分区表。
MogDB=# DROP INDEX ds_customer_address_p1_index1;
MogDB=# DROP INDEX ds_customer_address_p1_index2;
MogDB=# DROP TABLE customer_address_p1;
--删除表空间。
MogDB=# DROP TABLESPACE example1;
MogDB=# DROP TABLESPACE example2;
MogDB=# DROP TABLESPACE example3;
MogDB=# DROP TABLESPACE example4;

--创建列存表以及列存表GIN索引。
MogDB=# create table cgin_create_test(a int, b text) with (orientation = column);
CREATE TABLE
MogDB=# create index cgin_test on cgin_create_test using gin(to_tsvector('ngram', b));
CREATE INDEX

示例2：并行创建索引

--创建表
MogDB=# create table crepara_index_t1(c1 int,c2 int,c3 int,c4 text,c5 text,c6 text);
CREATE TABLE

--模拟800万条数据
insert into crepara_index_t1 select random()*10000,random() *10000,random()*10000,to_hex(random()*10000000),to_hex(random()*10000000),to_hex(random()*10000000) from generate_series(1,8000000);
INSERT 0 8000000
Time: 71333.366 ms

--查询记录总数
MogDB=# select count(*) from crepara_index_t1 ;
count  
---------
8000000
(1 row)
Time: 1068.158 ms

--修改表的属性parallel_workers=32
MogDB=# alter table crepara_index_t1 set (parallel_workers=32);
ALTER TABLE
--也可以在建表时指定parallel_workers例：
--create table crepara_index_t1_new(c1 int,c2 int,c3 int,c4 text,c5 text,c6 text) with(parallel_workers=32);

--创建索引并指定并行度为32
MogDB=# create index crepara_idx_01 on crepara_index_t1(c1,c2) parallel 32;
CREATE INDEX
Time: 11146.399 ms

CREATE INDEX

功能描述

注意事项

语法格式

参数说明

示例1

示例2：并行创建索引

相关链接