GLOBAL INDEX是分区表上的一种索引技术,可以创建在分区表的非分区键上,也支持提供唯一约束。

语法

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

说明

  • GLOBAL/LOCAL参数指定为GLOBAL即创建GLOBAL INDEX。
  • 如果不指定创建GLOBAL/LOCAL参数,则默认创建LOCAL INDEX。
  • GLOBAL INDEX的CREATE语法支持使用CONCURRENTLY模式创建。
  • 非分区表、包括分区表的子表上不支持创建GLOBAL INDEX。
  • GLOBAL INDEX不支持表达式索引。
  • 无法在分区表的分区列上创建GLOBAL INDEX。

GLOBAL INDEX拥有以下优势:

  • 能提供分区表中非分区列上的唯一约束。
  • 带分区表的查询但没有指定分区键场景,用于加速查询的性能,即分区键外的第二查找键。
  • 跨机并行查询支持加速创建B-Tree索引的GLOBAL索引,详情请参见使用跨机并行查询加速索引创建

示例

分区表使用时间分区,定期创建新分区,淘汰老分区。

CREATE TABLE partition_range (    id integer,
    a int,
    b int,
    created_date timestamp without time zone
)
PARTITION BY RANGE (created_date);
CREATE TABLE partition_range_part01 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part01 FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
CREATE TABLE partition_range_part02 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part02 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
CREATE TABLE partition_range_part03 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part03 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

当分区表较多时,部分查询如果不指定分区键created_date,则查询性能会较差。

SELECT * FROM partition_range WHERE id = $1

此时创建GLOBAL INDEX,查询性能可以获得较大提升,创建GLOBAL INDEX语句如下:

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global;

创建GLOBAL INDEX后,查询性能如下:

EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;
                              QUERY PLAN
-----------------------------------------------------------------------
 Global Index Scan using idx_partition_range_global on partition_range
   Index Cond: (id = 6)
(2 rows)

在有GLOBAL INDEX的分区表上,依然支持ATTACH和DETACH分区:

  • ATTACH新分区
    CREATE TABLE partition_range_part06 (    id integer,
        a int,
        b int,
        created_date timestamp without time zone
    );
    ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part06 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00');
  • DETACH老分区
    ALTER TABLE partition_range DETACH PARTITION partition_range_part01;