public inbox for [email protected]
help / color / mirror / Atom feedFrom: =?utf-8?B?c29uZ2ppbnpob3U=?= <[email protected]>
To: =?utf-8?B?cGdzcWwtaGFja2Vycw==?= <[email protected]>
Cc: =?utf-8?B?RGF2aWQgUm93bGV5?= <[email protected]>
Cc: =?utf-8?B?SmFwaW4gTGk=?= <[email protected]>
Subject: Modify an incorrect regression test case in the group by key value elimination function
Date: Tue, 18 Feb 2025 15:40:35 +0800
Message-ID: <[email protected]> (raw)
Dear hackers:
Two months ago, we enhanced the group by key value elimination function. This is a very useful function. When I was learning, I found a regression test case that was not suitable, as follows:
-- When there are multiple supporting unique indexes and the GROUP BY contains
-- columns to cover all of those, ensure we pick the index with the least
-- number of columns so that we can remove more columns from the GROUP BY.
explain (costs off) select a,b,c from t3 group by a,b,c;
QUERY PLAN
----------------------
HashAggregate
Group Key: c
-> Seq Scan on t3
(3 rows)
-- As above but try ordering the columns differently to ensure we get the
-- same result.
explain (costs off) select a,b,c from t3 group by c,a,b;
QUERY PLAN
----------------------
HashAggregate
Group Key: c
-> Seq Scan on t3
(3 rows)
Because the table structure of t3 is defined as follows(Its PK is deferrable):
postgres=# \d+ t3
Table "pg_temp_1.t3"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | not null | | plain | | |
b | integer | | not null | | plain | | |
c | integer | | not null | | plain | | |
Indexes:
"t3_pkey" PRIMARY KEY, btree (a, b) DEFERRABLE
"t3_c_uidx" UNIQUE, btree (c)
Not-null constraints:
"t3_a_not_null" NOT NULL "a"
"t3_b_not_null" NOT NULL "b"
"t3_c_not_null" NOT NULL "c"
Access method: heap
postgres=#
I think this test case does not fully reflect the original meaning. So I made a small change to this and look forward to your feedback. Thanks!
Attachments:
[application/octet-stream] v1_0001-Modify-an-incorrect-regression-test-case-in-the-grou.patch (2.7K, ../[email protected]/3-v1_0001-Modify-an-incorrect-regression-test-case-in-the-grou.patch)
download | inline diff:
From 86963d05c59d6f24b9745efe6cbad6d2c4b15dc3 Mon Sep 17 00:00:00 2001
From: songjinzhou <[email protected]>
Date: Mon, 17 Feb 2025 23:07:06 -0800
Subject: [PATCH] Modify an incorrect regression test case in the group by key
value elimination function
---
src/test/regress/expected/aggregates.out | 13 +++++++------
src/test/regress/sql/aggregates.sql | 5 +++--
2 files changed, 10 insertions(+), 8 deletions(-)
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index f2fb66388c..ce826af3cd 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1472,22 +1472,23 @@ explain (costs off) select b,c from t3 group by b,c;
-- When there are multiple supporting unique indexes and the GROUP BY contains
-- columns to cover all of those, ensure we pick the index with the least
-- number of columns so that we can remove more columns from the GROUP BY.
-explain (costs off) select a,b,c from t3 group by a,b,c;
+alter table t2 alter column z set not null, add constraint t2_z_uidx unique (z);
+explain (costs off) select x,y,z from t2 group by x,y,z;
QUERY PLAN
----------------------
HashAggregate
- Group Key: c
- -> Seq Scan on t3
+ Group Key: z
+ -> Seq Scan on t2
(3 rows)
-- As above but try ordering the columns differently to ensure we get the
-- same result.
-explain (costs off) select a,b,c from t3 group by c,a,b;
+explain (costs off) select x,y,z from t2 group by z,x,y;
QUERY PLAN
----------------------
HashAggregate
- Group Key: c
- -> Seq Scan on t3
+ Group Key: z
+ -> Seq Scan on t2
(3 rows)
-- Ensure we don't use a partial index as proof of functional dependency
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 77168bcc74..784b75728a 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -520,11 +520,12 @@ explain (costs off) select b,c from t3 group by b,c;
-- When there are multiple supporting unique indexes and the GROUP BY contains
-- columns to cover all of those, ensure we pick the index with the least
-- number of columns so that we can remove more columns from the GROUP BY.
-explain (costs off) select a,b,c from t3 group by a,b,c;
+alter table t2 alter column z set not null, add constraint t2_z_uidx unique (z);
+explain (costs off) select x,y,z from t2 group by x,y,z;
-- As above but try ordering the columns differently to ensure we get the
-- same result.
-explain (costs off) select a,b,c from t3 group by c,a,b;
+explain (costs off) select x,y,z from t2 group by z,x,y;
-- Ensure we don't use a partial index as proof of functional dependency
drop index t3_c_uidx;
--
2.43.0
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Modify an incorrect regression test case in the group by key value elimination function
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox