v1-0001-Reduce-Var-IS-NOT-NULL-quals-during-constant-folding.patch

From ab04f6d91498c85dba26ea7bd2b84526e22ba99b Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 19 Mar 2025 16:16:12 +0900
Subject: [PATCH v1] Reduce "Var IS [NOT] NULL" quals during constant folding

---
 src/backend/optimizer/plan/initsplan.c        | 26 +------
 src/backend/optimizer/util/clauses.c          | 69 ++++++++++++++++++-
 src/backend/optimizer/util/inherit.c          | 29 +++++---
 src/backend/optimizer/util/plancat.c          | 30 --------
 src/backend/optimizer/util/relnode.c          |  3 -
 src/backend/parser/parse_relation.c           | 30 ++++++++
 src/include/nodes/parsenodes.h                |  5 ++
 src/include/nodes/pathnodes.h                 |  6 --
 src/include/optimizer/optimizer.h             |  2 +
 src/include/parser/parse_relation.h           |  1 +
 .../regress/expected/generated_virtual.out    |  6 +-
 src/test/regress/expected/join.out            |  6 +-
 src/test/regress/expected/predicate.out       |  6 +-
 13 files changed, 136 insertions(+), 83 deletions(-)

diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 1d1aa27d450..fc6f4f2cef4 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -545,7 +545,7 @@ remove_useless_groupby_columns(PlannerInfo *root)
 				 */
 				if (!index->nullsnotdistinct &&
 					!bms_is_member(index->indexkeys[i],
-								   rel->notnullattnums))
+								   rte->notnullattnums))
 				{
 					nulls_check_ok = false;
 					break;
@@ -3048,36 +3048,16 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
  * expr_is_nonnullable
  *	  Check to see if the Expr cannot be NULL
  *
- * If the Expr is a simple Var that is defined NOT NULL and meanwhile is not
- * nulled by any outer joins, then we can know that it cannot be NULL.
+ * Currently we only support simple Vars.
  */
 static bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr)
 {
-	RelOptInfo *rel;
-	Var		   *var;
-
 	/* For now only check simple Vars */
 	if (!IsA(expr, Var))
 		return false;
 
-	var = (Var *) expr;
-
-	/* could the Var be nulled by any outer joins? */
-	if (!bms_is_empty(var->varnullingrels))
-		return false;
-
-	/* system columns cannot be NULL */
-	if (var->varattno < 0)
-		return true;
-
-	/* is the column defined NOT NULL? */
-	rel = find_base_rel(root, var->varno);
-	if (var->varattno > 0 &&
-		bms_is_member(var->varattno, rel->notnullattnums))
-		return true;
-
-	return false;
+	return var_is_nonnullable(root, (Var *) expr);
 }
 
 /*
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 43dfecfb47f..196407d423b 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -20,6 +20,7 @@
 #include "postgres.h"
 
 #include "access/htup_details.h"
+#include "catalog/pg_class.h"
 #include "catalog/pg_language.h"
 #include "catalog/pg_operator.h"
 #include "catalog/pg_proc.h"
@@ -41,6 +42,7 @@
 #include "parser/analyze.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_func.h"
+#include "parser/parsetree.h"
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "tcop/tcopprot.h"
@@ -2240,7 +2242,8 @@ rowtype_field_matches(Oid rowtypeid, int fieldnum,
  * only operators and functions that are reasonable to try to execute.
  *
  * NOTE: "root" can be passed as NULL if the caller never wants to do any
- * Param substitutions nor receive info about inlined functions.
+ * Param substitutions nor receive info about inlined functions nor reduce
+ * NullTest for Vars to constant true or constant false.
  *
  * NOTE: the planner assumes that this will always flatten nested AND and
  * OR clauses into N-argument form.  See comments in prepqual.c.
@@ -3535,6 +3538,31 @@ eval_const_expressions_mutator(Node *node,
 
 					return makeBoolConst(result, false);
 				}
+				if (!ntest->argisrow && arg && IsA(arg, Var) && context->root)
+				{
+					Var		   *varg = (Var *) arg;
+					bool		result;
+
+					if (var_is_nonnullable(context->root, varg))
+					{
+						switch (ntest->nulltesttype)
+						{
+							case IS_NULL:
+								result = false;
+								break;
+							case IS_NOT_NULL:
+								result = true;
+								break;
+							default:
+								elog(ERROR, "unrecognized nulltesttype: %d",
+									 (int) ntest->nulltesttype);
+								result = false; /* keep compiler quiet */
+								break;
+						}
+
+						return makeBoolConst(result, false);
+					}
+				}
 
 				newntest = makeNode(NullTest);
 				newntest->arg = (Expr *) arg;
