更新时间:2024-05-07 GMT+08:00

CREATE GLOBAL INDEX

功能描述

在指定的表上创建全局二级索引(Global Secondary Index,简称GSI)。

全局二级索引允许用户定义与基表分布不一致的索引,从而实现基表非分布列查询的单节点计划和基表非分布列上的unique/主键约束。

  1. 在升级未提交观察期间,不允许创建GSI。
  2. 建议在至少一列GSI的索引键上创建NOT NULL约束,以提速IUD的性能。
  3. 建议在GSI的基表创建普通索引提升IUD执行效率。
  4. 当前创建GSI生成的执行计划是先将基表数据拉回CN再下发给GSI所在DN,因此大数据量、大规模集群下性能较普通索引存在较大劣化(比如6节点SSD盘千万数据量下可能需近1小时),将在后续版本中优化创建GSI的实现机制,将目前的单点改为DN间并行提升创建效率。

注意事项

  • 同基表约束,GSI的分布列不支持更新(UPDATE)操作。
  • 只支持GTM-LITE模式下创建GSI,不支持其他GTM模式,在其他模式下创建GSI会报错。
  • 支持创建普通UBTree索引,不支持部分索引、表达式索引;Astore不支持创建GSI以外的UBTree,不支持对GSI创建分区。
  • 不支持创建与基表分布一致的GSI,执行时会报错。
  • 不支持在线创建GSI、在线重建GSI,会报语法错误;不支持parallel,数据节点侧会将parallel_workers设为0。
  • 支持对基表为hash分布的行存Astore表、分区表创建hash分布的GSI,不支持基表为复制表、HASHBUCKET、list/range分布、Ustore等,对于GSI本身不支持hash分布以外的分布。
  • 当基表为分区表时,GSI最多支持27列;当基表为非分区表时,GSI最多支持28列(包括索引键和分布键)。
  • 不支持UPSERT、建有GSI的基表上的IUD returning功能、回表、MERGE INTO、CLUSTER,以及SQL PATCH。
  • 当前版本会失效GSI的操作:
    • vacuum full相关
      • vacuum full 单表:失效表上所有GSI。
      • vacuum full 数据库:失效库上所有GSI。
    • reindex相关
      • reindex 单表(非在线):失效表上所有GSI。
      • reindex 数据库(非在线):失效库上所有GSI。
    • cluster相关
      • cluster 单表 using 普通索引:失效表上所有GSI。
      • cluster 单表:失效表上所有GSI。
      • cluster 数据库:失效库上已经聚簇过的表上的所有GSI。
    • copy/gds相关

      失效表上所有GSI。

    • partition相关

      MERGE PARTITION、EXCHANGE PARTITION、TRUNCATE PARTITION、DROP PARTITION、SPLIT PARTITION将失效分区表上所有GSI,对于EXCHANGE PARTITION也会同时失效普通表上的所有GSI。

  • 对于INSERT INTO SELECT批量插入场景、UPDATE/DELETE批量场景,执行计划采用回到CN的方式,性能较差(类比创建GSI性能)。
  • 对于INSERT、UPDATE、DELETE,执行计划采用分布式执行计划,会有性能损失。
  • 不支持对基表列名或者ctid、xc_node_hash、xmin、xmax、tableoid(当基表为分区表时)增加_new$$、_NEW$$后与自身列名重复的基表创建GSI。
  • 如果在执行VACUUM FULL、CLUSTER或者REINDEX操作时中断,表上的GSI可能会变为UNUSABLE状态,此时查询语句走GSI会报错,建议执行REINDEX INDEX重建GSI。

语法格式

CREATE GLOBAL [ UNIQUE ] INDEX [ [schema_name.]index_name ] ON table_name [ USING method ]
    ({ column_name [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) 
    [CONTAINING (containing_colname)] [DISTRIBUTE BY hash(dist_colnames)]
    [ TABLESPACE tablespace_name ];

参数说明

  • UNIQUE

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

  • schema_name

    模式的名称。

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

  • index_name

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

    取值范围:字符串,要符合标识符命名规范

  • table_name

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

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

  • USING method

    指定创建索引的方法。

    取值范围:ubtree,提供多版本B-tree索引,索引页面上包含事务信息。

  • column_name

    表中需要创建索引的列的名称(字段名)。

    如果索引方式支持多字段索引,可以声明多个字段,对于非分区基表最多可以声明28个字段,对于分区基表最多可以声明27个字段。

  • COLLATE collation

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

  • opclass

    操作符类的名称。对于索引的每一列可以指定一个操作符类,操作符类标识了索引那一列的使用的操作符。

  • ASC

    指定按升序排序(默认)。

  • DESC

    指定按降序排序。

  • NULLS FIRST

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

  • NULLS LAST

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

  • CONTAINING

    全局二级索引可以包含索引键以外的基表属性。

  • containing_colname

    全局二级索引包含的索引键以外的基表属性。

  • DISTRIBUTE BY

    指定全局二级索引分布键,其分布键需要与基表不同,且只能使用哈希分布方式。

  • dist_colnames

    全局二级索引分布键。

    需要被包含在column_name中。

  • TABLESPACE tablespace_name

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

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

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
--创建包含3列的基表test。
gaussdb=# CREATE TABLE test(c1 int, c2 int, c3 int);

--在test表的c2列上创建GSI,并包含c3列,基于c2列哈希分布。
gaussdb=#  CREATE GLOBAL INDEX idx_gsi_1 ON test(c2) CONTAINING(c3) DISTRIBUTE BY HASH(c2);

--创建包含3列的基表test2。
gaussdb=#  CREATE TABLE test2(c1 int, c2 int, c3 int);

--在test2表的c2列上创建GSI,并包含c3列,默认基于c2列哈希分布。
gaussdb=#  CREATE GLOBAL INDEX idx_gsi_2 ON test2(c2) CONTAINING(c3) ;

--创建包含3列的基表test3。
gaussdb=#  CREATE TABLE test3(c1 int, c2 int, c3 int);

--在test3表的c2列上创建UNIQUE形式的GSI,默认基于c2列哈希分布。
gaussdb=#  CREATE GLOBAL UNIQUE INDEX idx_gsi_3 ON test3(c2) DISTRIBUTE BY HASH(c2);