Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commitc6679e4

Browse files
committed
Optimize update of tables with generated columns
When updating a table row with generated columns, only recompute thosegenerated columns whose base columns have changed in this update andkeep the rest unchanged. This can result in a significant performancebenefit. The required information was already kept inRangeTblEntry.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 parentad3ae64 commitc6679e4

File tree

5 files changed

+38
-10
lines changed

5 files changed

+38
-10
lines changed

‎src/backend/commands/copy.c

Lines 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

‎src/backend/executor/execReplication.c

Lines 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)

‎src/backend/executor/nodeModifyTable.c

Lines 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,CmdTypecmdtype)
250250
{
251251
ResultRelInfo*resultRelInfo=estate->es_result_relation_info;
252252
Relationrel=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 (inti=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_attributeattr=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
Datumval;
@@ -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

‎src/include/executor/nodeModifyTable.h

Lines 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-
externvoidExecComputeStoredGenerated(EState*estate,TupleTableSlot*slot);
18+
externvoidExecComputeStoredGenerated(EState*estate,TupleTableSlot*slot,CmdTypecmdtype);
1919

2020
externModifyTableState*ExecInitModifyTable(ModifyTable*node,EState*estate,inteflags);
2121
externvoidExecEndModifyTable(ModifyTableState*node);

‎src/include/nodes/execnodes.h

Lines 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+
intri_NumGeneratedNeeded;
462+
460463
/* for removing junk attributes from tuples */
461464
JunkFilter*ri_junkFilter;
462465

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp