PG 10在分区表上执行查询时，会逐个检查每个分区的约束来看是否需要，如果分区很多在计划阶段会有较大的性能损失。PG 11通过”partition pruning“算法来快速的标识匹配的分区来改进性能，但PG 11仍然做了一些不必要的处理比如不管是否涉及仍然加载了所有分区的元数据。 PG 12更进一步，那就是在pruning后才加载元数据，如果不涉及大多数的分区那么在计划阶段可以带来明显的性能提升。 **创建分区表** ``` [local]:5432 pg12@testdb=# drop table if exists t_counter; NOTICE: table "t_counter" does not exist, skipping DROP TABLE Time: 29.768 ms [local]:5432 pg12@testdb=# create table t_counter(id int); CREATE TABLE Time: 120.165 ms [local]:5432 pg12@testdb=# insert into t_counter select generate_series(0,100000); INSERT 0 100001 Time: 333.637 ms [local]:5432 pg12@testdb=# drop table if exists t_hash_manypartitions; NOTICE: table "t_hash_manypartitions" does not exist, skipping DROP TABLE Time: 1.536 ms [local]:5432 pg12@testdb=# create table t_hash_manypartitions (c1 int,c2 varchar(40),c3 varchar(40)) partition by hash(c2); CREATE TABLE Time: 45.986 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# \o /tmp/script.sql [local]:5432 pg12@testdb=# select 'create table t_hash_manypartitions_' pg12@testdb-# ||id pg12@testdb-# ||' partition of t_hash_manypartitions for values with (modulus 8192,remainder '||id||');' pg12@testdb-# from t_counter pg12@testdb-# where id < 8192 pg12@testdb-# order by id ; Time: 78.499 ms [local]:5432 pg12@testdb=# \o [local]:5432 pg12@testdb=# [root@localhost ~]# tail -n 10 /tmp/script.sql create table t_hash_manypartitions_8184 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8184); create table t_hash_manypartitions_8185 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8185); create table t_hash_manypartitions_8186 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8186); create table t_hash_manypartitions_8187 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8187); create table t_hash_manypartitions_8188 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8188); create table t_hash_manypartitions_8189 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8189); create table t_hash_manypartitions_8190 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8190); create table t_hash_manypartitions_8191 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8191); (8192 rows) [local]:5432 pg12@testdb=# \i /tmp/script.sql ... CREATE TABLE Time: 20.784 ms CREATE TABLE Time: 21.107 ms psql:/tmp/script.sql:8196: ERROR: syntax error at or near "8192" LINE 1: (8192 rows) ^ Time: 0.198 ms [local]:5432 pg12@testdb=# ``` 插入数据 ``` insert into t_hash_manypartitions(c1,c2,c3) values(1,'c2-1','c3-1'); ``` **PG 11** 执行查询，条件为c2 = 'c2-1' ``` testdb=# begin; BEGIN testdb=# explain analyze select * from t_hash_manypartitions where c2 = 'c2-1'; QUERY PLAN -------------------------------------------------------------------------------------------- --------------------------------- Append (cost=0.00..14.38 rows=2 width=200) (actual time=1.516..1.516 rows=0 loops=1) -> Seq Scan on t_hash_manypartitions_4956 (cost=0.00..14.38 rows=2 width=200) (actual t ime=1.491..1.491 rows=0 loops=1) Filter: ((c2)::text = 'c2-1'::text) Planning Time: 1585.294 ms Execution Time: 2.502 ms (5 rows) ``` 计划时间超过1.5s，比较糟糕的结果。 查询锁信息 ``` [xdb@localhost ~]$ psql -d testdb -p 5433 psql (11.2) Type "help" for help. testdb=# select relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath testdb-# from pg_locks testdb-# where pid <> pg_backend_pid(); relation | locktype | virtualxid | transactionid | virtualtransaction | pid | mode | granted | fastpath ----------------------------+------------+------------+---------------+--------------------+ ------+-----------------+---------+---------- t_hash_manypartitions_15 | relation | | | 4/2 | 2695 | AccessShareLock | t | t t_hash_manypartitions_14 | relation | | | 4/2 | 2695 | AccessShareLock | t | t t_hash_manypartitions_13 | relation | | | 4/2 | 2695 | AccessShareLock | t | t ... testdb=# select count(*) from pg_locks where pid <> pg_backend_pid(); count ------- 8193 (1 row) ``` **PG 12** 执行查询 ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.639 ms [local]:5432 pg12@testdb=#* explain analyze select * from t_hash_manypartitions where c2 = 'c2-1'; QUERY PLAN -------------------------------------------------------------------------------------------- ----------------------------- Seq Scan on t_hash_manypartitions_4956 (cost=0.00..14.38 rows=2 width=200) (actual time=22 .356..22.356 rows=0 loops=1) Filter: ((c2)::text = 'c2-1'::text) Planning Time: 75.491 ms Execution Time: 22.603 ms (4 rows) Time: 519.835 ms [local]:5432 pg12@testdb=#* ``` 计划时间75ms，比起PG 11的1500ms快了2个数量级。 查询锁信息 ``` [local]:5432 pg12@testdb=# select relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath from pg_locks where pid <> pg_backend_pid(); relation | locktype | virtualxid | transactionid | virtualtransaction | pid | mode | granted | fastpath ----------------------------+------------+------------+---------------+--------------------+ ------+-----------------+---------+---------- t_hash_manypartitions_4956 | relation | | | 3/4 | 1591 | AccessShareLock | t | t t_hash_manypartitions | relation | | | 3/4 | 1591 | AccessShareLock | t | t | virtualxid | 3/4 | | 3/4 | 1591 | ExclusiveLock | t | t (3 rows) Time: 1.935 ms ``` 很好，只是给涉及的分区上锁而已。 **参考资料** [PostgreSQL 12: Partitioning is now faster ](https://www.2ndquadrant.com/en/blog/postgresql-12-partitioning/)