Parallel Query

Parallel Query

The Parallel Query Planner and Worker Processes

For a large scan the planner can add a Gather (or order-preserving Gather Merge) node: the session's backend, now the leader, starts background workers that run the plan below it on shares of the pages, and merges their rows while doing a share itself. The number of workers planned depends on table size: none below min_parallel_table_scan_size (8 MB), one at 8 MB and one more each time the table triples, capped by max_parallel_workers_per_gather (2). Pricing workers as free isolates that rule:

456-workers.sql: workers planned by table size and by the capsSQL

CREATE FUNCTION pg_temp.workers(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE plan jsonb;
BEGIN
  EXECUTE 'EXPLAIN (FORMAT JSON) ' || q INTO plan;
  RETURN coalesce(jsonb_path_query_first(plan, '$.**."Workers Planned"')::text, '0');
END $$;
SET parallel_setup_cost = 0;       -- price workers as free, so the size rule alone decides
SET parallel_tuple_cost = 0;
SELECT t AS "table", pg_size_pretty(pg_relation_size(t)) AS size,
       pg_temp.workers('SELECT count(*) FROM ' || t) AS workers
FROM unnest(array['order_items', 'orders', 'mart.sales']) AS t;

Output

    table    |  size   | workers
-------------+---------+---------
 order_items | 7040 kB | 0
 orders      | 8384 kB | 1
 mart.sales  | 19 MB   | 1

ALTER TABLE mart.sales SET (parallel_workers = 3) overrides the size rule but not the cap: the function then returned 2, and 3 only after SET max_parallel_workers_per_gather = 3. Exhausted server-wide pools (max_parallel_workers, max_worker_processes, 8 each) silently leave a query with fewer workers, or none. Normally costs decide: starting workers costs parallel_setup_cost (1,000) and passing each row to the leader parallel_tuple_cost (0.1), so a plain GROUP BY genre over the 19 MB mart.sales stays serial.

MENU