Postpone some stuff out of ExecInitModifyTable.

Arrange to do some things on-demand, rather than immediately during
executor startup, because there's a fair chance of never having to do
them at all:

* Don't open result relations' indexes until needed.

* Don't initialize partition tuple routing, nor the child-to-root
tuple conversion map, until needed.

This wins in UPDATEs on partitioned tables when only some of the
partitions will actually receive updates; with larger partition
counts the savings is quite noticeable.  Also, we can remove some
sketchy heuristics in ExecInitModifyTable about whether to set up
tuple routing.

Also, remove execPartition.c's private hash table tracking which
partitions were already opened by the ModifyTable node.  Instead
use the hash added to ModifyTable itself by commit 86dc90056.

To allow lazy computation of the conversion maps, we now set
ri_RootResultRelInfo in all child ResultRelInfos.  We formerly set it
only in some, not terribly well-defined, cases.  This has user-visible
side effects in that now more error messages refer to the root
relation instead of some partition (and provide error data in the
root's column order, too).  It looks to me like this is a strict
improvement in consistency, so I don't have a problem with the
output changes visible in this commit.

Extracted from a larger patch, which seemed to me to be too messy
to push in one commit.

Amit Langote, reviewed at different times by Heikki Linnakangas and
myself

Discussion: https://postgr.es/m/CA+HiwqG7ZruBmmih3wPsBZ4s0H2EhywrnXEduckY5Hr3fWzPWA@mail.gmail.com

4 files changed, 212 insertions, 252 deletions

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 9f86910a6bf..bf43e7d379e 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1231,11 +1231,19 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
 	resultRelInfo->ri_ReturningSlot = NULL;
 	resultRelInfo->ri_TrigOldSlot = NULL;
 	resultRelInfo->ri_TrigNewSlot = NULL;
+
+	/*
+	 * Only ExecInitPartitionInfo() and ExecInitPartitionDispatchInfo() pass
+	 * non-NULL partition_root_rri.  For child relations that are part of the
+	 * initial query rather than being dynamically added by tuple routing,
+	 * this field is filled in ExecInitModifyTable().
+	 */
 	resultRelInfo->ri_RootResultRelInfo = partition_root_rri;
 	resultRelInfo->ri_RootToPartitionMap = NULL;	/* set by
 													 * ExecInitRoutingInfo */
 	resultRelInfo->ri_PartitionTupleSlot = NULL;	/* ditto */
 	resultRelInfo->ri_ChildToRootMap = NULL;
+	resultRelInfo->ri_ChildToRootMapValid = false;
 	resultRelInfo->ri_CopyMultiInsertBuffer = NULL;
 }
 
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 558060e080f..99780ebb961 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -66,11 +66,17 @@
  *
  * partitions
  *		Array of 'max_partitions' elements containing a pointer to a
- *		ResultRelInfo for every leaf partitions touched by tuple routing.
+ *		ResultRelInfo for every leaf partition touched by tuple routing.
  *		Some of these are pointers to ResultRelInfos which are borrowed out of
- *		'subplan_resultrel_htab'.  The remainder have been built especially
- *		for tuple routing.  See comment for PartitionDispatchData->indexes for
- *		details on how this array is indexed.
+ *		the owning ModifyTableState node.  The remainder have been built
+ *		especially for tuple routing.  See comment for
+ *		PartitionDispatchData->indexes for details on how this array is
+ *		indexed.
+ *
+ * is_borrowed_rel
+ *		Array of 'max_partitions' booleans recording whether a given entry
+ *		in 'partitions' is a ResultRelInfo pointer borrowed from the owning
+ *		ModifyTableState node, rather than being built here.
  *
  * num_partitions
  *		The current number of items stored in the 'partitions' array.  Also
@@ -80,12 +86,6 @@
  * max_partitions
  *		The current allocated size of the 'partitions' array.
  *
- * subplan_resultrel_htab
- *		Hash table to store subplan ResultRelInfos by Oid.  This is used to
- *		cache ResultRelInfos from targets of an UPDATE ModifyTable node;
- *		NULL in other cases.  Some of these may be useful for tuple routing
- *		to save having to build duplicates.
- *
  * memcxt
  *		Memory context used to allocate subsidiary structs.
  *-----------------------
