From ce71993ae46930ddb1ff790807114384898b86a7 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 15 Sep 2025 16:27:50 +0200 Subject: [PATCH] Expand virtual generated columns in constraint expressions Virtual generated columns in constraint expressions need to be expanded because the optimizer matches these expressions to qual clauses. Failing to do so can cause us to miss opportunities for constraint exclusion. Author: Richard Guo Discussion: https://www.postgresql.org/message-id/flat/204804c0-798f-4c72-bd1f-36116024fda3%40eisentraut.org --- src/backend/optimizer/util/plancat.c | 9 ++++++++ .../regress/expected/generated_virtual.out | 23 +++++++++++++++++++ src/test/regress/sql/generated_virtual.sql | 15 ++++++++++++ 3 files changed, 47 insertions(+) diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 572d626b2c4..f8641204a67 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" @@ -1482,6 +1483,14 @@ get_relation_constraints(PlannerInfo *root, result = list_concat(result, rel->partition_qual); } + /* + * Expand virtual generated columns in the constraint expressions. + */ + if (result) + result = (List *) expand_generated_columns_in_expr((Node *) result, + relation, + varno); + table_close(relation, NoLock); return result; diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index aca6347babe..d8645192351 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1636,3 +1636,26 @@ select 1 from gtest32 t1 where exists (1 row) drop table gtest32; +-- Ensure that virtual generated columns in constraint expressions are expanded +create table gtest33 (a int, b int generated always as (a * 2) virtual not null, check (b > 10)); +set constraint_exclusion to on; +-- should get a dummy Result, not a seq scan +explain (costs off) +select * from gtest33 where b < 10; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +-- should get a dummy Result, not a seq scan +explain (costs off) +select * from gtest33 where b is null; + 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..adfe88d74ae 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -868,3 +868,18 @@ select 1 from gtest32 t1 where exists (select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2); drop table gtest32; + +-- Ensure that virtual generated columns in constraint expressions are expanded +create table gtest33 (a int, b int generated always as (a * 2) virtual not null, check (b > 10)); +set constraint_exclusion to on; + +-- should get a dummy Result, not a seq scan +explain (costs off) +select * from gtest33 where b < 10; + +-- should get a dummy Result, not a seq scan +explain (costs off) +select * from gtest33 where b is null; + +reset constraint_exclusion; +drop table gtest33; -- 2.47.3