PostgreSQL 12.4 Parallel Query doesn't work while EXPLAIN is OK

[mobigroup]

Hi,

I have strange behaviour when EXPLAIN returns parallel execution plan but the 
query execution is not parallel. The query looks as

SELECT
plpgsql_function(...parameters…)
FROM table as t
WHERE id=1
UNION ALL
SELECT
plpgsql_function(...parameters…)
FROM table as t
WHERE id=2
UNION ALL
...

EXPLAIN outputs looks ok:

Append  (cost=0.00..10.25 rows=8 width=32)
  ->  Gather  (cost=0.00..1.27 rows=1 width=32)
Workers Planned: 8
->  Parallel Seq Scan on table t  (cost=0.00..1.27 rows=1 width=32)
  Filter: (id = 1)
  ->  Gather  (cost=0.00..1.27 rows=1 width=32)
Workers Planned: 8
->  Parallel Seq Scan on table t_1  (cost=0.00..1.27 rows=1 width=32)
  Filter: (id = 2)
  ->  Gather  (cost=0.00..1.27 rows=1 width=32)
...


But the query still uses just one core and I see sequential RAISE NOTICE 
outputs from the function plpgsql_function().

select version();
 version

-
 PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled 
by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

Re: PostgreSQL 12.4 Parallel Query doesn't work while EXPLAIN is OK

[mobigroup]

Thanks for the ideas, I tested them:
> 
> The essential question here is when the function is executed, so you should 
> use
> EXPLAIN (VERBOSE) to see that.

Append  (cost=0.00..5.12 rows=4 width=32)
  ->  Gather  (cost=0.00..1.26 rows=1 width=32)
Output: plpgsql_function(...)
Workers Planned: 8
->  Parallel Seq Scan on public.table t  (cost=0.00..1.01 rows=1 
width=174)
  Output: ...
  Filter: (t.id  = 1)
  ->  Gather  (cost=0.00..1.26 rows=1 width=32)
Output: plpgsql_function(...)
Workers Planned: 8
->  Parallel Seq Scan on public.table t_1  (cost=0.00..1.01 rows=1 
width=174)
  Output: ...
  Filter: (t_1.id  = 2)

> Possible explanations:
> 
> - The function is executed after the "Gather" node.

The question is - could we do something to fix it?

>  Perhaps you didn't define it as PARALLEL SAFE.

The function is marked as "PARALLEL RESTRICTED” because it’s uses temp tables 
(and I tested it as PARALLEL SAFE with the same result… parallelisation doesn’t 
work anyway).

> 
> - Perhaps the tables are small.

Yes, but these settings applied when the table is created:

analyze table;
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set force_parallel_mode = on;
alter table table set (parallel_workers = 8);

P.S. Actually, I just need to run in parallel mode one function with a set of 
different arguments to utilise all available CPUs. That’s strange but I 
couldn’t google a way to do it.

Re: PostgreSQL 12.4 Parallel Query doesn't work while EXPLAIN is OK

[mobigroup]

Tom,

Ok, but how about reading from temporary tables? We could mark the function as 
"PARALLEL SAFE” instead of "PARALLEL RESTRICTED” in this case if it’s 
important. Actually, I rewrote the function without temp tables access but 
that’s not helpful - the function marked as "PARALLEL SAFE” is not parallel 
even without temp tables access.

—
Best regards, Alexey Pechnikov

> On 1 Dec 2020, at 21:58, Tom Lane  wrote:
> 
>> PARALLEL RESTRICTED