Clean up handling of constraint_exclusion and enable_partition_pruning.

tglsfdc · tglsfdc · commit 11ea45ffec9a · 2019-04-30T15:03:35.000-04:00
The interaction of these parameters was a bit confused/confusing, and in fact v11 entirely misses the opportunity to apply partition constraints when a partition is accessed directly (rather than indirectly from its parent). In HEAD, establish the principle that enable_partition_pruning controls partition pruning and nothing else. When accessing a partition via its parent, we do partition pruning (if enabled by enable_partition_pruning) and then there is no need to consider partition constraints in the constraint_exclusion logic. When accessing a partition directly, its partition constraints are applied by the constraint_exclusion logic, only if constraint_exclusion = on. In v11, we can't have such a clean division of these GUCs' effects, partly because we don't want to break compatibility too much in a released branch, and partly because the clean coding requires inheritance_planner to have applied partition pruning to a partitioned target table, which it doesn't in v11. However, we can tweak things enough to cover the missed case, which seems like a good idea since it's potentially a performance regression from v10. This patch keeps v11's previous behavior in which enable_partition_pruning overrides constraint_exclusion for an inherited target table, though. In HEAD, also teach relation_excluded_by_constraints that it's okay to use inheritable constraints when trying to prune a traditional inheritance tree. This might not be thought worthy of effort given that that feature is semi-deprecated now, but we have enough infrastructure that it only takes a couple more lines of code to do it correctly. Amit Langote and Tom Lane Discussion: https://postgr.es/m/9813f079-f16b-61c8-9ab7-4363cab28d80@lab.ntt.co.jp Discussion: https://postgr.es/m/29069.1555970894@sss.pgh.pa.us
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
@@ -4410,10 +4410,11 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
         The allowed values of <varname>constraint_exclusion</varname> are
         <literal>on</literal> (examine constraints for all tables),
         <literal>off</literal> (never examine constraints), and
-        <literal>partition</literal> (examine constraints only for inheritance child
-        tables and <literal>UNION ALL</literal> subqueries).
+        <literal>partition</literal> (examine constraints only for inheritance
+        child tables and <literal>UNION ALL</literal> subqueries).
         <literal>partition</literal> is the default setting.
-        It is often used with inheritance tables to improve performance.
+        It is often used with traditional inheritance trees to improve
+        performance.
       </para>
 
        <para>
@@ -4437,15 +4438,19 @@ SELECT * FROM parent WHERE key = 2400;
        <para>
         Currently, constraint exclusion is enabled by default
         only for cases that are often used to implement table partitioning via
-        inheritance tables.  Turning it on for all tables imposes extra
+        inheritance trees.  Turning it on for all tables imposes extra
         planning overhead that is quite noticeable on simple queries, and most
         often will yield no benefit for simple queries.  If you have no
-        inheritance partitioned tables you might prefer to turn it off entirely.
+        tables that are partitioned using traditional inheritance, you might
+        prefer to turn it off entirely.  (Note that the equivalent feature for
+        partitioned tables is controlled by a separate parameter,
+        <xref linkend="guc-enable-partition-pruning"/>.)
        </para>
 
        <para>
         Refer to <xref linkend="ddl-partitioning-constraint-exclusion"/> for
-        more information on using constraint exclusion and partitioning.
+        more information on using constraint exclusion to implement
+        partitioning.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
@@ -3918,22 +3918,11 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
 
    <note>
     <para>
