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
Appearance settings

Commit c6679e4

Browse filesBrowse files
peterepetere
committed
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. Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/b05e781a-fa16-6b52-6738-761181204567@2ndquadrant.com
1 parent ad3ae64 commit c6679e4
Copy full SHA for c6679e4

File tree

Expand file treeCollapse file tree

5 files changed

+38
-10
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

5 files changed

+38
-10
lines changed
Open diff view settings
Collapse file

‎src/backend/commands/copy.c‎

Copy file name to clipboardExpand all lines: src/backend/commands/copy.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3222,7 +3222,7 @@ CopyFrom(CopyState cstate)
32223222
/* Compute stored generated columns */
32233223
if (resultRelInfo->ri_RelationDesc->rd_att->constr &&
32243224
resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored)
3225-
ExecComputeStoredGenerated(estate, myslot);
3225+
ExecComputeStoredGenerated(estate, myslot, CMD_INSERT);
32263226

32273227
/*
32283228
* If the target is a plain table, check the constraints of
Collapse file

‎src/backend/executor/execReplication.c‎

Copy file name to clipboardExpand all lines: src/backend/executor/execReplication.c
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -419,7 +419,7 @@ ExecSimpleRelationInsert(EState *estate, TupleTableSlot *slot)
419419
/* Compute stored generated columns */
420420
if (rel->rd_att->constr &&
421421
rel->rd_att->constr->has_generated_stored)
422-
ExecComputeStoredGenerated(estate, slot);
422+
ExecComputeStoredGenerated(estate, slot, CMD_INSERT);
423423

424424
/* Check the constraints of the tuple */
425425
if (rel->rd_att->constr)
@@ -485,7 +485,7 @@ ExecSimpleRelationUpdate(EState *estate, EPQState *epqstate,
485485
/* Compute stored generated columns */
486486
if (rel->rd_att->constr &&
487487
rel->rd_att->constr->has_generated_stored)
488-
ExecComputeStoredGenerated(estate, slot);
488+
ExecComputeStoredGenerated(estate, slot, CMD_UPDATE);
489489

490490
/* Check the constraints of the tuple */
491491
if (rel->rd_att->constr)
Collapse file

‎src/backend/executor/nodeModifyTable.c‎

Copy file name to clipboardExpand all lines: src/backend/executor/nodeModifyTable.c
+31-6Lines changed: 31 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -246,7 +246,7 @@ ExecCheckTIDVisible(EState *estate,
246246
* Compute stored generated columns for a tuple
247247
*/
248248
void
249-
ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)
249+
ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot, CmdType cmdtype)
250250
{
251251
ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
252252
Relation rel = resultRelInfo->ri_RelationDesc;
@@ -269,25 +269,49 @@ ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)
269269

270270
resultRelInfo->ri_GeneratedExprs =
271271
(ExprState **) palloc(natts * sizeof(ExprState *));
272+
resultRelInfo->ri_NumGeneratedNeeded = 0;
272273

273274
for (int i = 0; i < natts; i++)
274275
{
275276
if (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED)
276277
{
277278
Expr *expr;
278279

280+
/*
281+
* If it's an update and the current column was not marked as
282+
* being updated, then we can skip the computation. But if
283+
* there is a BEFORE ROW UPDATE trigger, we cannot skip
284+
* because the trigger might affect additional columns.
285+
*/
286+
if (cmdtype == CMD_UPDATE &&
287+
!(rel->trigdesc && rel->trigdesc->trig_update_before_row) &&
288+
!bms_is_member(i + 1 - FirstLowInvalidHeapAttributeNumber,
289+
exec_rt_fetch(resultRelInfo->ri_RangeTableIndex, estate)->extraUpdatedCols))
290+
{
291+
resultRelInfo->ri_GeneratedExprs[i] = NULL;
292+
continue;
293+
}
294+
279295
expr = (Expr *) build_column_default(rel, i + 1);
280296
if (expr == NULL)
281297
elog(ERROR, "no generation expression found for column number %d of table \"%s\"",
282298
i + 1, RelationGetRelationName(rel));
283299

284300
resultRelInfo->ri_GeneratedExprs[i] = ExecPrepareExpr(expr, estate);
301+
resultRelInfo->ri_NumGeneratedNeeded++;
285302
}
286303
}
287304

