On Sun, 2021-02-14 at 22:47 +1300, David Rowley wrote: > On Sun, 14 Feb 2021 at 13:15, Seamus Abshere > > <sabsh...@alumni.princeton.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
