Some optimizations for COALESCE expressions during constant folding

From c91e9bd4be0f4e00b9c63881bd4e2c8d7d0a704f Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 25 Nov 2025 13:00:20 +0900
Subject: [PATCH v2] Simplify COALESCE expressions using proven non-null
 arguments

The COALESCE function returns the first of its arguments that is not
null.  When an argument is proven non-null, if it is the first
non-null-constant argument, the entire COALESCE expression can be
replaced by that argument.  If it is a subsequent argument, all
following arguments can be dropped, since they will never be reached.

Currently, we apply this simplification only to Const arguments.  We
can extend this logic to Var arguments, since we now have the NOT NULL
attribute information available during constant folding.  We can also
extend it to CoalesceExpr arguments based on the knowledge that a
CoalesceExpr cannot be NULL if at least one of its arguments can be
proven non-nullable.

This can help avoid the overhead of evaluating unreachable arguments.
It can also lead to better plans when the first argument is a NOT NULL
column and thus replaces the expression, as the planner no longer has
to treat the expression as non-strict, and can also leverage index
scans on that column.

Additionally, if we've determined that a CoalesceExpr is non-nullable,
we can reduce its corresponding NullTest quals to a constant during
constant folding.  It might be argued that it is uncommon to write
"COALESCE(...) IS [NOT] NULL" by hand, but this pattern is likely not
uncommon after view expansion, function inlining, or ORM query
generation.  Also, since the reduction does not cost much, I believe
the benefit justifies the cost.
---
 src/backend/optimizer/plan/initsplan.c        | 20 +----
 src/backend/optimizer/util/clauses.c          | 89 ++++++++++++++-----
 src/include/optimizer/optimizer.h             |  2 +-
 src/test/regress/expected/aggregates.out      | 10 +--
 .../regress/expected/generated_virtual.out    | 48 +++++-----
 src/test/regress/expected/predicate.out       | 39 ++++++++
 src/test/regress/sql/generated_virtual.sql    | 11 +--
 src/test/regress/sql/predicate.sql            | 20 +++++
 8 files changed, 162 insertions(+), 77 deletions(-)

diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 65d473d95b6..671c5cde8fc 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -3413,22 +3413,6 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
 										 restrictinfo->security_level);
 }
 
-/*
- * expr_is_nonnullable
- *	  Check to see if the Expr cannot be NULL
- *
- * Currently we only support simple Vars.
- */
-static bool
-expr_is_nonnullable(PlannerInfo *root, Expr *expr)
-{
-	/* For now only check simple Vars */
-	if (!IsA(expr, Var))
-		return false;
-
-	return var_is_nonnullable(root, (Var *) expr, true);
-}
-
 /*
  * restriction_is_always_true
  *	  Check to see if the RestrictInfo is always true.
@@ -3465,7 +3449,7 @@ restriction_is_always_true(PlannerInfo *root,
 		if (nulltest->argisrow)
 			return false;
 
-		return expr_is_nonnullable(root, nulltest->arg);
+		return expr_is_nonnullable(root, nulltest->arg, true);
 	}
 
 	/* If it's an OR, check its sub-clauses */
@@ -3530,7 +3514,7 @@ restriction_is_always_false(PlannerInfo *root,
 		if (nulltest->argisrow)
 			return false;
 
-		return expr_is_nonnullable(root, nulltest->arg);
+		return expr_is_nonnullable(root, nulltest->arg, true);
 	}
 
 	/* If it's an OR, check its sub-clauses */
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 202ba8ed4bb..9e6b5708efd 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -131,6 +131,7 @@ static Expr *simplify_function(Oid funcid,
 							   Oid result_collid, Oid input_collid, List **args_p,
 							   bool funcvariadic, bool process_args, bool allow_non_const,
 							   eval_const_expressions_context *context);
+static bool var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info);
 static List *reorder_function_arguments(List *args, int pronargs,
 										HeapTuple func_tuple);
 static List *add_function_defaults(List *args, int pronargs,
@@ -3318,10 +3319,10 @@ eval_const_expressions_mutator(Node *node,
 													   context);
 
 					/*
-					 * We can remove null constants from the list. For a
-					 * non-null constant, if it has not been preceded by any
-					 * other non-null-constant expressions then it is the
-					 * result. Otherwise, it's the next argument, but we can
+					 * We can remove null constants from the list.  For a
+					 * nonnullable expression, if it has not been preceded by
+					 * any non-null-constant expressions then it is the
+					 * result.  Otherwise, it's the next argument, but we can
 					 * drop following arguments since they will never be
 					 * reached.
 					 */
@@ -3334,6 +3335,14 @@ eval_const_expressions_mutator(Node *node,
 						newargs = lappend(newargs, e);
 						break;
 					}
+					if (expr_is_nonnullable(context->root, (Expr *) e, false))
+					{
+						if (newargs == NIL)
+							return e;	/* first expr */
+						newargs = lappend(newargs, e);
+						break;
+					}
+
 					newargs = lappend(newargs, e);
 				}
 
@@ -3557,30 +3566,27 @@ eval_const_expressions_mutator(Node *node,
 
 					return makeBoolConst(result, false);
 				}
-				if (!ntest->argisrow && arg && IsA(arg, Var) && context->root)
+				if (!ntest->argisrow && arg &&
+					expr_is_nonnullable(context->root, (Expr *) arg, false))
 				{
-					Var		   *varg = (Var *) arg;
 					bool		result;
 
-					if (var_is_nonnullable(context->root, varg, false))
+					switch (ntest->nulltesttype)
 					{
-						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);
+						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);
@@ -4209,7 +4215,7 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
  * use_rel_info indicates whether the corresponding RelOptInfo is available for
  * use.
  */
-bool
+static bool
 var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
 {
 	Bitmapset  *notnullattnums = NULL;
@@ -4261,6 +4267,41 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
 	return false;
 }
 
+/*
+ * expr_is_nonnullable
+ *	  Check to see if the Expr cannot be NULL
+ *
+ * Currently, we only support simple expressions such as Vars, Consts, and
+ * CoalesceExprs.  Support for other node types may be added in the future.
+ *
+ * use_rel_info is interpreted the same way as in var_is_nonnullable().
+ */
+bool
+expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
+{
+	if (IsA(expr, Var) && root)
+		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
+	if (IsA(expr, Const))
+		return !((Const *) expr)->constisnull;
+	if (IsA(expr, CoalesceExpr))
+	{
+		/*
+		 * A CoalesceExpr returns NULL if and only if all its arguments are
+		 * NULL.  Therefore, we can determine that a CoalesceExpr cannot be
+		 * NULL if at least one of its arguments can be proven non-nullable.
+		 */
+		CoalesceExpr *coalesceexpr = (CoalesceExpr *) expr;
+
+		foreach_ptr(Expr, arg, coalesceexpr->args)
+		{
+			if (expr_is_nonnullable(root, arg, use_rel_info))
+				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/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index d0aa8ab0c1c..ab3badcbda4 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -145,7 +145,7 @@ 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, bool use_rel_info);
+extern bool expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info);
 
 extern List *expand_function_arguments(List *args, bool include_out_arguments,
 									   Oid result_type,
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index bc83a6e188e..ad9c168822b 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1222,16 +1222,14 @@ select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
 -- interesting corner case: constant gets optimized into a seqscan
 explain (costs off)
   select max(100) from tenk1;
-                     QUERY PLAN                     
-----------------------------------------------------
+           QUERY PLAN            
+---------------------------------
  Result
    Replaces: MinMaxAggregate
    InitPlan minmax_1
      ->  Limit
-           ->  Result
-                 One-Time Filter: (100 IS NOT NULL)
-                 ->  Seq Scan on tenk1
-(7 rows)
+           ->  Seq Scan on tenk1
+(5 rows)
 
 select max(100) from tenk1;
  max 
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dde325e46c6..249e68be654 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1509,10 +1509,11 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 -- Ensure that nullingrel bits are propagated into the generation expressions
 explain (costs off)
@@ -1591,46 +1592,47 @@ where coalesce(t2.b, 1) = 2 or t1.a is null;
 -- Ensure that the generation expressions are wrapped into PHVs if needed
 explain (verbose, costs off)
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
-                          QUERY PLAN                           
----------------------------------------------------------------
+                             QUERY PLAN                              
+---------------------------------------------------------------------
  Nested Loop Left Join
-   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.a, 100)), t2.e
+   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.f, 100)), t2.e, t2.f
    Join Filter: false
    ->  Seq Scan on generated_virtual_tests.gtest32 t1
