PolarDB：全域索引

前提條件

支援的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（核心小版本14.6.4.0及以上）

select version();

使用限制

• 在帶有全域索引的分區表上，依然支援ATTACH/DETACH子分區。

• 在建立索引的文法中，通過指定GLOBAL關鍵字來建立全域索引。如果不指定，則預設建立局部索引。

• 全域索引支援CONCURRENTLY並發建立。

• 非分區表、帶有子分區的子表上不支援建立全域索引。

• 全域索引不支援運算式索引。

• 無法在分區表的分區列上建立全域索引。

文法

建立全域索引。

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 ]

樣本

加速非分區鍵查詢

1. 建立以時間列作為分區鍵的分區表。

   CREATE TABLE partition_range (
       id INT,
       a INT,
       b INT,
       created_date TIMESTAMP WITHOUT TIME ZONE
   ) PARTITION BY RANGE (created_date);
   
   CREATE TABLE partition_range_part01 PARTITION OF partition_range FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
   CREATE TABLE partition_range_part02 PARTITION OF partition_range FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
   CREATE TABLE partition_range_part03 PARTITION OF partition_range FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

2. 對該分區表進行非分區鍵的條件查詢。

   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   結果顯示如下：將會對所有的子分區進行掃描，無法利用到分區表的分區裁剪功能。

                           QUERY PLAN
   ------------------------------------------------------------
    Append
      ->  Seq Scan on partition_range_part01 partition_range_1
            Filter: (id = 1)
      ->  Seq Scan on partition_range_part02 partition_range_2
            Filter: (id = 1)
      ->  Seq Scan on partition_range_part03 partition_range_3
            Filter: (id = 1)
   (7 rows)

3. 對該分區表建立局部索引，並重新執行查詢。

   CREATE INDEX partition_range_idx_local ON partition_range(id);
   
   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   結果顯示如下：由於局部索引建立在每一個子分區上，所以依然需要掃描所有子分區的局部索引。

                                               QUERY PLAN
   --------------------------------------------------------------------------------------------------
    Append
      ->  Index Scan using partition_range_part01_id_idx on partition_range_part01 partition_range_1
            Index Cond: (id = 1)
      ->  Index Scan using partition_range_part02_id_idx on partition_range_part02 partition_range_2
            Index Cond: (id = 1)
      ->  Index Scan using partition_range_part03_id_idx on partition_range_part03 partition_range_3
            Index Cond: (id = 1)
   (7 rows)

4. 使用GLOBAL關鍵字對該分區表建立全域索引，並重新執行查詢。

   CREATE INDEX partition_range_idx_global ON partition_range(id) GLOBAL;
   
   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   結果顯示如下：資料庫將使用全域索引直接找到資料所在的子分區。

                                 QUERY PLAN
   -----------------------------------------------------------------------
    Global Index Scan using partition_range_idx_global on partition_range
      Index Cond: (id = 1)
   (2 rows)

非分區鍵的唯一約束

依然使用上述樣本中的分區表，其分區鍵為時間created_date，但其真正的唯一約束列為id。

CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "partition_range" lacks column "created_date" which is part of the partition key.

而對全域索引添加唯一約束則不會有這個限制，如下所示。

CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id) GLOBAL;

效能測試

使用pgbench工具產生scale為80000的資料，分別建立分區表與非分區表。

非分區鍵上的點查效能

非分區鍵上的TPC-B效能

結論

全域索引對分區表的點查和DML能夠帶來一個數量級的效能提升。