This is my first attempt for a patch to postgresql, please forgive me if
I have forgotten some step.
I recently got a new system, with many more CPU cores than previous
systems than I am used to, 128 cores
(which may not seem a large number to some). I was a bit unhappy that
even though I configured max_worker_processes
and max_parallel_workers to values above 90, my queries were only
allocating 10 workers, and with fiddling
with min_parallel_table_scan_size and min_parallel_index_scan_size I
could get it to 14 or so, the algorithm in allpaths.c
seems to base the number of workers on the log3 of the ratio of the
buffer size and the table/index scan size. I sugguest
replacing this with the square root of the ratio, which grows faster
than the log3. My thought is that there are ways
to configure postgresql so it won't allocate an excessive number of
workers, but there isn't an easy way to get more
workers.
I enclose the patch for consideration. My understanding is that a lot of
work is going into getting V19 ready,
if people would prefer to wait for that before considering my patch I
understand.
V/r
Greg
From 395d943f9fe86289d0831f11ef652f7342bfd792 Mon Sep 17 00:00:00 2001
From: Gregory Hennessy <greg.henne...@gmail.com>
Date: Wed, 16 Jul 2025 20:22:58 -0400
Subject: [PATCH] Change algorithm to use sqrt of buffer size rather than log3
---
src/backend/optimizer/path/allpaths.c | 18 ++++++++++--------
1 file changed, 10 insertions(+), 8 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 6cc6966b060..19e7b30453f 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -4273,18 +4273,21 @@ compute_parallel_worker(RelOptInfo *rel, double heap_pages, double index_pages,
int heap_parallel_workers = 1;
/*
- * Select the number of workers based on the log of the size of
- * the relation. This probably needs to be a good deal more
+ * Select the number of workers based on the sqrt of the size of
+ * the ratio of the heap_pages and the min table scan size.
+ * This allocates more workers than the previous method based on log3
+ * of the ratio. It is easier to make the number of workers smaller with a GUC
+ * than to make the number larger.
+ * This probably needs to be a good deal more
* sophisticated, but we need something here for now. Note that
* the upper limit of the min_parallel_table_scan_size GUC is
* chosen to prevent overflow here.
*/
heap_parallel_threshold = Max(min_parallel_table_scan_size, 1);
- while (heap_pages >= (BlockNumber) (heap_parallel_threshold * 3))
+ while (heap_pages >= (BlockNumber) (heap_parallel_threshold*heap_parallel_workers*heap_parallel_workers))
{
heap_parallel_workers++;
- heap_parallel_threshold *= 3;
- if (heap_parallel_threshold > INT_MAX / 3)
+ if (heap_parallel_threshold *heap_parallel_workers*heap_parallel_workers> INT_MAX )
break; /* avoid overflow */
}
@@ -4298,11 +4301,10 @@ compute_parallel_worker(RelOptInfo *rel, double heap_pages, double index_pages,
/* same calculation as for heap_pages above */
index_parallel_threshold = Max(min_parallel_index_scan_size, 1);
- while (index_pages >= (BlockNumber) (index_parallel_threshold * 3))
+ while (index_pages >= (BlockNumber) (index_parallel_threshold * index_parallel_workers*index_parallel_workers))
{
index_parallel_workers++;
- index_parallel_threshold *= 3;
- if (index_parallel_threshold > INT_MAX / 3)
+ if (index_parallel_threshold*index_parallel_workers*index_parallel_workers > INT_MAX)
break; /* avoid overflow */
}
--
2.50.1
