Parallel workers asked for, and granted

A slowdown with no cause in any view

Here is a failure that has cost a great many people an afternoon. A reporting query that has always taken forty seconds starts taking two minutes. The plan has not changed. The data has not grown meaningfully. The table statistics are current, the indexes are the same, nothing in the I/O counters looks unusual, and running the query in isolation at four in the morning reproduces the old forty seconds exactly.

What happened is that the query asked for four parallel workers and got one, because other queries had taken the rest. The plan is chosen when the query is planned and the workers are acquired when it runs, and if the pool is empty by then the query runs with whatever it can get rather than being replanned or refused. The result is a query that behaves like a slow query and has no slow component.

Until 18 the server told you nothing about this. There was a setting that capped the pool and there was no counter anywhere saying how often the cap was reached. You could infer it, if you were already suspicious, by reading an analyzed plan and comparing the workers planned against the workers launched, which requires you to be running the query at the moment the shortage occurs.

Version 18 counts it, per database, cumulatively.

SELECT datname, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_database WHERE datname = current_database();

ERROR:  column "parallel_workers_to_launch" does not exist
LINE 1: SELECT datname, parallel_workers_to_launch, parallel_workers...
                        ^

Starving a query on purpose

The fixture is four hundred thousand rows and a scan that matches nothing, with the planner pushed into choosing a parallel plan regardless of cost so that the measurement is about worker availability rather than about planner arithmetic.

CREATE TABLE trade_rows (trade_id bigint, symbol text);
INSERT INTO trade_rows SELECT g, md5(g::text) FROM generate_series(1, 400000) AS g;
ANALYZE trade_rows;

CREATE TABLE
INSERT 0 400000
ANALYZE

Then the pool is capped at one worker for the session, and two queries are run that each want four.

SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 1;
SELECT pg_stat_reset();
SELECT pg_sleep(1);
SELECT count(*) AS matched FROM trade_rows WHERE symbol LIKE '%zz%';
SELECT count(*) AS matched FROM trade_rows WHERE symbol LIKE '%yy%';
SELECT pg_sleep(1);
SELECT parallel_workers_to_launch AS wanted, parallel_workers_launched AS granted
FROM pg_stat_database WHERE datname = current_database();

SET
SET
SET
SET
SET
 pg_stat_reset 
---------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

 matched 
---------
       0
(1 row)

 matched 
---------
       0
(1 row)

 pg_sleep 
----------
 
(1 row)

 wanted | granted 
--------+---------
      8 |       2
(1 row)

Eight wanted across the two queries, two granted. Both queries ran, both returned the right answer, and both did roughly four times the work per worker that they were planned for. Nothing about either of them appears in the log, in the error counters, or in any other statistic.

Lift the cap and the same pair of queries is satisfied in full.

SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SELECT pg_stat_reset();
SELECT pg_sleep(1);
SELECT count(*) AS matched FROM trade_rows WHERE symbol LIKE '%zz%';
SELECT count(*) AS matched FROM trade_rows WHERE symbol LIKE '%yy%';
SELECT pg_sleep(1);
SELECT parallel_workers_to_launch AS wanted, parallel_workers_launched AS granted
FROM pg_stat_database WHERE datname = current_database();

SET
SET
SET
SET
SET
 pg_stat_reset 
---------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

 matched 
---------
       0
(1 row)

 matched 
---------
       0
(1 row)

 pg_sleep 
----------
 
(1 row)

 wanted | granted 
--------+---------
      8 |       8
(1 row)

Eight and eight. The two outputs differ by one setting and by nothing else, which is the whole argument for collecting this: the ratio between the two columns is a direct measure of whether the parallel pool is big enough for the work being asked of it, and it needs no baseline, no threshold and no knowledge of the queries.

Reading the ratio without fooling yourself

A satisfaction rate below one is not automatically a problem, and treating it as one will get the pool sized far too generously. Three things are worth knowing before acting on the number.

The counters are cumulative and per database, so what means something is the change over an interval rather than the level. A cluster that had one bad hour last month still carries that hour in its totals forever, or until the counters are reset, and a ratio computed from the raw totals will keep reporting a problem that ended weeks ago.