@@ -4153,6 +4181,45 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
 	return newexpr;
 }
 
+/*
+ * var_is_nonnullable
+ *	  Check to see if the Var cannot be NULL
+ *
+ * If the Var is defined NOT NULL and meanwhile is not nulled by any outer
+ * joins or grouping sets, then we can know that it cannot be NULL.
+ */
+bool
+var_is_nonnullable(PlannerInfo *root, Var *var)
+{
+	RangeTblEntry *rte;
+
+	Assert(IsA(var, Var));
+
+	/* could the Var be nulled by any outer joins or grouping sets? */
+	if (!bms_is_empty(var->varnullingrels))
+		return false;
+
+	/* system columns cannot be NULL */
+	if (var->varattno < 0)
+		return true;
+
+	/*
+	 * Check if the Var is defined as NOT NULL.  We must skip inheritance
+	 * parent tables, as some child tables may have a NOT NULL constraint for
+	 * a column while others may not.  This cannot happen with partitioned
+	 * tables, though.
+	 */
+	rte = planner_rt_fetch(var->varno, root);
+	if (rte->inh && rte->relkind != RELKIND_PARTITIONED_TABLE)
+		return false;
+
+	if (var->varattno > 0 &&
+		bms_is_member(var->varattno, rte->notnullattnums))
+		return true;
+
+	return false;
+}
+
 /*
  * expand_function_arguments: convert named-notation args to positional args
  * and/or insert default args, as needed
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index 17e51cd75d7..dda72d8adf8 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -466,8 +466,7 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
 								Index *childRTindex_p)
 {
 	Query	   *parse = root->parse;
-	Oid			parentOID PG_USED_FOR_ASSERTS_ONLY =
-		RelationGetRelid(parentrel);
+	Oid			parentOID = RelationGetRelid(parentrel);
 	Oid			childOID = RelationGetRelid(childrel);
 	RangeTblEntry *childrte;
 	Index		childRTindex;
@@ -479,15 +478,16 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
 	/*
 	 * Build an RTE for the child, and attach to query's rangetable list. We
 	 * copy most scalar fields of the parent's RTE, but replace relation OID,
-	 * relkind, and inh for the child.  Set the child's securityQuals to
-	 * empty, because we only want to apply the parent's RLS conditions
-	 * regardless of what RLS properties individual children may have. (This
-	 * is an intentional choice to make inherited RLS work like regular
-	 * permissions checks.) The parent securityQuals will be propagated to
-	 * children along with other base restriction clauses, so we don't need to
-	 * do it here.  Other infrastructure of the parent RTE has to be
-	 * translated to match the child table's column ordering, which we do
-	 * below, so a "flat" copy is sufficient to start with.
+	 * relkind, and inh for the child.  We also replace notnullattnums for the
+	 * child if its relation OID is different from the parent's.  Set the
+	 * child's securityQuals to empty, because we only want to apply the
+	 * parent's RLS conditions regardless of what RLS properties individual
+	 * children may have. (This is an intentional choice to make inherited RLS
+	 * work like regular permissions checks.) The parent securityQuals will be
+	 * propagated to children along with other base restriction clauses, so we
+	 * don't need to do it here.  Other infrastructure of the parent RTE has
+	 * to be translated to match the child table's column ordering, which we
+	 * do below, so a "flat" copy is sufficient to start with.
 	 */
 	childrte = makeNode(RangeTblEntry);
 	memcpy(childrte, parentrte, sizeof(RangeTblEntry));
@@ -507,6 +507,13 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
 	/* No permission checking for child RTEs. */
 	childrte->perminfoindex = 0;
 
+	/* Record NOT NULL columns for the child if needed. */
+	if (childOID != parentOID)
+	{
+		childrte->notnullattnums = NULL;
+		getRelationAttrs(childrel, childrte);
+	}
+
 	/* Link not-yet-fully-filled child RTE into data structures */
 	parse->rtable = lappend(parse->rtable, childrte);
 	childRTindex = list_length(parse->rtable);
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 0489ad36644..4a44ff18a29 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -162,36 +162,6 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	rel->attr_widths = (int32 *)
 		palloc0((rel->max_attr - rel->min_attr + 1) * sizeof(int32));
 