-         Output: t1.a, t1.b, t1.c, t1.d, t1.e
+         Output: t1.a, t1.b, t1.c, t1.d, t1.e, t1.f
    ->  Result
-         Output: t2.a, t2.e, 20, COALESCE(t2.a, 100)
+         Output: t2.a, t2.e, t2.f, 20, COALESCE(t2.f, 100)
          Replaces: Scan on t2
          One-Time Filter: false
 (9 rows)
 
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
- a | b | c | d | e 
----+---+---+---+---
-   |   |   |   |  
-   |   |   |   |  
+ a | b | c | d | e | f 
+---+---+---+---+---+---
+   |   |   |   |   |  
+   |   |   |   |   |  
 (2 rows)
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-                     QUERY PLAN                      
------------------------------------------------------
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+                       QUERY PLAN                       
+--------------------------------------------------------
  HashAggregate
-   Output: a, ((a * 2)), (20), (COALESCE(a, 100)), e
+   Output: a, ((a * 2)), (20), (COALESCE(f, 100)), e, f
    Hash Key: t.a
    Hash Key: (t.a * 2)
    Hash Key: 20
-   Hash Key: COALESCE(t.a, 100)
+   Hash Key: COALESCE(t.f, 100)
    Hash Key: t.e
+   Hash Key: t.f
    Filter: ((20) = 20)
    ->  Seq Scan on generated_virtual_tests.gtest32 t
-         Output: a, (a * 2), 20, COALESCE(a, 100), e
-(10 rows)
+         Output: a, (a * 2), 20, COALESCE(f, 100), e, f
+(11 rows)
 
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
- a | b | c  | d | e 
----+---+----+---+---
-   |   | 20 |   |  
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+ a | b | c  | d | e | f 
+---+---+----+---+---+---
+   |   | 20 |   |   |  
 (1 row)
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 66fb0854b88..fc12c0cd106 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -284,6 +284,45 @@ SELECT * FROM pred_tab t1
          ->  Seq Scan on pred_tab t2
 (9 rows)
 
+--
+-- Tests for NullTest reduction for COALESCE expressions
+--
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NOT NULL;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+(1 row)
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NOT NULL;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+(1 row)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
 DROP TABLE pred_tab;
 -- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
 -- parents.
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 2911439776c..81152b39a79 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,11 +817,12 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
 
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 
 -- Ensure that nullingrel bits are propagated into the generation expressions
@@ -859,8 +860,8 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false;
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
 alter table gtest32 alter column e type bigint using b;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 32302d60b6d..1fc83e762fc 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -133,6 +133,26 @@ SELECT * FROM pred_tab t1
         (SELECT 1 FROM pred_tab t3, pred_tab t4, pred_tab t5, pred_tab t6
          WHERE t1.a = t3.a AND t6.a IS NULL);
 
+--
+-- Tests for NullTest reduction for COALESCE expressions
+--
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NOT NULL;
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NOT NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+
 DROP TABLE pred_tab;
 
 -- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
-- 
2.39.5 (Apple Git-154)

From 7f68cbc99da8d564b841bcff71d3034aca610cf8 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 1 Dec 2025 10:38:44 +0900
Subject: [PATCH v3 1/3] Simplify COALESCE arguments using NOT NULL constraints

The COALESCE function returns the first of its arguments that is not
null.  When an argument is proven non-null, if it is the first
non-null-constant argument, the entire COALESCE expression can be
replaced by that argument.  If it is a subsequent argument, all
following arguments can be dropped, since they will never be reached.

Currently, we perform this simplification for Const arguments.  Since
we now have the NOT NULL attribute information available during
constant folding, we can extend this simplification to Var arguments.

This can help avoid the overhead of evaluating unreachable arguments.
It can also lead to better plans when the first argument is a NOT NULL
column and thus replaces the expression, as the planner no longer has
to treat the expression as non-strict, and can also leverage index
scans on that column.
---
 src/backend/optimizer/util/clauses.c          | 18 +++++--
 .../regress/expected/generated_virtual.out    | 48 ++++++++++---------
 src/test/regress/sql/generated_virtual.sql    | 11 +++--
 3 files changed, 44 insertions(+), 33 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index bda4c4eb292..2583cd66509 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3325,10 +3325,10 @@ eval_const_expressions_mutator(Node *node,
 													   context);
 
 					/*
-					 * We can remove null constants from the list. For a
-					 * non-null constant, if it has not been preceded by any
-					 * other non-null-constant expressions then it is the
-					 * result. Otherwise, it's the next argument, but we can
+					 * We can remove null constants from the list.  For a
+					 * nonnullable expression, if it has not been preceded by
+					 * any non-null-constant expressions then it is the
+					 * result.  Otherwise, it's the next argument, but we can
 					 * drop following arguments since they will never be
 					 * reached.
 					 */
@@ -3341,6 +3341,14 @@ eval_const_expressions_mutator(Node *node,
 						newargs = lappend(newargs, e);
 						break;
 					}
+					if (expr_is_nonnullable(context->root, (Expr *) e, false))
+					{
+						if (newargs == NIL)
+							return e;	/* first expr */
+						newargs = lappend(newargs, e);
+						break;
+					}
+
 					newargs = lappend(newargs, e);
 				}
 
@@ -4328,7 +4336,7 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 {
-	if (IsA(expr, Var))
+	if (IsA(expr, Var) && root)
 		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
 	if (IsA(expr, Const))
 		return !castNode(Const, expr)->constisnull;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dde325e46c6..249e68be654 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1509,10 +1509,11 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 -- Ensure that nullingrel bits are propagated into the generation expressions
 explain (costs off)
@@ -1591,46 +1592,47 @@ where coalesce(t2.b, 1) = 2 or t1.a is null;
 -- Ensure that the generation expressions are wrapped into PHVs if needed
 explain (verbose, costs off)
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
-                          QUERY PLAN                           
----------------------------------------------------------------
+                             QUERY PLAN                              
+---------------------------------------------------------------------
  Nested Loop Left Join
-   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.a, 100)), t2.e
+   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.f, 100)), t2.e, t2.f
    Join Filter: false
    ->  Seq Scan on generated_virtual_tests.gtest32 t1
-         Output: t1.a, t1.b, t1.c, t1.d, t1.e
+         Output: t1.a, t1.b, t1.c, t1.d, t1.e, t1.f
    ->  Result
-         Output: t2.a, t2.e, 20, COALESCE(t2.a, 100)
+         Output: t2.a, t2.e, t2.f, 20, COALESCE(t2.f, 100)
          Replaces: Scan on t2
          One-Time Filter: false
 (9 rows)
 
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
- a | b | c | d | e 
----+---+---+---+---
-   |   |   |   |  
-   |   |   |   |  
+ a | b | c | d | e | f 
+---+---+---+---+---+---
+   |   |   |   |   |  
+   |   |   |   |   |  
 (2 rows)
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-                     QUERY PLAN                      
------------------------------------------------------
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+                       QUERY PLAN                       
+--------------------------------------------------------
  HashAggregate
-   Output: a, ((a * 2)), (20), (COALESCE(a, 100)), e
+   Output: a, ((a * 2)), (20), (COALESCE(f, 100)), e, f
    Hash Key: t.a
    Hash Key: (t.a * 2)
    Hash Key: 20
-   Hash Key: COALESCE(t.a, 100)
+   Hash Key: COALESCE(t.f, 100)
    Hash Key: t.e
+   Hash Key: t.f
    Filter: ((20) = 20)
    ->  Seq Scan on generated_virtual_tests.gtest32 t
-         Output: a, (a * 2), 20, COALESCE(a, 100), e
-(10 rows)
+         Output: a, (a * 2), 20, COALESCE(f, 100), e, f
+(11 rows)
 
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
- a | b | c  | d | e 
----+---+----+---+---
-   |   | 20 |   |  
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+ a | b | c  | d | e | f 
+---+---+----+---+---+---
+   |   | 20 |   |   |  
 (1 row)
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 2911439776c..81152b39a79 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,11 +817,12 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
 
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 
 -- Ensure that nullingrel bits are propagated into the generation expressions
@@ -859,8 +860,8 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false;
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
 alter table gtest32 alter column e type bigint using b;
-- 
2.39.5 (Apple Git-154)

From cdc758281d53dd58d65fb727547762ed9a2744ab Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 25 Nov 2025 17:33:48 +0900
Subject: [PATCH v3 2/3] Teach expr_is_nonnullable() to handle CoalesceExprs

Currently, the function expr_is_nonnullable() checks only Const and
Var expressions to determine if an expression is non-nullable.  This
patch extends the detection logic to handle CoalesceExprs.

A CoalesceExpr returns NULL if and only if all its arguments are NULL.
Therefore, we can determine that a CoalesceExpr cannot be NULL if at
least one of its arguments can be proven non-nullable.

This can enable several downstream optimizations, such as reducing a
"COALESCE(...) IS [NOT] NULL" qual to constant true or false, and
converting "COUNT(COALESCE(...))" to the more efficient "COUNT(*)".
---
 src/backend/optimizer/util/clauses.c    | 56 +++++++++++++++----------
 src/test/regress/expected/predicate.out | 39 +++++++++++++++++
 src/test/regress/sql/predicate.sql      | 20 +++++++++
 3 files changed, 93 insertions(+), 22 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 2583cd66509..933ac38d62e 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3572,30 +3572,27 @@ eval_const_expressions_mutator(Node *node,
 
 					return makeBoolConst(result, false);
 				}
-				if (!ntest->argisrow && arg && IsA(arg, Var) && context->root)
+				if (!ntest->argisrow && arg &&
+					expr_is_nonnullable(context->root, (Expr *) arg, false))
 				{
-					Var		   *varg = (Var *) arg;
 					bool		result;
 
-					if (var_is_nonnullable(context->root, varg, false))
+					switch (ntest->nulltesttype)
 					{
-						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);
+						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);
@@ -4330,8 +4327,8 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
  * nullability information before RelOptInfos are generated.  These should
  * pass 'use_rel_info' as false.
  *
- * For now, we only support Var and Const.  Support for other node types may
- * be possible.
+ * For now, we only support Var, Const, and CoalesceExpr.  Support for other
+ * node types may be possible.
  */
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
@@ -4339,7 +4336,22 @@ expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 	if (IsA(expr, Var) && root)
 		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
 	if (IsA(expr, Const))
-		return !castNode(Const, expr)->constisnull;
+		return !((Const *) expr)->constisnull;
+	if (IsA(expr, CoalesceExpr))
+	{
+		/*
+		 * A CoalesceExpr returns NULL if and only if all its arguments are
+		 * NULL.  Therefore, we can determine that a CoalesceExpr cannot be
+		 * NULL if at least one of its arguments can be proven non-nullable.
+		 */
+		CoalesceExpr *coalesceexpr = (CoalesceExpr *) expr;
+
+		foreach_ptr(Expr, arg, coalesceexpr->args)
+		{
+			if (expr_is_nonnullable(root, arg, use_rel_info))
+				return true;
+		}
+	}
 
 	return false;
 }
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 66fb0854b88..fc12c0cd106 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -284,6 +284,45 @@ SELECT * FROM pred_tab t1
          ->  Seq Scan on pred_tab t2
 (9 rows)
 
+--
+-- Tests for NullTest reduction for COALESCE expressions
+--
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NOT NULL;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+(1 row)
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NOT NULL;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+(1 row)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
 DROP TABLE pred_tab;
 -- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
 -- parents.
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 32302d60b6d..1fc83e762fc 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -133,6 +133,26 @@ SELECT * FROM pred_tab t1
         (SELECT 1 FROM pred_tab t3, pred_tab t4, pred_tab t5, pred_tab t6
          WHERE t1.a = t3.a AND t6.a IS NULL);
 
+--
+-- Tests for NullTest reduction for COALESCE expressions
+--
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NOT NULL;
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NOT NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+
 DROP TABLE pred_tab;
 
 -- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
-- 
2.39.5 (Apple Git-154)

From edf52acd1f6b08c33d54e1e4378428c47ed9a67c Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 1 Dec 2025 16:14:53 +0900
Subject: [PATCH v3 3/3] Teach expr_is_nonnullable() to handle more expression
 types

---
 src/backend/optimizer/util/clauses.c | 105 +++++++++++++++++++++++----
 1 file changed, 90 insertions(+), 15 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 933ac38d62e..712f071a389 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4333,24 +4333,99 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 {
-	if (IsA(expr, Var) && root)
-		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
-	if (IsA(expr, Const))
-		return !((Const *) expr)->constisnull;
-	if (IsA(expr, CoalesceExpr))
+	switch (nodeTag(expr))
 	{
-		/*
-		 * A CoalesceExpr returns NULL if and only if all its arguments are
-		 * NULL.  Therefore, we can determine that a CoalesceExpr cannot be
-		 * NULL if at least one of its arguments can be proven non-nullable.
-		 */
-		CoalesceExpr *coalesceexpr = (CoalesceExpr *) expr;
+		case T_Var:
+			{
+				if (root)
+					return var_is_nonnullable(root, (Var *) expr, use_rel_info);
+			}
+			break;
+		case T_Const:
+			return !((Const *) expr)->constisnull;
+		case T_CoalesceExpr:
+			{
+				/*
+				 * A CoalesceExpr returns NULL if and only if all its
+				 * arguments are NULL.  Therefore, we can determine that a
+				 * CoalesceExpr cannot be NULL if at least one of its
+				 * arguments can be proven non-nullable.
+				 */
+				CoalesceExpr *coalesceexpr = (CoalesceExpr *) expr;
 
-		foreach_ptr(Expr, arg, coalesceexpr->args)
-		{
-			if (expr_is_nonnullable(root, arg, use_rel_info))
+				foreach_ptr(Expr, arg, coalesceexpr->args)
+				{
+					if (expr_is_nonnullable(root, arg, use_rel_info))
+						return true;
+				}
+			}
+			break;
+		case T_MinMaxExpr:
+			{
+				/*
+				 * Like CoalesceExpr, a MinMaxExpr returns NULL only if all
+				 * its arguments evaluate to NULL.
+				 */
+				MinMaxExpr *minmaxexpr = (MinMaxExpr *) expr;
+
+				foreach_ptr(Expr, arg, minmaxexpr->args)
+				{
+					if (expr_is_nonnullable(root, arg, use_rel_info))
+						return true;
+				}
+			}
+			break;
+		case T_ArrayExpr:
+			{
+				/*
+				 * An ARRAY[] expression always returns a valid Array object,
+				 * even if it is empty (ARRAY[]) or contains NULLs
+				 * (ARRAY[NULL]).  It never evaluates to a SQL NULL.
+				 */
 				return true;
-		}
+			}
+		case T_NullTest:
+			{
+				/*
+				 * An IS NULL / IS NOT NULL expression always returns a
+				 * boolean value.  It never returns SQL NULL.
+				 */
+				return true;
+			}
+		case T_BooleanTest:
+			{
+				/*
+				 * A BooleanTest expression always evaluates to a boolean
+				 * value.  It never returns SQL NULL.
+				 */
+				return true;
+			}
+		case T_CaseExpr:
+			{
+				/*
+				 * A CASE expression is non-nullable if all branch results are
+				 * non-nullable.  We must also verify that the default result
+				 * (ELSE) exists and is non-nullable.
+				 */
+				CaseExpr   *caseexpr = (CaseExpr *) expr;
+
+				/* The default result must be present and non-nullable */
+				if (caseexpr->defresult == NULL ||
+					!expr_is_nonnullable(root, caseexpr->defresult, use_rel_info))
+					return false;
+
+				/* All branch results must be non-nullable */
+				foreach_ptr(CaseWhen, casewhen, caseexpr->args)
+				{
+					if (!expr_is_nonnullable(root, casewhen->result, use_rel_info))
+						return false;
+				}
+
+				return true;
+			}
+			break;
+		default:
+			break;
 	}
 
 	return false;
-- 
2.39.5 (Apple Git-154)

From 3262a26abb4b6e43b6da0fc9537ff32541d7feee Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 1 Dec 2025 10:38:44 +0900
Subject: [PATCH v4 1/3] Simplify COALESCE expressions using non-nullable
 arguments

The COALESCE function returns the first of its arguments that is not
null.  When an argument is proven non-null, if it is the first
non-null-constant argument, the entire COALESCE expression can be
replaced by that argument.  If it is a subsequent argument, all
following arguments can be dropped, since they will never be reached.

Currently, we perform this simplification only for Const arguments.
This patch extends the simplification to support any expression that
can be proven non-nullable.

This can help avoid the overhead of evaluating unreachable arguments.
It can also lead to better plans when the first argument is proven
non-nullable and replaces the expression, as the planner no longer has
to treat the expression as non-strict, and can also leverage index
scans on the resulting expression.

There is an ensuing plan change in generated_virtual.out, and we have
to modify the test to ensure that it continues to test what it is
intended to.
---
 src/backend/optimizer/util/clauses.c          | 18 +++++--
 .../regress/expected/generated_virtual.out    | 48 ++++++++++---------
 src/test/regress/expected/predicate.out       | 33 +++++++++++++
 src/test/regress/sql/generated_virtual.sql    | 11 +++--
 src/test/regress/sql/predicate.sql            | 20 ++++++++
 5 files changed, 97 insertions(+), 33 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index bda4c4eb292..2583cd66509 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3325,10 +3325,10 @@ eval_const_expressions_mutator(Node *node,
 													   context);
 
 					/*
-					 * We can remove null constants from the list. For a
-					 * non-null constant, if it has not been preceded by any
-					 * other non-null-constant expressions then it is the
-					 * result. Otherwise, it's the next argument, but we can
+					 * We can remove null constants from the list.  For a
+					 * nonnullable expression, if it has not been preceded by
+					 * any non-null-constant expressions then it is the
+					 * result.  Otherwise, it's the next argument, but we can
 					 * drop following arguments since they will never be
 					 * reached.
 					 */
@@ -3341,6 +3341,14 @@ eval_const_expressions_mutator(Node *node,
 						newargs = lappend(newargs, e);
 						break;
 					}
+					if (expr_is_nonnullable(context->root, (Expr *) e, false))
+					{
+						if (newargs == NIL)
+							return e;	/* first expr */
+						newargs = lappend(newargs, e);
+						break;
+					}
+
 					newargs = lappend(newargs, e);
 				}
 
