Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc8f78b6

Browse files
author
Amit Kapila
committed
Add a new GUC and a reloption to enable inserts in parallel-mode.
Commit05c8482 added the implementation of parallel SELECT for"INSERT INTO ... SELECT ..." which may incur non-negligible overhead inthe additional parallel-safety checks that it performs, even when, in theend, those checks determine that parallelism can't be used. This isnormally only ever a problem in the case of when the target table has alarge number of partitions.A new GUC option "enable_parallel_insert" is added, to allow insert inparallel-mode. The default is on.In addition to the GUC option, the user may want a mechanism to allowinserts in parallel-mode with finer granularity at table level. The newtable option "parallel_insert_enabled" allows this. The default is true.Author: "Hou, Zhijie"Reviewed-by: Greg Nancarrow, Amit Langote, Takayuki Tsunakawa, Amit KapilaDiscussion:https://postgr.es/m/CAA4eK1K-cW7svLC2D7DHoGHxdAdg3P37BLgebqBOC2ZLc9a6QQ%40mail.gmail.comDiscussion:https://postgr.es/m/CAJcOf-cXnB5cnMKqWEp2E2z7Mvcd04iLVmV=qpFJrR3AcrTS3g@mail.gmail.com
1 parent5f79580 commitc8f78b6

File tree

15 files changed

+240
-15
lines changed

15 files changed

+240
-15
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5072,6 +5072,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
50725072
</listitem>
50735073
</varlistentry>
50745074

5075+
<varlistentry id="guc-enable-parallel-insert" xreflabel="enable_parallel_insert">
5076+
<term><varname>enable_parallel_insert</varname> (<type>boolean</type>)
5077+
<indexterm>
5078+
<primary><varname>enable_parallel_insert</varname> configuration parameter</primary>
5079+
</indexterm>
5080+
</term>
5081+
<listitem>
5082+
<para>
5083+
Enables or disables the query planner's use of parallel plans for
5084+
<command>INSERT</command> commands. The default is <literal>on</literal>.
5085+
When enabled, the planner performs additional parallel-safety checks
5086+
on the target table's attributes and indexes, in order to determine
5087+
if it's safe to use a parallel plan for <command>INSERT</command>. In
5088+
cases such as when the target table has a large number of partitions,
5089+
and particularly also when that table uses something parallel-unsafe
5090+
that prevents parallelism, the overhead of these checks may become
5091+
prohibitively high. To address this potential overhead in these cases,
5092+
this option can be used to disable the use of parallel plans for
5093+
<command>INSERT</command>.
5094+
</para>
5095+
</listitem>
5096+
</varlistentry>
5097+
50755098
</variablelist>
50765099
</sect2>
50775100
<sect2 id="runtime-config-query-constants">

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -722,7 +722,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
722722
<para>
723723
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
724724
fillfactor, toast and autovacuum storage parameters, as well as the
725-
planner parameter <varname>parallel_workers</varname>.
725+
planner parameter <varname>parallel_workers</varname> and
726+
<varname>parallel_insert_enabled</varname>.
726727
</para>
727728
</listitem>
728729
</varlistentry>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1409,6 +1409,32 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14091409
</listitem>
14101410
</varlistentry>
14111411

1412+
<varlistentry id="reloption-parallel-insert-enabled" xreflabel="parallel_insert_enabled">
1413+
<term><literal>parallel_insert_enabled</literal> (<type>boolean</type>)
1414+
<indexterm>
1415+
<primary><varname>parallel_insert_enabled</varname> storage parameter</primary>
1416+
</indexterm>
1417+
</term>
1418+
<listitem>
1419+
<para>
1420+
Enables or disables the query planner's use of parallel insert for
1421+
this table. When enabled (and provided that
1422+
<xref linkend="guc-enable-parallel-insert"/> is also <literal>true</literal>),
1423+
the planner performs additional parallel-safety checks on the table's
1424+
attributes and indexes, in order to determine if it's safe to use a
1425+
parallel plan for <command>INSERT</command>. The default is
1426+
<literal>true</literal>. In cases such as when the table has a large
1427+
number of partitions, and particularly also when that table uses a
1428+
parallel-unsafe feature that prevents parallelism, the overhead of these
1429+
checks may become prohibitively high. To address this potential overhead
1430+
in these cases, this option can be used to disable the use of parallel
1431+
insert for this table. Note that if the target table of the parallel
1432+
insert is partitioned, the <literal>parallel_insert_enabled</literal>
1433+
option values of the partitions are ignored.
1434+
</para>
1435+
</listitem>
1436+
</varlistentry>
1437+
14121438
<varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
14131439
<term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
14141440
<indexterm>

