[songjinzhou <tsinghualucky912@foxmail.com>]

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:

v1_0001-Modify-an-incorrect-regression-test-case-in-the-grou.patch (application/octet-stream, 2.7K) download
From 86963d05c59d6f24b9745efe6cbad6d2c4b15dc3 Mon Sep 17 00:00:00 2001
From: songjinzhou <tsinghualucky912@foxmail.com>
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