@@ -4328,7 +4336,7 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 {
-	if (IsA(expr, Var))
+	if (IsA(expr, Var) && root)
 		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
 	if (IsA(expr, Const))
 		return !castNode(Const, expr)->constisnull;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dde325e46c6..249e68be654 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1509,10 +1509,11 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 -- Ensure that nullingrel bits are propagated into the generation expressions
 explain (costs off)
@@ -1591,46 +1592,47 @@ where coalesce(t2.b, 1) = 2 or t1.a is null;
 -- Ensure that the generation expressions are wrapped into PHVs if needed
 explain (verbose, costs off)
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
-                          QUERY PLAN                           
----------------------------------------------------------------
+                             QUERY PLAN                              
+---------------------------------------------------------------------
  Nested Loop Left Join
-   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.a, 100)), t2.e
+   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.f, 100)), t2.e, t2.f
    Join Filter: false
    ->  Seq Scan on generated_virtual_tests.gtest32 t1
-         Output: t1.a, t1.b, t1.c, t1.d, t1.e
+         Output: t1.a, t1.b, t1.c, t1.d, t1.e, t1.f
    ->  Result
-         Output: t2.a, t2.e, 20, COALESCE(t2.a, 100)
+         Output: t2.a, t2.e, t2.f, 20, COALESCE(t2.f, 100)
          Replaces: Scan on t2
          One-Time Filter: false
 (9 rows)
 
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
- a | b | c | d | e 
----+---+---+---+---
-   |   |   |   |  
-   |   |   |   |  
+ a | b | c | d | e | f 
+---+---+---+---+---+---
+   |   |   |   |   |  
+   |   |   |   |   |  
 (2 rows)
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-                     QUERY PLAN                      
------------------------------------------------------
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+                       QUERY PLAN                       
+--------------------------------------------------------
  HashAggregate
-   Output: a, ((a * 2)), (20), (COALESCE(a, 100)), e
+   Output: a, ((a * 2)), (20), (COALESCE(f, 100)), e, f
    Hash Key: t.a
    Hash Key: (t.a * 2)
    Hash Key: 20
-   Hash Key: COALESCE(t.a, 100)
+   Hash Key: COALESCE(t.f, 100)
    Hash Key: t.e
+   Hash Key: t.f
    Filter: ((20) = 20)
    ->  Seq Scan on generated_virtual_tests.gtest32 t
-         Output: a, (a * 2), 20, COALESCE(a, 100), e
-(10 rows)
+         Output: a, (a * 2), 20, COALESCE(f, 100), e, f
+(11 rows)
 
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
- a | b | c  | d | e 
----+---+----+---+---
-   |   | 20 |   |  
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+ a | b | c  | d | e | f 
+---+---+----+---+---+---
+   |   | 20 |   |   |  
 (1 row)
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 66fb0854b88..9f9f795e892 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -443,3 +443,36 @@ SELECT * FROM pred_tab2, pred_tab1 WHERE pred_tab1.a IS NULL OR pred_tab1.b < 2;
 RESET constraint_exclusion;
 DROP TABLE pred_tab1;
 DROP TABLE pred_tab2;
+--
+-- Test that COALESCE expressions in predicates are simplified using
+-- non-nullable arguments.
+--
+CREATE TABLE pred_tab (a int NOT NULL, b int);
+-- Ensure that constant NULL arguments are dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(NULL, b, NULL, a) > 1;
+           QUERY PLAN           
+--------------------------------
+ Seq Scan on pred_tab
+   Filter: (COALESCE(b, a) > 1)
+(2 rows)
+
+-- Ensure that argument "b*a" is dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1;
+           QUERY PLAN           
+--------------------------------
+ Seq Scan on pred_tab
+   Filter: (COALESCE(b, a) > 1)
+(2 rows)
+
+-- Ensure that the entire COALESCE expression is replaced by "a"
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+   Filter: (a > 1)
+(2 rows)
+
+DROP TABLE pred_tab;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 2911439776c..81152b39a79 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,11 +817,12 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
 
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 
 -- Ensure that nullingrel bits are propagated into the generation expressions
@@ -859,8 +860,8 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false;
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
 alter table gtest32 alter column e type bigint using b;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 32302d60b6d..0c7e4b974ff 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -221,3 +221,23 @@ SELECT * FROM pred_tab2, pred_tab1 WHERE pred_tab1.a IS NULL OR pred_tab1.b < 2;
 RESET constraint_exclusion;
 DROP TABLE pred_tab1;
 DROP TABLE pred_tab2;
+
+--
+-- Test that COALESCE expressions in predicates are simplified using
+-- non-nullable arguments.
+--
+CREATE TABLE pred_tab (a int NOT NULL, b int);
+
+-- Ensure that constant NULL arguments are dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(NULL, b, NULL, a) > 1;
+
+-- Ensure that argument "b*a" is dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1;
+
+-- Ensure that the entire COALESCE expression is replaced by "a"
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
+
+DROP TABLE pred_tab;
-- 
2.39.5 (Apple Git-154)

From 6f7e7999218f79b76f15fda3e9fb2374c1c57f61 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 2 Dec 2025 16:01:08 +0900
Subject: [PATCH v4 2/3] Optimize ROW(...) IS [NOT] NULL using non-nullable
 fields

