PostgreSQL DBA(95) - PG 12 Partition(out of shared memory)

PostgreSQL 12 Beta3，创建包含8192个子分区的分区表，执行查询语句，在分区键上排序，出错。
数据库版本：

[local]:5432 pg12@testdb=# select version();                                                  version                                   -------------------------------------------------------------------------------------------- PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit(1 row)Time: 9.511 ms

数据表结构

[local]:5432 pg12@testdb=# \d t_hash_manypartitions        Partitioned table "public.t_hash_manypartitions" Column |         Type          | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1     | integer               |           |          |  c2     | character varying(40) |           |          |  c3     | character varying(40) |           |          | Partition key: HASH (c2)Number of partitions: 8191 (Use \d+ to list them.)

只有1行数据

[local]:5432 pg12@testdb=# insert into t_hash_manypartitions(c1,c2,c3) values(0,'c2-0','c3-0');INSERT 0 1Time: 14.038 ms[local]:5432 pg12@testdb=# select * from t_hash_manypartitions; c1 |  c2  |  c3  ----+------+------  0 | c2-0 | c3-0(1 row)Time: 917.996 ms[local]:5432 pg12@testdb=#

虽然只有1行数据，但全表扫描仍然很慢，接近1s，而普通表仅几毫秒。

[local]:5432 pg12@testdb=# select * from t_hash_manypartitions; c1 |  c2  |  c3  ----+------+------  0 | c2-0 | c3-0(1 row)Time: 898.615 ms[local]:5432 pg12@testdb=# select * from t_hash_manypartitions; c1 |  c2  |  c3  ----+------+------  0 | c2-0 | c3-0(1 row)Time: 898.783 ms[local]:5432 pg12@testdb=#

执行查询，在分区键c2上排序

[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;ERROR:  out of shared memoryHINT:  You might need to increase max_locks_per_transaction.CONTEXT:  parallel workerTime: 2420.971 ms (00:02.421)[local]:5432 pg12@testdb=#

提示out of shared memory，内存溢出

[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=128;ALTER SYSTEMTime: 7.705 ms[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;ERROR:  out of shared memoryHINT:  You might need to increase max_locks_per_transaction.CONTEXT:  parallel workerTime: 1988.893 ms (00:01.989)[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=512;ALTER SYSTEMTime: 13.137 ms[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;ERROR:  out of shared memoryHINT:  You might need to increase max_locks_per_transaction.CONTEXT:  parallel workerTime: 1968.974 ms (00:01.969)[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=8192;ALTER SYSTEMTime: 4.060 ms[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;ERROR:  out of shared memoryHINT:  You might need to increase max_locks_per_transaction.CONTEXT:  parallel workerTime: 1985.106 ms (00:01.985)[local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=16384;ALTER SYSTEMTime: 7.791 ms[local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2;ERROR:  out of shared memoryHINT:  You might need to increase max_locks_per_transaction.CONTEXT:  parallel workerTime: 1953.134 ms (00:01.953)[local]:5432 pg12@testdb=#

可以看到，增大该参数值至16384，仍然报错。 修改此参数需重启数据库，重启数据库后重新执行即可
查看执行计划，PG在每个分区上执行并行扫描，然后使用Parallel Append合并结果集，然后再执行排序。

[local]:5432 pg12@testdb=# explain select * from t_hash_manypartitions order by c2;                                                QUERY PLAN                                  -------------------------------------------------------------------------------------------- Gather Merge  (cost=455382.87..734442.42 rows=2391772 width=200)   Workers Planned: 2   ->  Sort  (cost=454382.84..457372.56 rows=1195886 width=200)         Sort Key: t_hash_manypartitions_1.c2         ->  Parallel Append  (cost=0.00..104753.25 rows=1195886 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_1  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_2  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_3  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_4  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_5  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_6  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_7  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_8  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_9  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_10  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_11  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_12  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_13  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_14  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_15  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_16  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_17  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_18  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_19  (cost=0.00..12.06 rows=206 width=200)               ->  Parallel Seq Scan on t_hash_manypartitions_20  (cost=0.00..12.06 rows=206--More--

在PG 11.2上则没有问题

testdb=# select version();                                                 version                                    -------------------------------------------------------------------------------------------- PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit(1 row)testdb=# select * from t_hash_manypartitions order by c2; c1 |  c2  |  c3  ----+------+------  0 | c2-0 | c3-0(1 row)testdb=#