Re: Parallel plans and "union all" subquery

On Tue, Nov 24, 2020 at 2:34 AM Luc Vlaming <luc(at)swarm64(dot)com> wrote:
>
> Hi,
>
> For this problem there is a patch I created, which is registered under
> https://commitfest.postgresql.org/30/2787/ that should fix this without
> any workarounds. Maybe someone can take a look at it?
>

I tried your patch with the latest PG source code (24/11), but
unfortunately a non-parallel plan was still produced in this case.

test=# explain
select count(*)
from (select
n1
from drop_me
union all
values(1)) ua;
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=1889383.54..1889383.55 rows=1 width=8)
-> Append (cost=0.00..1362834.03 rows=42123961 width=32)
-> Seq Scan on drop_me (cost=0.00..730974.60 rows=42123960 width=32)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=32)
-> Result (cost=0.00..0.01 rows=1 width=4)
(5 rows)

That's not to say your patch doesn't have merit - but maybe just not a
fix for this particular case.

As before, if the SQL is tweaked to align the types for the UNION, you
get a parallel plan:

test=# explain
select count(*)
from (select
n1
from drop_me
union all
values(1::numeric)) ua;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=821152.71..821152.72 rows=1 width=8)
-> Gather (cost=821152.50..821152.71 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=820152.50..820152.51 rows=1 width=8)
-> Parallel Append (cost=0.00..747235.71 rows=29166714 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Parallel Seq Scan on drop_me
(cost=0.00..601402.13 rows=29166713 width=0)
(7 rows)

Regards,
Greg Nancarrow
Fujitsu Australia