We break ROW(...) IS [NOT] NULL into separate tests on its component
fields.  During this breakdown, we can improve efficiency by utilizing
expr_is_nonnullable() to detect fields that are provably non-nullable.

If a component field is proven non-nullable, it affects the outcome
based on the test type.  For an IS NULL test, a single non-nullable
field refutes the whole NullTest, reducing it to constant FALSE.  For
an IS NOT NULL test, the check for that specific field is guaranteed
to succeed, so we can discard it from the list of component tests.

This extends the existing optimization logic, which previously only
handled Const fields, to support any expression that can be proven
non-nullable.

In passing, update the existing constant folding of NullTests to use
expr_is_nonnullable() instead of var_is_nonnullable(), enabling it to
benefit from future improvements to that function.
---
 src/backend/optimizer/util/clauses.c | 49 +++++++++++++++++-----------
 1 file changed, 30 insertions(+), 19 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 2583cd66509..ceb53c98726 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3528,6 +3528,20 @@ eval_const_expressions_mutator(Node *node,
 							continue;
 						}
 
+						/*
+						 * A proven non-nullable field refutes the whole
+						 * NullTest if the test is IS NULL; else we can
+						 * discard it.
+						 */
+						if (relem &&
+							expr_is_nonnullable(context->root, (Expr *) relem,
+												false))
+						{
+							if (ntest->nulltesttype == IS_NULL)
+								return makeBoolConst(false, false);
+							continue;
+						}
+
 						/*
 						 * Else, make a scalar (argisrow == false) NullTest
 						 * for this field.  Scalar semantics are required
@@ -3572,30 +3586,27 @@ eval_const_expressions_mutator(Node *node,
 
 					return makeBoolConst(result, false);
 				}
-				if (!ntest->argisrow && arg && IsA(arg, Var) && context->root)
+				if (!ntest->argisrow && arg &&
+					expr_is_nonnullable(context->root, (Expr *) arg, false))
 				{
-					Var		   *varg = (Var *) arg;
 					bool		result;
 
-					if (var_is_nonnullable(context->root, varg, false))
+					switch (ntest->nulltesttype)
 					{
-						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);
+						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);
-- 
2.39.5 (Apple Git-154)

From 7a1bb98154c2e9bd9f8bf0b69a2b55bd43fcde56 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 2 Dec 2025 16:27:26 +0900
Subject: [PATCH v4 3/3] Teach expr_is_nonnullable() to handle more expression
 types

Currently, the function expr_is_nonnullable() checks only Const and
Var expressions to determine if an expression is non-nullable.  This
patch extends the detection logic to handle more expression types.

This can enable several downstream optimizations, such as reducing
NullTest quals to constant truth values (e.g., "COALESCE(var, 1) IS
NULL" becomes FALSE) and converting "COUNT(expr)" to the more
efficient "COUNT(*)" when the expression is proven non-nullable.

This breaks a test case in test_predtest.sql, since we now simplify
"ARRAY[] IS NULL" to constant FALSE, preventing it from weakly
refuting a strict ScalarArrayOpExpr ("x = any(ARRAY[])").  To ensure
the refutation logic is still exercised as intended, wrap the array
argument in opaque_array().
---
 src/backend/optimizer/util/clauses.c          | 123 +++++++++++++++++-
 .../test_predtest/expected/test_predtest.out  |   2 +-
 .../test_predtest/sql/test_predtest.sql       |   2 +-
 src/test/regress/expected/predicate.out       | 117 ++++++++++++++++-
 src/test/regress/sql/predicate.sql            |  51 +++++++-
 5 files changed, 285 insertions(+), 10 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index ceb53c98726..11c42a06b50 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4341,16 +4341,127 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
  * nullability information before RelOptInfos are generated.  These should
  * pass 'use_rel_info' as false.
  *
- * For now, we only support Var and Const.  Support for other node types may
- * be possible.
+ * For now, we support only a limited set of expression types.  Support for
+ * additional node types can be added in the future.
  */
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 {
-	if (IsA(expr, Var) && root)
-		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
-	if (IsA(expr, Const))
-		return !castNode(Const, expr)->constisnull;
+	/* since this function recurses, it could be driven to stack overflow */
+	check_stack_depth();
+
+	switch (nodeTag(expr))
+	{
+		case T_Var:
+			{
+				if (root)
+					return var_is_nonnullable(root, (Var *) expr, use_rel_info);
+			}
+			break;
+		case T_Const:
+			return !((Const *) expr)->constisnull;
+		case T_CoalesceExpr:
+			{
+				/*
+				 * A CoalesceExpr returns NULL if and only if all its
+				 * arguments are NULL.  Therefore, we can determine that a
+				 * CoalesceExpr cannot be NULL if at least one of its
+				 * arguments can be proven non-nullable.
+				 */
+				CoalesceExpr *coalesceexpr = (CoalesceExpr *) expr;
+
+				foreach_ptr(Expr, arg, coalesceexpr->args)
+				{
+					if (expr_is_nonnullable(root, arg, use_rel_info))
+						return true;
+				}
+			}
+			break;
+		case T_MinMaxExpr:
+			{
+				/*
+				 * Like CoalesceExpr, a MinMaxExpr returns NULL only if all
+				 * its arguments evaluate to NULL.
+				 */
+				MinMaxExpr *minmaxexpr = (MinMaxExpr *) expr;
+
+				foreach_ptr(Expr, arg, minmaxexpr->args)
+				{
+					if (expr_is_nonnullable(root, arg, use_rel_info))
+						return true;
+				}
+			}
+			break;
+		case T_CaseExpr:
+			{
+				/*
+				 * A CASE expression is non-nullable if all branch results are
+				 * non-nullable.  We must also verify that the default result
+				 * (ELSE) exists and is non-nullable.
+				 */
+				CaseExpr   *caseexpr = (CaseExpr *) expr;
+
+				/* The default result must be present and non-nullable */
+				if (caseexpr->defresult == NULL ||
+					!expr_is_nonnullable(root, caseexpr->defresult, use_rel_info))
+					return false;
+
+				/* All branch results must be non-nullable */
+				foreach_ptr(CaseWhen, casewhen, caseexpr->args)
+				{
+					if (!expr_is_nonnullable(root, casewhen->result, use_rel_info))
+						return false;
+				}
+
+				return true;
+			}
+			break;
+		case T_ArrayExpr:
+			{
+				/*
+				 * An ARRAY[] expression always returns a valid Array object,
+				 * even if it is empty (ARRAY[]) or contains NULLs
+				 * (ARRAY[NULL]).  It never evaluates to a SQL NULL.
+				 */
+				return true;
+			}
+		case T_NullTest:
+			{
+				/*
+				 * An IS NULL / IS NOT NULL expression always returns a
+				 * boolean value.  It never returns SQL NULL.
+				 */
+				return true;
+			}
+		case T_BooleanTest:
+			{
+				/*
+				 * A BooleanTest expression always evaluates to a boolean
+				 * value.  It never returns SQL NULL.
+				 */
+				return true;
+			}
+		case T_DistinctExpr:
+			{
+				/*
+				 * IS DISTINCT FROM never returns NULL, effectively acting as
+				 * though NULL were a normal data value.
+				 */
+				return true;
+			}
+		case T_RelabelType:
+			{
+				/*
+				 * RelabelType does not change the nullability of the data.
+				 * The result is non-nullable if and only if the argument is
+				 * non-nullable.
+				 */
+				return expr_is_nonnullable(root, ((RelabelType *) expr)->arg,
+										   use_rel_info);
+			}
+		default:
+			break;
+	}
 
 	return false;
 }
diff --git a/src/test/modules/test_predtest/expected/test_predtest.out b/src/test/modules/test_predtest/expected/test_predtest.out
index 6d21bcd603e..ad82b4f8f91 100644
--- a/src/test/modules/test_predtest/expected/test_predtest.out
+++ b/src/test/modules/test_predtest/expected/test_predtest.out
@@ -1066,7 +1066,7 @@ w_r_holds         | t
 
 -- as does nullness of the array
 select * from test_predtest($$
-select x = any(opaque_array(array[y])), array[y] is null
+select x = any(opaque_array(array[y])), opaque_array(array[y]) is null
 from integers
 $$);
 -[ RECORD 1 ]-----+--
