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 1 Time: 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 memory HINT: You might need to increase max_locks_per_transaction. CONTEXT: parallel worker Time: 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 SYSTEM Time: 7.705 ms [local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2; ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. CONTEXT: parallel worker Time: 1988.893 ms (00:01.989) [local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=512; ALTER SYSTEM Time: 13.137 ms [local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2; ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. CONTEXT: parallel worker Time: 1968.974 ms (00:01.969) [local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=8192; ALTER SYSTEM Time: 4.060 ms [local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2; ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. CONTEXT: parallel worker Time: 1985.106 ms (00:01.985) [local]:5432 pg12@testdb=# alter system set max_locks_per_transaction=16384; ALTER SYSTEM Time: 7.791 ms [local]:5432 pg12@testdb=# select * from t_hash_manypartitions order by c2; ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. CONTEXT: parallel worker Time: 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=# ```