digoal
2019-03-31
PostgreSQL , 原生分区表 , ppas , lock , cache , plan , 性能 , pg_pathman
PostgreSQL 12 原生分区表性能大幅度提升。已与pg_pathman持平。此前用户可以使用pg_pathman加速或者使用阿里云PPAS。
《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) - 分区表性能优化 (堪比pg_pathman)》
性能提升23.57倍。
| 版本 | TPS |
|---|---|
| PG 11 | 11348 |
| PG 12 | 267447 |
在12前，使用原生分区表，PLAN时需要对所有分区创建RangeTblEntry与RelOptInfo结果，同时需要加锁。
pg_pathman插件的分区功能使用custom plan的方式，没有以上问题。所以很多用户会选择PG_PATHMAN来作为分区表使用。（当然pg_pathman还支持interval分区（自动添加分区）等功能。）
现在PG 12，原生分区功能，已经不存在如上问题。
PG 12带来的提升包括：
1、内存使用量降低
2、plan更快
3、整体TPS性能提升
Speed up planning when partitions can be pruned at plan time.
Previously, the planner created RangeTblEntry and RelOptInfo structs
for every partition of a partitioned table, even though many of them
might later be deemed uninteresting thanks to partition pruning logic.
This incurred significant overhead when there are many partitions.
Arrange to postpone creation of these data structures until after
we've processed the query enough to identify restriction quals for
the partitioned table, and then apply partition pruning before not
after creation of each partition's data structures. In this way
we need not open the partition relations at all for partitions that
the planner has no real interest in.
For queries that can be proven at plan time to access only a small
number of partitions, this patch improves the practical maximum
number of partitions from under 100 to perhaps a few thousand.
Amit Langote, reviewed at various times by Dilip Kumar, Jesper Pedersen,
Yoshikazu Imai, and David Rowley
Discussion: https://postgr.es/m/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp
do language plpgsql $$
declare
begin
execute format('create unlogged table p (id int primary key, info text, crt_time timestamp) partition by hash (id)');
for i in 0..255 loop
execute format('create unlogged table p%s partition of p for values WITH (MODULUS %s, REMAINDER %s)', i, 256, i);
end loop;
end;
$$;
PG 12 在操作单一分区时，很简单的方法可以界定以上PATCH，LOCK仅单个分区以及主分区。
postgres=# begin;
BEGIN
postgres=# insert into p values (1,'test',now());
INSERT 0 1
postgres=# select pid,relation::regclass,* from pg_locks ;
pid | relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
-------+----------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
57519 | p184 | relation | 13577 | 21711 | | | | | | | | 3/1106134 | 57519 | RowExclusiveLock | t | t
57519 | pg_locks | relation | 13577 | 12137 | | | | | | | | 3/1106134 | 57519 | AccessShareLock | t | t
57519 | p | relation | 13577 | 20234 | | | | | | | | 3/1106134 | 57519 | AccessShareLock | t | t
57519 | p | relation | 13577 | 20234 | | | | | | | | 3/1106134 | 57519 | RowExclusiveLock | t | t
57519 | | virtualxid | | | | | 3/1106134 | | | | | 3/1106134 | 57519 | ExclusiveLock | t | t
57519 | | transactionid | | | | | | 34480647 | | | | 3/1106134 | 57519 | ExclusiveLock | t | f
(6 rows)
PG 12 以前的版本，在操作单一分区时，LOCK所有分区。
postgres=# begin;
BEGIN
postgres=# insert into p values (1,'test',now());
INSERT 0 1
postgres=# select pid,relation::regclass,* from pg_locks ;
pid | relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
-------+----------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
57451 | p13 | relation | 13285 | 1920786 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p12 | relation | 13285 | 1920778 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p11 | relation | 13285 | 1920770 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p10 | relation | 13285 | 1920762 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p9 | relation | 13285 | 1920754 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p8 | relation | 13285 | 1920746 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p7 | relation | 13285 | 1920738 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p6 | relation | 13285 | 1920730 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p5 | relation | 13285 | 1920722 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p4 | relation | 13285 | 1920714 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p3 | relation | 13285 | 1920706 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p2 | relation | 13285 | 1920698 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p1 | relation | 13285 | 1920690 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | p0 | relation | 13285 | 1920682 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | pg_locks | relation | 13285 | 11645 | | | | | | | | 3/60843 | 57451 | AccessShareLock | t | t
57451 | p | relation | 13285 | 1920677 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | t
57451 | | virtualxid | | | | | 3/60843 | | | | | 3/60843 | 57451 | ExclusiveLock | t | t
57451 | p197 | relation | 13285 | 1922258 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p250 | relation | 13285 | 1922682 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p152 | relation | 13285 | 1921898 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p20 | relation | 13285 | 1920842 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p150 | relation | 13285 | 1921882 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p112 | relation | 13285 | 1921578 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p210 | relation | 13285 | 1922362 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p131 | relation | 13285 | 1921730 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p198 | relation | 13285 | 1922266 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p223 | relation | 13285 | 1922466 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p165 | relation | 13285 | 1922002 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p43 | relation | 13285 | 1921026 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p168 | relation | 13285 | 1922026 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p95 | relation | 13285 | 1921442 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p98 | relation | 13285 | 1921466 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p160 | relation | 13285 | 1921962 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p50 | relation | 13285 | 1921082 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p71 | relation | 13285 | 1921250 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p44 | relation | 13285 | 1921034 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p51 | relation | 13285 | 1921090 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p216 | relation | 13285 | 1922410 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p63 | relation | 13285 | 1921186 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p18 | relation | 13285 | 1920826 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p101 | relation | 13285 | 1921490 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p253 | relation | 13285 | 1922706 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p215 | relation | 13285 | 1922402 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p48 | relation | 13285 | 1921066 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p225 | relation | 13285 | 1922482 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p97 | relation | 13285 | 1921458 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p36 | relation | 13285 | 1920970 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p17 | relation | 13285 | 1920818 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p142 | relation | 13285 | 1921818 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p171 | relation | 13285 | 1922050 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p237 | relation | 13285 | 1922578 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p94 | relation | 13285 | 1921434 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p76 | relation | 13285 | 1921290 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p147 | relation | 13285 | 1921858 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p231 | relation | 13285 | 1922530 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p187 | relation | 13285 | 1922178 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p30 | relation | 13285 | 1920922 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p108 | relation | 13285 | 1921546 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p177 | relation | 13285 | 1922098 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p64 | relation | 13285 | 1921194 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p175 | relation | 13285 | 1922082 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p54 | relation | 13285 | 1921114 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p207 | relation | 13285 | 1922338 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p243 | relation | 13285 | 1922626 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p217 | relation | 13285 | 1922418 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p119 | relation | 13285 | 1921634 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p45 | relation | 13285 | 1921042 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p73 | relation | 13285 | 1921266 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p83 | relation | 13285 | 1921346 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p103 | relation | 13285 | 1921506 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p55 | relation | 13285 | 1921122 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p52 | relation | 13285 | 1921098 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p251 | relation | 13285 | 1922690 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p34 | relation | 13285 | 1920954 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p32 | relation | 13285 | 1920938 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p69 | relation | 13285 | 1921234 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p185 | relation | 13285 | 1922162 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p65 | relation | 13285 | 1921202 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p192 | relation | 13285 | 1922218 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p236 | relation | 13285 | 1922570 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p137 | relation | 13285 | 1921778 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p252 | relation | 13285 | 1922698 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p218 | relation | 13285 | 1922426 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p179 | relation | 13285 | 1922114 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p129 | relation | 13285 | 1921714 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p158 | relation | 13285 | 1921946 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p213 | relation | 13285 | 1922386 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p208 | relation | 13285 | 1922346 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p59 | relation | 13285 | 1921154 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p15 | relation | 13285 | 1920802 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p234 | relation | 13285 | 1922554 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p28 | relation | 13285 | 1920906 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p24 | relation | 13285 | 1920874 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p27 | relation | 13285 | 1920898 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p123 | relation | 13285 | 1921666 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p202 | relation | 13285 | 1922298 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p102 | relation | 13285 | 1921498 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p60 | relation | 13285 | 1921162 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p173 | relation | 13285 | 1922066 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p186 | relation | 13285 | 1922170 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p33 | relation | 13285 | 1920946 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p88 | relation | 13285 | 1921386 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p39 | relation | 13285 | 1920994 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p82 | relation | 13285 | 1921338 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p81 | relation | 13285 | 1921330 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p143 | relation | 13285 | 1921826 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p167 | relation | 13285 | 1922018 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p87 | relation | 13285 | 1921378 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p154 | relation | 13285 | 1921914 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p148 | relation | 13285 | 1921866 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p191 | relation | 13285 | 1922210 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p125 | relation | 13285 | 1921682 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p178 | relation | 13285 | 1922106 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p127 | relation | 13285 | 1921698 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p233 | relation | 13285 | 1922546 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p211 | relation | 13285 | 1922370 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p144 | relation | 13285 | 1921834 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p86 | relation | 13285 | 1921370 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p106 | relation | 13285 | 1921530 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p162 | relation | 13285 | 1921978 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p109 | relation | 13285 | 1921554 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p205 | relation | 13285 | 1922322 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p190 | relation | 13285 | 1922202 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p91 | relation | 13285 | 1921410 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p209 | relation | 13285 | 1922354 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p242 | relation | 13285 | 1922618 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p78 | relation | 13285 | 1921306 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p156 | relation | 13285 | 1921930 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p | relation | 13285 | 1920677 | | | | | | | | 3/60843 | 57451 | AccessShareLock | t | f
57451 | | transactionid | | | | | | 1330243980 | | | | 3/60843 | 57451 | ExclusiveLock | t | f
57451 | p196 | relation | 13285 | 1922250 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p203 | relation | 13285 | 1922306 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p164 | relation | 13285 | 1921994 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p90 | relation | 13285 | 1921402 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p135 | relation | 13285 | 1921762 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p232 | relation | 13285 | 1922538 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p22 | relation | 13285 | 1920858 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p182 | relation | 13285 | 1922138 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p38 | relation | 13285 | 1920986 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p166 | relation | 13285 | 1922010 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p96 | relation | 13285 | 1921450 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p14 | relation | 13285 | 1920794 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p92 | relation | 13285 | 1921418 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p247 | relation | 13285 | 1922658 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p238 | relation | 13285 | 1922586 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p107 | relation | 13285 | 1921538 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p116 | relation | 13285 | 1921610 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p79 | relation | 13285 | 1921314 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p181 | relation | 13285 | 1922130 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p134 | relation | 13285 | 1921754 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p117 | relation | 13285 | 1921618 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p228 | relation | 13285 | 1922506 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p245 | relation | 13285 | 1922642 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p118 | relation | 13285 | 1921626 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p31 | relation | 13285 | 1920930 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p121 | relation | 13285 | 1921650 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p229 | relation | 13285 | 1922514 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p141 | relation | 13285 | 1921810 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p246 | relation | 13285 | 1922650 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p220 | relation | 13285 | 1922442 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p58 | relation | 13285 | 1921146 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p104 | relation | 13285 | 1921514 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p226 | relation | 13285 | 1922490 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p159 | relation | 13285 | 1921954 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p75 | relation | 13285 | 1921282 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p199 | relation | 13285 | 1922274 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p189 | relation | 13285 | 1922194 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p176 | relation | 13285 | 1922090 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p42 | relation | 13285 | 1921018 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p89 | relation | 13285 | 1921394 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p85 | relation | 13285 | 1921362 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p46 | relation | 13285 | 1921050 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p169 | relation | 13285 | 1922034 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p115 | relation | 13285 | 1921602 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p62 | relation | 13285 | 1921178 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p26 | relation | 13285 | 1920890 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p132 | relation | 13285 | 1921738 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p163 | relation | 13285 | 1921986 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p170 | relation | 13285 | 1922042 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p35 | relation | 13285 | 1920962 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p200 | relation | 13285 | 1922282 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p74 | relation | 13285 | 1921274 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p230 | relation | 13285 | 1922522 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p122 | relation | 13285 | 1921658 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p126 | relation | 13285 | 1921690 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p180 | relation | 13285 | 1922122 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p149 | relation | 13285 | 1921874 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p194 | relation | 13285 | 1922234 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p201 | relation | 13285 | 1922290 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p77 | relation | 13285 | 1921298 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p111 | relation | 13285 | 1921570 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p206 | relation | 13285 | 1922330 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p128 | relation | 13285 | 1921706 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p29 | relation | 13285 | 1920914 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p193 | relation | 13285 | 1922226 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p240 | relation | 13285 | 1922602 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p72 | relation | 13285 | 1921258 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p184 | relation | 13285 | 1922154 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p56 | relation | 13285 | 1921130 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p37 | relation | 13285 | 1920978 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p110 | relation | 13285 | 1921562 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p244 | relation | 13285 | 1922634 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p99 | relation | 13285 | 1921474 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p67 | relation | 13285 | 1921218 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p227 | relation | 13285 | 1922498 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p113 | relation | 13285 | 1921586 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p68 | relation | 13285 | 1921226 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p80 | relation | 13285 | 1921322 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p161 | relation | 13285 | 1921970 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p248 | relation | 13285 | 1922666 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p25 | relation | 13285 | 1920882 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p214 | relation | 13285 | 1922394 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p157 | relation | 13285 | 1921938 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p153 | relation | 13285 | 1921906 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p47 | relation | 13285 | 1921058 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p66 | relation | 13285 | 1921210 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p21 | relation | 13285 | 1920850 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p84 | relation | 13285 | 1921354 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p241 | relation | 13285 | 1922610 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p41 | relation | 13285 | 1921010 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p19 | relation | 13285 | 1920834 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p235 | relation | 13285 | 1922562 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p255 | relation | 13285 | 1922722 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p130 | relation | 13285 | 1921722 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p219 | relation | 13285 | 1922434 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p140 | relation | 13285 | 1921802 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p188 | relation | 13285 | 1922186 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p138 | relation | 13285 | 1921786 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p212 | relation | 13285 | 1922378 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p61 | relation | 13285 | 1921170 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p249 | relation | 13285 | 1922674 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p155 | relation | 13285 | 1921922 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p114 | relation | 13285 | 1921594 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p53 | relation | 13285 | 1921106 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p136 | relation | 13285 | 1921770 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p23 | relation | 13285 | 1920866 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p16 | relation | 13285 | 1920810 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p222 | relation | 13285 | 1922458 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p239 | relation | 13285 | 1922594 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p183 | relation | 13285 | 1922146 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p93 | relation | 13285 | 1921426 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p204 | relation | 13285 | 1922314 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p224 | relation | 13285 | 1922474 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p221 | relation | 13285 | 1922450 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p105 | relation | 13285 | 1921522 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p151 | relation | 13285 | 1921890 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p100 | relation | 13285 | 1921482 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p133 | relation | 13285 | 1921746 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p254 | relation | 13285 | 1922714 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p174 | relation | 13285 | 1922074 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p124 | relation | 13285 | 1921674 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p120 | relation | 13285 | 1921642 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p40 | relation | 13285 | 1921002 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p146 | relation | 13285 | 1921850 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p195 | relation | 13285 | 1922242 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p139 | relation | 13285 | 1921794 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p70 | relation | 13285 | 1921242 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p57 | relation | 13285 | 1921138 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p49 | relation | 13285 | 1921074 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p172 | relation | 13285 | 1922058 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
57451 | p145 | relation | 13285 | 1921842 | | | | | | | | 3/60843 | 57451 | RowExclusiveLock | t | f
(261 rows)
256个分区，写入测试。
vi test.sql
\set id random(1,1000000000)
insert into p values (:id,md5(random()::text),now()) on conflict(id) do nothing;
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120 -h /tmp -p 8001 -U postgres postgres
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 32
number of threads: 32
duration: 120 s
number of transactions actually processed: 1361828
latency average = 2.820 ms
latency stddev = 0.831 ms
tps = 11348.115169 (including connections establishing)
tps = 11348.492358 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set id random(1,1000000000)
2.818 insert into p values (:id,md5(random()::text),now()) on conflict(id) do nothing;
/home/digoal/pg12/bin/pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120 -h /tmp -p 3335 -U postgres postgres
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 32
number of threads: 32
duration: 120 s
number of transactions actually processed: 32093836
latency average = 0.119 ms
latency stddev = 0.058 ms
tps = 267447.357761 (including connections establishing)
tps = 267456.481028 (excluding connections establishing)
statement latencies in milliseconds:
0.000 \set id random(1,1000000000)
0.119 insert into p values (:id,md5(random()::text),now()) on conflict(id) do nothing;
| 版本 | TPS |
|---|---|
| PG 11 | 11348 |
| PG 12 | 267447 |
《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》
《PostgreSQL 9.x, 10, 11 hash分区表 用法举例》
《分区表锁粒度差异 - pg_pathman VS native partition table》
《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》
《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) - 分区表性能优化 (堪比pg_pathman)》
《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
