From: Japin Li <japinli@hotmail.com>
To: "songjinzhou" <tsinghualucky912@foxmail.com>
Cc: "pgsql-hackers" <pgsql-hackers@lists.postgresql.org>,  "David Rowley"
 <dgrowleyml@gmail.com>
Subject: Re: Modify an incorrect regression test case in the group by key
 value elimination function
In-Reply-To: <tencent_CD414C79D39668455DF80D35143B87634C08@qq.com>
	(songjinzhou's message of "Tue, 18 Feb 2025 15:40:35 +0800")
References: <tencent_CD414C79D39668455DF80D35143B87634C08@qq.com>
Date: Tue, 18 Feb 2025 16:17:25 +0800
Message-ID:
 <ME0P300MB04455DD194CE6F5A8CAFAD85B6FA2@ME0P300MB0445.AUSP300.PROD.OUTLOOK.COM>
Content-Type: text/plain
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: ME0P300MB0445:EE_|SY0P300MB0353:EE_
X-MS-Office365-Filtering-Correlation-Id: 4c66800f-3beb-448d-c45c-08dd4ff4af58
X-Microsoft-Antispam:
	BCL:0;ARA:14566002|19110799003|5072599009|7092599003|15080799006|461199028|8060799006|440099028|3412199025;
X-Microsoft-Antispam-Message-Info:
	=?us-ascii?Q?0z/OF3YiPkESYpgniB4DLfVm9ZYiKZce2TSz2BBWPyd8FJBojP0eltjV1z1W?=
 =?us-ascii?Q?q7Is5vk4QMCzRuqCb7/a1v7KcJurQInHHVsMSV4W432GEtZIiFtegGd+eTzS?=
 =?us-ascii?Q?56HQvt41KPLA7oCuRXFzrgiV4e+n46vIP7c2hpZaUx16EtJkXIi0hPkJP7/E?=
 =?us-ascii?Q?EWmBqK4IaSTGpzsjHjN/PBlp+oxw6+rnxibfB9cVJEdYWHy4fg6n8TU2XCw/?=
 =?us-ascii?Q?mv92JNI5ejqL+TkFakHaqCquMqMAHYk3eBlst+q8xNjvwWzQUcllsNc9fpU+?=
 =?us-ascii?Q?tN2mCAF+E6G6FyUFKQtQitd64dL/RzEUj9AZtLga5oTHKRvknxLlE4QZM+xR?=
 =?us-ascii?Q?iVuBQ4GW0WrjUmc1xNBR7ZuBMRWc0GWCsVAS8VvlDdkP/6SMQQ8VVYngJMva?=
 =?us-ascii?Q?a8GadYKHJfZnsSwSMoeQrUdea/vnXdQqxPOQb83WF8luu0niBuUFvFj4jyyn?=
 =?us-ascii?Q?lro4bn2vgwRbV14MC4R/CGk5unpYhpqk4dPhIGtcM6cUYkF5+FuHqYdFAq27?=
 =?us-ascii?Q?cQ521Uef14jFWxeWmZ/sBNu2miB16ovNjgmJmioiOKUXtPiZDQax30BTQ6oY?=
 =?us-ascii?Q?XtgCWqO+k9MnDGlLjYx9Hf5/yyMmjPvZtTlQuXVSrCdII3eBLO6+qwy4DUTc?=
 =?us-ascii?Q?i7FLkRqswHEGRj4YPLHYChkaAwdMqX+zl4p7SAlRtVucBBgA19Cuf+5nJRcu?=
 =?us-ascii?Q?h3O5cGMagSQv3PPdpXXdWmjRqRkWMPezAt0mb2a3tMWPwI9vdeTliwFl7uA6?=
 =?us-ascii?Q?WKzio5nTCtL9U/tp4UjBy+CIVSpRIclLEkHmVcNMP+Of3OxbPOPBOxasIGwU?=
 =?us-ascii?Q?mD7gqzjLosyWPK91CdbzbzYPnAyQ1CRodGYs5H8vKsIxQPDo/d/lr/IEyher?=
 =?us-ascii?Q?LLJZ++SNsYDR8dPfec5T68yP34VK2yF+vhQs58wdjwFecw/p27TCYCIDAH/m?=
 =?us-ascii?Q?u6Xjg0NSR8ZedVJcEjs74rW75TEiZFiStRqn1OyqObWk+ruRX7rpPbyOkaOw?=
 =?us-ascii?Q?g/ihqJ0VBSPBk/ANSJXkMjSZC7ZlnMtIqXXhQyzhCIhKfwNTrIwGS2iIVsaw?=
 =?us-ascii?Q?K4j7PMBhhWlgjLzynwdaT22AywY2dQ=3D=3D?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?us-ascii?Q?ixTVSc+7NlzGnrrVS30UtFWtt9BSErclvscXQiDxkp7e8/aVpJhxYhAfkjzG?=
 =?us-ascii?Q?IbcWI+ZmM9WBEOu7YZZJ/Ne5SDfV74ia+fci55xX96yQ9I6HRGU3Agh8pWmh?=
 =?us-ascii?Q?r6WLoxF1Cm4PerrY+FnM3N9U52D41NXWtIJl4kpwWYsN+BKn+3iuPK+anBSb?=
 =?us-ascii?Q?Ngc85HflkLf5uNtCfOX6PYsPlQ/8cZwpRaXSaDjZ18aLyXseJvyiDpguX8eL?=
 =?us-ascii?Q?maikoHFaG9AH+m7ModDGu6SnUq1mYDKEIszUrgpgHMBmMkTVrYbXrzfqYCJ9?=
 =?us-ascii?Q?kFzlPKRv7S4xpUXUT2dCvxH8VuYuZ3cD+YcWH4tJGA9Eo61+CChagJLdXxPn?=
 =?us-ascii?Q?loWhgUHSH5VNMz4Kml5zezVuow2r8SX5g2oCzbyqRUVBR46dwyWLkgo0GyCc?=
 =?us-ascii?Q?fBMAvA/8UrOPcv7+rg3pqbm5OBtoTM8gGOeqgNOqzB63NxzTKczaRXjGY1BJ?=
 =?us-ascii?Q?1NMLuQCvsYjEojlOsoQID1dR/I+4qioYYDVbHBUPb+cl/r8Tyr4vWHRy/4rr?=
 =?us-ascii?Q?sGeecNeTqcyK6Bl1EUSAJ3sfRYSNE00XL6Cvc4bZJRetN5wjlWqd+tu97JVN?=
 =?us-ascii?Q?5Hp1Y1yq6Px5ox1YZEPZJosgbzrNeTrb2UQrA+9DOvfE+vaC8SYI5DEqUFmg?=
 =?us-ascii?Q?aVPDhxtU2cgiyShWi/+BVS4oXl3OJ7q4gYhjAqpPd0Xx2vxnOXR+3gxMNX+T?=
 =?us-ascii?Q?cyAELpFnkM0RfwyzKGOCmtoxpCZqh1AGizbMWWMPdKL8ymyW+7n0DoFj+Eky?=
 =?us-ascii?Q?2Yx/qFiB+RZeHnsVN1d8o2f0mQK+O/i1IprikJ++gzEKcvt8gbX+koOEkrBX?=
 =?us-ascii?Q?YxZ125pU4fUEieG9T+0neecwCVP7JRmQN2HuI6y4GSVPGDkUWwujyd5Td/uC?=
 =?us-ascii?Q?JWlqtzj0lhafgBhDsP7DznFcF0Ufn7ei9J0qaraG8LiJIUR8DY+IIynvIl6e?=
 =?us-ascii?Q?w+PJDmuMfgaBnDcUOwPrUjRmkGqtodyKZhZoZk5MEdNjxwcdexyvrmZVbZKb?=
 =?us-ascii?Q?E04BdKqu3IwAXLNMdoPUJ5vN3aqjSRT2xmHLtlP5eKPXo4H2b1Fd/Q3/uppf?=
 =?us-ascii?Q?XtfjT2NCpVXHPnY1LO+47eoPn3X0fiaIW9HnVC+tOxSy71QKtHWUEaDK15nY?=
 =?us-ascii?Q?TTJrKGOLVrYakLrnicqN6j1ZExKeo6qblMhvJ0h7nsr3d3PfNQtoba6y92Rc?=
 =?us-ascii?Q?fQ33iRupaxgYzmKpSfGUkss7QCWfEl4EKyepvaGsF2VYta3kwDMIgMAc5+0?=
 =?us-ascii?Q?=3D?=
X-OriginatorOrg: sct-15-20-7719-20-msonline-outlook-448bf.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 4c66800f-3beb-448d-c45c-08dd4ff4af58
X-MS-Exchange-CrossTenant-AuthSource: ME0P300MB0445.AUSP300.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 18 Feb 2025 08:17:29.3446
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg:
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY0P300MB0353
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/ME0P300MB04455DD194CE6F5A8CAFAD85B6FA2%40ME0P300MB0445.AUSP300.PROD.OUTLOOK.COM>
Precedence: bulk

On Tue, 18 Feb 2025 at 15:40, "songjinzhou" <tsinghualucky912@foxmail.com> wrote:
> 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!
>

Yeah, the primary key of t3 is deferred, so only the t3_c_uidx can be used.
The test case is inconsistent with comments.

It seems that the t2 does not have a unique index on z, do you forget to
create it in the patch?

-- 
Regrads,
Japin Li