-	/*
-	 * Record which columns are defined as NOT NULL.  We leave this
-	 * unpopulated for non-partitioned inheritance parent relations as it's
-	 * ambiguous as to what it means.  Some child tables may have a NOT NULL
-	 * constraint for a column while others may not.  We could work harder and
-	 * build a unioned set of all child relations notnullattnums, but there's
-	 * currently no need.  The RelOptInfo corresponding to the !inh
-	 * RangeTblEntry does get populated.
-	 */
-	if (!inhparent || relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-	{
-		for (int i = 0; i < relation->rd_att->natts; i++)
-		{
-			CompactAttribute *attr = TupleDescCompactAttr(relation->rd_att, i);
-
-			if (attr->attnotnull)
-			{
-				rel->notnullattnums = bms_add_member(rel->notnullattnums,
-													 i + 1);
-
-				/*
-				 * Per RemoveAttributeById(), dropped columns will have their
-				 * attnotnull unset, so we needn't check for dropped columns
-				 * in the above condition.
-				 */
-				Assert(!attr->attisdropped);
-			}
-		}
-	}
-
 	/*
 	 * Estimate relation size --- unless it's an inheritance parent, in which
 	 * case the size we want is not the rel's own size but the size of its
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index ff507331a06..6db5b1273ab 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -222,7 +222,6 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
 	rel->relid = relid;
 	rel->rtekind = rte->rtekind;
 	/* min_attr, max_attr, attr_needed, attr_widths are set below */
-	rel->notnullattnums = NULL;
 	rel->lateral_vars = NIL;
 	rel->indexlist = NIL;
 	rel->statlist = NIL;
@@ -727,7 +726,6 @@ build_join_rel(PlannerInfo *root,
 	joinrel->max_attr = 0;
 	joinrel->attr_needed = NULL;
 	joinrel->attr_widths = NULL;
-	joinrel->notnullattnums = NULL;
 	joinrel->nulling_relids = NULL;
 	joinrel->lateral_vars = NIL;
 	joinrel->lateral_referencers = NULL;
@@ -916,7 +914,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 	joinrel->max_attr = 0;
 	joinrel->attr_needed = NULL;
 	joinrel->attr_widths = NULL;
-	joinrel->notnullattnums = NULL;
 	joinrel->nulling_relids = NULL;
 	joinrel->lateral_vars = NIL;
 	joinrel->lateral_referencers = NULL;
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 04ecf64b1fc..7f719e8e398 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1520,6 +1520,7 @@ addRangeTableEntry(ParseState *pstate,
 	rte->inh = inh;
 	rte->relkind = rel->rd_rel->relkind;
 	rte->rellockmode = lockmode;
+	getRelationAttrs(rel, rte);
 
 	/*
 	 * Build the list of effective column names using user-supplied aliases
@@ -1605,6 +1606,7 @@ addRangeTableEntryForRelation(ParseState *pstate,
 	rte->inh = inh;
 	rte->relkind = rel->rd_rel->relkind;
 	rte->rellockmode = lockmode;
+	getRelationAttrs(rel, rte);
 
 	/*
 	 * Build the list of effective column names using user-supplied aliases
@@ -4022,3 +4024,31 @@ getRTEPermissionInfo(List *rteperminfos, RangeTblEntry *rte)
 
 	return perminfo;
 }
+
+/*
+ * getRelationAttrs
+ *		Record which columns of the given relation are defined as NOT NULL.
+ */
+void
+getRelationAttrs(Relation relation, RangeTblEntry *rte)
+{
+	Assert(rte->rtekind == RTE_RELATION);
+
+	for (int i = 0; i < relation->rd_att->natts; i++)
+	{
+		CompactAttribute *attr = TupleDescCompactAttr(relation->rd_att, i);
+
+		if (attr->attnotnull)
+		{
+			rte->notnullattnums = bms_add_member(rte->notnullattnums,
+												 i + 1);
+
+			/*
+			 * Per RemoveAttributeById(), dropped columns will have their
+			 * attnotnull unset, so we needn't check for dropped columns in
+			 * the above condition.
+			 */
+			Assert(!attr->attisdropped);
+		}
+	}
+}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 23c9e3c5abf..0ba80b792ef 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1080,6 +1080,9 @@ typedef struct RangeTblEntry
 	 * this RTE in the containing struct's list of same; 0 if permissions need
 	 * not be checked for this RTE.
 	 *
+	 * notnullattnums is zero-based set containing attnums of NOT NULL
+	 * columns.
+	 *
 	 * As a special case, relid, relkind, rellockmode, and perminfoindex can
 	 * also be set (nonzero) in an RTE_SUBQUERY RTE.  This occurs when we
 	 * convert an RTE_RELATION RTE naming a view into an RTE_SUBQUERY
@@ -1105,6 +1108,8 @@ typedef struct RangeTblEntry
 	Index		perminfoindex pg_node_attr(query_jumble_ignore);
 	/* sampling info, or NULL */
 	struct TableSampleClause *tablesample;
+	/* columns defined as NOT NULL */
+	Bitmapset  *notnullattnums;
 
 	/*
 	 * Fields valid for a subquery RTE (else NULL):
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index c24a1fc8514..11334d5bc1b 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -955,12 +955,6 @@ typedef struct RelOptInfo
 	Relids	   *attr_needed pg_node_attr(read_write_ignore);
 	/* array indexed [min_attr .. max_attr] */
 	int32	   *attr_widths pg_node_attr(read_write_ignore);
-
-	/*
-	 * Zero-based set containing attnums of NOT NULL columns.  Not populated
-	 * for rels corresponding to non-partitioned inh==true RTEs.
-	 */
-	Bitmapset  *notnullattnums;
 	/* relids of outer joins that can null this baserel */
 	Relids		nulling_relids;
 	/* LATERAL Vars and PHVs referenced by rel */
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 78e05d88c8e..748556c9163 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -154,6 +154,8 @@ extern Node *estimate_expression_value(PlannerInfo *root, Node *node);
 extern Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
 						   Oid result_collation);
 
+extern bool var_is_nonnullable(PlannerInfo *root, Var *var);
+
 extern List *expand_function_arguments(List *args, bool include_out_arguments,
 									   Oid result_type,
 									   struct HeapTupleData *func_tuple);
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index d59599cf242..b7a55b92ef6 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -106,6 +106,7 @@ extern RTEPermissionInfo *addRTEPermissionInfo(List **rteperminfos,
 											   RangeTblEntry *rte);
 extern RTEPermissionInfo *getRTEPermissionInfo(List *rteperminfos,
 											   RangeTblEntry *rte);
+extern void getRelationAttrs(Relation relation, RangeTblEntry *rte);
 extern bool isLockedRefname(ParseState *pstate, const char *refname);
 extern void addNSItemToQuery(ParseState *pstate, ParseNamespaceItem *nsitem,
 							 bool addToJoinList,
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dc09c85938e..a93f25a417e 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1472,11 +1472,11 @@ where coalesce(t2.b, 1) = 2;
 explain (costs off)
 select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
 where coalesce(t2.b, 1) = 2 or t1.a is null;
-                         QUERY PLAN                          
--------------------------------------------------------------
+               QUERY PLAN                
+-----------------------------------------
  Hash Left Join
    Hash Cond: (t1.a = t2.a)
-   Filter: ((COALESCE((t2.a * 2), 1) = 2) OR (t1.a IS NULL))
+   Filter: (COALESCE((t2.a * 2), 1) = 2)
    ->  Seq Scan on gtest32 t1
    ->  Hash
          ->  Seq Scan on gtest32 t2
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index a57bb18c24f..69877e310f6 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3639,8 +3639,8 @@ from nt3 as nt3
     ) as ss2
     on ss2.id = nt3.nt2_id
 where nt3.id = 1 and ss2.b3;
-                  QUERY PLAN                   
------------------------------------------------
+                  QUERY PLAN                  
+----------------------------------------------
  Nested Loop
    ->  Nested Loop
          ->  Index Scan using nt3_pkey on nt3
@@ -3649,7 +3649,7 @@ where nt3.id = 1 and ss2.b3;
                Index Cond: (id = nt3.nt2_id)
    ->  Index Only Scan using nt1_pkey on nt1
          Index Cond: (id = nt2.nt1_id)
-         Filter: (nt2.b1 AND (id IS NOT NULL))
+         Filter: (nt2.b1 AND true)
 (9 rows)
 
 select nt3.id
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index b79037748b7..e025c05261d 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -84,10 +84,10 @@ SELECT * FROM pred_tab t WHERE t.a IS NULL OR t.c IS NULL;
 -- are provably false
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
-               QUERY PLAN               
-----------------------------------------
+       QUERY PLAN       
+------------------------
  Seq Scan on pred_tab t
-   Filter: ((b IS NULL) OR (c IS NULL))
+   Filter: (b IS NULL)
 (2 rows)
 
 --
-- 
2.43.0