创建GLOBAL INDEX_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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/global_partitioned_index ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
global_partitioned_index：
    GLOBAL PARTITION BY {RANGE (column_list) (index_partitioning_clause) |
                            HASH (column_list) (hash_partitions_by_quantity)}

说明

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

GLOBAL INDEX拥有以下优势：

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

全局分区索引（Global Partitioned Index）：

Global Partitioned Index支持将GLOBAL INDEX进行range或hash分区，分区的语法和partition table类似。
Global Partitioned Index不支持按照List分区。
Global Partitioned Index只支持在分区表主表上创建，支持创建在分区键的列上。
Global Partitioned Index支持全局唯一约束。
Global Partitioned Index分区列必须指定索引列的左前缀。
Global Partitioned range Index支持有序扫描。
Global Partitioned hash Index不支持有序扫描。
Global Partitioned hash Index只有等指值表达式支持索引剪枝优化。
Global Partitioned Index支持索引剪枝优化，即优化器剪枝和执行器剪枝。

示例

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

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;

创建Global partition unique range index：

CREATE TABLE partition_range (
    a int,
    b int,
    id integer,
    created_date timestamp without time zone
)
PARTITION BY RANGE (created_date);
CREATE TABLE partition_range_part01 (
    a int,
    b int,
    id integer,
    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 (
    a int,
    b int,
    id integer,
    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 UNIQUE INDEX idx_partition_range_global ON partition_range(id) global
PARTITION BY range (id)
(
PARTITION idx_gpi_1_min_5 values less than(5),
PARTITION idx_gpi_2_5_10 values less than(10),
PARTITION idx_gpi_3_10_max values less than(maxvalue)
);

创建Global Partition unique hash Index：

CREATE UNIQUE INDEX global_hash_idx ON partition_range(id) global

PARTITION BY HASH(id) PARTITIONS 8;

剪枝优化。

Global Partition range Index优化器剪枝：范围条件剪枝。

explain (costs off) select id, to_char(created_date, 'YYYYMMDD') from partition_range where id >= 5 and id < 7;

显示结果如下：

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Global Partitioned Index Scan(1/3) using idx_partition_range_global on partition_range
   Index Cond: ((id >= 5) AND (id < 7))
(2 rows)

Global Partition hash Index优化器剪枝：等值条件剪枝。

explain (costs off) select id, to_char(created_date, 'YYYYMMDD') from partition_range where id = 6;

显示结果如下：

                              QUERY PLAN
------------------------------------------------------------------------
 Global Partitioned Index Scan(1/3) using idx_global on partition_range
   Index Cond: (id = 6)
(2 rows)

Global Partition Index执行剪枝：prepare语句使用执行器剪枝（initial prune）。

INSERT INTO partition_range (id,a,b,created_date) VALUES(1, 1,1,'2020-01-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(2, 1,1,'2020-04-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(3, 1,1,'2020-02-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(4, 1,1,'2020-05-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(5, 1,1,'2020-03-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(6, 1,1,'2019-12-12');
INSERT INTO partition_range (id,a,b,created_date) VALUES(7, 1,1,'2020-05-02');

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global
PARTITION BY range (id)
(
PARTITION idx_gpi_1_min_5 values less than(5),
PARTITION idx_gpi_2_5_10 values less than(10),
PARTITION idx_gpi_3_10_max values less than(maxvalue)
);

set polar_comp_custom_plan_tries =0；
prepare e1(int) as select * from partition_range where id = $1;
EXECUTE e1(3);

显示结果如下：

NOTICE:  global partitioned index idx_partition_range_global do initial prune (1/3)
id | a | b |    created_date
----+---+---+--------------------
  3 | 1 | 1 | 02-FEB-20 00:00:00
(1 row)

Global Partition Index执行剪枝：prepare语句使用执行器剪枝（exec prune）。

set polar_comp_custom_plan_tries =0；
prepare e2(int,int) as select * from partition_range where id between $1 and $2 and id >= (select 6);
EXECUTE e2(1,9);

显示结果如下：

NOTICE:  global partitioned index idx_partition_range_global do exec prune (1/3)
 a | b | id |       created_date
---+---+----+--------------------------
 1 | 1 |  6 | Thu Dec 12 00:00:00 2019
 1 | 1 |  7 | Sat May 02 00:00:00 2020
(2 rows)

Global Partition Index执行剪枝：子查询使用执行器剪枝（exec prune）。

create table tbl1(col1 int);
insert into tbl1 values (501), (505), (5);
create table tprt (col1 int, col2 int) partition by range (col1);
create table tprt_1 partition of tprt for values from (1) to (501);
create table tprt_2 partition of tprt for values from (501) to (1001);
create table tprt_3 partition of tprt for values from (1001) to (2001);
insert into tprt values (10,501), (550,505),(20,5), (1010, 500);
create index tprt1_idx on tprt_1 (col1);
create index tprt2_idx on tprt_2 (col1);
create index tprt3_idx on tprt_3 (col1);
create index idx_global_tprt on tprt(col2) global
PARTITION BY range (col2)
(
PARTITION idx_global_tprt_less_100 values less than(100),
PARTITION idx_global_tprt_101_200 values less than(200),
PARTITION idx_global_tprt_201_max values less than(maxvalue)
);
set enable_hashjoin = off;
set enable_mergejoin = off;
set enable_material=off;
explain (costs off) select (select col1 from tprt a where a.col2 = b.col1) as x ,b.col1 from tbl1 b;

显示结果如下：

                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on tbl1 b
   SubPlan 1
     ->  Global Partitioned Index Scan(3/3) using idx_global_tprt on tprt a
           Index Cond: (col2 = b.col1)
(4 rows)

select (select col1 from tprt a where a.col2 = b.col1) as x ,b.col1 from tbl1 b;

显示结果如下：

NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
  x  | col1
-----+------
  10 |  501
 550 |  505
  20 |    5
(3 rows)

Global Partition Index执行剪枝：多表join使用执行器剪枝（exec prune）。

explain (costs off) select /*+ leading(b t) */ t.col2 ,b.col1 from tbl1 b, tprt t where t.col2 = b.col1;

显示结果如下：

NOTICE:  prune partition child table tprt_1 after choose global index
NOTICE:  prune partition child table tprt_2 after choose global index
NOTICE:  prune partition child table tprt_3 after choose global index
                                QUERY PLAN
--------------------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on tbl1 b
   ->  Global Partitioned Index Scan(3/3) using idx_global_tprt on tprt t
         Index Cond: (col2 = b.col1)
(4 rows)

select /*+ leading(b t) */ t.col2 ,b.col1 from tbl1 b, tprt t where t.col2 = b.col1;

显示结果如下：

NOTICE:  prune partition child table tprt_1 after choose global index
NOTICE:  prune partition child table tprt_2 after choose global index
NOTICE:  prune partition child table tprt_3 after choose global index
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
 col2 | col1
------+------
  501 |  501
  505 |  505
    5 |    5
(3 rows)