全局索引

前提条件

支持的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能够带来一个数量级的性能提升。