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!.

Answer 1

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.)