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

On Sun, 2021-02-14 at 22:47 +1300, David Rowley wrote:
> 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.

That would be great; I have been hit by this before.

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

Hmm - that didn't work when I tried it, but perhaps I should try again.

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

I think this is an area where parallel query could be improved.

One think is runtime partition pruning: if the optimizer thinks that
it will have to scan a lot of partitions, it will plan a lot of workers.
But if the executor reduces that number to 1, we end up with way too
many workers.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com