磐维数据库分区表索引可用性测试

概述

• 分区表支持两种索引：全局（global）索引和本地（local）索引，默认索引是全局索引，创建本地索引需要指定local。
• 测试环境：
  • 数据库版本 PanWeiDB_V2.0-S3.0.1_B01
  • 兼容模式：Oracle

postgres=# select pw_version();
                                 pw_version                                  
-----------------------------------------------------------------------------
 (PanWeiDB_V2.0-S3.0.1_B01) compiled at 2024-09-29 19:37:43 commit d086caf  +
  product name:PanWeiDB                                                     +
  version:V2.0-S3.0.1_B01                                                   +
  commit:d086caf                                                            +
  openGauss version:5.0.0                                                   +
  host:x86_64-pc-linux-gnu
(1 row)

postgres=# select datname,datcompatibility from pg_database;
  datname  | datcompatibility 
-----------+------------------
 template1 | A
 template0 | A
 panweidb  | A
 postgres  | A
(4 rows)

创建测试表和索引

• 创建一个range分区表，并创建一个全局索引，一个分区索引

CREATE TABLE range_t (
id INT NOT NULL ,
fname VARCHAR(30),
lname VARCHAR(30),
birth DATE NOT NULL )
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (50),
PARTITION p2 VALUES LESS THAN (100),
PARTITION p3 VALUES LESS THAN (150));

create index idx1 on range_t(id);
create index idx2 on range_t(birth) local;

• 查看表结构
  
• 查看分区信息

postgres=# select relname,parttype,parentid,boundaries from pg_partition;
   relname    | parttype | parentid | boundaries 
--------------+----------+----------+------------
 range_t      | r        |    19591 | 
 p1           | p        |    19591 | {50}
 p2           | p        |    19591 | {100}
 p3           | p        |    19591 | {150}
 p1_birth_idx | x        |    19599 | 
 p2_birth_idx | x        |    19599 | 
 p3_birth_idx | x        |    19599 | 
(7 rows)

• 插入数据

postgres=# INSERT INTO range_t VALUES  (107, 'Mary', 'Jones','1997-10-17'), (16, 'Frank','White','2001-05-07'), (55, 'Linda', 'Black','1991-03-19');
INSERT 0 3

• 查看执行计划

postgres=# explain select * from range_t where id=16;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Bitmap Heap Scan on range_t  (cost=4.27..7.39 rows=2 width=168)
   Recheck Cond: (id = 16)
   ->  Bitmap Index Scan on idx1  (cost=0.00..4.26 rows=2 width=0)
         Index Cond: (id = 16)
(4 rows)

postgres=# explain select * from range_t where birth>'2000-01-01';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..15.16 rows=138 width=168)
   Iterations: 3
   ->  Partitioned Seq Scan on range_t  (cost=0.00..15.16 rows=138 width=168)
         Filter: (birth > '2000-01-01'::oradate)
         Selected Partitions:  1..3
(5 rows)

进行表分区操作

• 通过不同的分区操作，观察全局索引及本地索引的失效情况

添加分区

• 添加分区

postgres=# alter table range_t add partition p4 VALUES LESS THAN (200);
ALTER TABLE

• 观察索引状态，全局索引和本地索引都正常
  

删除分区

• 删除分区

postgres=# alter table range_t drop partition p4;
ALTER TABLE

• 查看索引状态，可以发现全局索引不可用，id=16的查询不再走索引扫描
  

postgres=# select a.relname,t.indisusable,t.indisvalid from pg_class a,pg_index t where t.indexrelid=a.oid and a.relname='idx1';
 relname | indisusable | indisvalid 
---------+-------------+------------
 idx1    | f           | t
(1 row)

postgres=# explain select * from range_t where id=16;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..8.16 rows=2 width=168)
   Iterations: 1
   ->  Partitioned Seq Scan on range_t  (cost=0.00..8.16 rows=2 width=168)
         Filter: (id = 16)
         Selected Partitions:  1
(5 rows)

• 重建索引，索引状态变为可用

postgres=# reindex index idx1;
REINDEX
postgres=# select a.relname,t.indisusable,t.indisvalid from pg_class a,pg_index t where t.indexrelid=a.oid and a.relname='idx1';
 relname | indisusable | indisvalid 
---------+-------------+------------
 idx1    | t           | t
(1 row)

postgres=# explain select * from range_t where id=16;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Bitmap Heap Scan on range_t  (cost=4.27..7.39 rows=2 width=168)
   Recheck Cond: (id = 16)
   ->  Bitmap Index Scan on idx1  (cost=0.00..4.26 rows=2 width=0)
         Index Cond: (id = 16)
(4 rows)

• 生产环境可以选择在线创建索引，可以不阻塞DML

postgres=# create index concurrently idx1_new on range_t(id);
CREATE INDEX

• 删除分区时，加上update global index子句，全局索引不失效

postgres=# alter table range_t drop partition p3 update global index;
ALTER TABLE

清空分区

• truncate partition测试结果同drop partition