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 ]
    [ 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索引，詳情請參見使用跨機並行查詢加速索引建立。

樣本

1. 建立分區表，使用時間分區，定期建立新分區，淘汰老分區。

   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');
   CREATE TABLE partition_range_part04 (
       id integer,
       a int,
       b int,
       created_date timestamp without time zone
   );
   ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part04 FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00');
   CREATE TABLE partition_range_part05 (
       id integer,
       a int,
       b int,
       created_date timestamp without time zone
   );
   ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part05 FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00');

2. 當分區表較多時，部分查詢如果不指定分區鍵created_date，則查詢效能會較差。

   EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;

   返回結果如下：

                   QUERY PLAN                
   ------------------------------------------
    Append
      ->  Seq Scan on partition_range_part01
            Filter: (id = 6)
      ->  Seq Scan on partition_range_part02
            Filter: (id = 6)
      ->  Seq Scan on partition_range_part03
            Filter: (id = 6)
   (7 rows)

3. 此時建立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;