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)