diff --git a/src/test/modules/test_predtest/sql/test_predtest.sql b/src/test/modules/test_predtest/sql/test_predtest.sql
index 072eb5b0d50..dc59f0c22f0 100644
--- a/src/test/modules/test_predtest/sql/test_predtest.sql
+++ b/src/test/modules/test_predtest/sql/test_predtest.sql
@@ -431,7 +431,7 @@ $$);
 
 -- as does nullness of the array
 select * from test_predtest($$
-select x = any(opaque_array(array[y])), array[y] is null
+select x = any(opaque_array(array[y])), opaque_array(array[y]) is null
 from integers
 $$);
 
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 9f9f795e892..0c739d33cdb 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -443,11 +443,11 @@ SELECT * FROM pred_tab2, pred_tab1 WHERE pred_tab1.a IS NULL OR pred_tab1.b < 2;
 RESET constraint_exclusion;
 DROP TABLE pred_tab1;
 DROP TABLE pred_tab2;
+CREATE TABLE pred_tab (a int NOT NULL, b int, c int);
 --
 -- Test that COALESCE expressions in predicates are simplified using
 -- non-nullable arguments.
 --
-CREATE TABLE pred_tab (a int NOT NULL, b int);
 -- Ensure that constant NULL arguments are dropped
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab WHERE COALESCE(NULL, b, NULL, a) > 1;
@@ -475,4 +475,119 @@ SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
    Filter: (a > 1)
 (2 rows)
 
+--
+-- Test detection of non-nullable expressions in predicates
+--
+-- CoalesceExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, c) IS NULL;
+             QUERY PLAN             
+------------------------------------
+ Seq Scan on pred_tab
+   Filter: (COALESCE(b, c) IS NULL)
+(2 rows)
+
+-- MinMaxExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, a) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, c) IS NULL;
+             QUERY PLAN             
+------------------------------------
+ Seq Scan on pred_tab
+   Filter: (GREATEST(b, c) IS NULL)
+(2 rows)
+
+-- CaseExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a ELSE a END) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN b ELSE a END) IS NULL;
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Seq Scan on pred_tab
+   Filter: (CASE WHEN (c > 0) THEN b ELSE a END IS NULL)
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a END) IS NULL;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Seq Scan on pred_tab
+   Filter: (CASE WHEN (c > 0) THEN a ELSE NULL::integer END IS NULL)
+(2 rows)
+
+-- ArrayExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ARRAY[b] IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- NullTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (b IS NULL) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- BooleanTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ((a > 1) IS TRUE) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- DistinctExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a IS DISTINCT FROM b) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- RelabelType
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a::oid) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
 DROP TABLE pred_tab;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 0c7e4b974ff..1cbe398cf45 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -222,11 +222,12 @@ RESET constraint_exclusion;
 DROP TABLE pred_tab1;
 DROP TABLE pred_tab2;
 
+CREATE TABLE pred_tab (a int NOT NULL, b int, c int);
+
 --
 -- Test that COALESCE expressions in predicates are simplified using
 -- non-nullable arguments.
 --
-CREATE TABLE pred_tab (a int NOT NULL, b int);
 
 -- Ensure that constant NULL arguments are dropped
 EXPLAIN (COSTS OFF)
@@ -240,4 +241,52 @@ SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1;
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
 
+--
+-- Test detection of non-nullable expressions in predicates
+--
+
+-- CoalesceExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, c) IS NULL;
+
+-- MinMaxExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, a) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, c) IS NULL;
+
+-- CaseExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a ELSE a END) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN b ELSE a END) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a END) IS NULL;
+
+-- ArrayExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ARRAY[b] IS NULL;
+
+-- NullTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (b IS NULL) IS NULL;
+
+-- BooleanTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ((a > 1) IS TRUE) IS NULL;
+
+-- DistinctExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a IS DISTINCT FROM b) IS NULL;
+
+-- RelabelType
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a::oid) IS NULL;
+
 DROP TABLE pred_tab;
-- 
2.39.5 (Apple Git-154)

From 90bfe4cf17a600511d68d7ab186592eb974efe2d Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 1 Dec 2025 10:38:44 +0900
Subject: [PATCH v5 1/3] Simplify COALESCE expressions using non-nullable
 arguments

The COALESCE function returns the first of its arguments that is not
null.  When an argument is proven non-null, if it is the first
non-null-constant argument, the entire COALESCE expression can be
replaced by that argument.  If it is a subsequent argument, all
following arguments can be dropped, since they will never be reached.

Currently, we perform this simplification only for Const arguments.
This patch extends the simplification to support any expression that
can be proven non-nullable.

This can help avoid the overhead of evaluating unreachable arguments.
It can also lead to better plans when the first argument is proven
non-nullable and replaces the expression, as the planner no longer has
to treat the expression as non-strict, and can also leverage index
scans on the resulting expression.

There is an ensuing plan change in generated_virtual.out, and we have
to modify the test to ensure that it continues to test what it is
intended to.
---
 src/backend/optimizer/util/clauses.c          | 18 +++++--
 .../regress/expected/generated_virtual.out    | 48 ++++++++++---------
 src/test/regress/expected/predicate.out       | 33 +++++++++++++
 src/test/regress/sql/generated_virtual.sql    | 11 +++--
 src/test/regress/sql/predicate.sql            | 20 ++++++++
 5 files changed, 97 insertions(+), 33 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index ddafc21c819..ac21057ba51 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3325,10 +3325,10 @@ eval_const_expressions_mutator(Node *node,
 													   context);
 
 					/*
-					 * We can remove null constants from the list. For a
-					 * non-null constant, if it has not been preceded by any
-					 * other non-null-constant expressions then it is the
-					 * result. Otherwise, it's the next argument, but we can
+					 * We can remove null constants from the list.  For a
+					 * nonnullable expression, if it has not been preceded by
+					 * any non-null-constant expressions then it is the
+					 * result.  Otherwise, it's the next argument, but we can
 					 * drop following arguments since they will never be
 					 * reached.
 					 */
@@ -3341,6 +3341,14 @@ eval_const_expressions_mutator(Node *node,
 						newargs = lappend(newargs, e);
 						break;
 					}
+					if (expr_is_nonnullable(context->root, (Expr *) e, false))
+					{
+						if (newargs == NIL)
+							return e;	/* first expr */
+						newargs = lappend(newargs, e);
+						break;
+					}
+
 					newargs = lappend(newargs, e);
 				}
 
@@ -4328,7 +4336,7 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 {
-	if (IsA(expr, Var))
+	if (IsA(expr, Var) && root)
 		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
 	if (IsA(expr, Const))
 		return !castNode(Const, expr)->constisnull;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dde325e46c6..249e68be654 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1509,10 +1509,11 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 -- Ensure that nullingrel bits are propagated into the generation expressions
 explain (costs off)
@@ -1591,46 +1592,47 @@ where coalesce(t2.b, 1) = 2 or t1.a is null;
 -- Ensure that the generation expressions are wrapped into PHVs if needed
 explain (verbose, costs off)
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
-                          QUERY PLAN                           
----------------------------------------------------------------
+                             QUERY PLAN                              
+---------------------------------------------------------------------
  Nested Loop Left Join
-   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.a, 100)), t2.e
+   Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.f, 100)), t2.e, t2.f
    Join Filter: false
    ->  Seq Scan on generated_virtual_tests.gtest32 t1
-         Output: t1.a, t1.b, t1.c, t1.d, t1.e
+         Output: t1.a, t1.b, t1.c, t1.d, t1.e, t1.f
    ->  Result