‎src/backend/access/common/reloptions.c

Lines changed: 19 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
168168
},
169169
true
170170
},
171+
{
172+
{
173+
"parallel_insert_enabled",
174+
"Enables \"parallel insert\" feature for this table",
175+
RELOPT_KIND_HEAP |RELOPT_KIND_PARTITIONED,
176+
ShareUpdateExclusiveLock
177+
},
178+
true
179+
},
171180
/* list terminator */
172181
{{NULL}}
173182
};
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
18591868
{"vacuum_index_cleanup",RELOPT_TYPE_BOOL,
18601869
offsetof(StdRdOptions,vacuum_index_cleanup)},
18611870
{"vacuum_truncate",RELOPT_TYPE_BOOL,
1862-
offsetof(StdRdOptions,vacuum_truncate)}
1871+
offsetof(StdRdOptions,vacuum_truncate)},
1872+
{"parallel_insert_enabled",RELOPT_TYPE_BOOL,
1873+
offsetof(StdRdOptions,parallel_insert_enabled)}
18631874
};
18641875

18651876
return (bytea*)build_reloptions(reloptions,validate,kind,
@@ -1961,13 +1972,15 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
19611972
bytea*
19621973
partitioned_table_reloptions(Datumreloptions,boolvalidate)
19631974
{
1964-
/*
1965-
* There are no options for partitioned tables yet, but this is able to do
1966-
* some validation.
1967-
*/
1975+
staticconstrelopt_parse_elttab[]= {
1976+
{"parallel_insert_enabled",RELOPT_TYPE_BOOL,
1977+
offsetof(PartitionedTableRdOptions,parallel_insert_enabled)}
1978+
};
1979+
19681980
return (bytea*)build_reloptions(reloptions,validate,
19691981
RELOPT_KIND_PARTITIONED,
1970-
0,NULL,0);
1982+
sizeof(PartitionedTableRdOptions),
1983+
tab,lengthof(tab));
19711984
}
19721985

19731986
/*

‎src/backend/optimizer/path/costsize.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -129,6 +129,8 @@ Costdisable_cost = 1.0e10;
129129

130130
intmax_parallel_workers_per_gather=2;
131131

132+
boolenable_parallel_insert= true;
133+
132134
boolenable_seqscan= true;
133135
boolenable_indexscan= true;
134136
boolenable_indexonlyscan= true;

‎src/backend/optimizer/util/clauses.c

Lines changed: 31 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1265,8 +1265,10 @@ target_rel_chk_constr_max_parallel_hazard(Relation rel,
12651265
*
12661266
* It's not possible in the following cases:
12671267
*
1268-
* 1) INSERT...ON CONFLICT...DO UPDATE
1269-
* 2) INSERT without SELECT
1268+
* 1) enable_parallel_insert is off
1269+
* 2) INSERT...ON CONFLICT...DO UPDATE
1270+
* 3) INSERT without SELECT
1271+
* 4) the reloption parallel_insert_enabled is set to off
12701272
*
12711273
* (Note: we don't do in-depth parallel-safety checks here, we do only the
12721274
* cheaper tests that can quickly exclude obvious cases for which
@@ -1277,12 +1279,17 @@ bool
12771279
is_parallel_allowed_for_modify(Query*parse)
12781280
{
12791281
boolhasSubQuery;
1282+
boolparallel_enabled;
12801283
RangeTblEntry*rte;
12811284
ListCell*lc;
1285+
Relationrel;
12821286

12831287
if (!IsModifySupportedInParallelMode(parse->commandType))
12841288
return false;
12851289

1290+
if (!enable_parallel_insert)
1291+
return false;
1292+
12861293
/*
12871294
* UPDATE is not currently supported in parallel-mode, so prohibit
12881295
* INSERT...ON CONFLICT...DO UPDATE...
@@ -1313,7 +1320,28 @@ is_parallel_allowed_for_modify(Query *parse)
13131320
}
13141321
}
13151322

1316-
returnhasSubQuery;
1323+
if (!hasSubQuery)
1324+
return false;
1325+
1326+
/*
1327+
* Check if parallel_insert_enabled is enabled for the target table, if
1328+
* not, skip the safety checks.
1329+
*
1330+
* (Note: if the target table is partitioned, the parallel_insert_enabled
1331+
* option setting of the partitions are ignored).
1332+
*/
1333+
rte=rt_fetch(parse->resultRelation,parse->rtable);
1334+
1335+
/*
1336+
* The target table is already locked by the caller (this is done in the
1337+
* parse/analyze phase), and remains locked until end-of-transaction.
1338+
*/
1339+
rel=table_open(rte->relid,NoLock);
1340+
1341+
parallel_enabled=RelationGetParallelInsert(rel, true);
1342+
table_close(rel,NoLock);
1343+
1344+
returnparallel_enabled;
13171345
}
13181346

