Re: Parallel hints in PostgreSQL with consistent perfromance
> *1st time query executed with PARALLEL DEGREE 2 * > explain analyze select /*+* PARALLEL(A 2)* */ * from > test_compare_all_col_src1 A; > QUERY > PLAN > > > Gather (cost=10.00..45524.73 rows=949636 width=97) (actual > time=0.673..173.017 rows=955000 loops=1) >Workers Planned: 4 > * Workers Launched: 4* >-> Parallel Seq Scan on test_compare_all_col_src1 a > (cost=0.00..44565.09 rows=237409 width=97) (actual time=0.039..51.941 > rows=191000 loops=5) > Planning Time: 0.093 ms > * Execution Time: 209.745 ms* > (6 rows) > Your alias is not enclosed in double quotes, so it is downcased to "a" (as can be seen from the alias printed in the plan). But pg_hint_plan hints don't follow the downcasing convention, so the hint on "A" does not match the alias "a", and so is ignored. Cheers, Jeff >
Re: Slow GroupAggregate and Sort
On Mon, Jan 1, 2024 at 9:57 AM Darwin Correa wrote: > Hello, Happy New Year! I add my responses in blue. > > > > El Thu, 28 Dec 2023 13:06:18 -0500, *Jeff Janes > >* escribió > > 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. > 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) Cheers, Jeff
Re: Parallel hints in PostgreSQL with consistent perfromance
On Thu, Jan 4, 2024 at 7:13 AM Jeff Janes wrote: > > >> *1st time query executed with PARALLEL DEGREE 2 * >> explain analyze select /*+* PARALLEL(A 2)* */ * from >> test_compare_all_col_src1 A; >> >> QUERY PLAN >> >> >> Gather (cost=10.00..45524.73 rows=949636 width=97) (actual >> time=0.673..173.017 rows=955000 loops=1) >>Workers Planned: 4 >> * Workers Launched: 4* >>-> Parallel Seq Scan on test_compare_all_col_src1 a >> (cost=0.00..44565.09 rows=237409 width=97) (actual time=0.039..51.941 >> rows=191000 loops=5) >> Planning Time: 0.093 ms >> * Execution Time: 209.745 ms* >> (6 rows) >> > > Your alias is not enclosed in double quotes, so it is downcased to "a" (as > can be seen from the alias printed in the plan). But pg_hint_plan hints > don't follow the downcasing convention, so the hint on "A" does not match > the alias "a", and so is ignored. > * >> Thanks Jeff for the response ! It worked with "A" alias * > Cheers, > > Jeff > >>