-         Output: t2.a, t2.e, 20, COALESCE(t2.a, 100)
+         Output: t2.a, t2.e, t2.f, 20, COALESCE(t2.f, 100)
          Replaces: Scan on t2
          One-Time Filter: false
 (9 rows)
 
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
- a | b | c | d | e 
----+---+---+---+---
-   |   |   |   |  
-   |   |   |   |  
+ a | b | c | d | e | f 
+---+---+---+---+---+---
+   |   |   |   |   |  
+   |   |   |   |   |  
 (2 rows)
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-                     QUERY PLAN                      
------------------------------------------------------
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+                       QUERY PLAN                       
+--------------------------------------------------------
  HashAggregate
-   Output: a, ((a * 2)), (20), (COALESCE(a, 100)), e
+   Output: a, ((a * 2)), (20), (COALESCE(f, 100)), e, f
    Hash Key: t.a
    Hash Key: (t.a * 2)
    Hash Key: 20
-   Hash Key: COALESCE(t.a, 100)
+   Hash Key: COALESCE(t.f, 100)
    Hash Key: t.e
+   Hash Key: t.f
    Filter: ((20) = 20)
    ->  Seq Scan on generated_virtual_tests.gtest32 t
-         Output: a, (a * 2), 20, COALESCE(a, 100), e
-(10 rows)
+         Output: a, (a * 2), 20, COALESCE(f, 100), e, f
+(11 rows)
 
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
- a | b | c  | d | e 
----+---+----+---+---
-   |   | 20 |   |  
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+ a | b | c  | d | e | f 
+---+---+----+---+---+---
+   |   | 20 |   |   |  
 (1 row)
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 94c343fe030..520b46cd321 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -484,3 +484,36 @@ SELECT * FROM pred_tab WHERE a < 3 AND b IS NOT NULL AND c IS NOT NULL;
 (2 rows)
 
 DROP TABLE pred_tab;
+--
+-- Test that COALESCE expressions in predicates are simplified using
+-- non-nullable arguments.
+--
+CREATE TABLE pred_tab (a int NOT NULL, b int);
+-- Ensure that constant NULL arguments are dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(NULL, b, NULL, a) > 1;
+           QUERY PLAN           
+--------------------------------
+ Seq Scan on pred_tab
+   Filter: (COALESCE(b, a) > 1)
+(2 rows)
+
+-- Ensure that argument "b*a" is dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1;
+           QUERY PLAN           
+--------------------------------
+ Seq Scan on pred_tab
+   Filter: (COALESCE(b, a) > 1)
+(2 rows)
+
+-- Ensure that the entire COALESCE expression is replaced by "a"
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+   Filter: (a > 1)
+(2 rows)
+
+DROP TABLE pred_tab;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 2911439776c..81152b39a79 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,11 +817,12 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100)),
-  e int
+  d int generated always as (coalesce(f, 100)),
+  e int,
+  f int
 );
 
-insert into gtest32 values (1), (2);
+insert into gtest32 (a, f) values (1, 1), (2, 2);
 analyze gtest32;
 
 -- Ensure that nullingrel bits are propagated into the generation expressions
@@ -859,8 +860,8 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false;
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
-select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e, f) having c = 20;
 
 -- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
 alter table gtest32 alter column e type bigint using b;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 7d4fda1bc18..c3d1a81ada1 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -240,3 +240,23 @@ SELECT * FROM pred_tab WHERE a < 3 AND b IS NOT NULL AND c IS NOT NULL;
 SELECT * FROM pred_tab WHERE a < 3 AND b IS NOT NULL AND c IS NOT NULL;
 
 DROP TABLE pred_tab;
+
+--
+-- Test that COALESCE expressions in predicates are simplified using
+-- non-nullable arguments.
+--
+CREATE TABLE pred_tab (a int NOT NULL, b int);
+
+-- Ensure that constant NULL arguments are dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(NULL, b, NULL, a) > 1;
+
+-- Ensure that argument "b*a" is dropped
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1;
+
+-- Ensure that the entire COALESCE expression is replaced by "a"
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
+
+DROP TABLE pred_tab;
-- 
2.39.5 (Apple Git-154)

From 4e2654e3ac5d07442df052fb7f15f13f509a207b Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 2 Dec 2025 16:01:08 +0900
Subject: [PATCH v5 2/3] Optimize ROW(...) IS [NOT] NULL using non-nullable
 fields

We break ROW(...) IS [NOT] NULL into separate tests on its component
fields.  During this breakdown, we can improve efficiency by utilizing
expr_is_nonnullable() to detect fields that are provably non-nullable.

If a component field is proven non-nullable, it affects the outcome
based on the test type.  For an IS NULL test, a single non-nullable
field refutes the whole NullTest, reducing it to constant FALSE.  For
an IS NOT NULL test, the check for that specific field is guaranteed
to succeed, so we can discard it from the list of component tests.

This extends the existing optimization logic, which previously only
handled Const fields, to support any expression that can be proven
non-nullable.

In passing, update the existing constant folding of NullTests to use
expr_is_nonnullable() instead of var_is_nonnullable(), enabling it to
benefit from future improvements to that function.
---
 src/backend/optimizer/util/clauses.c | 49 +++++++++++++++++-----------
 1 file changed, 30 insertions(+), 19 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index ac21057ba51..eaeadcbcc51 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3528,6 +3528,20 @@ eval_const_expressions_mutator(Node *node,
 							continue;
 						}
 
