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

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 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

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