Add GUC enable_partition_pruning

This controls both plan-time and execution-time new-style partition
pruning.  While finer-grain control is possible (maybe using an enum GUC
instead of boolean), there doesn't seem to be much need for that.

This new parameter controls partition pruning for all queries:
trivially, SELECT queries that affect partitioned tables are naturally
under its control since they are using the new technology.  However,
while UPDATE/DELETE queries do not use the new code, we make the new GUC
control their behavior also (stealing control from
constraint_exclusion), because it is more natural, and it leads to a
more natural transition to the future in which those queries will also
use the new pruning code.

Constraint exclusion still controls pruning for regular inheritance
situations (those not involving partitioned tables).

Author: David Rowley
Review: Amit Langote, Ashutosh Bapat, Justin Pryzby, David G. Johnston
Discussion: https://postgr.es/m/CAKJS1f_0HwsxJG9m+nzU+CizxSdGtfe6iF_ykPYBiYft302DCw@mail.gmail.com

9 files changed, 65 insertions, 18 deletions

diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 3991a0ce83a..f2d00c5e312 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2291,7 +2291,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node)
 	WRITE_FLOAT_FIELD(tuple_fraction, "%.4f");
 	WRITE_FLOAT_FIELD(limit_tuples, "%.0f");
 	WRITE_UINT_FIELD(qual_security_level);
-	WRITE_BOOL_FIELD(hasInheritedTarget);
+	WRITE_ENUM_FIELD(inhTargetKind, InheritanceKind);
 	WRITE_BOOL_FIELD(hasJoinRTEs);
 	WRITE_BOOL_FIELD(hasLateralRTEs);
 	WRITE_BOOL_FIELD(hasDeletedRTEs);
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 3ba3f87eb7c..9ed73da0f79 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -901,7 +901,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 	 * store the relids of all partitions which could possibly contain a
 	 * matching tuple, and skip anything else in the loop below.
 	 */
-	if (rte->relkind == RELKIND_PARTITIONED_TABLE &&
+	if (enable_partition_pruning &&
+		rte->relkind == RELKIND_PARTITIONED_TABLE &&
 		rel->baserestrictinfo != NIL)
 	{
 		live_children = prune_append_rel_partitions(rel);
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 957f751bd48..a2a7e0c5202 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -138,6 +138,7 @@ bool		enable_partitionwise_join = false;
 bool		enable_partitionwise_aggregate = false;
 bool		enable_parallel_append = true;
 bool		enable_parallel_hash = true;
+bool		enable_partition_pruning = true;
 
 typedef struct
 {
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 280f21cd45c..0317763f43c 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -1077,7 +1077,8 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path)
 		subplans = lappend(subplans, subplan);
 	}
 
-	if (rel->reloptkind == RELOPT_BASEREL &&
+	if (enable_partition_pruning &&
+		rel->reloptkind == RELOPT_BASEREL &&
 		best_path->partitioned_rels != NIL)
 	{
 		List	   *prunequal;
@@ -1979,7 +1980,7 @@ create_groupingsets_plan(PlannerInfo *root, GroupingSetsPath *best_path)
 	 * create_modifytable_plan).  Fortunately we can't be because there would
 	 * never be grouping in an UPDATE/DELETE; but let's Assert that.
 	 */
-	Assert(!root->hasInheritedTarget);
+	Assert(root->inhTargetKind == INHKIND_NONE);
 	Assert(root->grouping_map == NULL);
 	root->grouping_map = grouping_map;
 
@@ -2141,7 +2142,7 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path)
 	 * create_modifytable_plan).  Fortunately we can't be because there would
 	 * never be aggregates in an UPDATE/DELETE; but let's Assert that.
 	 */
-	Assert(!root->hasInheritedTarget);
+	Assert(root->inhTargetKind == INHKIND_NONE);
 	Assert(root->minmax_aggs == NIL);
 	root->minmax_aggs = best_path->mmaggregates;
 
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 2e298f83573..24e6ee026e1 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -623,7 +623,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
 	root->grouping_map = NULL;
 	root->minmax_aggs = NIL;
 	root->qual_security_level = 0;
-	root->hasInheritedTarget = false;
+	root->inhTargetKind = INHKIND_NONE;
 	root->hasRecursion = hasRecursion;
 	if (hasRecursion)
 		root->wt_param_id = SS_assign_special_param(root);