+						/*
+						 * A proven non-nullable field refutes the whole
+						 * NullTest if the test is IS NULL; else we can
+						 * discard it.
+						 */
+						if (relem &&
+							expr_is_nonnullable(context->root, (Expr *) relem,
+												false))
+						{
+							if (ntest->nulltesttype == IS_NULL)
+								return makeBoolConst(false, false);
+							continue;
+						}
+
 						/*
 						 * Else, make a scalar (argisrow == false) NullTest
 						 * for this field.  Scalar semantics are required
@@ -3572,30 +3586,27 @@ eval_const_expressions_mutator(Node *node,
 
 					return makeBoolConst(result, false);
 				}
-				if (!ntest->argisrow && arg && IsA(arg, Var) && context->root)
+				if (!ntest->argisrow && arg &&
+					expr_is_nonnullable(context->root, (Expr *) arg, false))
 				{
-					Var		   *varg = (Var *) arg;
 					bool		result;
 
-					if (var_is_nonnullable(context->root, varg, false))
+					switch (ntest->nulltesttype)
 					{
-						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);
+						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);
-- 
2.39.5 (Apple Git-154)

From fa5e8f83b3d729e1c6512d78504e929741573f32 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 2 Dec 2025 16:27:26 +0900
Subject: [PATCH v5 3/3] Teach expr_is_nonnullable() to handle more expression
 types

Currently, the function expr_is_nonnullable() checks only Const and
Var expressions to determine if an expression is non-nullable.  This
patch extends the detection logic to handle more expression types.

This can enable several downstream optimizations, such as reducing
NullTest quals to constant truth values (e.g., "COALESCE(var, 1) IS
NULL" becomes FALSE) and converting "COUNT(expr)" to the more
efficient "COUNT(*)" when the expression is proven non-nullable.

This breaks a test case in test_predtest.sql, since we now simplify
"ARRAY[] IS NULL" to constant FALSE, preventing it from weakly
refuting a strict ScalarArrayOpExpr ("x = any(ARRAY[])").  To ensure
the refutation logic is still exercised as intended, wrap the array
argument in opaque_array().
---
 src/backend/optimizer/util/clauses.c          | 123 +++++++++++++++++-
 .../test_predtest/expected/test_predtest.out  |   2 +-
 .../test_predtest/sql/test_predtest.sql       |   2 +-
 src/test/regress/expected/predicate.out       | 117 ++++++++++++++++-
 src/test/regress/sql/predicate.sql            |  50 ++++++-
 5 files changed, 284 insertions(+), 10 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index eaeadcbcc51..67b7de16fc5 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4341,16 +4341,127 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
  * nullability information before RelOptInfos are generated.  These should
  * pass 'use_rel_info' as false.
  *
- * For now, we only support Var and Const.  Support for other node types may
- * be possible.
+ * For now, we support only a limited set of expression types.  Support for
+ * additional node types can be added in the future.
  */
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 {
-	if (IsA(expr, Var) && root)
-		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
-	if (IsA(expr, Const))
-		return !castNode(Const, expr)->constisnull;
+	/* since this function recurses, it could be driven to stack overflow */
+	check_stack_depth();
+
+	switch (nodeTag(expr))
+	{
+		case T_Var:
+			{
+				if (root)
+					return var_is_nonnullable(root, (Var *) expr, use_rel_info);
+			}
+			break;
+		case T_Const:
+			return !((Const *) expr)->constisnull;
+		case T_CoalesceExpr:
+			{
+				/*
+				 * A CoalesceExpr returns NULL if and only if all its
+				 * arguments are NULL.  Therefore, we can determine that a
+				 * CoalesceExpr cannot be NULL if at least one of its
+				 * arguments can be proven non-nullable.
+				 */
+				CoalesceExpr *coalesceexpr = (CoalesceExpr *) expr;
+
+				foreach_ptr(Expr, arg, coalesceexpr->args)
+				{
+					if (expr_is_nonnullable(root, arg, use_rel_info))
+						return true;
+				}
+			}
+			break;
+		case T_MinMaxExpr:
+			{
+				/*
+				 * Like CoalesceExpr, a MinMaxExpr returns NULL only if all
+				 * its arguments evaluate to NULL.
+				 */
+				MinMaxExpr *minmaxexpr = (MinMaxExpr *) expr;
+
+				foreach_ptr(Expr, arg, minmaxexpr->args)
+				{
+					if (expr_is_nonnullable(root, arg, use_rel_info))
+						return true;
+				}
+			}
+			break;
+		case T_CaseExpr:
+			{
+				/*
+				 * A CASE expression is non-nullable if all branch results are
+				 * non-nullable.  We must also verify that the default result
+				 * (ELSE) exists and is non-nullable.
+				 */
+				CaseExpr   *caseexpr = (CaseExpr *) expr;
+
+				/* The default result must be present and non-nullable */
+				if (caseexpr->defresult == NULL ||
+					!expr_is_nonnullable(root, caseexpr->defresult, use_rel_info))
+					return false;
+
+				/* All branch results must be non-nullable */
+				foreach_ptr(CaseWhen, casewhen, caseexpr->args)
+				{
+					if (!expr_is_nonnullable(root, casewhen->result, use_rel_info))
+						return false;
+				}
+
+				return true;
+			}
+			break;
+		case T_ArrayExpr:
+			{
+				/*
+				 * An ARRAY[] expression always returns a valid Array object,
+				 * even if it is empty (ARRAY[]) or contains NULLs
+				 * (ARRAY[NULL]).  It never evaluates to a SQL NULL.
+				 */
+				return true;
+			}
+		case T_NullTest:
+			{
+				/*
+				 * An IS NULL / IS NOT NULL expression always returns a
+				 * boolean value.  It never returns SQL NULL.
+				 */
+				return true;
+			}
+		case T_BooleanTest:
+			{
+				/*
+				 * A BooleanTest expression always evaluates to a boolean
+				 * value.  It never returns SQL NULL.
+				 */
+				return true;
+			}
+		case T_DistinctExpr:
+			{
+				/*
+				 * IS DISTINCT FROM never returns NULL, effectively acting as
+				 * though NULL were a normal data value.
+				 */
+				return true;
+			}
+		case T_RelabelType:
+			{
+				/*
+				 * RelabelType does not change the nullability of the data.
+				 * The result is non-nullable if and only if the argument is
+				 * non-nullable.
+				 */
+				return expr_is_nonnullable(root, ((RelabelType *) expr)->arg,
+										   use_rel_info);
+			}
+		default:
+			break;
+	}
 
 	return false;
 }
diff --git a/src/test/modules/test_predtest/expected/test_predtest.out b/src/test/modules/test_predtest/expected/test_predtest.out
index 6d21bcd603e..ad82b4f8f91 100644
--- a/src/test/modules/test_predtest/expected/test_predtest.out
+++ b/src/test/modules/test_predtest/expected/test_predtest.out
@@ -1066,7 +1066,7 @@ w_r_holds         | t
 
 -- as does nullness of the array
 select * from test_predtest($$
-select x = any(opaque_array(array[y])), array[y] is null
+select x = any(opaque_array(array[y])), opaque_array(array[y]) is null
 from integers
 $$);
 -[ RECORD 1 ]-----+--
diff --git a/src/test/modules/test_predtest/sql/test_predtest.sql b/src/test/modules/test_predtest/sql/test_predtest.sql
index 072eb5b0d50..dc59f0c22f0 100644
--- a/src/test/modules/test_predtest/sql/test_predtest.sql
+++ b/src/test/modules/test_predtest/sql/test_predtest.sql
@@ -431,7 +431,7 @@ $$);
 
 -- as does nullness of the array
 select * from test_predtest($$
-select x = any(opaque_array(array[y])), array[y] is null
+select x = any(opaque_array(array[y])), opaque_array(array[y]) is null
 from integers
 $$);
 
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 520b46cd321..8ff1172008e 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -488,7 +488,7 @@ DROP TABLE pred_tab;
 -- Test that COALESCE expressions in predicates are simplified using
 -- non-nullable arguments.
 --
-CREATE TABLE pred_tab (a int NOT NULL, b int);
+CREATE TABLE pred_tab (a int NOT NULL, b int, c int);
 -- Ensure that constant NULL arguments are dropped
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab WHERE COALESCE(NULL, b, NULL, a) > 1;
@@ -516,4 +516,119 @@ SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
    Filter: (a > 1)
 (2 rows)
 
+--
+-- Test detection of non-nullable expressions in predicates
+--
+-- CoalesceExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, c) IS NULL;
+             QUERY PLAN             
+------------------------------------
+ Seq Scan on pred_tab
+   Filter: (COALESCE(b, c) IS NULL)
+(2 rows)
+
+-- MinMaxExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, a) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, c) IS NULL;
+             QUERY PLAN             
+------------------------------------
+ Seq Scan on pred_tab
+   Filter: (GREATEST(b, c) IS NULL)
+(2 rows)
+
+-- CaseExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a ELSE a END) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN b ELSE a END) IS NULL;
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Seq Scan on pred_tab
+   Filter: (CASE WHEN (c > 0) THEN b ELSE a END IS NULL)
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a END) IS NULL;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Seq Scan on pred_tab
+   Filter: (CASE WHEN (c > 0) THEN a ELSE NULL::integer END IS NULL)
+(2 rows)
+
+-- ArrayExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ARRAY[b] IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- NullTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (b IS NULL) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- BooleanTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ((a > 1) IS TRUE) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- DistinctExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a IS DISTINCT FROM b) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- RelabelType
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a::oid) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
 DROP TABLE pred_tab;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index c3d1a81ada1..db72b11bb22 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -245,7 +245,7 @@ DROP TABLE pred_tab;
 -- Test that COALESCE expressions in predicates are simplified using
 -- non-nullable arguments.
 --
-CREATE TABLE pred_tab (a int NOT NULL, b int);
+CREATE TABLE pred_tab (a int NOT NULL, b int, c int);
 
 -- Ensure that constant NULL arguments are dropped
 EXPLAIN (COSTS OFF)
@@ -259,4 +259,52 @@ SELECT * FROM pred_tab WHERE COALESCE(b, a, b*a) > 1;
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab WHERE COALESCE(a, b) > 1;
 
+--
+-- Test detection of non-nullable expressions in predicates
+--
+
+-- CoalesceExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, c) IS NULL;
+
+-- MinMaxExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, a) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE GREATEST(b, c) IS NULL;
+
+-- CaseExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a ELSE a END) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN b ELSE a END) IS NULL;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (CASE WHEN c > 0 THEN a END) IS NULL;
+
+-- ArrayExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ARRAY[b] IS NULL;
+
+-- NullTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (b IS NULL) IS NULL;
+
+-- BooleanTest
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE ((a > 1) IS TRUE) IS NULL;
+
+-- DistinctExpr
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a IS DISTINCT FROM b) IS NULL;
+
+-- RelabelType
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE (a::oid) IS NULL;
+
 DROP TABLE pred_tab;
-- 
2.39.5 (Apple Git-154)