How parallel_leader_participation Controls Parallel Query Execution in PostgreSQL

Postgres uses parallel query execution for large datasets, which improves performance. Here, multiple worker processes work together to complete a query execution. Anyway, normally in postgres, the leader process (the backend process that actually starts the query) also participates in the parallel execution.

Postgres has a configuration parameter, parallel_leader_participation, that can control whether the leader is participating in the execution.

What Is parallel_leader_participation?

parallel_leader_participation determines if the leader process can participate in the executing portion of a parallel query. In postgres, this parameter is ON by default; you can just check in your psql session with:

SHOW parallel_leader_participation;

This can be set to ON (by default) or OFF:

ON: The leader process participates in parallel execution along with the parallel workers.

OFF: The leader process does not participate in the parallel portion. Instead, it coordinates the workers and collects their results.

Demonstrating parallel_leader_participation

Now, let's see the role of the parallel_leader_participation parameter when we execute a query. First, let's create a table containing 10 million records:

CREATE TABLE parallel_demo AS
SELECT
    i AS id,
    i % 1000 AS category,
    md5(i::text) AS payload
FROM generate_series(1, 10000000) AS i;
ANALYZE parallel_demo;

Next, we will configure postgres to use two parallel workers for our demonstration.

SET max_parallel_workers_per_gather = 2;
SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;

1. When parallel_leader_participation is ON

The parameter is already ON by default, but let’s keep it ON with a SET command as shown below.

SET parallel_leader_participation = on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT category, COUNT(*), SUM(id)
FROM parallel_demo
GROUP BY category;

The output:

                                                                        QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=166480.52..166528.87 rows=1000 width=20) (actual time=10395.864..10409.864 rows=1000 loops=1)
   Group Key: category
   Buffers: shared hit=2862 read=90656
   ->  Gather Merge  (cost=166480.52..166503.87 rows=2000 width=20) (actual time=10395.804..10403.663 rows=3000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=2862 read=90656
         ->  Sort  (cost=166480.50..166483.00 rows=1000 width=20) (actual time=10371.000..10372.597 rows=1000 loops=3)
               Sort Key: category
               Sort Method: quicksort  Memory: 64kB
               Buffers: shared hit=2862 read=90656
               Worker 0:  Sort Method: quicksort  Memory: 64kB
               Worker 1:  Sort Method: quicksort  Memory: 64kB
               ->  Partial HashAggregate  (cost=166420.67..166430.67 rows=1000 width=20) (actual time=10367.822..10369.381 rows=1000 loops=3)
                     Group Key: category
                     Batches: 1  Memory Usage: 193kB
                     Buffers: shared hit=2848 read=90656
                     Worker 0:  Batches: 1  Memory Usage: 193kB
                     Worker 1:  Batches: 1  Memory Usage: 193kB
                     ->  Parallel Seq Scan on parallel_demo  (cost=0.00..135170.67 rows=4166667 width=8) (actual time=0.203..5134.979 rows=3333333 loops=3)
                           Buffers: shared hit=2848 read=90656
 Planning Time: 0.113 ms
 JIT:
   Functions: 21
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.051 ms (Deform 0.547 ms), Inlining 0.000 ms, Optimization 1.128 ms, Emission 20.279 ms, Total 23.459 ms
 Execution Time: 10412.334 ms
(27 rows)

You can find the lines:

Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on parallel_demo
   (actual time=0.203..5134.979 rows=3333333 loops=3)

Note that we can see the parallel seq scan node has 3 loops here (loops = 3).

This indicates that 3 processes participated in the execution (parallel scan). That is two parallel workers and the leader process since we have parallel_leader_participation being ON. The leader is thus contributing to the execution instead of just collecting the results. And our query completed execution in 10412.334 ms.

2. When parallel_leader_participation is OFF

Now, let’s disable the parameter and run the same query again to see what happens. Run the SET query below to turn off parallel_leader_participation:

SET parallel_leader_participation = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT category, COUNT(*), SUM(id)
FROM parallel_demo
GROUP BY category;

The output:

                                                               QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=181063.85..181112.20 rows=1000 width=20) (actual time=15215.907..15223.295 rows=1000 loops=1)
   Group Key: category
   Buffers: shared hit=2958 read=90560
   ->  Gather Merge  (cost=181063.85..181087.20 rows=2000 width=20) (actual time=15211.143..15214.122 rows=2000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=2958 read=90560
         ->  Sort  (cost=181063.83..181066.33 rows=1000 width=20) (actual time=15175.787..15177.253 rows=1000 loops=2)
               Sort Key: category
               Buffers: shared hit=2958 read=90560
               Worker 0:  Sort Method: quicksort  Memory: 64kB
               Worker 1:  Sort Method: quicksort  Memory: 64kB
               ->  Partial HashAggregate  (cost=181004.00..181014.00 rows=1000 width=20) (actual time=15172.683..15174.181 rows=1000 loops=2)
                     Group Key: category
                     Buffers: shared hit=2944 read=90560
                     Worker 0:  Batches: 1  Memory Usage: 193kB
                     Worker 1:  Batches: 1  Memory Usage: 193kB
                     ->  Parallel Seq Scan on parallel_demo  (cost=0.00..143504.00 rows=5000000 width=8) (actual time=0.080..7407.278 rows=5000000 loops=2)
                           Buffers: shared hit=2944 read=90560
 Planning Time: 0.092 ms
 JIT:
   Functions: 21
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.740 ms (Deform 0.484 ms), Inlining 0.000 ms, Optimization 1.070 ms, Emission 16.437 ms, Total 19.247 ms
 Execution Time: 15225.404 ms
(25 rows)

You can see the lines:

Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on parallel_demo
   (actual time=0.080..7407.278 rows=5000000 loops=2)

Did you notice something different from the above query here? See the loops reduced to 2 (loops = 2) in this execution when we turned off the parallel_leader_participation parameter. The 2 loops are the actual parallel workers alone doing the execution. The leader process is not participating in the parallel portion of the query. It coordinates the workers and collects their results. And the query execution time in this run appears to be 15225.404 ms (increased from 10.4 seconds to 15.2 seconds)

Here is a comparison of both executions:

ParameterONOFF
Workers Planned22
Workers Launched22
Parallel Scan Loops32
Execution Time10412 ms15225 ms

From this test, we saw that enabling parallel_leader_participation resulted in a lower execution time. That is because the leader process also worked along with the other two workers in parallel scan, thus improving performance. It does not mean that enabling this parameter will always produce better results. The result depends on the query, the amount of data being processed, join conditions, and the work required to collect the result from workers.

parallel_leader_participation is one of the configuration parameters available in postgres that allows us to control the leader process participation during parallel query execution. Even if it is enabled by default, understanding its purpose helps us better understand the parallel query execution mechanism. The intention of this demonstration is simply to introduce this parameter and show how its behaviour can be observed through EXPLAIN ANALYZE.

WhatsApp