[[422438]]

本文轉載自微信公眾號「數據和云」，作者高云龍。轉載本文請聯系數據和云公眾號。

一、概述

openGauss分區表支持兩種索引：全局(global)索引和本地(local)索引。

分區表創建索引不支持concurrently語法，默認索引是全局索引，創建本地索引需要指定local。

創建主鍵約束和唯一約束必須要包含分區字段，創建本地唯一索引也必須要包含分區字段，但是創建全局唯一索引沒有這個限制。

postgres=# create index concurrently on part_index_test(col2,col1,col3); 
ERROR:  cannot create concurrent partitioned indexes 

本文主要闡述添加或刪除分區對索引的影響。

數據庫版本：openGauss 1.1.0

二、測試

1.建表語句

范圍分區中的間隔語法可以自動追加分區表，這里以間隔分區表為例：

create table part_index_test( 
partid varchar(32) not null, 
col1 varchar(2) not null, 
col2 date not null, 
col3 varchar(8) not null 
)partition by range(col2) 
interval('1 day') 
( 
partition part1 values less than ('20210331'), 
partition part2 values less than ('20210401') 
); 

全局索引：

create index on part_index_test(col2,col1,col3); 

本地索引：

create index on part_index_test(col2,col1,col3) local; 

2.測試數據

間隔分區是以1天為單位，所以新增一天的數據，會自動增加一個以sys_p開頭的自增分區：

insert into part_index_test select generate_series(1,1000),'1','20210401',generate_series(1,1000); 
insert into part_index_test select generate_series(1,1000),'1','20210402',generate_series(1,1000); 
insert into part_index_test select generate_series(1,1000),'1','20210403',generate_series(1,1000); 
insert into part_index_test select generate_series(1,1000),'1','20210404',generate_series(1,1000); 
insert into part_index_test select generate_series(1,1000),'1','20210405',generate_series(1,1000); 
insert into part_index_test select generate_series(1,1000),'1','20210406',generate_series(1,1000); 

3.刪除分區語法

• 先通過pg_partition系統表查看分區

select relname,parttype,parentid,boundaries from pg_partition; 

• 默認刪除分區：

alter table part_index_test DROP PARTITIONpartition_name; 

• update global index 方式刪除分區：

alter table part_index_test DROP PARTITION partition_name update global index; 

4.索引使用

explain select * from part_index_test where col2=$1 and partid=$2; 

5.測試結果

總結：

1、添加/刪除分區不影響本地索引使用。

2、添加分區不影響全局索引使用，默認刪除分區方式，全局索引失效，需要對全局索引重建;update global index方式刪除分區，不影響全局索引使用。

三、示例

1.分區表準備

• 創建分區表

create table part_range_lessthan_int( 
id serial primary key, 
col1 varchar(16)) 
partition by range(id) 
( 
partition p1 values less than(1000), 
partition p2 values less than(2000), 
partition p3 values less than(3000), 
partition p4 values less than(maxvalue) 
); 

• 創建全局索引

create unique index on part_range_lessthan_int(col1); 

• 初始化數據

insert into part_range_lessthan_int select generate_series(1,5000),'tuple'||generate_series(1,5000); 

• 默認刪除分區SQL

select now();alter table part_range_lessthan_int drop partition p1; 

• update global index 刪除分區SQL

select now();alter table part_range_lessthan_int drop partition p1 update global index; 

• 查詢SQL

analyze;select now();explain select * from part_range_lessthan_int where col1='tuple2500'; 

2.默認刪除分區語法

3.update global index 刪除分區

關于作者

高云龍，云和恩墨服務總監，長期從事PG運維工作，目前在支持openGauss生態發展。