Re: Why is Postgres only using 8 cores for partitioned count? [Parallel Append]

On Sun, 14 Feb 2021 at 13:15, Seamus Abshere
<sabshere(at)alumni(dot)princeton(dot)edu> wrote:
> The comment from Robert says: (src/backend/optimizer/path/allpaths.c)
>
> /*
> * If the use of parallel append is permitted, always request at least
> * log2(# of children) workers.
>
> In my case, every partition takes 1 second to scan, I have 64 cores, I have 64 partitions, and the wall time is 8 seconds with 8 workers.
>
> I assume that if it it planned significantly more workers (16? 32? even 64?), it would get significantly faster (even accounting for transaction cost). So why doesn't it ask for more? Note that I've set max_parallel_workers=512, etc. (postgresql.conf in my first message).

There's perhaps an argument for allowing ALTER TABLE <partitioned
table> SET (parallel_workers=N); to be set on partitioned tables, but
we don't currently allow it.

What you might want to try is setting that for any of those 64
partitions. Shortly above the code comment that you quoted above,
there's some code that finds the path for the partition with the
maximum number of parallel workers. If one of those partitions is
using, say 64 workers because you set the partitions
"parallel_workers" setting to 64, and providing you have
max_parallel_workers_per_gather set highly enough, then your Append
should get 64 workers.

You'll need to be careful though since changing the partitions
parallel_workers may affect things for other queries too. Also, if you
were to only change 1 partition and that partition were to be pruned,
then you'd not get the 64 workers.

David