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.