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