-     Currently, pruning of partitions during the planning of an
-     <command>UPDATE</command> or <command>DELETE</command> command is
-     implemented using the constraint exclusion method (however, it is
-     controlled by the <literal>enable_partition_pruning</literal> rather than
-     <literal>constraint_exclusion</literal>) &mdash; see the following section
-     for details and caveats that apply.
-    </para>
-
-    <para>
-     Also, execution-time partition pruning currently only occurs for the
-     <literal>Append</literal> node type, not <literal>MergeAppend</literal>.
-    </para>
-
-    <para>
-     Both of these behaviors are likely to be changed in a future release
-     of <productname>PostgreSQL</productname>.
+     Execution-time partition pruning currently only occurs for the
+     <literal>Append</literal> node type, not
+     for <literal>MergeAppend</literal> or <literal>ModifyTable</literal>
+     nodes.  That is likely to be changed in a future release of
+     <productname>PostgreSQL</productname>.
     </para>
    </note>
   </sect2>
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
@@ -1324,8 +1324,9 @@ inheritance_planner(PlannerInfo *root)
 		parent_rte->securityQuals = NIL;
 
 		/*
-		 * Mark whether we're planning a query to a partitioned table or an
-		 * inheritance parent.
+		 * HACK: setting this to a value other than INHKIND_NONE signals to
+		 * relation_excluded_by_constraints() to treat the result relation as
+		 * being an appendrel member.
 		 */
 		subroot->inhTargetKind =
 			partitioned_relids ? INHKIND_PARTITIONED : INHKIND_INHERITED;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
@@ -66,7 +66,9 @@ static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
 static int32 get_rel_data_width(Relation rel, int32 *attr_widths);
 static List *get_relation_constraints(PlannerInfo *root,
 						 Oid relationObjectId, RelOptInfo *rel,
-						 bool include_notnull);
+						 bool include_noinherit,
+						 bool include_notnull,
+						 bool include_partition);
 static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
 				  Relation heapRelation);
 static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
