expand virtual generated columns in get_relation_constraints()

Started by Peter Eisentraut4 months ago3 messages

peter@eisentraut.org

4 months ago

1 attachment(s)

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.

Attachments:

v1-0001-Expand-virtual-columns-in-get_relation_constraint.patchtext/plain; charset=UTF-8; name=v1-0001-Expand-virtual-columns-in-get_relation_constraint.patchDownload

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

Richard Guo

guofenglinux@gmail.com

4 months ago

In reply to: Peter Eisentraut (#1)

1 attachment(s)

Re: expand virtual generated columns in get_relation_constraints()

On Wed, Sep 3, 2025 at 11:05 PM Peter Eisentraut <peter@eisentraut.org> wrote:

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.

I'm afraid this fix isn't thorough: it covers only CHECK constraints
but not NOT NULL or partitioning constraints. For example,

create table vt (a int, b int generated always as (a * 2) virtual not null);
set constraint_exclusion to on;

explain (costs off) select * from vt where b is null;
QUERY PLAN
-----------------------------
Seq Scan on vt
Filter: ((a * 2) IS NULL)
(2 rows)

We should get a dummy Result rather than a SeqScan.

What I have in mind is something like the attached v2, which covers
all types of constraints.

- Richard

Attachments:

v2-0001-Expand-virtual-generated-columns-in-constraint-ex.patchapplication/octet-stream; name=v2-0001-Expand-virtual-generated-columns-in-constraint-ex.patchDownload

From c6822a1bcf04d83a6bb5c154698c937655c344a4 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 11 Sep 2025 11:04:04 +0900
Subject: [PATCH v2] 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.
---
 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 4536bdd6cb4..c0074967091 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.39.5 (Apple Git-154)

Peter Eisentraut

peter@eisentraut.org

4 months ago

In reply to: Richard Guo (#2)

Re: expand virtual generated columns in get_relation_constraints()

On 11.09.25 04:16, Richard Guo wrote:

On Wed, Sep 3, 2025 at 11:05 PM Peter Eisentraut <peter@eisentraut.org> wrote:

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.

I'm afraid this fix isn't thorough: it covers only CHECK constraints
but not NOT NULL or partitioning constraints. For example,

create table vt (a int, b int generated always as (a * 2) virtual not null);
set constraint_exclusion to on;

explain (costs off) select * from vt where b is null;
QUERY PLAN
-----------------------------
Seq Scan on vt
Filter: ((a * 2) IS NULL)
(2 rows)

We should get a dummy Result rather than a SeqScan.

What I have in mind is something like the attached v2, which covers
all types of constraints.

Thanks, I have committed that.