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

Hi David,

That is a great suggestion, except my partitions are foreign tables.

I wonder if I could figure out a single empty partition that’s just a normal table... but I’m using hash partitioning, so I think I would lose a modulus.

Best,
Seamus

--
Seamus Abshere, SCEA
+1 (608) 772-0696
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere

On Sun, Feb 14, 2021, at 4:47 AM, 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.
>
> 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
>