v1-0001-Expand-virtual-columns-in-get_relation_constraint.patch

From 9e41e9ae0837288658d410d355b95909fd58b37e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 3 Sep 2025 15:41:17 +0200
Subject: [PATCH v1] Expand virtual columns in get_relation_constraints()

Otherwise, some opportunities for constraint exclusion will be missed
if a constraint contains virtual generated columns.
---
 src/backend/optimizer/util/plancat.c            |  3 +++
 src/test/regress/expected/generated_virtual.out | 16 ++++++++++++++++
 src/test/regress/sql/generated_virtual.sql      | 12 ++++++++++++
 3 files changed, 31 insertions(+)

diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 4536bdd6cb4..2b66d3fdd5d 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -42,6 +42,7 @@
 #include "parser/parse_relation.h"
 #include "parser/parsetree.h"
 #include "partitioning/partdesc.h"
+#include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "statistics/statistics.h"
 #include "storage/bufmgr.h"
@@ -1407,6 +1408,8 @@ get_relation_constraints(PlannerInfo *root,
 
 			cexpr = stringToNode(constr->check[i].ccbin);
 
+			cexpr = expand_generated_columns_in_expr(cexpr, relation, 1);
+
 			/*
 			 * Fix Vars to have the desired varno.  This must be done before
 			 * const-simplification because eval_const_expressions reduces
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..234477d2f9f 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1636,3 +1636,19 @@ select 1 from gtest32 t1 where exists
 (1 row)
 
 drop table gtest32;
+--
+-- test expansion for constraint exclusion
+-- (get_relation_constraints() in plancat.c)
+--
+create table gtest33 (a int, b int generated always as (a * 2) virtual, check (b > 10));
+set constraint_exclusion to on;
+-- should get one-time filter, not a seq scan
+explain (costs off) select * from gtest33 where b < 10;
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+reset constraint_exclusion;
+drop table gtest33;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ba19bc4c701..af8139af4d3 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -868,3 +868,15 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
   (select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2);
 
 drop table gtest32;
+
+--
+-- test expansion for constraint exclusion
+-- (get_relation_constraints() in plancat.c)
+--
+
+create table gtest33 (a int, b int generated always as (a * 2) virtual, check (b > 10));
+set constraint_exclusion to on;
+-- should get one-time filter, not a seq scan
+explain (costs off) select * from gtest33 where b < 10;
+reset constraint_exclusion;
+drop table gtest33;

base-commit: 01d6e5b2cf90737395344a8233cae5891c191357
-- 
2.51.0