Re: optimizing number of workers

From: Greg Hennessy <greg(dot)hennessy(at)gmail(dot)com>
Date: Thursday, July 10, 2025 at 4:40 PM
To: pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: optimizing number of workers

Having just received a shiny new dual CPU machine to use as a postgresql
server, I'm trying to do some reasonable efforts to configure it correctly. The hard
ware has 128 cores, and I am running a VM with Redhat 9 and Postgresql 16.9.

In postgresql.conf I have:
max_worker_processes = 90 # (change requires restart)
max_parallel_workers_per_gather = 72 # gsh 26 oct 2022
max_parallel_maintenance_workers = 72 # gsh 12 jun 2025
max_parallel_workers = 72 # gsh 12 jun 2025
max_logical_replication_workers = 72 # gsh 12 jun 2025
max_sync_workers_per_subscription = 72 # gsh 12 jun 2025
autovacuum_max_workers = 12 # max number of autovacuum subprocesses

When I do a simple count of a large (large being 1.8 Billion entries), I get
about 10 workers used.

prod_v1_0_0_rc1=# explain (analyze, buffers) select count(*) from gaiadr3.gaia_source;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=14379796.81..14379796.82 rows=1 width=8) (actual time=16702.806..16705.479 rows=1 loops=1)
Buffers: shared hit=2507481
-> Gather (cost=14379795.78..14379796.79 rows=10 width=8) (actual time=16702.513..16705.470 rows=11 loops=1)
Workers Planned: 10
Workers Launched: 10
Buffers: shared hit=2507481
-> Partial Aggregate (cost=14379785.78..14379785.79 rows=1 width=8) (actual time=16691.820..16691.821 rows=1 loops=11)
Buffers: shared hit=2507481
-> Parallel Index Only Scan using gaia_source_nest128 on gaia_source (cost=0.58..13926632.85 rows=181261171 width=0) (actual time=0.025..9559.644 rows=164700888 loops=11)
Heap Fetches: 0
Buffers: shared hit=2507481
Planning:
Buffers: shared hit=163
Planning Time: 14.898 ms
Execution Time: 16705.592 ms

Postgres has chosen to use only a small fraction of the CPU's I have on
my machine. Given the query returns an answer in about 8 seconds, it may be
that Postgresql has allocated the proper number of works. But if I wanted
to try to tweak some config parameters to see if using more workers
would give me an answer faster, I don't seem to see any obvious knobs
to turn. Are there parameters that I can adjust to see if I can increase
throughput? Would adjusting parallel_setup_cost or parallel_tuple_cost
likely to be of help?
I believe you can decrease min_parallel_table_scan_size (default is 8MB) and min_parallel_index_scan_size (default 5112kB). The number of workers depends also on a multiple of these settings.