PostgreSQL 与 openGauss 之分区性能
概述
PostgreSQL 与 openGauss 分区表定义差异,请参考https://www.modb.pro/db/41393。
openGauss1.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
添加分区表
PostgreSQL 数据库:
--创建父表
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);
openGauss 数据库:
--创建分区表
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 查询性能最快。
此测试受限于服务器环境,数据仅做参考比对。
标签:hash,分区,partition,col1,part,ms,table,PostgreSQL,openGauss From: https://www.cnblogs.com/renxyz/p/18073584