288305
MemoryContextSwitchTo(oldContext);
289306
}
290307

308+
/*
309+
* If no generated columns have been affected by this change, then skip
310+
* the rest.
311+
*/
312+
if (resultRelInfo->ri_NumGeneratedNeeded == 0)
313+
return;
314+
291315
oldContext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
292316

293317
values = palloc(sizeof(*values) * natts);
@@ -300,7 +324,8 @@ ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot)
300324
{
301325
Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
302326

303-
if (attr->attgenerated == ATTRIBUTE_GENERATED_STORED)
327+
if (attr->attgenerated == ATTRIBUTE_GENERATED_STORED &&
328+
resultRelInfo->ri_GeneratedExprs[i])
304329
{
305330
ExprContext *econtext;
306331
Datum val;
@@ -392,7 +417,7 @@ ExecInsert(ModifyTableState *mtstate,
392417
*/
393418
if (resultRelationDesc->rd_att->constr &&
394419
resultRelationDesc->rd_att->constr->has_generated_stored)
395-
ExecComputeStoredGenerated(estate, slot);
420+
ExecComputeStoredGenerated(estate, slot, CMD_INSERT);
396421

397422
/*
398423
* insert into foreign table: let the FDW do it
@@ -427,7 +452,7 @@ ExecInsert(ModifyTableState *mtstate,
427452
*/
428453
if (resultRelationDesc->rd_att->constr &&
429454
resultRelationDesc->rd_att->constr->has_generated_stored)
430-
ExecComputeStoredGenerated(estate, slot);
455+
ExecComputeStoredGenerated(estate, slot, CMD_INSERT);
431456

432457
/*
433458
* Check any RLS WITH CHECK policies.
@@ -1088,7 +1113,7 @@ ExecUpdate(ModifyTableState *mtstate,
10881113
*/
10891114
if (resultRelationDesc->rd_att->constr &&
10901115
resultRelationDesc->rd_att->constr->has_generated_stored)
1091-
ExecComputeStoredGenerated(estate, slot);
1116+
ExecComputeStoredGenerated(estate, slot, CMD_UPDATE);
10921117

10931118
/*
10941119
* update in foreign table: let the FDW do it
@@ -1125,7 +1150,7 @@ ExecUpdate(ModifyTableState *mtstate,
11251150
*/
11261151
if (resultRelationDesc->rd_att->constr &&
11271152
resultRelationDesc->rd_att->constr->has_generated_stored)
1128-
ExecComputeStoredGenerated(estate, slot);
1153+
ExecComputeStoredGenerated(estate, slot, CMD_UPDATE);
11291154

11301155
/*
11311156
* Check any RLS UPDATE WITH CHECK policies
Collapse file

‎src/include/executor/nodeModifyTable.h‎

Copy file name to clipboardExpand all lines: src/include/executor/nodeModifyTable.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515

1616
#include "nodes/execnodes.h"
1717

18-
extern void ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot);
18+
extern void ExecComputeStoredGenerated(EState *estate, TupleTableSlot *slot, CmdType cmdtype);
1919

2020
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags);
2121
extern void ExecEndModifyTable(ModifyTableState *node);
Collapse file

‎src/include/nodes/execnodes.h‎

Copy file name to clipboardExpand all lines: src/include/nodes/execnodes.h
+3Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -457,6 +457,9 @@ typedef struct ResultRelInfo
457457
/* array of stored generated columns expr states */
458458
ExprState **ri_GeneratedExprs;
459459

460+
/* number of stored generated columns we need to compute */
461+
int ri_NumGeneratedNeeded;
462+
460463
/* for removing junk attributes from tuples */
461464
JunkFilter *ri_junkFilter;
462465

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.