Content-Type: multipart/mixed; boundary="------------aWpiGBHdW30wwsFBq4WhjC1b"
Message-ID: <d4493a28-589a-5328-fed5-250f2d7d3e2a@gmail.com>
Date: Tue, 26 Mar 2024 17:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Parallel Aggregates for string_agg and array_agg
Content-Language: en-US
To: David Rowley <dgrowleyml@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
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>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: 
 <CAApHDvrKaB83BQQiS0PptWJYyccH0c9ZnYeCmTRSJ4TEwO5Ekg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d4493a28-589a-5328-fed5-250f2d7d3e2a%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------aWpiGBHdW30wwsFBq4WhjC1b
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
--------------aWpiGBHdW30wwsFBq4WhjC1b
Content-Type: text/x-patch; charset=UTF-8; name="aggregates.patch"
Content-Disposition: attachment; filename="aggregates.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvYWdncmVnYXRlcy5vdXQg
Yi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2FnZ3JlZ2F0ZXMub3V0CmluZGV4IGQ1NGEy
NTVlNTguLjNkZTk4YzkxNmQgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0
ZWQvYWdncmVnYXRlcy5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9hZ2dy
ZWdhdGVzLm91dApAQCAtMTkzMSw2ICsxOTMxLDEyIEBAIHNlbGVjdCBzdHJpbmdfYWdnKHYs
IGRlY29kZSgnZWUnLCAnaGV4JykpIGZyb20gYnl0ZWFfdGVzdF90YWJsZTsKIGRyb3AgdGFi
bGUgYnl0ZWFfdGVzdF90YWJsZTsKIC0tIFRlc3QgcGFyYWxsZWwgc3RyaW5nX2FnZyBhbmQg
YXJyYXlfYWdnCiBjcmVhdGUgdGFibGUgcGFnZ190ZXN0ICh4IGludCwgeSBpbnQpOworc2Vs
ZWN0IHBnX3NsZWVwKDMpOworIHBnX3NsZWVwIAorLS0tLS0tLS0tLQorIAorKDEgcm93KQor
CiBpbnNlcnQgaW50byBwYWdnX3Rlc3QKIHNlbGVjdCAoY2FzZSB4ICUgNCB3aGVuIDEgdGhl
biBudWxsIGVsc2UgeCBlbmQpLCB4ICUgMTAKIGZyb20gZ2VuZXJhdGVfc2VyaWVzKDEsNTAw
MCkgeDsKQEAgLTE5NjcsNiArMTk3MywxMiBAQCBmcm9tICgKIAkpIGExCiApIGEyCiBncm91
cCBieSB5Oworc2VsZWN0IHBnX3NsZWVwKDMpOworIHBnX3NsZWVwIAorLS0tLS0tLS0tLQor
IAorKDEgcm93KQorCiAtLSBFbnN1cmUgcmVzdWx0cyBhcmUgY29ycmVjdC4KIHNlbGVjdCAq
IGZyb20gdl9wYWdnX3Rlc3Qgb3JkZXIgYnkgeTsKICB5IHwgdG1pbiB8IHRtYXggfCB0bmRp
c3RpbmN0IHwgYm1pbiB8IGJtYXggfCBibmRpc3RpbmN0IHwgYW1pbiB8IGFtYXggfCBhbmRp
c3RpbmN0IHwgYWFtaW4gfCBhYW1heCB8IGFhbmRpc3RpbmN0IApAQCAtMTk4Myw2ICsxOTk1
LDEyIEBAIHNlbGVjdCAqIGZyb20gdl9wYWdnX3Rlc3Qgb3JkZXIgYnkgeTsKICA5IHwgICAx
OSB8IDQ5OTkgfCAgICAgICAgMjUwIHwgMTAxOSB8IDk5OSAgfCAgICAgICAgMjUwIHwgICAx
OSB8IDQ5OTkgfCAgICAgICAgMjUwIHwgICAgMTkgfCAgNDk5OSB8ICAgICAgICAgMjUwCiAo
MTAgcm93cykKIAorc2VsZWN0IHBnX3NsZWVwKDMpOworIHBnX3NsZWVwIAorLS0tLS0tLS0t
LQorIAorKDEgcm93KQorCiAtLSBFbnN1cmUgcGFyYWxsZWwgYWdncmVnYXRpb24gaXMgYWN0
dWFsbHkgYmVpbmcgdXNlZC4KIGV4cGxhaW4gKGNvc3RzIG9mZikgc2VsZWN0ICogZnJvbSB2
X3BhZ2dfdGVzdCBvcmRlciBieSB5OwogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKZGlm
ZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2FnZ3JlZ2F0ZXMuc3FsIGIvc3JjL3Rl
c3QvcmVncmVzcy9zcWwvYWdncmVnYXRlcy5zcWwKaW5kZXggNDQxZTAxZDE1MC4uMjVkZDkw
ZWM2NSAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvYWdncmVnYXRlcy5zcWwK
KysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvYWdncmVnYXRlcy5zcWwKQEAgLTc1MCw2ICs3
NTAsNyBAQCBkcm9wIHRhYmxlIGJ5dGVhX3Rlc3RfdGFibGU7CiAKIC0tIFRlc3QgcGFyYWxs
ZWwgc3RyaW5nX2FnZyBhbmQgYXJyYXlfYWdnCiBjcmVhdGUgdGFibGUgcGFnZ190ZXN0ICh4
IGludCwgeSBpbnQpOworc2VsZWN0IHBnX3NsZWVwKDMpOwogaW5zZXJ0IGludG8gcGFnZ190
ZXN0CiBzZWxlY3QgKGNhc2UgeCAlIDQgd2hlbiAxIHRoZW4gbnVsbCBlbHNlIHggZW5kKSwg
eCAlIDEwCiBmcm9tIGdlbmVyYXRlX3NlcmllcygxLDUwMDApIHg7CkBAIC03ODksOSArNzkw
LDExIEBAIGZyb20gKAogKSBhMgogZ3JvdXAgYnkgeTsKIAorc2VsZWN0IHBnX3NsZWVwKDMp
OwogLS0gRW5zdXJlIHJlc3VsdHMgYXJlIGNvcnJlY3QuCiBzZWxlY3QgKiBmcm9tIHZfcGFn
Z190ZXN0IG9yZGVyIGJ5IHk7CiAKK3NlbGVjdCBwZ19zbGVlcCgzKTsKIC0tIEVuc3VyZSBw
YXJhbGxlbCBhZ2dyZWdhdGlvbiBpcyBhY3R1YWxseSBiZWluZyB1c2VkLgogZXhwbGFpbiAo
Y29zdHMgb2ZmKSBzZWxlY3QgKiBmcm9tIHZfcGFnZ190ZXN0IG9yZGVyIGJ5IHk7CiAK

--------------aWpiGBHdW30wwsFBq4WhjC1b--