EXPLAIN ANALYZE in Parallel

Reading EXPLAIN ANALYZE for a Parallel Plan

Parallelism pays on bigger tables, so 456-x10.sql stacks ten copies of mart.sales (order IDs shifted) into mart.sales_x10: 1,379,440 rows in 186 MB, enough for the size rule to plan three workers. VERBOSE shows what each worker did:

457-explain.sql: a parallel aggregate, worker by workerSQL

SET max_parallel_workers_per_gather = 3;
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, BUFFERS OFF)
SELECT genre, sum(gross_amount) AS gross, count(*) AS lines
FROM mart.sales_x10 WHERE status <> 'cancelled' GROUP BY genre;

Output

 Finalize GroupAggregate (actual time=1036.626..1050.288 rows=6.00 loops=1)
   ->  Gather Merge (actual time=1036.614..1050.268 rows=24.00 loops=1)
         Workers Planned: 3
         Workers Launched: 3
...
                     ->  Parallel Seq Scan on mart.sales_x10 (... rows=324712.50 loops=4)
...
                           Worker 0:  actual time=0.023..489.158 rows=336674.00 loops=1
                           Worker 1:  actual time=0.017..384.191 rows=298629.00 loops=1
                           Worker 2:  actual time=0.017..537.902 rows=362167.00 loops=1
 Execution Time: 1050.427 ms

The Parallel Seq Scan ran in four processes (loops=4: three workers and the leader) that claimed blocks from a shared counter, so their shares differ, and node figures are averages per loop: 4 × 324,712.5 rows minus the workers' 997,470 leaves 301,380 for the leader. Each process built a Partial HashAggregate of six sums and counts; Gather Merge collected the 24 partial rows in order and Finalize GroupAggregate combined them. count(DISTINCT ...) cannot be split this way, which is why the view in Materialized vs Regular Views sorted every line. When a parallel query is slow, check Workers Launched against Workers Planned.

parallel_timing.sh times it with zero to four workers, best of seven. On the shared host (load average 4.5-7.7 from other lanes), serial runs took 1.39-1.76 s and three workers 0.62-0.63 s (2.2 to 2.9 times faster); four were slower than three. On mart.sales the gain was 1.4 to 1.6 times.

MENU