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=#
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。