Gather in PostgreSQL EXPLAIN

What it does

Everything below a Gather node is the parallel part of the plan. When the query starts, Gather asks for background worker processes, and each worker runs a copy of the plan below it. The leader (the process serving your session) runs a copy too, unless parallel_leader_participation is off. Nodes such as Parallel Seq Scan make sure the copies share the work instead of repeating it.

Gather reads rows from all the processes and hands them to the node above in whatever order they arrive, so any sort order from below is lost. When order matters, the planner uses Gather Merge instead.

There’s only one Gather (or Gather Merge) at the top of each parallel part; parallel plans aren’t nested.

When the planner picks it

Whenever it chooses a parallel plan and doesn’t need the rows in order. Typical shapes:

• Gather → Parallel Seq Scan for a filter on a large table.
• Finalize Aggregate → Gather → Partial Aggregate → Parallel Seq Scan for count(*) or sum() on a large table.
• Gather → Hash Join with a parallel scan on the outer side.

Parallel plans need a table above min_parallel_table_scan_size (8 MB by default), max_parallel_workers_per_gather above zero (default 2), and nothing parallel-unsafe in the query.

Reading its numbers

Gather  (cost=1000.00..17592.03 rows=29667 width=34) (actual time=0.110..22.535 rows=30000.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=7267 read=1150

• Workers Planned: how many workers the planner asked for, at most max_parallel_workers_per_gather.
• Workers Launched: how many actually started. Fewer than planned, or 0, means the server ran out of workers: max_parallel_workers (default 8) and max_worker_processes (default 8) are limits for the whole server, shared by every session. The query still runs, with whoever started.
• rows: the total that came through Gather from all processes. Below it, rows are averages per process (see loops and actual time), so the child’s rows=10000.00 loops=3 and Gather’s rows=30000.00 describe the same rows.
• cost=1000.00..: the startup cost includes parallel_setup_cost (default 1000), the planner’s price for starting workers. Each row passed up adds parallel_tuple_cost (default 0.1).
• Buffers: totals for all processes.

With VERBOSE, nodes below Gather also print a Worker N: line each, with that worker’s rows and time.

When it’s a problem

• Workers Launched is lower than Workers Planned. Other sessions were using the workers. On a server that runs many parallel queries at once, either raise max_parallel_workers and max_worker_processes (the latter needs a restart, and each worker is a full process using CPU and memory) or lower max_parallel_workers_per_gather so queries ask for fewer.
• Too many rows go through it. The leader receives every row from every worker, one by one. A parallel SELECT * that returns hundreds of thousands of rows spends its time in Gather, and can be slower than a serial scan. Parallelism pays off when the work below Gather shrinks the rows: a filter, an aggregate, a join that discards most rows.
• Parallelism hides a missing index. A Gather over a Parallel Seq Scan that keeps a few rows out of millions is three processes doing a slow job together. An index is usually the better fix.

To see what the query costs without parallelism, run SET max_parallel_workers_per_gather = 0; in your session and compare.

Example

PostgreSQL 18.6, default settings. The orders table has 1,000,000 rows; 3% have status refunded:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
VACUUM ANALYZE orders;

(Our test table also had a foreign key to customers and two indexes; neither is used here.)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'refunded';

Gather  (cost=1000.00..17592.03 rows=29667 width=34) (actual time=0.110..22.535 rows=30000.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=7267 read=1150
  ->  Parallel Seq Scan on orders  (cost=0.00..13625.33 rows=12361 width=34) (actual time=0.008..18.537 rows=10000.00 loops=3)
        Filter: (status = 'refunded'::text)
        Rows Removed by Filter: 323333
        Buffers: shared hit=7267 read=1150
Planning:
  Buffers: shared hit=12
Planning Time: 0.136 ms
Execution Time: 23.443 ms

To see what happens when no workers are free, limit the session to none (max_parallel_workers can be set per session) and count the same rows:

SET max_parallel_workers = 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE status = 'refunded';

Finalize Aggregate  (cost=14656.45..14656.46 rows=1 width=8) (actual time=50.157..50.210 rows=1.00 loops=1)
  Buffers: shared hit=7224 read=1193
  ->  Gather  (cost=14656.24..14656.45 rows=2 width=8) (actual time=50.154..50.206 rows=1.00 loops=1)
        Workers Planned: 2
        Workers Launched: 0
        Buffers: shared hit=7224 read=1193
        ->  Partial Aggregate  (cost=13656.24..13656.25 rows=1 width=8) (actual time=50.052..50.052 rows=1.00 loops=1)
              Buffers: shared hit=7224 read=1193
              ->  Parallel Seq Scan on orders  (cost=0.00..13625.33 rows=12361 width=0) (actual time=0.009..48.756 rows=30000.00 loops=1)
                    Filter: (status = 'refunded'::text)
                    Rows Removed by Filter: 970000
                    Buffers: shared hit=7224 read=1193
Planning Time: 0.055 ms
Execution Time: 50.230 ms

The plan didn’t change (it was made before execution), but loops=1 shows the leader did all the work. With two workers the same count took 43 ms; alone, 50 ms. On a busy server that difference is what you lose when workers run out.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. Its Activity monitor lists the sessions on the server and what each is running.