Support partition pruning on boolcol IS [NOT] UNKNOWN

david-rowley · david-rowley · commit 07c36c1333e0 · 2024-03-04T14:40:22.000+13:00
While working on 4c2369a, I noticed we went out of our way not to support clauses on boolean partitioned tables in the form of "IS UNKNOWN" and "IS NOT UNKNOWN". It's almost as much code to disallow this as it is to allow it, so let's allow it. Discussion: https://postgr.es/m/CAApHDvobKtcN6+xOuOfcutfp6T7jP=JPA9y3=MAEqnuKdDsQrw@mail.gmail.com
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
@@ -200,7 +200,7 @@ static PartClauseMatchStatus match_boolean_partition_clause(Oid partopfamily,
 															Expr *clause,
 															Expr *partkey,
 															Expr **outconst,
-															bool *noteq);
+															bool *notclause);
 static void partkey_datum_from_expr(PartitionPruneContext *context,
 									Expr *expr, int stateidx,
 									Datum *value, bool *isnull);
@@ -1798,13 +1798,14 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
 	Oid			partopfamily = part_scheme->partopfamily[partkeyidx],
 				partcoll = part_scheme->partcollation[partkeyidx];
 	Expr	   *expr;
-	bool		noteq;
+	bool		notclause;
 
 	/*
 	 * Recognize specially shaped clauses that match a Boolean partition key.
 	 */
 	boolmatchstatus = match_boolean_partition_clause(partopfamily, clause,
-													 partkey, &expr, &noteq);
+													 partkey, &expr,
+													 &notclause);
 
 	if (boolmatchstatus == PARTCLAUSE_MATCH_CLAUSE)
 	{
@@ -1818,7 +1819,7 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
 		 * punt it off to gen_partprune_steps_internal() to generate pruning
 		 * steps.
 		 */
-		if (noteq)
+		if (notclause)
 		{
 			List	   *new_clauses;
 			List	   *or_clause;
@@ -1836,9 +1837,8 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
 			else
 			{
 				/*
-				 * We only expect match_boolean_partition_clause to match for
-				 * IS_NOT_TRUE and IS_NOT_FALSE.  IS_NOT_UNKNOWN is not
-				 * supported.
+				 * We only expect match_boolean_partition_clause to return
+				 * PARTCLAUSE_MATCH_CLAUSE for IS_NOT_TRUE and IS_NOT_FALSE.
 				 */
 				Assert(false);
 			}
@@ -1876,6 +1876,15 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
 
 		return PARTCLAUSE_MATCH_CLAUSE;
 	}
+	else if (boolmatchstatus == PARTCLAUSE_MATCH_NULLNESS)
+	{
+		/*
+		 * Handle IS UNKNOWN and IS NOT UNKNOWN.  These just logically
+		 * translate to IS NULL and IS NOT NULL.
+		 */
+		*clause_is_not_null = notclause;
+		return PARTCLAUSE_MATCH_NULLNESS;
+	}
 	else if (IsA(clause, OpExpr) &&
 			 list_length(((OpExpr *) clause)->args) == 2)
 	{
@@ -3652,22 +3661,23 @@ perform_pruning_combine_step(PartitionPruneContext *context,
  * match_boolean_partition_clause
  *
  * If we're able to match the clause to the partition key as specially-shaped
- * boolean clause, set *outconst to a Const containing a true or false value,
- * set *noteq according to if the clause was in the "not" form, i.e. "is not
- * true" or "is not false", and return PARTCLAUSE_MATCH_CLAUSE.  Returns
- * PARTCLAUSE_UNSUPPORTED if the clause is not a boolean clause or if the
- * boolean clause is unsuitable for partition pruning.  Returns
- * PARTCLAUSE_NOMATCH if it's a bool quals but just does not match this
- * partition key.  *outconst is set to NULL in the latter two cases.
+ * boolean clause, set *outconst to a Const containing a true, false or NULL
+ * value, set *notclause according to if the clause was in the "not" form,
+ * i.e. "IS NOT TRUE", "IS NOT FALSE" or "IS NOT UNKNOWN" and return
+ * PARTCLAUSE_MATCH_CLAUSE for "IS [NOT] (TRUE|FALSE)" clauses and
+ * PARTCLAUSE_MATCH_NULLNESS for "IS [NOT] UNKNOWN" clauses.  Otherwise,
+ * return PARTCLAUSE_UNSUPPORTED if the clause cannot be used for partition
+ * pruning, and PARTCLAUSE_NOMATCH for supported clauses that do not match this
+ * 'partkey'.
  */
 static PartClauseMatchStatus
 match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
-							   Expr **outconst, bool *noteq)
+							   Expr **outconst, bool *notclause)
 {
 	Expr	   *leftop;
 
 	*outconst = NULL;
-	*noteq = false;
+	*notclause = false;
 
 	/*
 	 * Partitioning currently can only use built-in AMs, so checking for
@@ -3680,11 +3690,6 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
 	{
 		BooleanTest *btest = (BooleanTest *) clause;
 
-		/* Only IS [NOT] TRUE/FALSE are any good to us */
-		if (btest->booltesttype == IS_UNKNOWN ||
-			btest->booltesttype == IS_NOT_UNKNOWN)
-			return PARTCLAUSE_UNSUPPORTED;
-
 		leftop = btest->arg;
 		if (IsA(leftop, RelabelType))
 			leftop = ((RelabelType *) leftop)->arg;
@@ -3694,23 +3699,28 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
 			switch (btest->booltesttype)
 			{
 				case IS_NOT_TRUE:
-					*noteq = true;
+					*notclause = true;
 					/* fall through */
 				case IS_TRUE:
 					*outconst = (Expr *) makeBoolConst(true, false);
-					break;
+					return PARTCLAUSE_MATCH_CLAUSE;
 				case IS_NOT_FALSE:
-					*noteq = true;
+					*notclause = true;
 					/* fall through */
 				case IS_FALSE:
 					*outconst = (Expr *) makeBoolConst(false, false);
-					break;
+					return PARTCLAUSE_MATCH_CLAUSE;
+				case IS_NOT_UNKNOWN:
+					*notclause = true;
+					/* fall through */
+				case IS_UNKNOWN:
+					return PARTCLAUSE_MATCH_NULLNESS;
 				default:
 					return PARTCLAUSE_UNSUPPORTED;
 			}
 		}
-		if (*outconst)
-			return PARTCLAUSE_MATCH_CLAUSE;
+		/* does not match partition key */
+		return PARTCLAUSE_NOMATCH;
 	}
 	else
 	{
@@ -3726,12 +3736,11 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
 			*outconst = (Expr *) makeBoolConst(!is_not_clause, false);
 		else if (equal(negate_clause((Node *) leftop), partkey))
 			*outconst = (Expr *) makeBoolConst(is_not_clause, false);
+		else
+			return PARTCLAUSE_NOMATCH;
 
-		if (*outconst)
-			return PARTCLAUSE_MATCH_CLAUSE;
+		return PARTCLAUSE_MATCH_CLAUSE;
 	}