@@ -1157,24 +1159,32 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
 /*
  * get_relation_constraints
  *
- * Retrieve the validated CHECK constraint expressions of the given relation.
+ * Retrieve the applicable constraint expressions of the given relation.
  *
  * Returns a List (possibly empty) of constraint expressions.  Each one
  * has been canonicalized, and its Vars are changed to have the varno
  * indicated by rel->relid.  This allows the expressions to be easily
  * compared to expressions taken from WHERE.
  *
+ * If include_noinherit is true, it's okay to include constraints that
+ * are marked NO INHERIT.
+ *
  * If include_notnull is true, "col IS NOT NULL" expressions are generated
  * and added to the result for each column that's marked attnotnull.
  *
+ * If include_partition is true, and the relation is a partition,
+ * also include the partitioning constraints.
+ *
  * Note: at present this is invoked at most once per relation per planner
  * run, and in many cases it won't be invoked at all, so there seems no
  * point in caching the data in RelOptInfo.
  */
 static List *
 get_relation_constraints(PlannerInfo *root,
 						 Oid relationObjectId, RelOptInfo *rel,
-						 bool include_notnull)
+						 bool include_noinherit,
+						 bool include_notnull,
+						 bool include_partition)
 {
 	List	   *result = NIL;
 	Index		varno = rel->relid;
@@ -1198,10 +1208,13 @@ get_relation_constraints(PlannerInfo *root,
 
 			/*
 			 * If this constraint hasn't been fully validated yet, we must
-			 * ignore it here.
+			 * ignore it here.  Also ignore if NO INHERIT and we weren't told
+			 * that that's safe.
 			 */
 			if (!constr->check[i].ccvalid)
 				continue;
+			if (constr->check[i].ccnoinherit && !include_noinherit)
+				continue;
 
 			cexpr = stringToNode(constr->check[i].ccbin);
 
@@ -1266,13 +1279,9 @@ get_relation_constraints(PlannerInfo *root,
 	}
 
 	/*
-	 * Append partition predicates, if any.
-	 *
-	 * For selects, partition pruning uses the parent table's partition bound
-	 * descriptor, instead of constraint exclusion which is driven by the
-	 * individual partition's partition constraint.
+	 * Add partitioning constraints, if requested.
 	 */
-	if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
+	if (include_partition && relation->rd_rel->relispartition)
 	{
 		List	   *pcqual = RelationGetPartitionQual(relation);
 
@@ -1377,7 +1386,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
  *
  * Detect whether the relation need not be scanned because it has either
  * self-inconsistent restrictions, or restrictions inconsistent with the
- * relation's validated CHECK constraints.
+ * relation's applicable constraints.
  *
  * Note: this examines only rel->relid, rel->reloptkind, and
  * rel->baserestrictinfo; therefore it can be called before filling in
@@ -1387,6 +1396,9 @@ bool
 relation_excluded_by_constraints(PlannerInfo *root,
 								 RelOptInfo *rel, RangeTblEntry *rte)
 {
+	bool		include_noinherit;
+	bool		include_notnull;
+	bool		include_partition = false;
 	List	   *safe_restrictions;
 	List	   *constraint_pred;
 	List	   *safe_constraints;
@@ -1395,6 +1407,13 @@ relation_excluded_by_constraints(PlannerInfo *root,
 	/* As of now, constraint exclusion works only with simple relations. */
 	Assert(IS_SIMPLE_REL(rel));
 
+	/*
+	 * If there are no base restriction clauses, we have no hope of proving
+	 * anything below, so fall out quickly.
+	 */
+	if (rel->baserestrictinfo == NIL)
+		return false;
+
 	/*
 	 * Regardless of the setting of constraint_exclusion, detect
 	 * constant-FALSE-or-NULL restriction clauses.  Because const-folding will
@@ -1415,6 +1434,17 @@ relation_excluded_by_constraints(PlannerInfo *root,
 			return true;
 	}
 
+	/*
+	 * Partition pruning will not have been applied to an inherited target
+	 * relation, so if enable_partition_pruning is true, force consideration
+	 * of the rel's partition constraints.  (Thus constraint_exclusion will be
+	 * effectively forced 'on' for this case.  This is done better in v12.)
+	 */
+	if (enable_partition_pruning &&
+		rel->relid == root->parse->resultRelation &&
+		root->inhTargetKind != INHKIND_NONE)
+		include_partition = true;
+
 	/*
 	 * Skip further tests, depending on constraint_exclusion.
 	 */
@@ -1423,33 +1453,43 @@ relation_excluded_by_constraints(PlannerInfo *root,
 		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.
+			 * In 'off' mode, never make any further tests, except if forcing
+			 * include_partition.
 			 */
-			if (root->inhTargetKind == INHKIND_PARTITIONED)
+			if (include_partition)
 				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.
+			 * appendrel members.  Normally, they are RELOPT_OTHER_MEMBER_REL
+			 * relations, but we also consider inherited target relations as
+			 * appendrel members for the purposes of constraint exclusion.
+			 *
+			 * In the former case, partition pruning was already applied, so
+			 * there is no need to consider the rel's partition constraints
+			 * here.  In the latter case, we already set include_partition
+			 * properly (i.e., do it if enable_partition_pruning).
 			 */
-			if ((rel->reloptkind != RELOPT_OTHER_MEMBER_REL) &&
-				!(rel->reloptkind == RELOPT_BASEREL &&
-				  root->inhTargetKind != INHKIND_NONE &&
-				  rel->relid == root->parse->resultRelation))
-				return false;
-			break;
+			if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
+				(rel->relid == root->parse->resultRelation &&
+				 root->inhTargetKind != INHKIND_NONE))
+				break;			/* appendrel member, so process it */
+			return false;
 
 		case CONSTRAINT_EXCLUSION_ON:
+
+			/*
+			 * In 'on' mode, always apply constraint exclusion.  If we are
+			 * considering a baserel that is a partition (i.e., it was
+			 * directly named rather than expanded from a parent table), then
+			 * its partition constraints haven't been considered yet, so
+			 * include them in the processing here.
+			 */
+			if (rel->reloptkind == RELOPT_BASEREL)
+				include_partition = true;
 			break;				/* always try to exclude */
 	}
 
@@ -1478,24 +1518,40 @@ relation_excluded_by_constraints(PlannerInfo *root,
 		return true;
 
 	/*
-	 * Only plain relations have constraints.  In a partitioning hierarchy,
-	 * but not with regular table inheritance, it's OK to assume that any
-	 * constraints that hold for the parent also hold for every child; for
-	 * instance, table inheritance allows the parent to have constraints
-	 * marked NO INHERIT, but table partitioning does not.  We choose to check
-	 * whether the partitioning parents can be excluded here; doing so
-	 * consumes some cycles, but potentially saves us the work of excluding
-	 * each child individually.
+	 * Only plain relations have constraints, so stop here for other rtekinds.
+	 */
+	if (rte->rtekind != RTE_RELATION)
+		return false;
+
+	/*
+	 * In a partitioning hierarchy, but not with regular table inheritance,
+	 * it's OK to assume that any constraints that hold for the parent also
+	 * hold for every child; for instance, table inheritance allows the parent
+	 * to have constraints marked NO INHERIT, but table partitioning does not.
+	 * We choose to check whether the partitioning parents can be excluded
+	 * here; doing so consumes some cycles, but potentially saves us the work
+	 * of excluding each child individually.
+	 *
+	 * This is unnecessarily stupid, but making it smarter seems out of scope
+	 * for v11.
 	 */
-	if (rte->rtekind != RTE_RELATION ||
-		(rte->inh && rte->relkind != RELKIND_PARTITIONED_TABLE))
+	if (rte->inh && rte->relkind != RELKIND_PARTITIONED_TABLE)
 		return false;
 
 	/*
-	 * OK to fetch the constraint expressions.  Include "col IS NOT NULL"
-	 * expressions for attnotnull columns, in case we can refute those.
+	 * Given the above restriction, we can always include NO INHERIT and NOT
+	 * NULL constraints.
+	 */
+	include_noinherit = true;
+	include_notnull = true;
+
+	/*
+	 * Fetch the appropriate set of constraint expressions.
 	 */
-	constraint_pred = get_relation_constraints(root, rte->relid, rel, true);
+	constraint_pred = get_relation_constraints(root, rte->relid, rel,
+											   include_noinherit,
+											   include_notnull,
+											   include_partition);
 
 	/*
 	 * We do not currently enforce that CHECK constraints contain only
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
@@ -3417,4 +3417,46 @@ select * from listp where a = (select 2) and b <> 10;
          Filter: ((b <> 10) AND (a = $0))
 (5 rows)
 
+--
+-- check that a partition directly accessed in a query is excluded with
+-- constraint_exclusion = on
+--
+-- turn off partition pruning, so that it doesn't interfere
+set enable_partition_pruning to off;
+-- setting constraint_exclusion to 'partition' disables exclusion
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+     QUERY PLAN     
+--------------------
+ Seq Scan on listp1
+   Filter: (a = 2)
+(2 rows)
+
+explain (costs off) update listp1 set a = 1 where a = 2;
+        QUERY PLAN        
+--------------------------
+ Update on listp1
+   ->  Seq Scan on listp1
+         Filter: (a = 2)
+(3 rows)
+
+-- constraint exclusion enabled
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+explain (costs off) update listp1 set a = 1 where a = 2;
+           QUERY PLAN           
+--------------------------------
+ Update on listp1
+   ->  Result
+         One-Time Filter: false
+(3 rows)
+
+reset constraint_exclusion;
+reset enable_partition_pruning;
 drop table listp;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
@@ -899,4 +899,24 @@ create table listp2_10 partition of listp2 for values in (10);
 explain (analyze, costs off, summary off, timing off)
 select * from listp where a = (select 2) and b <> 10;
 
+--
+-- check that a partition directly accessed in a query is excluded with
+-- constraint_exclusion = on
+--
+
+-- turn off partition pruning, so that it doesn't interfere
+set enable_partition_pruning to off;
+
+-- setting constraint_exclusion to 'partition' disables exclusion
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) update listp1 set a = 1 where a = 2;
+-- constraint exclusion enabled
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) update listp1 set a = 1 where a = 2;
+
+reset constraint_exclusion;
+reset enable_partition_pruning;
+
 drop table listp;
