postgresql 13 数据库分区表性能之一 pg_pathman 在分区数为 64、128、256、512、1024 的简单测试
颛孙轩昂
os: centos 7.8
db: postgresql 13.1
版本
# cat /etc/centos-release
CentOS Linux release 7.8.2003 (Core)
#
# yum list installed |grep -i postgresql
postgresql13.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-contrib.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-devel.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-docs.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-libs.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-llvmjit.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-plperl.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-plpython3.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-pltcl.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-server.x86_64 13.1-1PGDG.rhel7 @pgdg13
postgresql13-test.x86_64 13.1-1PGDG.rhel7 @pgdg13
# yum list installed |grep -i pathman
pg_pathman_13.x86_64 1.5.12-1.rhel7 @pgdg13
# su - postgres
Last login: Fri Dec 18 15:39:51 CST 2020 on pts/0
$ psql
psql (13.1)
Type "help" for help.
postgres=# select name,setting from pg_settings where name like '%partition%';
name | setting
-----------------------------------+---------
enable_partition_pruning | on
enable_partitionwise_aggregate | off
enable_partitionwise_join | off
pg_pathman.enable_auto_partition | on
pg_pathman.enable_partitionfilter | on
pg_pathman.enable_partitionrouter | off
(6 rows)
postgres=# select name,setting from pg_settings where name like 'pg_pathman%' order by name;
name | setting
--------------------------------------+----------
pg_pathman.enable | on
pg_pathman.enable_auto_partition | on
pg_pathman.enable_bounds_cache | on
pg_pathman.enable_partitionfilter | on
pg_pathman.enable_partitionrouter | off
pg_pathman.enable_runtimeappend | on
pg_pathman.enable_runtimemergeappend | on
pg_pathman.insert_into_fdw | postgres
pg_pathman.override_copy | on
(9 rows)
postgres=# create database yewudb;
yewudb=# \c yewudb
yewudb=# create extension pg_pathman;
yewudb=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+----------------------------------
pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
yewudb=# \timing
64分区
drop table if exists tmp_hash64 cascade;
create table tmp_hash64(
id bigint not null,
name text not null,
insert_timestamp timestamp with time zone not null
);
create index idx_tmp_hash64_id on tmp_hash64(id)
;
select create_hash_partitions(
'tmp_hash64'::regclass, -- 主表oid
'id', -- 分区字段，一定要not null约束
64, -- 分区表数量
false -- 不立即将数据从主表迁移到分区
);
select set_enable_parent('tmp_hash64'::regclass, false);
插入数据
insert into tmp_hash64
select id,
id::text,
current_date - mod(id,1000)
from generate_series(1,1000000) as id;
INSERT 0 1000000
Time: 10710.247 ms (00:10.710)
有分区键
explain
select id,
name,
insert_timestamp
from tmp_hash64
where id=99999
;
QUERY PLAN
-------------------------------------------------------------------------------------------
Index Scan using tmp_hash64_12_id_idx on tmp_hash64_12 (cost=0.29..8.30 rows=1 width=22)
Index Cond: (id = 99999)
(2 rows)
Time: 4.078 ms
select id,
name,
insert_timestamp
from tmp_hash64
where id=99999
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 0.674 ms
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash64 a,
tmp_hash64 b
where 1=1
and a.id=99999
and a.id=b.id
;
QUERY PLAN
------------------------------------------------------------------------------------------------
Nested Loop (cost=9.41..222.16 rows=2916 width=48)
-> Bitmap Heap Scan on tmp_hash64_12 a (cost=4.70..92.79 rows=54 width=48)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash64_12_id_idx (cost=0.00..4.69 rows=54 width=0)
Index Cond: (id = 99999)
-> Materialize (cost=4.70..93.06 rows=54 width=8)
-> Bitmap Heap Scan on tmp_hash64_12 b (cost=4.70..92.79 rows=54 width=8)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash64_12_id_idx (cost=0.00..4.69 rows=54 width=0)
Index Cond: (id = 99999)
(10 rows)
Time: 1.838 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash64 a,
tmp_hash64 b
where 1=1
and a.id=99999
and a.id=b.id
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 0.798 ms
无分区键
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash64 a,
tmp_hash64 b
where 1=1
and a.name='99999'
and a.name=b.name
;
(264 rows)
Time: 17.679 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash64 a,
tmp_hash64 b
where 1=1
and a.name='99999'
and a.name=b.name
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 887.742 ms
128分区
drop table if exists tmp_hash128 cascade;
create table tmp_hash128(
id bigint not null,
name text not null,
insert_timestamp timestamp with time zone not null
);
create index idx_tmp_hash128_id on tmp_hash128(id)
;
select create_hash_partitions(
'tmp_hash128'::regclass, -- 主表oid
'id', -- 分区字段，一定要not null约束
128, -- 分区表数量
false -- 不立即将数据从主表迁移到分区
);
select set_enable_parent('tmp_hash128'::regclass, false);
插入数据
insert into tmp_hash128
select id,
id::text,
current_date - mod(id,1000)
from generate_series(1,1000000) as id;
INSERT 0 1000000
Time: 11580.045 ms (00:11.580)
有分区键
explain
select id,
name,
insert_timestamp
from tmp_hash128
where id=99999
;
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using tmp_hash128_12_id_idx on tmp_hash128_12 (cost=0.28..8.30 rows=1 width=22)
Index Cond: (id = 99999)
(2 rows)
Time: 7.222 ms
select id,
name,
insert_timestamp
from tmp_hash128
where id=99999
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 5.419 ms
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash128 a,
tmp_hash128 b
where 1=1
and a.id=99999
and a.id=b.id
;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop (cost=8.98..108.14 rows=729 width=48)
-> Bitmap Heap Scan on tmp_hash128_12 a (cost=4.49..49.48 rows=27 width=48)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash128_12_id_idx (cost=0.00..4.48 rows=27 width=0)
Index Cond: (id = 99999)
-> Materialize (cost=4.49..49.62 rows=27 width=8)
-> Bitmap Heap Scan on tmp_hash128_12 b (cost=4.49..49.48 rows=27 width=8)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash128_12_id_idx (cost=0.00..4.48 rows=27 width=0)
Index Cond: (id = 99999)
(10 rows)
Time: 2.618 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash128 a,
tmp_hash128 b
where 1=1
and a.id=99999
and a.id=b.id
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 0.708 ms
无分区键
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash128 a,
tmp_hash128 b
where 1=1
and a.name='99999'
and a.name=b.name
;
(520 rows)
Time: 49.063 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash128 a,
tmp_hash128 b
where 1=1
and a.name='99999'
and a.name=b.name
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 225.191 ms
256分区
drop table if exists tmp_hash256 cascade;
create table tmp_hash256(
id bigint not null,
name text not null,
insert_timestamp timestamp with time zone not null
);
create index idx_tmp_hash256_id on tmp_hash256(id)
;
select create_hash_partitions(
'tmp_hash256'::regclass, -- 主表oid
'id', -- 分区字段，一定要not null约束
256, -- 分区表数量
false -- 不立即将数据从主表迁移到分区
);
select set_enable_parent('tmp_hash256'::regclass, false);
插入数据
insert into tmp_hash256
select id,
id::text,
current_date - mod(id,1000)
from generate_series(1,1000000) as id;
INSERT 0 1000000
Time: 10985.800 ms (00:10.986)
有分区键
explain
select id,
name,
insert_timestamp
from tmp_hash256
where id=99999
;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tmp_hash256_12 (cost=4.39..28.11 rows=14 width=48)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash256_12_id_idx (cost=0.00..4.39 rows=14 width=0)
Index Cond: (id = 99999)
(4 rows)
Time: 2.453 ms
select id,
name,
insert_timestamp
from tmp_hash256
where id=99999
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 8.704 ms
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash256 a,
tmp_hash256 b
where 1=1
and a.id=99999
and a.id=b.id
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.56..12.61 rows=1 width=48)
-> Index Scan using tmp_hash256_12_id_idx on tmp_hash256_12 a (cost=0.28..8.30 rows=1 width=22)
Index Cond: (id = 99999)
-> Index Only Scan using tmp_hash256_12_id_idx on tmp_hash256_12 b (cost=0.28..4.30 rows=1 width=8)
Index Cond: (id = 99999)
(5 rows)
Time: 0.800 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash256 a,
tmp_hash256 b
where 1=1
and a.id=99999
and a.id=b.id
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 0.839 ms
无分区键
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash256 a,
tmp_hash256 b
where 1=1
and a.name='99999'
and a.name=b.name
;
(1032 rows)
Time: 76.555 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash256 a,
tmp_hash256 b
where 1=1
and a.name='99999'
and a.name=b.name
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 268.585 ms
512分区
drop table if exists tmp_hash512 cascade;
create table tmp_hash512(
id bigint not null,
name text not null,
insert_timestamp timestamp with time zone not null
);
create index idx_tmp_hash512_id on tmp_hash512(id)
;
select create_hash_partitions(
'tmp_hash512'::regclass, -- 主表oid
'id', -- 分区字段，一定要not null约束
512, -- 分区表数量
false -- 不立即将数据从主表迁移到分区
);
select set_enable_parent('tmp_hash512'::regclass, false);
插入数据
insert into tmp_hash512
select id,
id::text,
current_date - mod(id,1000)
from generate_series(1,1000000) as id;
INSERT 0 1000000
Time: 12022.231 ms (00:12.022)
有分区键
explain
select id,
name,
insert_timestamp
from tmp_hash512
where id=99999
;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tmp_hash512_268 (cost=4.33..16.19 rows=7 width=48)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash512_268_id_idx (cost=0.00..4.33 rows=7 width=0)
Index Cond: (id = 99999)
(4 rows)
Time: 7.290 ms
select id,
name,
insert_timestamp
from tmp_hash512
where id=99999
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 22.381 ms
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash512 a,
tmp_hash512 b
where 1=1
and a.id=99999
and a.id=b.id
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.56..12.60 rows=1 width=48)
-> Index Scan using tmp_hash512_268_id_idx on tmp_hash512_268 a (cost=0.28..8.29 rows=1 width=22)
Index Cond: (id = 99999)
-> Index Only Scan using tmp_hash512_268_id_idx on tmp_hash512_268 b (cost=0.28..4.29 rows=1 width=8)
Index Cond: (id = 99999)
(5 rows)
Time: 21.018 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash512 a,
tmp_hash512 b
where 1=1
and a.id=99999
and a.id=b.id
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 1.117 ms
无分区键
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash512 a,
tmp_hash512 b
where 1=1
and a.name='99999'
and a.name=b.name
;
JIT:
Functions: 3073
Options: Inlining false, Optimization false, Expressions true, Deforming true
(2059 rows)
Time: 959.745 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash512 a,
tmp_hash512 b
where 1=1
and a.name='99999'
and a.name=b.name
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 434.730 ms
1024分区
drop table if exists tmp_hash1024 cascade;
create table tmp_hash1024(
id bigint not null,
name text not null,
insert_timestamp timestamp with time zone not null
);
create index idx_tmp_hash1024_id on tmp_hash1024(id)
;
select create_hash_partitions(
'tmp_hash1024'::regclass, -- 主表oid
'id', -- 分区字段，一定要not null约束
1024, -- 分区表数量
false -- 不立即将数据从主表迁移到分区
);
select set_enable_parent('tmp_hash1024'::regclass, false);
插入数据
insert into tmp_hash1024
select id,
id::text,
current_date - mod(id,1000)
from generate_series(1,1000000) as id;
INSERT 0 1000000
Time: 12743.607 ms (00:12.744)
有分区键
explain
select id,
name,
insert_timestamp
from tmp_hash1024
where id=99999
;
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on tmp_hash1024_780 (cost=4.32..12.79 rows=5 width=48)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash1024_780_id_idx (cost=0.00..4.31 rows=5 width=0)
Index Cond: (id = 99999)
(4 rows)
Time: 1.513 ms
select id,
name,
insert_timestamp
from tmp_hash1024
where id=99999
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 0.922 ms
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash1024 a,
tmp_hash1024 b
where 1=1
and a.id=99999
and a.id=b.id
;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Nested Loop (cost=8.63..25.90 rows=25 width=48)
-> Bitmap Heap Scan on tmp_hash1024_780 a (cost=4.32..12.79 rows=5 width=48)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash1024_780_id_idx (cost=0.00..4.31 rows=5 width=0)
Index Cond: (id = 99999)
-> Materialize (cost=4.32..12.81 rows=5 width=8)
-> Bitmap Heap Scan on tmp_hash1024_780 b (cost=4.32..12.79 rows=5 width=8)
Recheck Cond: (id = 99999)
-> Bitmap Index Scan on tmp_hash1024_780_id_idx (cost=0.00..4.31 rows=5 width=0)
Index Cond: (id = 99999)
(10 rows)
Time: 15.729 ms
select a.id,
a.name,
a.insert_timestamp
from tmp_hash1024 a,
tmp_hash1024 b
where 1=1
and a.id=99999
and a.id=b.id
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 0.539 ms
无分区键
explain
select a.id,
a.name,
a.insert_timestamp
from tmp_hash1024 a,
tmp_hash1024 b
where 1=1
and a.name='99999'
and a.name=b.name
;
JIT:
Functions: 6145
Options: Inlining false, Optimization false, Expressions true, Deforming true
(4107 rows)
Time: 1911.705 ms (00:01.912)
select a.id,
a.name,
a.insert_timestamp
from tmp_hash1024 a,
tmp_hash1024 b
where 1=1
and a.name='99999'
and a.name=b.name
;
id | name | insert_timestamp
-------+-------+------------------------
99999 | 99999 | 2018-03-25 00:00:00+08
(1 row)
Time: 578.505 ms
参考：
类似资料：