Slow GroupAggregate and Sort

Hello great day, we have a strange case with slow query and would like some help.

I've already read the article https://wiki.postgresql.org/wiki/Slow_Query_Questions

Explain: https://paste.depesz.com/s/PLP

Explain2: https://explain-postgresql.com/archive/explain/8e4b573c5f7bcf3a0d30675a430051fd:0:2023-12-26

Query: https://paste.depesz.com/s/fd3

DDL: https://paste.depesz.com/s/vBW

tunning: https://paste.depesz.com/s/dXa

We have citus cluster with the following configuration: 1 master + 3 data nodes, each machine have:

- 24 cores (Intel Xeon E5 2620)

- 192 GB RAM

- 1TB SSD

each node has configured postgres settings using tuning.sql

The main Table DDL is in (ddl.sql)

also distributed are as follow:

SELECT create_distributed_table('salert_post', 'id',shard_count := 72);

SELECT create_distributed_table('salert_q56', 'post',

                                colocate_with => 'salert_post');

when run the query (query.sql)  as you can see in explain (plan4_v3.txt) citus take about 18s to run all fragments

but each fragment take at most 2s, so my questions are- why citus take this time in run all fragments?

- if I tuned each postgres node efficiently why take much time to make sort and aggregate with citus results?

good night, I hope you can help me with some ideas

also we remove partitions, and test only with citus, but query took more than a minute.

as a note, we not have 72 shards on the same node we have 72 in total, 24 shards each node.

I think the problem was in Sort and in GroupAggregate  I no have idea how speed up this in master node, because the Custom Scan (Citus Adaptive)  is not too slow, the most time is consumed in master on Sort and group

I hope you can help me.