Improve inheritance_planner()'s performance for large inheritance sets.

tglsfdc · tglsfdc · commit d8f9ab776c57 · 2015-06-22T18:53:27.000-04:00
Commit c03ad56 introduced a planner performance regression for UPDATE/DELETE on large inheritance sets. It required copying the append_rel_list (which is of size proportional to the number of inherited tables) once for each inherited table, thus resulting in O(N^2) time and memory consumption. While it's difficult to avoid that in general, the extra work only has to be done for append_rel_list entries that actually reference subquery RTEs, which inheritance-set entries will not. So we can buy back essentially all of the loss in cases without subqueries in FROM; and even for those, the added work is mainly proportional to the number of UNION ALL subqueries. Back-patch to 9.2, like the previous commit. Tom Lane and Dean Rasheed, per a complaint from Thomas Munro.
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
@@ -787,6 +787,8 @@ inheritance_planner(PlannerInfo *root)
 {
 	Query	   *parse = root->parse;
 	int			parentRTindex = parse->resultRelation;
+	Bitmapset  *subqueryRTindexes;
+	Bitmapset  *modifiableARIindexes;
 	List	   *final_rtable = NIL;
 	int			save_rel_array_size = 0;
 	RelOptInfo **save_rel_array = NULL;
@@ -796,6 +798,7 @@ inheritance_planner(PlannerInfo *root)
 	List	   *returningLists = NIL;
 	List	   *rowMarks;
 	ListCell   *lc;
+	Index		rti;
 
 	/*
 	 * We generate a modified instance of the original Query for each target
@@ -811,13 +814,54 @@ inheritance_planner(PlannerInfo *root)
 	 * (1) would result in a rangetable of length O(N^2) for N targets, with
 	 * at least O(N^3) work expended here; and (2) would greatly complicate
 	 * management of the rowMarks list.
+	 *
+	 * To begin with, generate a bitmapset of the relids of the subquery RTEs.
+	 */
+	subqueryRTindexes = NULL;
+	rti = 1;
+	foreach(lc, parse->rtable)
+	{
+		RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+		if (rte->rtekind == RTE_SUBQUERY)
+			subqueryRTindexes = bms_add_member(subqueryRTindexes, rti);
+		rti++;
+	}
+
+	/*
+	 * Next, we want to identify which AppendRelInfo items contain references
+	 * to any of the aforesaid subquery RTEs.  These items will need to be
+	 * copied and modified to adjust their subquery references; whereas the
+	 * other ones need not be touched.  It's worth being tense over this
+	 * because we can usually avoid processing most of the AppendRelInfo
+	 * items, thereby saving O(N^2) space and time when the target is a large
+	 * inheritance tree.  We can identify AppendRelInfo items by their
+	 * child_relid, since that should be unique within the list.
+	 */
+	modifiableARIindexes = NULL;
+	if (subqueryRTindexes != NULL)
+	{
+		foreach(lc, root->append_rel_list)
+		{
+			AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
+
+			if (bms_is_member(appinfo->parent_relid, subqueryRTindexes) ||
+				bms_is_member(appinfo->child_relid, subqueryRTindexes) ||
+				bms_overlap(pull_varnos((Node *) appinfo->translated_vars),
+							subqueryRTindexes))
+				modifiableARIindexes = bms_add_member(modifiableARIindexes,
+													  appinfo->child_relid);
+		}
+	}
+
+	/*
+	 * And now we can get on with generating a plan for each child table.
 	 */
 	foreach(lc, root->append_rel_list)
 	{
 		AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
 		PlannerInfo subroot;
 		Plan	   *subplan;
-		Index		rti;
 
 		/* append_rel_list contains all append rels; ignore others */
 		if (appinfo->parent_relid != parentRTindex)
@@ -851,9 +895,29 @@ inheritance_planner(PlannerInfo *root)
 		/*
 		 * The append_rel_list likewise might contain references to subquery
 		 * RTEs (if any subqueries were flattenable UNION ALLs).  So prepare
-		 * to apply ChangeVarNodes to that, too.
+		 * to apply ChangeVarNodes to that, too.  As explained above, we only
+		 * want to copy items that actually contain such references; the rest
+		 * can just get linked into the subroot's append_rel_list.
+		 *
+		 * If we know there are no such references, we can just use the outer
+		 * append_rel_list unmodified.
 		 */
-		subroot.append_rel_list = (List *) copyObject(root->append_rel_list);
+		if (modifiableARIindexes != NULL)
+		{
+			ListCell   *lc2;
+
+			subroot.append_rel_list = NIL;
+			foreach(lc2, root->append_rel_list)
+			{
+				AppendRelInfo *appinfo2 = (AppendRelInfo *) lfirst(lc2);
+
+				if (bms_is_member(appinfo2->child_relid, modifiableARIindexes))
+					appinfo2 = (AppendRelInfo *) copyObject(appinfo2);
+
+				subroot.append_rel_list = lappend(subroot.append_rel_list,
+												  appinfo2);
+			}
+		}
 
 		/*
 		 * Add placeholders to the child Query's rangetable list to fill the
@@ -873,7 +937,7 @@ inheritance_planner(PlannerInfo *root)
 		 * since subquery RTEs couldn't contain any references to the target
 		 * rel.
 		 */
-		if (final_rtable != NIL)
+		if (final_rtable != NIL && subqueryRTindexes != NULL)
 		{
 			ListCell   *lr;
 
@@ -882,7 +946,7 @@ inheritance_planner(PlannerInfo *root)
 			{
 				RangeTblEntry *rte = (RangeTblEntry *) lfirst(lr);
 
-				if (rte->rtekind == RTE_SUBQUERY)
+				if (bms_is_member(rti, subqueryRTindexes))
 				{
 					Index		newrti;
 
@@ -895,7 +959,20 @@ inheritance_planner(PlannerInfo *root)
 					newrti = list_length(subroot.parse->rtable) + 1;
 					ChangeVarNodes((Node *) subroot.parse, rti, newrti, 0);
 					ChangeVarNodes((Node *) subroot.rowMarks, rti, newrti, 0);
-					ChangeVarNodes((Node *) subroot.append_rel_list, rti, newrti, 0);
+					/* Skip processing unchanging parts of append_rel_list */
+					if (modifiableARIindexes != NULL)
+					{
+						ListCell   *lc2;
+
+						foreach(lc2, subroot.append_rel_list)
+						{
+							AppendRelInfo *appinfo2 = (AppendRelInfo *) lfirst(lc2);
+
+							if (bms_is_member(appinfo2->child_relid,
+											  modifiableARIindexes))
+								ChangeVarNodes((Node *) appinfo2, rti, newrti, 0);
+						}
+					}
 					rte = copyObject(rte);
 					subroot.parse->rtable = lappend(subroot.parse->rtable,
 													rte);