@@ -1424,8 +1424,13 @@ inheritance_planner(PlannerInfo *root)
 		Assert(subroot->join_info_list == NIL);
 		/* and we haven't created PlaceHolderInfos, either */
 		Assert(subroot->placeholder_list == NIL);
-		/* hack to mark target relation as an inheritance partition */
-		subroot->hasInheritedTarget = true;
+
+		/*
+		 * Mark if we're planning a query to a partitioned table or an
+		 * inheritance parent.
+		 */
+		subroot->inhTargetKind =
+			partitioned_relids ? INHKIND_PARTITIONED : INHKIND_INHERITED;
 
 		/*
 		 * If the child is further partitioned, remember it as a parent. Since
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 45d82da4591..c3f46a26c3a 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -914,7 +914,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
 	subroot->grouping_map = NULL;
 	subroot->minmax_aggs = NIL;
 	subroot->qual_security_level = 0;
-	subroot->hasInheritedTarget = false;
+	subroot->inhTargetKind = INHKIND_NONE;
 	subroot->hasRecursion = false;
 	subroot->wt_param_id = -1;
 	subroot->non_recursive_path = NULL;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 1ff0ef4866b..6973fe34586 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1272,7 +1272,7 @@ get_relation_constraints(PlannerInfo *root,
 	 * descriptor, instead of constraint exclusion which is driven by the
 	 * individual partition's partition constraint.
 	 */
-	if (root->parse->commandType != CMD_SELECT)
+	if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
 	{
 		List	   *pcqual = RelationGetPartitionQual(relation);
 
@@ -1415,14 +1415,41 @@ relation_excluded_by_constraints(PlannerInfo *root,
 			return true;
 	}
 
-	/* Skip further tests if constraint exclusion is disabled for the rel */
-	if (constraint_exclusion == CONSTRAINT_EXCLUSION_OFF ||
-		(constraint_exclusion == CONSTRAINT_EXCLUSION_PARTITION &&
-		 !(rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
-		   (root->hasInheritedTarget &&
-			rel->reloptkind == RELOPT_BASEREL &&
-			rel->relid == root->parse->resultRelation))))
-		return false;
+	/*
+	 * Skip further tests, depending on constraint_exclusion.
+	 */
+	switch (constraint_exclusion)
+	{
+		case CONSTRAINT_EXCLUSION_OFF:
+			/*
+			 * Don't prune if feature turned off -- except if the relation is
+			 * a partition.  While partprune.c-style partition pruning is not
+			 * yet in use for all cases (update/delete is not handled), it
+			 * would be a UI horror to use different user-visible controls
+			 * depending on such a volatile implementation detail.  Therefore,
+			 * for partitioned tables we use enable_partition_pruning to
+			 * control this behavior.
+			 */
+			if (root->inhTargetKind == INHKIND_PARTITIONED)
+				break;
+			return false;
+
+		case CONSTRAINT_EXCLUSION_PARTITION:
+			/*
+			 * When constraint_exclusion is set to 'partition' we only handle
+			 * OTHER_MEMBER_RELs, or BASERELs in cases where the result target
+			 * is an inheritance parent or a partitioned table.
+			 */
+			if ((rel->reloptkind != RELOPT_OTHER_MEMBER_REL) &&
+				!(rel->reloptkind == RELOPT_BASEREL &&
+				  root->inhTargetKind != INHKIND_NONE &&
+				  rel->relid == root->parse->resultRelation))
+				  return false;
+			break;
+
+		case CONSTRAINT_EXCLUSION_ON:
+			break;		/* always try to exclude */
+	}
 
 	/*
 	 * Check for self-contradictory restriction clauses.  We dare not make
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index fa92ce2e683..44dfa92722c 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -952,6 +952,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 	{
+		{"enable_partition_pruning", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enable plan-time and run-time partition pruning."),
+			gettext_noop("Allows the query planner and executor to compare partition "
+						 "bounds to conditions in the query to determine which "
+						 "partitions must be scanned.")
+		},
+		&enable_partition_pruning,
+		true,
+		NULL, NULL, NULL
+	},
+	{
 		{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
 			gettext_noop("Enables genetic query optimization."),
 			gettext_noop("This algorithm attempts to do planning without "
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 66d09388278..3d88e80a204 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -308,6 +308,7 @@
 #enable_partitionwise_join = off
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
+#enable_partition_pruning = on
 
 # - Planner Cost Constants -