PostgreSQL 与 MogDB/openGauss 之 HASH分区性能

概述

PostgreSQL 与 MogDB分区表定义差异，请参考https://www.modb.pro/db/41393

MogDB 1.1.0开始支持hash/list分区，hash分区表最多支持64个分区，否则会报
ERROR: Un-support feature
DETAIL: The partition’s length should be less than 65.

本次对PostgreSQL 和 openGauss 64个子分区表的常规操作对比

服务器配置：虚拟机8G4C50G
数据库版本：PostgreSQL13.1、openGauss1.1.0

添加分区表

PG数据库

--创建父表
CREATE TABLE partition_table(
 id          int,
 col1        character varying(16),
 create_time timestamptz
) PARTITION BY HASH(id);

--添加分区
SELECT 'CREATE TABLE partition_table_' || n || ' PARTITION of partition_table FOR VALUES WITH (MODULUS 64, REMAINDER  ' || n || ');' FROM generate_series(0,63) as n ;\gexec

--初始化数据
INSERT INTO partition_table(id,col1,create_time) SELECT round(100000000*random()), n || '_col1',now() FROM generate_series(1,10000000) n;

--添加索引
CREATE INDEX ON partition_table USING BTREE(id);
CREATE INDEX ON partition_table USING BTREE(col1);

OG数据库

--创建分区表
create table partition_table(
	id int,
	col1 varchar(16),
	create_time timestamptz default now()) 
partition by hash(id)
(partition part_hash_1,
partition part_hash_2,
partition part_hash_3,
partition part_hash_4,
partition part_hash_5,
partition part_hash_6,
partition part_hash_7,
partition part_hash_8,
partition part_hash_9,
partition part_hash_10,
partition part_hash_11,
partition part_hash_12,
partition part_hash_13,
partition part_hash_14,
partition part_hash_15,
partition part_hash_16,
partition part_hash_17,
partition part_hash_18,
partition part_hash_19,
partition part_hash_20,
partition part_hash_21,
partition part_hash_22,
partition part_hash_23,
partition part_hash_24,
partition part_hash_25,
partition part_hash_26,
partition part_hash_27,
partition part_hash_28,
partition part_hash_29,
partition part_hash_30,
partition part_hash_31,
partition part_hash_32,
partition part_hash_33,
partition part_hash_34,
partition part_hash_35,
partition part_hash_36,
partition part_hash_37,
partition part_hash_38,
partition part_hash_39,
partition part_hash_40,
partition part_hash_41,
partition part_hash_42,
partition part_hash_43,
partition part_hash_44,
partition part_hash_45,
partition part_hash_46,
partition part_hash_47,
partition part_hash_48,
partition part_hash_49,
partition part_hash_50,
partition part_hash_51,
partition part_hash_52,
partition part_hash_53,
partition part_hash_54,
partition part_hash_55,
partition part_hash_56,
partition part_hash_57,
partition part_hash_58,
partition part_hash_59,
partition part_hash_60,
partition part_hash_61,
partition part_hash_62,
partition part_hash_63,
partition part_hash_64);

--初始化数据
INSERT INTO partition_table(id,col1,create_time) SELECT round(100000000*random()), n || '_col1',now() FROM generate_series(1,10000000) n;

--添加全局索引
CREATE INDEX ON partition_table USING BTREE(id);
CREATE INDEX ON partition_table USING BTREE(col1);

--添加本地索引
CREATE INDEX ON partition_table USING BTREE(id) local;
CREATE INDEX ON partition_table USING BTREE(col1) local;

测试方法

采用pgbench压测工具，自定义压测脚本的方式来对比

cat bench.sql
\set idpp random(1,100000)
--insert into partition_table values(:idpp,:idpp||'_col1',now());
--update partition_table set create_time=now() where id=:idpp;
--update partition_table set create_time=now() where col1=:idpp||'_col1';
--select * from partition_table where id=:idpp;
--select * from partition_table where col1=:idpp||'_col1';

pgbench -p 5432 -j 30 -c 30 -M prepared -T 30 -n yunlong -f bench.sql

结果对比

结果对比发现，
1、Postgresql13.1 版本在分区方面总来看优越于openGauss1.1.0
2、opengauss 全局索引会比本地索引性能更好，但全局索引维护成本高
3、非分区键查询，带全局索引的opengauss查询性能最快

此测试受限于服务器环境，数据仅做参考比对