From 24d46071082aa4a87c39829ea4908c298799a940 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Mon, 23 Nov 2020 17:57:24 -0600 Subject: [PATCH v7 3/3] Dynamically switch to multi-insert mode.. by popular request --- src/backend/executor/nodeModifyTable.c | 45 +++++++++++++++++++------- src/backend/tcop/utility.c | 4 --- src/backend/utils/misc/guc.c | 19 ++++++----- src/include/executor/nodeModifyTable.h | 3 +- src/include/nodes/execnodes.h | 1 + src/test/regress/expected/insert.out | 4 +++ src/test/regress/sql/insert.sql | 2 ++ 7 files changed, 51 insertions(+), 27 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 05f70f140e..9b774d502a 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -74,7 +74,7 @@ static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, TupleTableSlot *slot, ResultRelInfo **partRelInfo); /* guc */ -bool insert_in_bulk = false; +int bulk_insert_ntuples = 1000; /* * Verify that the tuples to be produced by INSERT or UPDATE match the @@ -413,11 +413,29 @@ ExecInsert(ModifyTableState *mtstate, resultRelationDesc = resultRelInfo->ri_RelationDesc; + /* Use bulk insert after a threshold number of tuples */ + // XXX: maybe this should only be done if it's not a partitioned table or + // if the partitions don't support miinfo, which uses its own bistates + mtstate->ntuples++; + if (mtstate->bistate == NULL && + mtstate->operation == CMD_INSERT && + onconflict == ONCONFLICT_NONE && + mtstate->ntuples > bulk_insert_ntuples && + bulk_insert_ntuples >= 0) + { + elog(DEBUG1, "enabling bulk insert"); + mtstate->bistate = GetBulkInsertState(); + } + if (!mtstate->miinfo || mtstate->operation != CMD_INSERT || onconflict != ONCONFLICT_NONE) ; /* If multi-inserts aren't possible at all, don't check further .. */ else if (proute == NULL) - use_multi_insert = true; + { + if (mtstate->miinfo->ntuples++ >= bulk_insert_ntuples && + bulk_insert_ntuples >= 0) + use_multi_insert = true; + } else { /* @@ -438,12 +456,21 @@ ExecInsert(ModifyTableState *mtstate, /* * Disable multi-inserts when the partition has BEFORE/INSTEAD * OF triggers, or if the partition is a foreign partition. + * The number of tuples eligible for multi-insert is tracked separately + * from the total number of tuples in case it's not supported for some + * partitions. */ - use_multi_insert = !has_before_insert_row_trig && + if (!has_before_insert_row_trig && !has_instead_insert_row_trig && - resultRelInfo->ri_FdwRoutine == NULL; + resultRelInfo->ri_FdwRoutine == NULL && + mtstate->miinfo->ntuples++ >= bulk_insert_ntuples && + bulk_insert_ntuples >= 0) + use_multi_insert = true; } + if (use_multi_insert && mtstate->miinfo->ntuples - 1 == bulk_insert_ntuples) + elog(DEBUG1, "enabling multi insert"); + /* * BEFORE ROW INSERT Triggers. * @@ -2296,12 +2323,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->mt_arowmarks = (List **) palloc0(sizeof(List *) * nplans); mtstate->mt_nplans = nplans; - - if (insert_in_bulk && operation == CMD_INSERT && - node->onConflictAction == ONCONFLICT_NONE) - mtstate->bistate = GetBulkInsertState(); - else - mtstate->bistate = NULL; + mtstate->bistate = NULL; /* * Set miinfo if it can support multi-insert. This is the equivalent of @@ -2309,8 +2331,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) */ if (operation != CMD_INSERT || - node->onConflictAction != ONCONFLICT_NONE || - !insert_in_bulk) + node->onConflictAction != ONCONFLICT_NONE) mtstate->miinfo = NULL; else if (mtstate->rootResultRelInfo->ri_TrigDesc != NULL && (mtstate->rootResultRelInfo->ri_TrigDesc->trig_insert_before_row || diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index a0a4034409..81ac9b1cb2 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -611,10 +611,6 @@ standard_ProcessUtility(PlannedStmt *pstmt, SetPGVariable("transaction_deferrable", list_make1(item->arg), true); - else if (strcmp(item->defname, "bulk") == 0) - SetPGVariable("bulk_insert", - list_make1(item->arg), - true); } } break; diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index c470314134..1126740021 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2037,16 +2037,6 @@ static struct config_bool ConfigureNamesBool[] = NULL, NULL, NULL }, - { - {"bulk_insert", PGC_USERSET, CLIENT_CONN_STATEMENT, - gettext_noop("Sets the transaction to bulk insert mode."), - gettext_noop("A ring buffer of limited size will be used."), - }, - &insert_in_bulk, - false, - NULL, NULL, NULL - }, - /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL @@ -3410,6 +3400,15 @@ static struct config_int ConfigureNamesInt[] = check_huge_page_size, NULL, NULL }, + { + {"bulk_insert_ntuples", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Enable bulk insertions after this number of tuples."), + gettext_noop("A ring buffer of limited size will be used and updates done in batch"), + }, + &bulk_insert_ntuples, + 1000, -1, INT_MAX, + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h index ebe62c2e40..71de7cf80e 100644 --- a/src/include/executor/nodeModifyTable.h +++ b/src/include/executor/nodeModifyTable.h @@ -17,7 +17,7 @@ #include "executor/executor.h" // XXX #include "nodes/execnodes.h" -extern PGDLLIMPORT bool insert_in_bulk; +extern PGDLLIMPORT int bulk_insert_ntuples; extern void ExecComputeStoredGenerated(ResultRelInfo *resultRelInfo, EState *estate, TupleTableSlot *slot, @@ -74,6 +74,7 @@ typedef struct MultiInsertInfo EState *estate; /* Executor state */ CommandId mycid; /* Command Id */ int ti_options; /* table insert options */ + size_t ntuples; /* Number of rows *eligible* for multi-insert */ /* Line number for errors in copyfrom.c */ uint64 cur_lineno; diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index ab7b8fb51b..477b326d06 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1172,6 +1172,7 @@ typedef struct ModifyTableState BulkInsertState bistate; /* state for bulk insert like INSERT SELECT, when miinfo cannot be used */ ResultRelInfo *prevResultRelInfo; /* last child inserted with bistate */ struct MultiInsertInfo *miinfo; + size_t ntuples; /* Number of tuples inserted; */ /* * Slot for storing tuples in the root partitioned table's rowtype during diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index e0c83d7427..b894180152 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -477,7 +477,11 @@ select * from hash_parted; (2 rows) -- exercise bulk insert to partitions +SET client_min_messages=debug; insert into hash_parted select generate_series(1,9999); +DEBUG: enabling bulk insert +DEBUG: enabling multi insert +RESET client_min_messages; select count(1) from hash_parted; count ------- diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index 99ec18d9a2..ff9b57af5e 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -289,7 +289,9 @@ insert into hash_parted values(11); insert into hpart0 values(12); select * from hash_parted; -- exercise bulk insert to partitions +SET client_min_messages=debug; insert into hash_parted select generate_series(1,9999); +RESET client_min_messages; select count(1) from hash_parted; commit; -- test that index was updated -- 2.17.0