The pool is server-wide and the counter is per database. On a cluster with several busy databases, the shortage one database experiences was usually caused by another, and the only way to see that is to sum the wanted and granted columns across all rows rather than looking at one. That is also the argument for collecting every row rather than only the database your application uses.

And a shortage is not always worth fixing. Parallel workers are processes, each with its own memory allowance, and a pool sized to satisfy every peak is a pool sized for a peak that happens twice a week. The useful question is not whether the ratio is below one but whether it is below one during the window somebody cares about, which is a question the cumulative counters can answer only if you are sampling them regularly.

There is a fourth consideration that the columns cannot see at all. A query can be planned with no parallelism whatsoever because the planner decided against it, and that query never asks for a worker and never appears in either column. So a low wanted count is not evidence that the workload has no appetite for parallelism; it may be evidence that the planner is not choosing parallel plans, which is a different investigation entirely and starts in the plan rather than in the statistics.

The same pair arrived per statement

The statements extension gained the same two counters in this release, which is where the database-level numbers become actionable.

CREATE EXTENSION pg_stat_statements;
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = 'pg_stat_statements'::regclass
  AND a.attname LIKE 'parallel%'
ORDER BY a.attname;

CREATE EXTENSION
          attname           
----------------------------
 parallel_workers_launched
 parallel_workers_to_launch
(2 rows)

The database-level pair tells you that the cluster has a shortage. The statement-level pair tells you which query is being starved and which query is doing the starving, and those are frequently not the same query: a single large report that grabs every available worker for ten minutes will show a satisfaction rate near one for itself while everything that runs alongside it shows a rate near zero.

Collecting both is the right answer. The database-level counters are cheap, always present and safe to scrape on a short interval. The statement-level ones require the extension, cost what the extension costs, and are the ones you go to once the cluster-level ratio says there is something to find.

Two settings that both look like the answer

Parallelism is governed by a pair of settings whose names are similar enough that they get confused, and the counters above behave very differently depending on which one is the constraint.

One of them caps the workers a single gather node may use. It is a planning-time limit: the planner knows about it and chooses a plan that asks for at most that many. When this setting is the constraint, the wanted count is simply lower and the satisfaction rate stays near one. Nothing looks wrong, because nothing is failing; the queries are just planned smaller than the machine could handle.

The other caps the workers available across the whole server. It is a run-time limit: the planner does not consult it, so queries keep asking for as many as the first setting allows and the shortfall appears at execution. When this is the constraint, the satisfaction rate falls and the queries silently degrade, which is the situation the measurement above reproduces.

The distinction decides what the graph means. A satisfaction rate near one with a low wanted count is a planner that is not being ambitious, and raising the server-wide pool changes nothing at all. A satisfaction rate well below one is a pool that is genuinely exhausted, and raising the per-gather limit makes it worse by increasing demand against the same supply.

There is a third participant that neither column counts. The leader process usually does a share of the work itself alongside the workers, so a query planned for four workers that gets none is not four times slower; it is doing on one process what it would have done on five. That is why a starved parallel query degrades rather than fails, and why the effect on any individual query is milder than the counters make it look.

What to add to the monitoring

• Sum both columns across every row of the per-database statistics and graph the two together rather than the ratio alone. The ratio hides whether a change came from wanting less or getting more, and those are opposite situations.
• Alert on the satisfaction rate over a short window rather than on the totals, and set the window to match the period that matters to the people who own the queries.
• Record the pool size alongside the counters. The ratio is uninterpretable without it, and the setting is the thing you will be arguing about when the graph is presented.
• When the ratio is poor, check the planner’s appetite before raising the cap. A workload that wants far more workers than the machine has processors is usually one where the per-gather limit is set too high rather than one where the pool is too small.

What it costs

The per-database counters need nothing enabled, are maintained whether or not anyone reads them, and add two columns to a view that has one row per database. There is no realistic cluster on which collecting them is expensive.

They reset with everything else in that view, which is the one thing to watch: a routine that clears the database statistics to make a report cover one day also clears these, and the satisfaction rate immediately afterwards is computed from a handful of samples rather than from a meaningful population. Give it a few minutes before believing it.