Hello, my answers in blue again
---- El Wed, 03 Jan 2024 21:43:15 -0500, Jeff Janes <jeff.janes@xxxxxxxxx> escribió ----
On Mon, Jan 1, 2024 at 9:57 AM Darwin Correa <dcorrea@jedai.group> wrote:Hello, Happy New Year! I add my responses in blue.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?Based o citus docs the recommended shards is 2x cpu cores in my case I've tested with few shards and 1:1, 2:1 shards but always have slow query time in the last step (sorting and grouping) in máster node.That might make sense if PostgreSQL didn't do parallelization itself. But according to your plan, PostgreSQL itself tries to parallelize 4 ways (although fails, as it can't find any available workers) and then you have 24 nodes all doing the same thing, all with only 12 CPU. That doesn't seem good. although it now does seem unrelated to the issue at hand.
But the coordinator (who make sort and aggr) are in separate server (each node si phisically other server) I no understand why if cooridnator not aree too busy, and I've already test with less shards, and time increment.
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.After scratch table filled sort took 32s, explain (https://explain.dalibo.com/plan/8a3h26hcc6328c11)So that plan shows the sort to be egregiously slow, and with no involvement of citus and no apparent reason for slowness. I'm thinking you have a pathological collation being used. What is your default collation? (Your DDL shows that no non-default collations are in use, but doesn't indicate what the default is)
The collation is en_US.UFT-8, can you give more detail or which refer to "pathological collation" please to research about that? and the data store in this tables and this column specific are only alphanumeric charactres a-z,A-Z and numbers, nothing special
Cheers,Jeff
Correa P. // software architect | Veintimilla y Leonidas Plaza 0999965925 | |||||||
// DESARROLLO E INNOVACIÓN TECNOLÓGICA | ||||||||
| | ||||||||
