[[422438]]

本文轉(zhuǎn)載自微信公眾號「數(shù)據(jù)和云」，作者高云龍。轉(zhuǎn)載本文請聯(lián)系數(shù)據(jù)和云公眾號。

一、概述

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

分區(qū)表創(chuàng)建索引不支持concurrently語法，默認(rèn)索引是全局索引，創(chuàng)建本地索引需要指定local。

創(chuàng)建主鍵約束和唯一約束必須要包含分區(qū)字段，創(chuàng)建本地唯一索引也必須要包含分區(qū)字段，但是創(chuàng)建全局唯一索引沒有這個(gè)限制。

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

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

數(shù)據(jù)庫版本：openGauss 1.1.0

二、測試

1.建表語句

范圍分區(qū)中的間隔語法可以自動(dòng)追加分區(qū)表，這里以間隔分區(qū)表為例：

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.測試數(shù)據(jù)

間隔分區(qū)是以1天為單位，所以新增一天的數(shù)據(jù)，會(huì)自動(dòng)增加一個(gè)以sys_p開頭的自增分區(qū)：

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.刪除分區(qū)語法

• 先通過pg_partition系統(tǒng)表查看分區(qū)

select relname,parttype,parentid,boundaries from pg_partition; 

• 默認(rèn)刪除分區(qū)：

alter table part_index_test DROP PARTITIONpartition_name; 

• update global index 方式刪除分區(qū)：

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.測試結(jié)果

總結(jié)：

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

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

三、示例

1.分區(qū)表準(zhǔn)備

• 創(chuàng)建分區(qū)表

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) 
); 

• 創(chuàng)建全局索引

create unique index on part_range_lessthan_int(col1); 

• 初始化數(shù)據(jù)

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

• 默認(rèn)刪除分區(qū)SQL

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

• update global index 刪除分區(qū)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.默認(rèn)刪除分區(qū)語法

3.update global index 刪除分區(qū)

關(guān)于作者

高云龍，云和恩墨服務(wù)總監(jiān)，長期從事PG運(yùn)維工作，目前在支持openGauss生態(tài)發(fā)展。