When updating a table row with generated columns, we only need to
recompute those generated columns whose base columns have changed in
this update and keep the rest unchanged. This can result in a
significant performance benefit (easy to reproduce for example with a
tsvector column). The required information was already kept in
RangeTblEntry.extraUpdatedCols; we just have to make use of it.
A small problem is that right now ExecSimpleRelationUpdate() does not
populate extraUpdatedCols. That needs fixing first. This is also
related to the issue discussed in "logical replication does not fire
per-column triggers"[0]. I'll leave my patch here while that issue is
being resolved.
[0]:
https://www.postgresql.org/message-id/flat/21673e2d-597c-6afe-637e-e8b10425b240%402ndquadrant.com
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 3b80f3147c0e983a2e8ad41be7df54c6480c0d8f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 20 Dec 2019 22:52:31 +0100
Subject: [PATCH] Optimize update of tables with generated columns
When updating a table row with generated columns, only recompute those
generated columns whose base columns have changed in this update and
keep the rest unchanged. This can result in a significant performance
benefit. The required information was already kept in
RangeTblEntry.extraUpdatedCols; we just have to make use of it.
FIXME: ExecSimpleRelationUpdate() does not currently populate
extraUpdatedCols. That needs fixing first.
---
src/backend/commands/copy.c | 2 +-
src/backend/executor/execReplication.c | 4 +--
src/backend/executor/nodeModifyTable.c | 37 +++++++++++++++++++++-----
src/include/executor/nodeModifyTable.h | 2 +-
src/include/nodes/execnodes.h | 3 +++
5 files changed, 38 insertions(+), 10 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 42a147b67d..a0758cd39c 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -3221,7 +3221,7 @@ CopyFrom(CopyState cstate)
/* Compute stored generated columns */
if
(resultRelInfo->ri_RelationDesc->rd_att->constr &&
resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(estate,
myslot);
+ ExecComputeStoredGenerated(estate,
myslot, CMD_INSERT);
/*
* If the target is a plain table, check the
constraints of
diff --git a/src/backend/executor/execReplication.c
b/src/backend/executor/execReplication.c
index 95e027c970..8cbec36b18 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -416,7 +416,7 @@ ExecSimpleRelationInsert(EState *estate, TupleTableSlot
*slot)
/* Compute stored generated columns */
if (rel->rd_att->constr &&
rel->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(estate, slot);
+ ExecComputeStoredGenerated(estate, slot, CMD_INSERT);
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
@@ -482,7 +482,7 @@ ExecSimpleRelationUpdate(EState *estate, EPQState *epqstate,
/* Compute stored generated columns */
if (rel->rd_att->constr &&
rel->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(estate, slot);
+ ExecComputeStoredGenerated(estate, slot, CMD_UPDATE);
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index 9ba1d78344..5878041ee6 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -246,7 +246,7 @@ ExecCheckTIDVisible(EState *estate,
* Compute stored generated columns for a tuple
*/
void
-ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)
+ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot, CmdType
cmdtype)
{
ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
Relation rel = resultRelInfo->ri_RelationDesc;
@@ -269,6 +269,7 @@ ExecComputeStoredGenerated(EState *estate, TupleTableSlot
*slot)
resultRelInfo->ri_GeneratedExprs =
(ExprState **) palloc(natts * sizeof(ExprState *));
+ resultRelInfo->ri_NumGeneratedNeeded = 0;
for (int i = 0; i < natts; i++)
{
@@ -276,18 +277,41 @@ ExecComputeStoredGenerated(EState *estate, TupleTableSlot
*slot)
{
Expr *expr;
+ /*
+ * If it's an update and the current column was
not marked as
+ * being updated, then we can skip the
computation. But if
+ * there is a BEFORE ROW UPDATE trigger, we
cannot skip
+ * because the trigger might affect additional
columns.
+ */
+ if (cmdtype == CMD_UPDATE &&
+ !(rel->trigdesc &&
rel->trigdesc->trig_update_before_row) &&
+ !bms_is_member(i + 1 -
FirstLowInvalidHeapAttributeNumber,
+
exec_rt_fetch(resultRelInfo->ri_RangeTableIndex, estate)->extraUpdatedCols))
+ {
+ resultRelInfo->ri_GeneratedExprs[i] =
NULL;
+ continue;
+ }
+
expr = (Expr *) build_column_default(rel, i +
1);
if (expr == NULL)
elog(ERROR, "no generation expression
found for column number %d of table \"%s\"",
i + 1,
RelationGetRelationName(rel));
resultRelInfo->ri_GeneratedExprs[i] =
ExecPrepareExpr(expr, estate);
+ resultRelInfo->ri_NumGeneratedNeeded++;
}
}
MemoryContextSwitchTo(oldContext);
}
+ /*
+ * If no generated columns have been affected by this change, then skip
+ * the rest.
+ */
+ if (resultRelInfo->ri_NumGeneratedNeeded == 0)
+ return;
+
oldContext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
values = palloc(sizeof(*values) * natts);
@@ -300,7 +324,8 @@ ExecComputeStoredGenerated(EState *estate, TupleTableSlot
*slot)
{
Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
- if (attr->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_STORED &&
+ resultRelInfo->ri_GeneratedExprs[i])
{
ExprContext *econtext;
Datum val;
@@ -392,7 +417,7 @@ ExecInsert(ModifyTableState *mtstate,
*/
if (resultRelationDesc->rd_att->constr &&
resultRelationDesc->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(estate, slot);
+ ExecComputeStoredGenerated(estate, slot, CMD_INSERT);
/*
* insert into foreign table: let the FDW do it
@@ -427,7 +452,7 @@ ExecInsert(ModifyTableState *mtstate,
*/
if (resultRelationDesc->rd_att->constr &&
resultRelationDesc->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(estate, slot);
+ ExecComputeStoredGenerated(estate, slot, CMD_INSERT);
/*
* Check any RLS WITH CHECK policies.
@@ -1088,7 +1113,7 @@ ExecUpdate(ModifyTableState *mtstate,
*/
if (resultRelationDesc->rd_att->constr &&
resultRelationDesc->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(estate, slot);
+ ExecComputeStoredGenerated(estate, slot, CMD_UPDATE);
/*
* update in foreign table: let the FDW do it
@@ -1125,7 +1150,7 @@ ExecUpdate(ModifyTableState *mtstate,
*/
if (resultRelationDesc->rd_att->constr &&
resultRelationDesc->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(estate, slot);
+ ExecComputeStoredGenerated(estate, slot, CMD_UPDATE);
/*
* Check any RLS UPDATE WITH CHECK policies
diff --git a/src/include/executor/nodeModifyTable.h
b/src/include/executor/nodeModifyTable.h
index 891b119608..d44a64cb49 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -15,7 +15,7 @@
#include "nodes/execnodes.h"
-extern void ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot);
+extern void ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot,
CmdType cmdtype);
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState
*estate, int eflags);
extern void ExecEndModifyTable(ModifyTableState *node);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 0c2a77aaf8..93cca2e08d 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -456,6 +456,9 @@ typedef struct ResultRelInfo
/* array of stored generated columns expr states */
ExprState **ri_GeneratedExprs;
+ /* number of stored generated columns we need to compute */
+ int ri_NumGeneratedNeeded;
+
/* for removing junk attributes from tuples */
JunkFilter *ri_junkFilter;
--
2.24.1
