Hello David, 23.01.2023 07:37, David Rowley wrote:
I've now pushed this.
I've discovered that the test query: -- Ensure parallel aggregation is actually being used. explain (costs off) select * from v_pagg_test order by y; added by 16fd03e95 fails sometimes. For instance: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=urutu&dt=2024-03-19%2021%3A04%3A05 --- /home/bf/bf-build/urutu/HEAD/pgsql/src/test/regress/expected/aggregates.out 2024-02-24 06:42:47.039073180 +0000 +++ /home/bf/bf-build/urutu/HEAD/pgsql.build/src/test/regress/results/aggregates.out 2024-03-19 22:24:18.155876135 +0000 @@ -1993,14 +1993,16 @@Sort Key: pagg_test.y, (((unnest(regexp_split_to_array((string_agg((pagg_test.x)::text, ','::text)), ','::text))))::integer)
-> Result -> ProjectSet - -> Finalize HashAggregate + -> Finalize GroupAggregate ... https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=phycodurus&dt=2024-02-28%2007%3A38%3A27 https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=avocet&dt=2024-02-08%2008%3A47%3A45 I suspect that these failures caused by autovacuum. I could reproduce this plan change when running multiple tests in parallel, and also with the attached test patch applied (several sleeps are needed for autovacuum/relation_needs_vacanalyze() to get a non-zero mod_since_analyze value from pgstat): TEMP_CONFIG=/tmp/temp.config TESTS="test_setup create_index create_aggregate aggregates" make -s check-tests where /tmp/temp.config is: autovacuum_naptime = 1 log_autovacuum_min_duration = 0 log_min_messages = INFO log_min_error_statement = log log_statement = 'all' With EXPLAIN (VERBOSE), I see a slight change of the Seq Scan cost/rows estimate: ... -> Parallel Seq Scan on public.pagg_test (cost=0.00..48.99 rows=2599 width=8) vs .. -> Parallel Seq Scan on public.pagg_test (cost=0.00..48.00 rows=2500 width=8) (after automatic analyze of table "regression.public.pagg_test") Best regards, Alexander
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index d54a255e58..3de98c916d 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1931,6 +1931,12 @@ select string_agg(v, decode('ee', 'hex')) from bytea_test_table; drop table bytea_test_table; -- Test parallel string_agg and array_agg create table pagg_test (x int, y int); +select pg_sleep(3); + pg_sleep +---------- + +(1 row) + insert into pagg_test select (case x % 4 when 1 then null else x end), x % 10 from generate_series(1,5000) x; @@ -1967,6 +1973,12 @@ from ( ) a1 ) a2 group by y; +select pg_sleep(3); + pg_sleep +---------- + +(1 row) + -- Ensure results are correct. select * from v_pagg_test order by y; y | tmin | tmax | tndistinct | bmin | bmax | bndistinct | amin | amax | andistinct | aamin | aamax | aandistinct @@ -1983,6 +1995,12 @@ select * from v_pagg_test order by y; 9 | 19 | 4999 | 250 | 1019 | 999 | 250 | 19 | 4999 | 250 | 19 | 4999 | 250 (10 rows) +select pg_sleep(3); + pg_sleep +---------- + +(1 row) + -- Ensure parallel aggregation is actually being used. explain (costs off) select * from v_pagg_test order by y; QUERY PLAN diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 441e01d150..25dd90ec65 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -750,6 +750,7 @@ drop table bytea_test_table; -- Test parallel string_agg and array_agg create table pagg_test (x int, y int); +select pg_sleep(3); insert into pagg_test select (case x % 4 when 1 then null else x end), x % 10 from generate_series(1,5000) x; @@ -789,9 +790,11 @@ from ( ) a2 group by y; +select pg_sleep(3); -- Ensure results are correct. select * from v_pagg_test order by y; +select pg_sleep(3); -- Ensure parallel aggregation is actually being used. explain (costs off) select * from v_pagg_test order by y;