Postgres 9.6: Parallel query does not take max_parallel_workers_per_gather setting

Question

Postgres 9.6; Centos 6.7 ; 24 cores

BigTable1 contains 1,500,000,000 rows; weight 180GB.

max_worker_processes = 20
max_parallel_workers_per_gather = 12

1) When running

EXPLAIN
SELECT
    date_id, id1, id2, id3, id4, topdomain, ftype, SUM(imps), SUM(cls)
FROM BigTable1
WHERE
    date_id BETWEEN 2017021200 AND 2017022400             
    AND date_id BETWEEN 2017020000 AND 2017029999   
GROUP BY
date_id, id1, id2, id3, id4, topdomain, ftype;

No “Workers Planned:” used at all. Why?

2) When running the same query when in the session defined

set max_parallel_workers_per_gather = 5;

“Workers Planned: 5” appear. The execution time was improved only by 25%.

2.1) Why “Workers Planned:” appears only after this setting? 2.2) Why we could not see a much better improvement when running with max_parallel_workers_per_gather = 5 ?

Thank you!.

What kind of hardware do you have? How many drives? What type of drives? And how fast are your drives? And what about RAM? Etc. — Frank Heikens, Mar 02 '17 at 20:14

score 16 · Answer 1 · answered Mar 27 '17 at 21:10

When PostgreSQL considers a parallel sequential scan, it decides how many workers should be used based on the relation size (or the parallel_workers storage parameter for the driving table) and computes the cost of a parallel plan with that number of workers. This is compared to the cost of a serial plan, and the cheaper plan wins. Plans with other numbers of workers are not considered, so it can happen that the cost of the serial plan is less than the cost of the plan considered but more than the cost of some plan with a different number of workers. This probably happened in your case.

Since you didn't post the EXPLAIN ANALYZE output, we can't see how many groups your query is producing, but my guess is that it's a fairly large number. In PostgreSQL 9.6, a parallel aggregate must be performed by aggregating a portion of the data in each worker (a PartialAggregate) and then merging groups with the same keys in the leader (a FinalizeAggregate). Between these two steps, a Gather node is required to transmit the partially grouped data from the workers to the leader. This Gather node is somewhat expensive, so the most likely reason why you saw only limited speedup is that the number of groups being transferred was large. The cost of sending all of those groups, and of merging groups that occurred in more than one worker, may have looked too high to justify parallelism with a higher number of workers, but may have looked like a win with a lesser number of workers. These same costs probably account for the fact that even when parallel query was used, you saw only a 25% speedup.

If you post the EXPLAIN ANALYZE output with and without parallel query (that is, with "Workers Planned: 5" and with no parallelism), it might be possible to understand more clearly what is happening in your case.

(Source: I am one of the principal authors of PostgreSQL's parallel query support.)

score 4 · Answer 2 · edited May 23 '17 at 12:17

If you are just looking to test the parallel query bits then you can have a look at force_parallel_mode and setting it to on.

force_parallel_mode (enum)

Allows the use of parallel queries for testing purposes even in cases where no performance benefit is expected. The allowed values of force_parallel_mode are off (use parallel mode only when it is expected to improve performance), on (force parallel query for all queries for which it is thought to be safe), and regress (like on, but with additional behavior changes as explained below).

And, just as robert-haas mentioned below, Without force_parallel_modethe optimizer will potentially decide parallel query isn't the quickest... see the params below:

select
  name,
  setting,
  unit,
  short_desc
from pg_settings
where name in (
  'force_parallel_mode',
  'min_parallel_relation_size',
  'parallel_setup_cost',
  'parallel_tuble_cost',
  'max_parallel_workers_per_gather' )
  limit 10 ;

postgres=> 
---------------------------------+---------+------+---------------------------------------------------------------------------------------------
 force_parallel_mode             | off     |      | Forces use of parallel query facilities.
 max_parallel_workers_per_gather | 0       |      | Sets the maximum number of parallel processes per executor node.
 min_parallel_relation_size      | 1024    | 8kB  | Sets the minimum size of relations to be considered for parallel scan.
 parallel_setup_cost             | 1000    |      | Sets the planner's estimate of the cost of starting up worker processes for parallel query.
(4 rows)

Postgres 9.6: Parallel query does not take max_parallel_workers_per_gather setting

2 Answers2