We (I) missed expanding virtual generated columns in
get_relation_constraints() in plancat.c. That way, some opportunities
for constraint exclusion will be missed if a constraint contains virtual
generated columns, as can be shown in the attached test case (thanks to
Richard Guo). Simple fix attached.
From 9e41e9ae0837288658d410d355b95909fd58b37e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@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
