作者
digoal
日期
2021-03-18
标签
PostgreSQL , 并行计算 , guc 参数
背景
增加参数enable_parallel_insert , 控制insert语句是否可以使用并行计算, 默认为on.
同时这个参数可以在表级别设置, 也就是说某些表可以开启并行插入, 某些表可以关闭并行插入, 这些都是用户可以自由设定的.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c8f78b616167bf8e24bc5dc69112c37755ed3058
```
Add a new GUC and a reloption to enable inserts in parallel-mode. master github/master
author Amit Kapila akapila@postgresql.org
Thu, 18 Mar 2021 01:55:27 +0000 (07:25 +0530)
committer Amit Kapila akapila@postgresql.org
Thu, 18 Mar 2021 01:55:27 +0000 (07:25 +0530)
commit c8f78b616167bf8e24bc5dc69112c37755ed3058
tree 71ba14ed3e7b3056581ef777f1675fba846ff64f tree
parent 5f79580ad69f6e696365bdc63bc265f45bd77211 commit | diff
Add a new GUC and a reloption to enable inserts in parallel-mode.
Commit 05c8482f7f added the implementation of parallel SELECT for
"INSERT INTO ... SELECT ..." which may incur non-negligible overhead in
the additional parallel-safety checks that it performs, even when, in the
end, those checks determine that parallelism can't be used. This is
normally only ever a problem in the case of when the target table has a
large number of partitions.
A new GUC option "enable_parallel_insert" is added, to allow insert in
parallel-mode. The default is on.
In addition to the GUC option, the user may want a mechanism to allow
inserts in parallel-mode with finer granularity at table level. The new
table option "parallel_insert_enabled" allows this. The default is true.
Author: "Hou, Zhijie"
Reviewed-by: Greg Nancarrow, Amit Langote, Takayuki Tsunakawa, Amit Kapila
Discussion: https://postgr.es/m/CAA4eK1K-cW7svLC2D7DHoGHxdAdg3P37BLgebqBOC2ZLc9a6QQ%40mail.gmail.com
Discussion: https://postgr.es/m/CAJcOf-cXnB5cnMKqWEp2E2z7Mvcd04iLVmV=qpFJrR3AcrTS3g@mail.gmail.com
```
```
+) with (parallel_insert_enabled = off);
create table para_insert_f1 (
unique1 int4 REFERENCES para_insert_p1(unique1),
stringu1 name
);
--
+-- Disable guc option enable_parallel_insert
+--
+set enable_parallel_insert = off;
+-- Test INSERT with underlying query when enable_parallel_insert=off and reloption.parallel_insert_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+ QUERY PLAN
+--------------------------
+ Insert on para_insert_p1
+ -> Seq Scan on tenk1
+(2 rows)
+
+--
+-- Reset guc option enable_parallel_insert
+--
+reset enable_parallel_insert;
+--
+-- Test INSERT with underlying query when enable_parallel_insert=on and reloption.parallel_insert_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+ QUERY PLAN
+--------------------------
+ Insert on para_insert_p1
+ -> Seq Scan on tenk1
+(2 rows)
+
+--
+-- Enable reloption parallel_insert_enabled
+--
+alter table para_insert_p1 set (parallel_insert_enabled = on);
+--
-- Test INSERT with underlying query.
-- (should create plan with parallel SELECT, Gather parent node)
--
@@ -362,9 +395,28 @@ explain (costs off) insert into testdef(a,d) select a,a*8 from test_data;
--
-- Test INSERT into partition with underlying query.
--
-create table parttable1 (a int, b name) partition by range (a);
+create table parttable1 (a int, b name) partition by range (a) with (parallel_insert_enabled=off);
create table parttable1_1 partition of parttable1 for values from (0) to (5000);
create table parttable1_2 partition of parttable1 for values from (5000) to (10000);
+--
+-- Test INSERT into partition when reloption.parallel_insert_enabled=off
+-- (should not create a parallel plan)
+--
+explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
+ QUERY PLAN
+-------------------------
+ Insert on parttable1
+ -> Seq Scan on tenk1
+(2 rows)
+
+--
+-- Enable reloption parallel_insert_enabled
+--
+alter table parttable1 set (parallel_insert_enabled = on);
+--
+-- Test INSERT into partition when reloption.parallel_insert_enabled=on
+-- (should create a parallel plan)
+--
explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
QUERY PLAN
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
