From 19eea95d1eca89a90f3f1b2992e248e3e3459caa Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Tue, 27 Apr 2021 09:09:13 -0500 Subject: [PATCH v11] ExecInsert to use BulkInsertState --- src/backend/executor/nodeModifyTable.c | 34 ++++++++++++++++++++++++-- src/backend/utils/misc/guc.c | 9 +++++++ src/include/executor/nodeModifyTable.h | 2 ++ src/include/nodes/execnodes.h | 5 ++++ src/test/regress/expected/insert.out | 15 ++++++++++++ src/test/regress/sql/insert.sql | 10 ++++++++ 6 files changed, 73 insertions(+), 2 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index d328856ae5..9ce81dc922 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -81,6 +81,8 @@ static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, ResultRelInfo *targetRelInfo, TupleTableSlot *slot, ResultRelInfo **partRelInfo); +/* guc */ +int bulk_insert_ntuples = 1000; /* * Verify that the tuples to be produced by INSERT match the @@ -625,6 +627,19 @@ 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->ntuples > bulk_insert_ntuples && + bulk_insert_ntuples >= 0) + { + elog(DEBUG1, "enabling bulk insert"); + mtstate->bistate = GetBulkInsertState(); + } + + /* * Open the table's indexes, if we have not done so already, so that we * can add new index entries for the inserted tuple. @@ -893,7 +908,7 @@ ExecInsert(ModifyTableState *mtstate, table_tuple_insert_speculative(resultRelationDesc, slot, estate->es_output_cid, 0, - NULL, + mtstate->bistate, specToken); /* insert index entries for tuple */ @@ -930,10 +945,17 @@ ExecInsert(ModifyTableState *mtstate, } else { + if (proute && mtstate->prevResultRelInfo != resultRelInfo) + { + if (mtstate->bistate) + ReleaseBulkInsertStatePin(mtstate->bistate); + mtstate->prevResultRelInfo = resultRelInfo; + } + /* insert the tuple normally */ table_tuple_insert(resultRelationDesc, slot, estate->es_output_cid, - 0, NULL); + 0, mtstate->bistate); /* insert index entries for tuple */ if (resultRelInfo->ri_NumIndices > 0) @@ -2736,6 +2758,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->resultRelInfo = (ResultRelInfo *) palloc(nrels * sizeof(ResultRelInfo)); + mtstate->bistate = NULL; + /*---------- * Resolve the target relation. This is the same as: * @@ -3196,6 +3220,12 @@ ExecEndModifyTable(ModifyTableState *node) } } + if (node->bistate) + { + FreeBulkInsertState(node->bistate); + table_finish_bulk_insert(node->rootResultRelInfo->ri_RelationDesc, 0); + } + /* * Close all the partitioned tables, leaf partitions, and their indices * and release the slot used for tuple routing, if set. diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index d2ce4a8450..984e636c5d 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -51,6 +51,7 @@ #include "commands/vacuum.h" #include "commands/variable.h" #include "common/string.h" +#include "executor/nodeModifyTable.h" #include "funcapi.h" #include "jit/jit.h" #include "libpq/auth.h" @@ -3569,6 +3570,14 @@ static struct config_int ConfigureNamesInt[] = 0, 0, INT_MAX, check_client_connection_check_interval, 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 */ { diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h index 83e2965531..479124899a 100644 --- a/src/include/executor/nodeModifyTable.h +++ b/src/include/executor/nodeModifyTable.h @@ -15,6 +15,8 @@ #include "nodes/execnodes.h" +extern PGDLLIMPORT int bulk_insert_ntuples; + extern void ExecComputeStoredGenerated(ResultRelInfo *resultRelInfo, EState *estate, TupleTableSlot *slot, CmdType cmdtype); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 37cb4f3d59..f2bd880d28 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -14,6 +14,7 @@ #ifndef EXECNODES_H #define EXECNODES_H +#include "access/heapam.h" #include "access/tupconvert.h" #include "executor/instrument.h" #include "fmgr.h" @@ -1202,6 +1203,10 @@ typedef struct ModifyTableState EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */ bool fireBSTriggers; /* do we need to fire stmt triggers? */ + BulkInsertState bistate; /* state for bulk insert like INSERT SELECT, when miinfo cannot be used */ + ResultRelInfo *prevResultRelInfo; /* last child inserted with bistate */ + size_t ntuples; /* Number of tuples inserted */ + /* * These fields are used for inherited UPDATE and DELETE, to track which * target relation a given tuple is from. If there are a lot of target diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 5063a3dc22..ea1b15ff83 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -483,6 +483,21 @@ Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'), part_xx_yy FOR VALUES IN ('xx', 'yy'), PARTITIONED, part_default DEFAULT, PARTITIONED +-- bulk inserts +truncate hash_parted; +begin; +-- exercise bulk insert to partitions +SET client_min_messages=debug1; +insert into hash_parted select generate_series(1,9999); +DEBUG: enabling bulk insert +RESET client_min_messages; +select count(1) from hash_parted; + count +------- + 9999 +(1 row) + +commit; -- cleanup drop table range_parted, list_parted; drop table hash_parted; diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index bfaa8a3b27..1a792084c1 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -302,6 +302,16 @@ from hash_parted order by part; -- partitions \d+ list_parted +-- bulk inserts +truncate hash_parted; +begin; +-- exercise bulk insert to partitions +SET client_min_messages=debug1; +insert into hash_parted select generate_series(1,9999); +RESET client_min_messages; +select count(1) from hash_parted; +commit; + -- cleanup drop table range_parted, list_parted; drop table hash_parted; -- 2.17.0