本文转载自微信公众号「数据和云」，作者高云龙。转载本文请联系数据和云公众号。
一、概述
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.测试结果
|
- |
添加分区 |
删除分区 |
备注 |
|
全局索引 |
生效 |
默认语法失效/update global index语法生效 |
索引失效后，需要reindex |
|
本地索引 |
生效 |
生效 |
总结：
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生态发展。
