Обсуждение: PostgreSQL 12.4 Parallel Query doesn't work while EXPLAIN is OK
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
On Tue, 2020-12-01 at 14:38 +0700, mobigroup wrote:
> I have strange behaviour when EXPLAIN returns parallel execution plan but the query execution is not parallel. The
querylooks 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
The essential question here is when the function is executed, so you should use
EXPLAIN (VERBOSE) to see that.
Possible explanations:
- The function is executed after the "Gather" node.
Perhaps you didn't define it as PARALLEL SAFE.
- Perhaps the tables are small.
During a parallel sequential scan, each worker is assigned a range of blocks to scan,
so all rows found in a single block are scanned by a single worker.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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.
mobigroup <pechnikov@mobigroup.ru> writes:
>> 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
thesame result… parallelisation doesn’t work anyway).
If it writes into temp tables then it can't be run in a worker anyway.
regards, tom lane
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
Best regards, Alexey Pechnikov
On 1 Dec 2020, at 21:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:PARALLEL RESTRICTED