@@ -98,9 +98,9 @@ struct PartitionTupleRouting
 	int			num_dispatch;
 	int			max_dispatch;
 	ResultRelInfo **partitions;
+	bool	   *is_borrowed_rel;
 	int			num_partitions;
 	int			max_partitions;
-	HTAB	   *subplan_resultrel_htab;
 	MemoryContext memcxt;
 };
 
@@ -153,16 +153,7 @@ typedef struct PartitionDispatchData
 	int			indexes[FLEXIBLE_ARRAY_MEMBER];
 }			PartitionDispatchData;
 
-/* struct to hold result relations coming from UPDATE subplans */
-typedef struct SubplanResultRelHashElem
-{
-	Oid			relid;			/* hash key -- must be first */
-	ResultRelInfo *rri;
-} SubplanResultRelHashElem;
-
 
-static void ExecHashSubPlanResultRelsByOid(ModifyTableState *mtstate,
-										   PartitionTupleRouting *proute);
 static ResultRelInfo *ExecInitPartitionInfo(ModifyTableState *mtstate,
 											EState *estate, PartitionTupleRouting *proute,
 											PartitionDispatch dispatch,
@@ -173,7 +164,8 @@ static void ExecInitRoutingInfo(ModifyTableState *mtstate,
 								PartitionTupleRouting *proute,
 								PartitionDispatch dispatch,
 								ResultRelInfo *partRelInfo,
-								int partidx);
+								int partidx,
+								bool is_borrowed_rel);
 static PartitionDispatch ExecInitPartitionDispatchInfo(EState *estate,
 													   PartitionTupleRouting *proute,
 													   Oid partoid, PartitionDispatch parent_pd,
@@ -215,11 +207,9 @@ static void find_matching_subplans_recurse(PartitionPruningData *prunedata,
  * it should be estate->es_query_cxt.
  */
 PartitionTupleRouting *
-ExecSetupPartitionTupleRouting(EState *estate, ModifyTableState *mtstate,
-							   Relation rel)
+ExecSetupPartitionTupleRouting(EState *estate, Relation rel)
 {
 	PartitionTupleRouting *proute;
-	ModifyTable *node = mtstate ? (ModifyTable *) mtstate->ps.plan : NULL;
 
 	/*
 	 * Here we attempt to expend as little effort as possible in setting up
@@ -241,17 +231,6 @@ ExecSetupPartitionTupleRouting(EState *estate, ModifyTableState *mtstate,
 	ExecInitPartitionDispatchInfo(estate, proute, RelationGetRelid(rel),
 								  NULL, 0, NULL);
 
-	/*
-	 * If performing an UPDATE with tuple routing, we can reuse partition
-	 * sub-plan result rels.  We build a hash table to map the OIDs of
-	 * partitions present in mtstate->resultRelInfo to their ResultRelInfos.
-	 * Every time a tuple is routed to a partition that we've yet to set the
-	 * ResultRelInfo for, before we go to the trouble of making one, we check
-	 * for a pre-made one in the hash table.
-	 */
-	if (node && node->operation == CMD_UPDATE)
-		ExecHashSubPlanResultRelsByOid(mtstate, proute);
-
 	return proute;
 }
 
@@ -351,7 +330,6 @@ ExecFindPartition(ModifyTableState *mtstate,
 		is_leaf = partdesc->is_leaf[partidx];
 		if (is_leaf)
 		{
-
 			/*
 			 * We've reached the leaf -- hurray, we're done.  Look to see if
 			 * we've already got a ResultRelInfo for this partition.
@@ -364,42 +342,33 @@ ExecFindPartition(ModifyTableState *mtstate,
 			}
 			else
 			{
-				bool		found = false;
-
 				/*
-				 * We have not yet set up a ResultRelInfo for this partition,
-				 * but if we have a subplan hash table, we might have one
-				 * there.  If not, we'll have to create one.
+				 * If the partition is known in the owning ModifyTableState
+				 * node, we can re-use that ResultRelInfo instead of creating
+				 * a new one with ExecInitPartitionInfo().
 				 */
-				if (proute->subplan_resultrel_htab)
+				rri = ExecLookupResultRelByOid(mtstate,
+											   partdesc->oids[partidx],
+											   true, false);
+				if (rri)
 				{
-					Oid			partoid = partdesc->oids[partidx];
-					SubplanResultRelHashElem *elem;
+					/* Verify this ResultRelInfo allows INSERTs */
+					CheckValidResultRel(rri, CMD_INSERT);
 
-					elem = hash_search(proute->subplan_resultrel_htab,
-									   &partoid, HASH_FIND, NULL);
-					if (elem)
-					{
-						found = true;
-						rri = elem->rri;
-
-						/* Verify this ResultRelInfo allows INSERTs */
-						CheckValidResultRel(rri, CMD_INSERT);
-
-						/*
-						 * Initialize information needed to insert this and
-						 * subsequent tuples routed to this partition.
-						 */
-						ExecInitRoutingInfo(mtstate, estate, proute, dispatch,
-											rri, partidx);
-					}
+					/*
+					 * Initialize information needed to insert this and
+					 * subsequent tuples routed to this partition.
+					 */
+					ExecInitRoutingInfo(mtstate, estate, proute, dispatch,
+										rri, partidx, true);
 				}
-
-				/* We need to create a new one. */
-				if (!found)
+				else
+				{
+					/* We need to create a new one. */
 					rri = ExecInitPartitionInfo(mtstate, estate, proute,
 												dispatch,
 												rootResultRelInfo, partidx);
+				}
 			}
 			Assert(rri != NULL);
 
@@ -510,50 +479,6 @@ ExecFindPartition(ModifyTableState *mtstate,
 }
 
 /*
- * ExecHashSubPlanResultRelsByOid
- *		Build a hash table to allow fast lookups of subplan ResultRelInfos by
- *		partition Oid.  We also populate the subplan ResultRelInfo with an
- *		ri_PartitionRoot.
- */
-static void
-ExecHashSubPlanResultRelsByOid(ModifyTableState *mtstate,
-							   PartitionTupleRouting *proute)
-{
-	HASHCTL		ctl;
-	HTAB	   *htab;
-	int			i;
-
-	ctl.keysize = sizeof(Oid);
-	ctl.entrysize = sizeof(SubplanResultRelHashElem);
-	ctl.hcxt = CurrentMemoryContext;
-
-	htab = hash_create("PartitionTupleRouting table", mtstate->mt_nrels,
-					   &ctl, HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
-	proute->subplan_resultrel_htab = htab;
-
-	/* Hash all subplans by their Oid */
-	for (i = 0; i < mtstate->mt_nrels; i++)
-	{
-		ResultRelInfo *rri = &mtstate->resultRelInfo[i];
-		bool		found;
-		Oid			partoid = RelationGetRelid(rri->ri_RelationDesc);
-		SubplanResultRelHashElem *elem;
-
-		elem = (SubplanResultRelHashElem *)
-			hash_search(htab, &partoid, HASH_ENTER, &found);
-		Assert(!found);
-		elem->rri = rri;
-
-		/*
-		 * This is required in order to convert the partition's tuple to be
-		 * compatible with the root partitioned table's tuple descriptor. When
-		 * generating the per-subplan result rels, this was not set.
-		 */
-		rri->ri_RootResultRelInfo = mtstate->rootResultRelInfo;
-	}
-}
-
-/*
  * ExecInitPartitionInfo
  *		Lock the partition and initialize ResultRelInfo.  Also setup other
  *		information for the partition and store it in the next empty slot in
@@ -613,7 +538,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
 	 * didn't build the withCheckOptionList for partitions within the planner,
 	 * but simple translation of varattnos will suffice.  This only occurs for
 	 * the INSERT case or in the case of UPDATE tuple routing where we didn't
-	 * find a result rel to reuse in ExecSetupPartitionTupleRouting().
+	 * find a result rel to reuse.
 	 */
 	if (node && node->withCheckOptionLists != NIL)
 	{
@@ -676,7 +601,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
 	 * build the returningList for partitions within the planner, but simple
 	 * translation of varattnos will suffice.  This only occurs for the INSERT
 	 * case or in the case of UPDATE tuple routing where we didn't find a
-	 * result rel to reuse in ExecSetupPartitionTupleRouting().
+	 * result rel to reuse.
 	 */
 	if (node && node->returningLists != NIL)
 	{
@@ -734,7 +659,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
 
 	/* Set up information needed for routing tuples to the partition. */
 	ExecInitRoutingInfo(mtstate, estate, proute, dispatch,
-						leaf_part_rri, partidx);
+						leaf_part_rri, partidx, false);
 
 	/*
 	 * If there is an ON CONFLICT clause, initialize state for it.
@@ -911,15 +836,6 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
 	}
 
 	/*
-	 * Also, if transition capture is required, store a map to convert tuples
-	 * from partition's rowtype to the root partition table's.
-	 */
-	if (mtstate->mt_transition_capture || mtstate->mt_oc_transition_capture)
-		leaf_part_rri->ri_ChildToRootMap =
-			convert_tuples_by_name(RelationGetDescr(leaf_part_rri->ri_RelationDesc),
-								   RelationGetDescr(rootResultRelInfo->ri_RelationDesc));
-
-	/*
 	 * Since we've just initialized this ResultRelInfo, it's not in any list
 	 * attached to the estate as yet.  Add it, so that it can be found later.
 	 *
@@ -949,7 +865,8 @@ ExecInitRoutingInfo(ModifyTableState *mtstate,
 					PartitionTupleRouting *proute,
 					PartitionDispatch dispatch,
 					ResultRelInfo *partRelInfo,
-					int partidx)
+					int partidx,
+					bool is_borrowed_rel)
 {
 	ResultRelInfo *rootRelInfo = partRelInfo->ri_RootResultRelInfo;
 	MemoryContext oldcxt;
@@ -1029,6 +946,8 @@ ExecInitRoutingInfo(ModifyTableState *mtstate,
 			proute->max_partitions = 8;
 			proute->partitions = (ResultRelInfo **)
 				palloc(sizeof(ResultRelInfo *) * proute->max_partitions);
+			proute->is_borrowed_rel = (bool *)
+				palloc(sizeof(bool) * proute->max_partitions);
 		}
 		else
 		{
@@ -1036,10 +955,14 @@ ExecInitRoutingInfo(ModifyTableState *mtstate,
 			proute->partitions = (ResultRelInfo **)
 				repalloc(proute->partitions, sizeof(ResultRelInfo *) *
 						 proute->max_partitions);
+			proute->is_borrowed_rel = (bool *)
+				repalloc(proute->is_borrowed_rel, sizeof(bool) *
+						 proute->max_partitions);
 		}
 	}
 
 	proute->partitions[rri_index] = partRelInfo;
+	proute->is_borrowed_rel[rri_index] = is_borrowed_rel;
 	dispatch->indexes[partidx] = rri_index;
 
 	MemoryContextSwitchTo(oldcxt);
@@ -1199,7 +1122,6 @@ void
 ExecCleanupTupleRouting(ModifyTableState *mtstate,
 						PartitionTupleRouting *proute)
 {
-	HTAB	   *htab = proute->subplan_resultrel_htab;
 	int			i;
 
 	/*
@@ -1230,20 +1152,11 @@ ExecCleanupTupleRouting(ModifyTableState *mtstate,
 														   resultRelInfo);
 
 		/*
-		 * Check if this result rel is one belonging to the node's subplans,
-		 * if so, let ExecEndPlan() clean it up.
+		 * Close it if it's not one of the result relations borrowed from the
+		 * owning ModifyTableState; those will be closed by ExecEndPlan().
 		 */
-		if (htab)
-		{
-			Oid			partoid;
-			bool		found;
-
-			partoid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
-
-			(void) hash_search(htab, &partoid, HASH_FIND, &found);
-			if (found)
-				continue;
-		}
+		if (proute->is_borrowed_rel[i])
+			continue;
 
 		ExecCloseIndices(resultRelInfo);
 		table_close(resultRelInfo->ri_RelationDesc, NoLock);
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 42632cb4d88..ad11392b99d 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -1225,6 +1225,32 @@ ExecGetReturningSlot(EState *estate, ResultRelInfo *relInfo)
 	return relInfo->ri_ReturningSlot;
 }
 
+/*
+ * Return the map needed to convert given child result relation's tuples to
+ * the rowtype of the query's main target ("root") relation.  Note that a
+ * NULL result is valid and means that no conversion is needed.
+ */
+TupleConversionMap *
+ExecGetChildToRootMap(ResultRelInfo *resultRelInfo)
+{
+	/* If we didn't already do so, compute the map for this child. */
+	if (!resultRelInfo->ri_ChildToRootMapValid)
+	{
+		ResultRelInfo *rootRelInfo = resultRelInfo->ri_RootResultRelInfo;
+
+		if (rootRelInfo)
+			resultRelInfo->ri_ChildToRootMap =
+				convert_tuples_by_name(RelationGetDescr(resultRelInfo->ri_RelationDesc),
+									   RelationGetDescr(rootRelInfo->ri_RelationDesc));
+		else					/* this isn't a child result rel */
+			resultRelInfo->ri_ChildToRootMap = NULL;
+
+		resultRelInfo->ri_ChildToRootMapValid = true;
+	}
+
+	return resultRelInfo->ri_ChildToRootMap;
+}
+
 /* Return a bitmap representing columns being inserted */
 Bitmapset *
 ExecGetInsertedCols(ResultRelInfo *relinfo, EState *estate)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index bf65785e643..249555f234b 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -493,6 +493,14 @@ ExecInsert(ModifyTableState *mtstate,
 	resultRelationDesc = resultRelInfo->ri_RelationDesc;
 
 	/*
+	 * Open the table's indexes, if we have not done so already, so that we
+	 * can add new index entries for the inserted tuple.
+	 */
+	if (resultRelationDesc->rd_rel->relhasindex &&
+		resultRelInfo->ri_IndexRelationDescs == NULL)
+		ExecOpenIndices(resultRelInfo, onconflict != ONCONFLICT_NONE);
+
+	/*
 	 * BEFORE ROW INSERT Triggers.
 	 *
 	 * Note: We fire BEFORE ROW TRIGGERS for every attempted insertion in an
@@ -1276,7 +1284,6 @@ ExecCrossPartitionUpdate(ModifyTableState *mtstate,
 						 TupleTableSlot **inserted_tuple)
 {
 	EState	   *estate = mtstate->ps.state;
-	PartitionTupleRouting *proute = mtstate->mt_partition_tuple_routing;
 	TupleConversionMap *tupconv_map;
 	bool		tuple_deleted;
 	TupleTableSlot *epqslot = NULL;
@@ -1296,13 +1303,35 @@ ExecCrossPartitionUpdate(ModifyTableState *mtstate,
 				 errdetail("The result tuple would appear in a different partition than the original tuple.")));
 
 	/*
-	 * When an UPDATE is run on a leaf partition, we will not have partition
-	 * tuple routing set up.  In that case, fail with partition constraint
-	 * violation error.
+	 * When an UPDATE is run directly on a leaf partition, simply fail with a
+	 * partition constraint violation error.
 	 */
-	if (proute == NULL)
+	if (resultRelInfo == mtstate->rootResultRelInfo)
 		ExecPartitionCheckEmitError(resultRelInfo, slot, estate);
 
+	/* Initialize tuple routing info if not already done. */
+	if (mtstate->mt_partition_tuple_routing == NULL)
+	{
+		Relation	rootRel = mtstate->rootResultRelInfo->ri_RelationDesc;
+		MemoryContext oldcxt;
+
+		/* Things built here have to last for the query duration. */
+		oldcxt = MemoryContextSwitchTo(estate->es_query_cxt);
+
+		mtstate->mt_partition_tuple_routing =
+			ExecSetupPartitionTupleRouting(estate, rootRel);
+
+		/*
+		 * Before a partition's tuple can be re-routed, it must first be
+		 * converted to the root's format, so we'll need a slot for storing
+		 * such tuples.
+		 */
+		Assert(mtstate->mt_root_tuple_slot == NULL);
+		mtstate->mt_root_tuple_slot = table_slot_create(rootRel, NULL);
+
+		MemoryContextSwitchTo(oldcxt);
+	}
+
 	/*
 	 * Row movement, part 1.  Delete the tuple, but skip RETURNING processing.
 	 * We want to return rows from INSERT.
@@ -1364,7 +1393,7 @@ ExecCrossPartitionUpdate(ModifyTableState *mtstate,
 	 * convert the tuple into root's tuple descriptor if needed, since
 	 * ExecInsert() starts the search from root.
 	 */
-	tupconv_map = resultRelInfo->ri_ChildToRootMap;
+	tupconv_map = ExecGetChildToRootMap(resultRelInfo);
 	if (tupconv_map != NULL)
 		slot = execute_attr_map_slot(tupconv_map->attrMap,
 									 slot,
@@ -1436,6 +1465,14 @@ ExecUpdate(ModifyTableState *mtstate,
 
 	ExecMaterializeSlot(slot);
 
+	/*
+	 * Open the table's indexes, if we have not done so already, so that we
+	 * can add new index entries for the updated tuple.
+	 */
+	if (resultRelationDesc->rd_rel->relhasindex &&
+		resultRelInfo->ri_IndexRelationDescs == NULL)
+		ExecOpenIndices(resultRelInfo, false);
+
 	/* BEFORE ROW UPDATE Triggers */
 	if (resultRelInfo->ri_TrigDesc &&
 		resultRelInfo->ri_TrigDesc->trig_update_before_row)
@@ -2244,38 +2281,8 @@ ExecModifyTable(PlanState *pstate)
 
 			/* If it's not the same as last time, we need to locate the rel */
 			if (resultoid != node->mt_lastResultOid)
-			{
-				if (node->mt_resultOidHash)
-				{
-					/* Use the pre-built hash table to locate the rel */
-					MTTargetRelLookup *mtlookup;
-
-					mtlookup = (MTTargetRelLookup *)
-						hash_search(node->mt_resultOidHash, &resultoid,
-									HASH_FIND, NULL);
-					if (!mtlookup)
-						elog(ERROR, "incorrect result rel OID %u", resultoid);
-					node->mt_lastResultOid = resultoid;
-					node->mt_lastResultIndex = mtlookup->relationIndex;
-					resultRelInfo = node->resultRelInfo + mtlookup->relationIndex;
-				}
-				else
-				{
-					/* With few target rels, just do a simple search */
-					int			ndx;
-
-					for (ndx = 0; ndx < node->mt_nrels; ndx++)
-					{
-						resultRelInfo = node->resultRelInfo + ndx;
-						if (RelationGetRelid(resultRelInfo->ri_RelationDesc) == resultoid)
-							break;
-					}
-					if (ndx >= node->mt_nrels)
-						elog(ERROR, "incorrect result rel OID %u", resultoid);
-					node->mt_lastResultOid = resultoid;
-					node->mt_lastResultIndex = ndx;
-				}
-			}
+				resultRelInfo = ExecLookupResultRelByOid(node, resultoid,
+														 false, true);
 		}
 
 		/*
@@ -2466,6 +2473,61 @@ ExecModifyTable(PlanState *pstate)
 	return NULL;
 }
 
+/*
+ * ExecLookupResultRelByOid
+ * 		If the table with given OID is among the result relations to be
+ * 		updated by the given ModifyTable node, return its ResultRelInfo.
+ *
+ * If not found, return NULL if missing_ok, else raise error.
+ *
+ * If update_cache is true, then upon successful lookup, update the node's
+ * one-element cache.  ONLY ExecModifyTable may pass true for this.
+ */
+ResultRelInfo *
+ExecLookupResultRelByOid(ModifyTableState *node, Oid resultoid,
+						 bool missing_ok, bool update_cache)
+{
+	if (node->mt_resultOidHash)
+	{
+		/* Use the pre-built hash table to locate the rel */
+		MTTargetRelLookup *mtlookup;
+
+		mtlookup = (MTTargetRelLookup *)
+			hash_search(node->mt_resultOidHash, &resultoid, HASH_FIND, NULL);
+		if (mtlookup)
+		{
+			if (update_cache)
+			{
+				node->mt_lastResultOid = resultoid;
+				node->mt_lastResultIndex = mtlookup->relationIndex;
+			}
+			return node->resultRelInfo + mtlookup->relationIndex;
+		}
+	}
+	else
+	{
+		/* With few target rels, just search the ResultRelInfo array */
+		for (int ndx = 0; ndx < node->mt_nrels; ndx++)
+		{
+			ResultRelInfo *rInfo = node->resultRelInfo + ndx;
+
+			if (RelationGetRelid(rInfo->ri_RelationDesc) == resultoid)
+			{
+				if (update_cache)
+				{
+					node->mt_lastResultOid = resultoid;
+					node->mt_lastResultIndex = ndx;
+				}
+				return rInfo;
+			}
+		}
+	}
+
+	if (!missing_ok)
+		elog(ERROR, "incorrect result relation OID %u", resultoid);
+	return NULL;
+}
+
 /* ----------------------------------------------------------------
  *		ExecInitModifyTable
  * ----------------------------------------------------------------
@@ -2482,7 +2544,6 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	ListCell   *l;
 	int			i;
 	Relation	rel;
-	bool		update_tuple_routing_needed = node->partColsUpdated;
 
 	/* check for unsupported flags */
 	Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
@@ -2554,8 +2615,18 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 		Index		resultRelation = lfirst_int(l);
 
 		if (resultRelInfo != mtstate->rootResultRelInfo)
+		{
 			ExecInitResultRelation(estate, resultRelInfo, resultRelation);
 
+			/*
+			 * For child result relations, store the root result relation
+			 * pointer.  We do so for the convenience of places that want to
+			 * look at the query's original target relation but don't have the
+			 * mtstate handy.
+			 */
+			resultRelInfo->ri_RootResultRelInfo = mtstate->rootResultRelInfo;
+		}
+
 		/* Initialize the usesFdwDirectModify flag */
 		resultRelInfo->ri_usesFdwDirectModify = bms_is_member(i,
 															  node->fdwDirectModifyPlans);
@@ -2581,32 +2652,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	{
 		resultRelInfo = &mtstate->resultRelInfo[i];
 
-		/*
-		 * If there are indices on the result relation, open them and save
-		 * descriptors in the result relation info, so that we can add new
-		 * index entries for the tuples we add/update.  We need not do this
-		 * for a DELETE, however, since deletion doesn't affect indexes. Also,
-		 * inside an EvalPlanQual operation, the indexes might be open
-		 * already, since we share the resultrel state with the original
-		 * query.
-		 */
-		if (resultRelInfo->ri_RelationDesc->rd_rel->relhasindex &&
-			operation != CMD_DELETE &&
-			resultRelInfo->ri_IndexRelationDescs == NULL)
-			ExecOpenIndices(resultRelInfo,
-							node->onConflictAction != ONCONFLICT_NONE);
-
-		/*
-		 * If this is an UPDATE and a BEFORE UPDATE trigger is present, the
-		 * trigger itself might modify the partition-key values. So arrange
-		 * for tuple routing.
-		 */
-		if (resultRelInfo->ri_TrigDesc &&
-			resultRelInfo->ri_TrigDesc->trig_update_before_row &&
-			operation == CMD_UPDATE)
-			update_tuple_routing_needed = true;
-
-		/* Also let FDWs init themselves for foreign-table result rels */
+		/* Let FDWs init themselves for foreign-table result rels */
 		if (!resultRelInfo->ri_usesFdwDirectModify &&
 			resultRelInfo->ri_FdwRoutine != NULL &&
 			resultRelInfo->ri_FdwRoutine->BeginForeignModify != NULL)
@@ -2619,52 +2665,20 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 															 i,
 															 eflags);
 		}
-
-		/*
-		 * If needed, initialize a map to convert tuples in the child format
-		 * to the format of the table mentioned in the query (root relation).
-		 * It's needed for update tuple routing, because the routing starts
-		 * from the root relation.  It's also needed for capturing transition
-		 * tuples, because the transition tuple store can only store tuples in
-		 * the root table format.
-		 *
-		 * For INSERT, the map is only initialized for a given partition when
-		 * the partition itself is first initialized by ExecFindPartition().
-		 */
-		if (update_tuple_routing_needed ||
-			(mtstate->mt_transition_capture &&
-			 mtstate->operation != CMD_INSERT))
-			resultRelInfo->ri_ChildToRootMap =
-				convert_tuples_by_name(RelationGetDescr(resultRelInfo->ri_RelationDesc),
-									   RelationGetDescr(mtstate->rootResultRelInfo->ri_RelationDesc));
 	}
 
 	/* Get the root target relation */
 	rel = mtstate->rootResultRelInfo->ri_RelationDesc;
 
 	/*
-	 * If it's not a partitioned table after all, UPDATE tuple routing should
-	 * not be attempted.
-	 */
-	if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
-		update_tuple_routing_needed = false;
-
-	/*
-	 * Build state for tuple routing if it's an INSERT or if it's an UPDATE of
-	 * partition key.
+	 * Build state for tuple routing if it's a partitioned INSERT.  An UPDATE
+	 * might need this too, but only if it actually moves tuples between
+	 * partitions; in that case setup is done by ExecCrossPartitionUpdate.
 	 */
 	if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
-		(operation == CMD_INSERT || update_tuple_routing_needed))
+		operation == CMD_INSERT)
 		mtstate->mt_partition_tuple_routing =
-			ExecSetupPartitionTupleRouting(estate, mtstate, rel);
-
-	/*
-	 * For update row movement we'll need a dedicated slot to store the tuples
-	 * that have been converted from partition format to the root table
-	 * format.
-	 */
-	if (update_tuple_routing_needed)
-		mtstate->mt_root_tuple_slot = table_slot_create(rel, NULL);
+			ExecSetupPartitionTupleRouting(estate, rel);
 
 	/*
 	 * Initialize any WITH CHECK OPTION constraints if needed.
@@ -2743,7 +2757,11 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	/* Set the list of arbiter indexes if needed for ON CONFLICT */
 	resultRelInfo = mtstate->resultRelInfo;
 	if (node->onConflictAction != ONCONFLICT_NONE)
+	{
+		/* insert may only have one relation, inheritance is not expanded */
+		Assert(nrels == 1);
 		resultRelInfo->ri_onConflictArbiterIndexes = node->arbiterIndexes;
+	}
 
 	/*
 	 * If needed, Initialize target list, projection and qual for ON CONFLICT
@@ -2755,9 +2773,6 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 		TupleDesc	relationDesc;
 		TupleDesc	tupDesc;
 
-		/* insert may only have one relation, inheritance is not expanded */
-		Assert(nrels == 1);
-
 		/* already exists if created by RETURNING processing above */
 		if (mtstate->ps.ps_ExprContext == NULL)
 			ExecAssignExprContext(estate, &mtstate->ps);
@@ -3036,22 +3051,20 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
 	 */
 	if (operation == CMD_INSERT)
 	{
+		/* insert may only have one relation, inheritance is not expanded */
+		Assert(nrels == 1);
 		resultRelInfo = mtstate->resultRelInfo;
-		for (i = 0; i < nrels; i++)
+		if (!resultRelInfo->ri_usesFdwDirectModify &&
+			resultRelInfo->ri_FdwRoutine != NULL &&
+			resultRelInfo->ri_FdwRoutine->GetForeignModifyBatchSize &&
+			resultRelInfo->ri_FdwRoutine->ExecForeignBatchInsert)
 		{
-			if (!resultRelInfo->ri_usesFdwDirectModify &&
-				resultRelInfo->ri_FdwRoutine != NULL &&
-				resultRelInfo->ri_FdwRoutine->GetForeignModifyBatchSize &&
-				resultRelInfo->ri_FdwRoutine->ExecForeignBatchInsert)
-				resultRelInfo->ri_BatchSize =
-					resultRelInfo->ri_FdwRoutine->GetForeignModifyBatchSize(resultRelInfo);
-			else
-				resultRelInfo->ri_BatchSize = 1;
-
+			resultRelInfo->ri_BatchSize =
+				resultRelInfo->ri_FdwRoutine->GetForeignModifyBatchSize(resultRelInfo);
 			Assert(resultRelInfo->ri_BatchSize >= 1);
-
-			resultRelInfo++;
 		}
+		else
+			resultRelInfo->ri_BatchSize = 1;
 	}
 
 	/*