How to Ensure a Query Runs in Parallel in PostgreSQL?

Question

I have a PostgreSQL 16 database setup with the following configuration:

-- DB Version: 16
-- OS Type: linux
-- DB Type: oltp
-- Total Memory (RAM): 64 GB
-- CPUs num: 32
-- Connections num: 200
-- Data Storage: ssd
show effective_cache_size; --48 GB
show effective_io_concurrency; --200
show huge_pages; --try
show maintenance_work_mem; --4GB
show max_connections; --200
show max_parallel_maintenance_workers; --4
show max_parallel_workers; --32
show max_parallel_workers_per_gather; --4
show max_wal_size; --8GB
show max_worker_processes; --32
show min_wal_size; --2GB
show parallel_setup_cost; --0.1
show random_page_cost; --1.1
show shared_buffers; --16GB
show wal_buffers; --16MB
show work_mem; --256MB

I am running a CREATE TABLE query with multiple joins. Sometimes the query runs in parallel, but other times it does not. I want to ensure that the query runs in parallel or at least increase the chances of it running in parallel for that specific transaction session. It is also fine if parallel workers assigned are fewer than usual because without parallel workers the query is very slow.

The configuration below worked best for me for some time, but it caused an error similar to this issue.

ALTER SYSTEM SET maintenance_work_mem = '8GB';
ALTER SYSTEM SET work_mem = '2GB';
ALTER SYSTEM SET effective_io_concurrency = '400';
ALTER SYSTEM SET max_parallel_workers_per_gather = '16';
ALTER SYSTEM SET wal_buffers = '1GB';

I am attaching a select query plan for reference: query plan.

I want to maximize CPU utilization (currently less than 10%) and RAM utilization (currently less than 30%).

I am aware that force_parallel_mode has been deprecated. How can I achieve consistent parallel execution for my queries in PostgreSQL?

• Purushottam Nawale: Yes, I did VACUUM ANALYZE before processing.
• user1937198: what does the query plan look like if you disable parallel query? How close are they in costing, and why postgres think its viable if its always slow?
• Purushottam Nawale: The error I received was exactly this could not resize shared memory segment "/PostgreSQL.433001968" to 8589934592 bytes: No space left on device and my parallel_tuple_cost is 0.1. So am I supposed to make it 0 for that session for query planner favor parallelism?
• jjanes: Based on the ERROR message byte value, I would say the problem is that maintenance_work_mem is set too high, but the CONTEXT field of the error message should validate that (in an autovac?). You didn't describe any reason for it to be set that high, so I'd probably undo that change.

Answer 1

The two answers you already have are mostly correct. You can use debug_parallel_query but it is a rather extreme way to do it and can result in use of parallel plans even when they are actually slower. In my hands, doing this will often result in a silly plan which is "Single Copy" and launches only one worker and has that worker do all the work (no leader participation).

Setting parallel_workers on a table will (as far as I know) not change whether a parallel plan is chosen, but will just change how many workers are launched if a parallel plan is chosen, so that probably won't help you. Changing min_parallel_table_scan_size and min_parallel_index_scan_size will help you only if those are the specific reasons you are not getting parallel plans, which based on your description I suspect is not the case (it would help to see an EXPLAIN plan for the case where it chooses not to use parallel query).

The most powerful knob you have here is parallel_tuple_cost. Setting it to be zero would be a bit extreme (but not so as extreme as using debug_parallel_query would be). I often set it to zero when testing, if I want to encourage a parallel plan to see what will happen. But I probably wouldn't do that in production. I think the minimum sensible value for this is about 0.01 (one tenth the default) and that is what I would start with in your situation, where you already know that parallel query is being underused.

The correct setting for this is going to depend on your server usage. If only one database connection is likely to be active at a time, then any CPUs not used for that connection will just go to waste, so this situation justifies the lowest settings for cpu_tuple_cost. On the other hand, if the server is very busy such that many sessions are active at once and fight for CPU, using parallel query will just make them fight harder which would suggest a higher setting for cpu_tuple_cost (or in the extreme, just changing max_parallel_workers_per_gather=0 to disable parallel query altogether).

Answer 2

You should not force PostgreSQL to use parallel query. Instead, tell it that it can use many parallel worker processes for your query if it thinks that a parallel plan will win:

1. You can override PostgreSQL's heuristics for determining the number of parallel workers for scans of a certain table:

   ALTER TABLE tab SET (parallel_workers = 16);
   

2. You can lower min_parallel_table_scan_size and min_parallel_index_scan_size to make PostgreSQL's heuristics choose more parallel worker processes.

Answer 3

Actually, force_parallel_mode was just renamed to debug_parallel_query in Postgres 16. The release notes:

Rename server variable force_parallel_mode to debug_parallel_query (David Rowley)

You can just use that setting to force parallel mode. In your session only:

SET debug_parallel_query = on;

Like the new name suggests, it's really meant for debugging, because you might force Postgres into a bad query plan.

Also, with this setting, Postgres will plan a Gather node where possible, but only actually use parallel workers if any are available at the time of execution.