From: | Luc Vlaming <luc(at)swarm64(dot)com> |
---|---|
To: | Phil Florent <philflorent(at)hotmail(dot)com>, Greg Nancarrow <gregn4422(at)gmail(dot)com> |
Cc: | "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Parallel plans and "union all" subquery |
Date: | 2020-11-23 15:34:02 |
Message-ID: | 5bb12356-a13c-5dd6-8bf5-703fd90c5188@swarm64.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 23-11-2020 13:17, Phil Florent wrote:
> Hi Greg,
>
> The implicit conversion was the cause of the non parallel plan, thanks
> for the explanation and the workarounds. It can cause a huge difference
> in terms of performance, I will give the information to our developers.
>
> Regards,
>
> Phil
>
>
>
> ------------------------------------------------------------------------
> *De :* Greg Nancarrow <gregn4422(at)gmail(dot)com>
> *Envoyé :* lundi 23 novembre 2020 06:04
> *À :* Phil Florent <philflorent(at)hotmail(dot)com>
> *Cc :* pgsql-hackers(at)lists(dot)postgresql(dot)org
> <pgsql-hackers(at)lists(dot)postgresql(dot)org>
> *Objet :* Re: Parallel plans and "union all" subquery
> On Sun, Nov 22, 2020 at 11:51 PM Phil Florent <philflorent(at)hotmail(dot)com>
> wrote:
>>
>>
>> Hi,
>>
>>
>> I have a question about parallel plans. I also posted it on the general list but perhaps it's a question for hackers. Here is my test case :
>>
>>
>> explain
>> select count(*)
>> from (select
>> n1
>> from drop_me
>> union all
>> values(1)) ua;
>>
>>
>> QUERY PLAN
>> --------------------------------------------------------------------------------
>> Aggregate (cost=2934739.24..2934739.25 rows=1 width=8)
>> -> Append (cost=0.00..2059737.83 rows=70000113 width=32)
>> -> Seq Scan on drop_me (cost=0.00..1009736.12 rows=70000112 width=6)
>> -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=32)
>> -> Result (cost=0.00..0.01 rows=1 width=4)
>> JIT:
>> Functions: 4
>> Options: Inlining true, Optimization true, Expressions true, Deforming true
>>
>>
>> No parallel plan, 2s6
>>
>>
>> I read the documentation but I don't get the reason of the "noparallel" seq scan of drop_me in the last case ?
>>
>
> Without debugging this, it looks to me that the UNION type resolution
> isn't working as well as it possibly could in this case, for the
> generation of a parallel plan. I found that with a minor tweak to your
> SQL, either for the table creation or query, it will produce a
> parallel plan.
>
> Noting that currently you're creating the drop_me table with a
> "numeric" column, you can either:
>
> (1) Change the table creation
>
> FROM:
> create unlogged table drop_me as select generate_series(1,7e7) n1;
> TO:
> create unlogged table drop_me as select generate_series(1,7e7)::int n1;
>
>
> OR
>
>
> (2) Change the query
>
> FROM:
> explain
> select count(*)
> from (select
> n1
> from drop_me
> union all
> values(1)) ua;
>
> TO:
>
> 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
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?
Regards,
Luc
Swarm64
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2020-11-23 15:35:54 | Re: Online verification of checksums |
Previous Message | Alvaro Herrera | 2020-11-23 15:30:05 | Re: remove spurious CREATE INDEX CONCURRENTLY wait |