Skip to content

Commit c8f78b6

Browse files
author
Amit Kapila
committed
Add a new GUC and a reloption to enable inserts in parallel-mode.
Commit 05c8482 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
1 parent 5f79580 commit c8f78b6

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(Datum reloptions, bool validate)
19631974
{
1964-
/*
1965-
* There are no options for partitioned tables yet, but this is able to do
1966-
* some validation.
1967-
*/
1975+
static const relopt_parse_elt tab[] = {
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 @@ Cost disable_cost = 1.0e10;
129129

130130
int max_parallel_workers_per_gather = 2;
131131

132+
bool enable_parallel_insert = true;
133+
132134
bool enable_seqscan = true;
133135
bool enable_indexscan = true;
134136
bool enable_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
bool hasSubQuery;
1282+
bool parallel_enabled;
12801283
RangeTblEntry *rte;
12811284
ListCell *lc;
1285+
Relation rel;
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-
return hasSubQuery;
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+
return parallel_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
extern PGDLLIMPORT Cost disable_cost;
4949
extern PGDLLIMPORT int max_parallel_workers_per_gather;
50+
extern PGDLLIMPORT bool enable_parallel_insert;
5051
extern PGDLLIMPORT bool enable_seqscan;
5152
extern PGDLLIMPORT bool enable_indexscan;
5253
extern PGDLLIMPORT bool enable_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
int parallel_workers; /* max number of parallel workers */
307307
bool vacuum_index_cleanup; /* enables index vacuuming and cleanup */
308308
bool vacuum_truncate; /* enables vacuum to truncate a relation */
309+
bool parallel_insert_enabled; /* enables planner's use of
310+
* parallel insert */
309311
} StdRdOptions;
310312

311313
#define HEAP_MIN_FILLFACTOR 10
@@ -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+
typedef struct PartitionedTableRdOptions
433+
{
434+
int32 vl_len_; /* varlena header (do not touch directly!) */
435+
bool parallel_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+
#define RelationGetParallelInsert(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
unique1 int4 PRIMARY KEY,
6363
stringu1 name
64-
);
64+
) with (parallel_insert_enabled = off);
6565
create table para_insert_f1 (
6666
unique1 int4 REFERENCES para_insert_p1(unique1),
6767
stringu1 name
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)