From: Alexander Lakhin <exclusion@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Parallel Aggregates for string_agg and array_agg
Date: Tue, 26 Mar 2024 17:00:00 +0300
Message-ID: <d4493a28-589a-5328-fed5-250f2d7d3e2a@gmail.com> (raw)
In-Reply-To: <CAApHDvrKaB83BQQiS0PptWJYyccH0c9ZnYeCmTRSJ4TEwO5Ekg@mail.gmail.com>
References: <CAKJS1f8cB3cc5AK=wPvByhU_jKkgd81iRt2kE6cru8uxx2HCuA@mail.gmail.com>
<1b6ee15c-c218-761e-0120-c1ed438e2db2@2ndquadrant.com>
<657be73a-a21d-0ae1-5c62-870afbdfddd4@2ndquadrant.com>
<CAKJS1f-bKAD+s-CJetRQPjmrR-gF-kfvwfzhQm-A0PA-nsr1xw@mail.gmail.com>
<20180326022647.GL24540@tamriel.snowman.net>
<CAKJS1f-axkksX3XzduLJuTL=o8CSwf1NaQYO6=iHSXR_B3pCKQ@mail.gmail.com>
<20180326132009.GN24540@tamriel.snowman.net>
<CAKJS1f8JVpUUkYP2c+RyVRdpS1dnjB_DA5fbcDSL3qfMRqc81Q@mail.gmail.com>
<6538.1522096067@sss.pgh.pa.us>
<CA+TgmoZL6-J-_EGWLKxFWjEu5_advC-59C1LnFOJQRgH+E=Q=g@mail.gmail.com>
<15683.1525208375@sss.pgh.pa.us>
<CAKJS1f-3fs_u235GKt0fC7D+9vJ9ApB-c1tDVTB_U0HUdGxw-Q@mail.gmail.com>
<CAApHDvpRddvXK=8b8YZAKa_UPseHwhkBFH+5UZVoeGwq7GmqSQ@mail.gmail.com>
<2800168.1659485900@sss.pgh.pa.us>
<CAApHDvpNuGFkjmQ4b3VWxo9MSYxsR6yGykemLRRNU8heYhiNTA@mail.gmail.com>
<CAApHDvrKaB83BQQiS0PptWJYyccH0c9ZnYeCmTRSJ4TEwO5Ekg@mail.gmail.com>
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%3A05inline patch 1: /home/bf/bf-build/urutu/HEAD/pgsql.build/src/test/regress/results/aggregates.out (+0/-0 lines) download inline-1.patch--- /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%3A27https://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
Patches and attachments:download all (2 files, .tar.gz)aggregates.patch (text/x-patch, 2.1K) downloaddiff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.outindex 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.sqlindex 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: exclusion@gmail.com, dgrowleyml@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Parallel Aggregates for string_agg and array_agg
In-Reply-To: <d4493a28-589a-5328-fed5-250f2d7d3e2a@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox