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

From: songjinzhou <tsinghualucky912(at)foxmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: David Rowley <dgrowleyml(at)gmail(dot)com>, Japin Li <japinli(at)hotmail(dot)com>
Subject: Modify an incorrect regression test case in the group by key value elimination function
Date: 2025-02-18 07:40:35
Message-ID: tencent_CD414C79D39668455DF80D35143B87634C08@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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!

Attachment Content-Type Size
v1_0001-Modify-an-incorrect-regression-test-case-in-the-grou.patch application/octet-stream 2.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2025-02-18 07:50:22 Re: ReplicationSlotRelease() crashes when the instance is in the single user mode
Previous Message Hayato Kuroda (Fujitsu) 2025-02-18 07:29:51 RE: ReplicationSlotRelease() crashes when the instance is in the single user mode