On Thu, Dec 28, 2023 at 12:03 PM Darwin Correa <dcorrea@jedai.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
