Re: parallel query

Mariel Cherkassky wrote:
> I wanted to a few questions regarding the parallel parameters : max_worker_processes and max_parallel_workers_per_gather.
>
> 1)Basically, max_worker_processes should be set to the number of cpus I have in the machine ?

Setting it higher would not be smart.
Setting it lower can also be a good idea; it depends
on your workload.

> 2)If I set max_worker_processes to X and max_parallel_workers_per_gather to Y (X>Y)
> it means that I will have at max (X/2) queries that can run in parallel. Am I right ?
> For example, max_worker_processes =8,max_parallel_workers_per_gather =4, it means
> that at max I can have 4 queries that are running in parallel ? and at min 2 queries
> (or none) can run in parallel ?

That is correct, but unless you set "max_parallel_workers_per_gather" to 1, one
query can use more than one parallel worker, and then you can have fewer
concurrent queries.

It also depends on the size of the table or index how many workers PostgreSQL will use.

> 3)So If I calculate my work_mem based on the number of sessions I have : (TOTAL_MEM/2/NUM_OF_CONNECTIONS)
> I should add 8 to the NUM_OF_CONNECTIONS to have a new value for the work_mem in order to consider queries that run in parallel..

Yes, but don't forget that one query can use "work_mem" several times if the
execution plan has several memory intensive nodes.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com