Running parallel functions with union all

Question

I'm trying to run 3 functions at the same time using parallelism and union all. I've read that union queries are candidates for parallelism but i can't get it to work.

The query looks like this (the function is the same, but it's on a different schema, but the result is the same even on the same schema)

-- The real function is a medium size sql query with CTEs, but i get the same result with this
CREATE OR REPLACE FUNCTION fn(jsonb)
 RETURNS table(
    i int
 )
 LANGUAGE sql
 PARALLEL SAFE
 SET search_path from current
AS $function$
    select 1
$function$;


select * from catalog.fn('{}')
union all
select * from customer01.fn('{}')
union all
select * from customer02.fn('{}')

And i'm using this setting to force parallelism (i've tried different numbers)

I get this plan

Gather  (cost=0.25..28.00 rows=3000 width=4) (actual time=7.320..10.669 rows=3 loops=1)
  Output: fn.i
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=388
  ->  Parallel Append  (cost=0.25..28.00 rows=1500 width=4) (actual time=2.283..2.313 rows=2 loops=2)
        Buffers: shared hit=388
        Worker 0:  actual time=2.420..2.422 rows=1 loops=1
          Buffers: shared hit=193
        Worker 1:  actual time=2.146..2.205 rows=2 loops=1
          Buffers: shared hit=195
        ->  Function Scan on catalog.fn  (cost=0.25..10.25 rows=1000 width=4) (actual time=2.145..2.145 rows=1 loops=1)
              Output: fn.i
              Function Call: catalog.fn('{}'::jsonb)
              Buffers: shared hit=193
              Worker 1:  actual time=2.145..2.145 rows=1 loops=1
                Buffers: shared hit=193
        ->  Function Scan on customer01.fn fn_1  (cost=0.25..10.25 rows=1000 width=4) (actual time=2.419..2.419 rows=1 loops=1)
              Output: fn_1.i
              Function Call: customer01.fn('{}'::jsonb)
              Buffers: shared hit=193
              Worker 0:  actual time=2.419..2.419 rows=1 loops=1
                Buffers: shared hit=193
        ->  Function Scan on customer02.fn fn_2  (cost=0.25..10.25 rows=1000 width=4) (actual time=0.057..0.057 rows=1 loops=1)
              Output: fn_2.i
              Function Call: fn('{}'::jsonb)
              Buffers: shared hit=2
              Worker 1:  actual time=0.057..0.057 rows=1 loops=1
                Buffers: shared hit=2
Query Identifier: -2124590841971516102
Planning:
  Buffers: shared hit=2
Planning Time: 0.174 ms
Execution Time: 10.705 ms

No matter what i do, i always get 2 workers only. You can see that a function gets worker 0, and the other two gets worker 1.

With a normal query i get all the workers.

select * from catalog.product
union all
select * from customer01.product
union all
select * from customer02.product

/*
Gather  (cost=0.00..44925.16 rows=610216 width=1896) (actual time=10.779..115.180 rows=610216 loops=1)
  Output: product_2....
  Workers Planned: 8
  Workers Launched: 8
  Buffers: shared hit=43781
  ->  Parallel Append  (cost=0.00..44925.16 rows=76277 width=1896) (actual time=0.008..19.008 rows=76277 loops=8)
        Buffers: shared hit=43781
...
*/

I don't understand what costs are, but what i want to acomplish is that i get 3 workers, 1 for each function, forcing postgresql to not care about what they do. What i see is that i'm trying to do the most basic example of parallelism, because of union all the 3 functions are independent, but it's the one i just can't get it to work.

I'm using PG 14.0

Answer 1

You cannot get that at the moment.

The number of workers is hard-coded as log2(n) + 1, where n is the partition count.

There were attempts make that configurable for PostgreSQL v14, but the problem where and how to specify that proved too hard.

See this closely related question that explains more details.