13191347
/*****************************************************************************

‎src/backend/utils/misc/guc.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1122,6 +1122,16 @@ static struct config_bool ConfigureNamesBool[] =
11221122
true,
11231123
NULL,NULL,NULL
11241124
},
1125+
{
1126+
{"enable_parallel_insert",PGC_USERSET,QUERY_TUNING_METHOD,
1127+
gettext_noop("Enables the planner's use of parallel plans for INSERT commands."),
1128+
NULL,
1129+
GUC_EXPLAIN
1130+
},
1131+
&enable_parallel_insert,
1132+
true,
1133+
NULL,NULL,NULL
1134+
},
11251135
{
11261136
/* Not for general use --- used by SET SESSION AUTHORIZATION */
11271137
{"is_superuser",PGC_INTERNAL,UNGROUPED,

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -371,6 +371,7 @@
371371
#enable_partitionwise_aggregate = off
372372
#enable_parallel_hash = on
373373
#enable_partition_pruning = on
374+
#enable_parallel_insert = on
374375

375376
# - Planner Cost Constants -
376377

‎src/bin/psql/tab-complete.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1118,6 +1118,7 @@ static const char *const table_storage_parameters[] = {
11181118
"autovacuum_vacuum_threshold",
11191119
"fillfactor",
11201120
"log_autovacuum_min_duration",
1121+
"parallel_insert_enabled",
11211122
"parallel_workers",
11221123
"toast.autovacuum_enabled",
11231124
"toast.autovacuum_freeze_max_age",

‎src/include/optimizer/cost.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ typedef enum
4747
/* parameter variables and flags (see also optimizer.h) */
4848
externPGDLLIMPORTCostdisable_cost;
4949
externPGDLLIMPORTintmax_parallel_workers_per_gather;
50+
externPGDLLIMPORTboolenable_parallel_insert;
5051
externPGDLLIMPORTboolenable_seqscan;
5152
externPGDLLIMPORTboolenable_indexscan;
5253
externPGDLLIMPORTboolenable_indexonlyscan;

‎src/include/utils/rel.h

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -306,6 +306,8 @@ typedef struct StdRdOptions
306306
intparallel_workers;/* max number of parallel workers */
307307
boolvacuum_index_cleanup;/* enables index vacuuming and cleanup */
308308
boolvacuum_truncate;/* enables vacuum to truncate a relation */
309+
boolparallel_insert_enabled;/* enables planner's use of
310+
* parallel insert */
309311
}StdRdOptions;
310312

311313
#defineHEAP_MIN_FILLFACTOR10
@@ -423,6 +425,29 @@ typedef struct ViewOptions
423425
((ViewOptions *) (relation)->rd_options)->check_option ==\
424426
VIEW_OPTION_CHECK_OPTION_CASCADED)
425427

