Re: Slow GroupAggregate and Sort

On Thu, Dec 28, 2023 at 12:03 PM Darwin Correa <dcorrea(at)jedai(dot)group> wrote:

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

Where is plan4_v3.txt? Is that hidden in some non-obvious way in one of
your links?

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

I only see that one arbitrary fragment takes 2.7s, with no indication
whether that one is the slowest one or not. But I am not used to reading
citus plans.

> 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 thought the point of sharding was to bring more CPU and RAM to bear than
can feasibly be obtained in one machine. Doesn't that make 24 shards per
machine completely nuts?

>
> 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
>

You want to know why citus is so slow here, but also say it isn't slow and
something else is slow instead?

I'd break this down into more manageable chunks for investigation.
Populate one scratch table (on one node, not a hypertable) with all 2.6
million rows. See how long it takes to populate it based on the citus
query, and separately see how long it takes to run the aggregate query on
the populated scratch table.

What version of PostgreSQL (and citus) are you using? In my hands (without
citus being involved), the sort includes "users" as the last column, to
support the count(distinct users) operation. I don't know why yours
doesn't do that.

Cheers,

Jeff