-
-	return PARTCLAUSE_NOMATCH;
 }
 
 /*
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
@@ -1093,16 +1093,11 @@ explain (costs off) select * from boolpart where a is not true and a is not fals
 (2 rows)
 
 explain (costs off) select * from boolpart where a is unknown;
-                  QUERY PLAN                   
------------------------------------------------
- Append
-   ->  Seq Scan on boolpart_f boolpart_1
-         Filter: (a IS UNKNOWN)
-   ->  Seq Scan on boolpart_t boolpart_2
-         Filter: (a IS UNKNOWN)
-   ->  Seq Scan on boolpart_default boolpart_3
-         Filter: (a IS UNKNOWN)
-(7 rows)
+              QUERY PLAN               
+---------------------------------------
+ Seq Scan on boolpart_default boolpart
+   Filter: (a IS UNKNOWN)
+(2 rows)
 
 explain (costs off) select * from boolpart where a is not unknown;
                   QUERY PLAN                   
@@ -1200,6 +1195,18 @@ explain (costs off) select * from boolpart where a is not false;
          Filter: (a IS NOT FALSE)
 (5 rows)
 
+explain (costs off) select * from boolpart where a is not unknown;
+                  QUERY PLAN                   
+-----------------------------------------------
+ Append
+   ->  Seq Scan on boolpart_f boolpart_1
+         Filter: (a IS NOT UNKNOWN)
+   ->  Seq Scan on boolpart_t boolpart_2
+         Filter: (a IS NOT UNKNOWN)
+   ->  Seq Scan on boolpart_default boolpart_3
+         Filter: (a IS NOT UNKNOWN)
+(7 rows)
+
 select * from boolpart where a is not true;
  a 
 ---
@@ -1220,6 +1227,35 @@ select * from boolpart where a is not false;
  
 (2 rows)
 
+select * from boolpart where a is not unknown;
+ a 
+---
+ f
+ t
+(2 rows)
+
+-- check that all partitions are pruned when faced with conflicting clauses
+explain (costs off) select * from boolpart where a is not unknown and a is unknown;
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from boolpart where a is false and a is unknown;
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from boolpart where a is true and a is unknown;
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
 -- inverse boolean partitioning - a seemingly unlikely design, but we've got
 -- code for it, so we'd better test it.
 create table iboolpart (a bool) partition by list ((not a));
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
@@ -186,10 +186,17 @@ insert into boolpart values(null);
 explain (costs off) select * from boolpart where a is not true;
 explain (costs off) select * from boolpart where a is not true and a is not false;
 explain (costs off) select * from boolpart where a is not false;
+explain (costs off) select * from boolpart where a is not unknown;
 
 select * from boolpart where a is not true;
 select * from boolpart where a is not true and a is not false;
 select * from boolpart where a is not false;
+select * from boolpart where a is not unknown;
+
+-- check that all partitions are pruned when faced with conflicting clauses
+explain (costs off) select * from boolpart where a is not unknown and a is unknown;
+explain (costs off) select * from boolpart where a is false and a is unknown;
+explain (costs off) select * from boolpart where a is true and a is unknown;
 
 -- inverse boolean partitioning - a seemingly unlikely design, but we've got
 -- code for it, so we'd better test it.
