Modify an incorrect regression test case in the group by key value elimination function

songjinzhou <tsinghualucky912@foxmail.com>

From: songjinzhou <tsinghualucky912@foxmail.com>
To: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Cc: David Rowley <dgrowleyml@gmail.com>, Japin Li <japinli@hotmail.com>
Date: 2025-02-18T07:40:35Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Fix poorly written regression test

Attachments

Dear hackers:&nbsp;


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;
&nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp;
----------------------
&nbsp;HashAggregate
&nbsp; &nbsp;Group Key: c
&nbsp; &nbsp;-&gt; &nbsp;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;
&nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp;
----------------------
&nbsp;HashAggregate
&nbsp; &nbsp;Group Key: c
&nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t3
(3 rows)


Because the table structure of t3 is defined as follows(Its PK is deferrable):


postgres=# \d+ t3
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table "pg_temp_1.t3"
&nbsp;Column | &nbsp;Type &nbsp; | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
&nbsp;a &nbsp; &nbsp; &nbsp;| integer | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | not null | &nbsp; &nbsp; &nbsp; &nbsp; | plain &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|
&nbsp;b &nbsp; &nbsp; &nbsp;| integer | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | not null | &nbsp; &nbsp; &nbsp; &nbsp; | plain &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|
&nbsp;c &nbsp; &nbsp; &nbsp;| integer | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | not null | &nbsp; &nbsp; &nbsp; &nbsp; | plain &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|
Indexes:
&nbsp; &nbsp; "t3_pkey" PRIMARY KEY, btree (a, b) DEFERRABLE
&nbsp; &nbsp; "t3_c_uidx" UNIQUE, btree (c)
Not-null constraints:
&nbsp; &nbsp; "t3_a_not_null" NOT NULL "a"
&nbsp; &nbsp; "t3_b_not_null" NOT NULL "b"
&nbsp; &nbsp; "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!