On 2/2/2024 09:02, Tom Lane wrote:
Alexander Korotkov <aekorot...@gmail.com> writes:
I'm going to push this if there are no objections.
One of the test cases added by this commit has not been very
stable in the buildfarm. Latest example is here:
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prion&dt=2024-02-01%2021%3A28%3A04
and I've seen similar failures intermittently on other machines.
I'd suggest building this test atop a table that is more stable
than pg_class. You're just waving a red flag in front of a bull
if you expect stable statistics from that during a regression run.
Nor do I see any particular reason for pg_class to be especially
suited to the test.
Yeah, It is my fault. Please, see in the attachment the patch fixing that.
--
regards,
Andrei Lepikhov
Postgres Professional
From 11a049d95ee48e38ad569aab7663d8de91f946ad Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepik...@postgrespro.ru>
Date: Fri, 2 Feb 2024 10:39:55 +0700
Subject: [PATCH] Replace the GROUP-BY optimization test with the same based on
something less volatile when the pg_class relation.
---
src/test/regress/expected/aggregates.out | 32 +++++++++++-------------
src/test/regress/sql/aggregates.sql | 9 +++----
2 files changed, 18 insertions(+), 23 deletions(-)
diff --git a/src/test/regress/expected/aggregates.out
b/src/test/regress/expected/aggregates.out
index 7a73c19314..c2e1b8c9ed 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -2873,7 +2873,6 @@ SELECT y,x,array_agg(distinct w) FROM btg WHERE y < 0
GROUP BY x,y;
(6 rows)
RESET enable_incremental_sort;
-DROP TABLE btg;
-- The case, when scanning sort order correspond to aggregate sort order but
-- can not be found in the group-by list
CREATE TABLE agg_sort_order (c1 int PRIMARY KEY, c2 int);
@@ -2901,32 +2900,31 @@ DROP TABLE agg_sort_order CASCADE;
SET enable_hashjoin = off;
SET enable_nestloop = off;
explain (COSTS OFF)
-SELECT c1.relname,c1.relpages
-FROM pg_class c1 JOIN pg_class c2 ON (c1.relname=c2.relname AND
c1.relpages=c2.relpages)
-GROUP BY c1.reltuples,c1.relpages,c1.relname
-ORDER BY c1.relpages, c1.relname, c1.relpages*c1.relpages;
- QUERY PLAN
----------------------------------------------------------------------------------------------
+SELECT b1.x,b1.w FROM btg b1 JOIN btg b2 ON (b1.z=b2.z AND b1.w=b2.w)
+GROUP BY b1.x,b1.z,b1.w ORDER BY b1.z, b1.w, b1.x*b1.x;
+ QUERY PLAN
+-------------------------------------------------------------------
Incremental Sort
- Sort Key: c1.relpages, c1.relname, ((c1.relpages * c1.relpages))
- Presorted Key: c1.relpages, c1.relname
+ Sort Key: b1.z, b1.w, ((b1.x * b1.x))
+ Presorted Key: b1.z, b1.w
-> Group
- Group Key: c1.relpages, c1.relname, c1.reltuples
+ Group Key: b1.z, b1.w, b1.x
-> Incremental Sort
- Sort Key: c1.relpages, c1.relname, c1.reltuples
- Presorted Key: c1.relpages, c1.relname
+ Sort Key: b1.z, b1.w, b1.x
+ Presorted Key: b1.z, b1.w
-> Merge Join
- Merge Cond: ((c1.relpages = c2.relpages) AND (c1.relname
= c2.relname))
+ Merge Cond: ((b1.z = b2.z) AND (b1.w = b2.w))
-> Sort
- Sort Key: c1.relpages, c1.relname
- -> Seq Scan on pg_class c1
+ Sort Key: b1.z, b1.w
+ -> Seq Scan on btg b1
-> Sort
- Sort Key: c2.relpages, c2.relname
- -> Seq Scan on pg_class c2
+ Sort Key: b2.z, b2.w
+ -> Seq Scan on btg b2
(16 rows)
RESET enable_hashjoin;
RESET enable_nestloop;
+DROP TABLE btg;
RESET enable_hashagg;
RESET max_parallel_workers;
RESET max_parallel_workers_per_gather;
diff --git a/src/test/regress/sql/aggregates.sql
b/src/test/regress/sql/aggregates.sql
index 916dbf908f..3548fbb8db 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1229,8 +1229,6 @@ EXPLAIN (VERBOSE, COSTS OFF)
SELECT y,x,array_agg(distinct w) FROM btg WHERE y < 0 GROUP BY x,y;
RESET enable_incremental_sort;
-DROP TABLE btg;
-
-- The case, when scanning sort order correspond to aggregate sort order but
-- can not be found in the group-by list
CREATE TABLE agg_sort_order (c1 int PRIMARY KEY, c2 int);
@@ -1245,13 +1243,12 @@ DROP TABLE agg_sort_order CASCADE;
SET enable_hashjoin = off;
SET enable_nestloop = off;
explain (COSTS OFF)
-SELECT c1.relname,c1.relpages
-FROM pg_class c1 JOIN pg_class c2 ON (c1.relname=c2.relname AND
c1.relpages=c2.relpages)
-GROUP BY c1.reltuples,c1.relpages,c1.relname
-ORDER BY c1.relpages, c1.relname, c1.relpages*c1.relpages;
+SELECT b1.x,b1.w FROM btg b1 JOIN btg b2 ON (b1.z=b2.z AND b1.w=b2.w)
+GROUP BY b1.x,b1.z,b1.w ORDER BY b1.z, b1.w, b1.x*b1.x;
RESET enable_hashjoin;
RESET enable_nestloop;
+DROP TABLE btg;
RESET enable_hashagg;
RESET max_parallel_workers;
RESET max_parallel_workers_per_gather;
--
2.43.0
