max_parallel_workers vs. max_parallel_workers_per_gather

user_8523137 0
Viewed 1498

I have tested the parallelism with simple query and I don't understand the results.

I checked the following parameters from pg_settings:

max_parallel_workers = 8
max_parallel_workers_per_gather = 2

I run the following query (the table contains ~16M rows):

explain analyze
select *
from tbl
where value<>-1

results:

Gather (cost=1000.00 .. 1136714.86 rows=580941 width=78 actual time=0.495..3057.813 rows = 587886 loops=1)
workers planned: 2
workers launched: 2
 -> parallel seq scan on tbl (cost=0.00..10776.76 rows=242059 width=718) (actual time=0.095..2968.77 rows=195962 loops=3)  
    filter: (value<>-1::integer)
    rows removed by filter: 5389091
plain time: 0.175ms
exection time: 3086.243ms
  1. why the query run with only 2 workers and not 8 ?
  2. what is the different between max_parallel_workers and max_parallel_workers_per_gather ? when each of the values will be used ?
parallel-processing postgresql
1 Answers

max_parallel_workers limits the number of parallel worker processes that can be active at the same time in the whole database cluster. It must be less or equal to max_worker_processes.

max_parallel_workers_per_gather limits the number of parallel worker processes that can be used for a single query.

The idea is to limit the resources for parallel query while making sure that no single query can hog all the resources.

user_64643080
Related