428+
/*
429+
* PartitionedTableRdOptions
430+
*Contents of rd_options for partitioned tables
431+
*/
432+
typedefstructPartitionedTableRdOptions
433+
{
434+
int32vl_len_;/* varlena header (do not touch directly!) */
435+
boolparallel_insert_enabled;/* enables planner's use of
436+
* parallel insert */
437+
}PartitionedTableRdOptions;
438+
439+
/*
440+
* RelationGetParallelInsert
441+
*Returns the relation's parallel_insert_enabled reloption setting.
442+
*Note multiple eval of argument!
443+
*/
444+
#defineRelationGetParallelInsert(relation,defaultpd) \
445+
((relation)->rd_options ?\
446+
(relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?\
447+
((PartitionedTableRdOptions *) (relation)->rd_options)->parallel_insert_enabled : \
448+
((StdRdOptions *) (relation)->rd_options)->parallel_insert_enabled) :\
449+
(defaultpd))
450+
426451
/*
427452
* RelationIsValid
428453
*True iff relation descriptor is valid.

‎src/test/regress/expected/insert_parallel.out

Lines changed: 54 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -61,12 +61,45 @@ set max_parallel_workers_per_gather=4;
6161
create table para_insert_p1 (
6262
unique1int4PRIMARY KEY,
6363
stringu1name
64-
);
64+
) with (parallel_insert_enabled = off);
6565
create table para_insert_f1 (
6666
unique1int4REFERENCES para_insert_p1(unique1),
6767
stringu1name
6868
);
6969
--
70+
-- Disable guc option enable_parallel_insert
71+
--
72+
set enable_parallel_insert = off;
73+
-- Test INSERT with underlying query when enable_parallel_insert=off and reloption.parallel_insert_enabled=off.
74+
-- (should create plan with serial INSERT + SELECT)
75+
--
76+
explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
77+
QUERY PLAN
78+
--------------------------
79+
Insert on para_insert_p1
80+
-> Seq Scan on tenk1
81+
(2 rows)
82+
83+
--
84+
-- Reset guc option enable_parallel_insert
85+
--
86+
reset enable_parallel_insert;
87+
--
88+
-- Test INSERT with underlying query when enable_parallel_insert=on and reloption.parallel_insert_enabled=off.
89+
-- (should create plan with serial INSERT + SELECT)
90+
--
91+
explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
92+
QUERY PLAN
93+
--------------------------
94+
Insert on para_insert_p1
95+
-> Seq Scan on tenk1
96+
(2 rows)
97+
98+
--
99+
-- Enable reloption parallel_insert_enabled
100+
--
101+
alter table para_insert_p1 set (parallel_insert_enabled = on);
102+
--
70103
-- Test INSERT with underlying query.
71104
-- (should create plan with parallel SELECT, Gather parent node)
72105
--
@@ -362,9 +395,28 @@ explain (costs off) insert into testdef(a,d) select a,a*8 from test_data;
362395
--
363396
-- Test INSERT into partition with underlying query.
364397
--
365-
create table parttable1 (a int, b name) partition by range (a);
398+
create table parttable1 (a int, b name) partition by range (a) with (parallel_insert_enabled=off);
366399
create table parttable1_1 partition of parttable1 for values from (0) to (5000);
367400
create table parttable1_2 partition of parttable1 for values from (5000) to (10000);
401+
--
402+
-- Test INSERT into partition when reloption.parallel_insert_enabled=off
403+
-- (should not create a parallel plan)
404+
--
405+
explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
406+
QUERY PLAN
407+
-------------------------
408+
Insert on parttable1
409+
-> Seq Scan on tenk1
410+
(2 rows)
411+
412+
--
413+
-- Enable reloption parallel_insert_enabled
414+
--
415+
alter table parttable1 set (parallel_insert_enabled = on);
416+
--
417+
-- Test INSERT into partition when reloption.parallel_insert_enabled=on
418+
-- (should create a parallel plan)
419+
--
368420
explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
369421
QUERY PLAN
370422
----------------------------------------

‎src/test/regress/expected/sysviews.out

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -107,13 +107,14 @@ select name, setting from pg_settings where name like 'enable%';
107107
enable_nestloop | on
108108
enable_parallel_append | on
109109
enable_parallel_hash | on
110+
enable_parallel_insert | on
110111
enable_partition_pruning | on
111112
enable_partitionwise_aggregate | off
112113
enable_partitionwise_join | off
113114
enable_seqscan | on
114115
enable_sort | on
115116
enable_tidscan | on
116-
(18 rows)
117+
(19 rows)
117118

118119
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
119120
-- more-or-less working. We can't test their contents in any great detail

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp