Thread: Using distinct in an aggregate prevents parallel execution?

Consider this simplified example:

    select c.id, 
           count(*) as total_orders,
           sum(p.price) as total_value
    from customer c
      join orders o ON c.id = o.customer_id 
      join order_line ol ON o.id = ol.order_id 
      join product p ON ol.product_id = p.id
    group by c.id;

This uses parallel execution quite nicely: https://explain.depesz.com/s/aSPNn

However, the query is incorrect as it does not count the number of orders, but (essentially) the number of
order_lines.

This can easily be fixed using:

    select c.id, 
           count(distinct o.id) as total_orders,
           sum(p.price) as total_value
    from customer c
      join orders o ON c.id = o.customer_id 
      join order_line ol ON o.id = ol.order_id 
      join product p ON ol.product_id = p.id
    group by c.id;  

But in that case Postgres 10.4 decides to no longer use parallel execution: https://explain.depesz.com/s/7Ua3

Which increases the query execution time quite a bit (from 3 to 8 seconds). 

Is this a known limitation? 

Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> Is this a known limitation? 

Yes, unless somebody has done radical restructuring of the aggregation
code while I wasn't looking.

agg(DISTINCT ...) is currently implemented inside the Agg plan node,
so it's an indivisible black box to everything else.  That was a
simple, minimum-code-footprint method for implementing the feature
back when; but it's got lots of drawbacks, and one is that there's
no reasonable way to parallelize.

I'd anticipate that before we could even start to think of parallelizing,
we'd have to split out the distinct-ification processing into a separate
plan node.

agg(... ORDER BY ...) has got the same problem, and it'd likely be
advisable to fix that at the same time.

            regards, tom lane

Tom Lane schrieb am 06.06.2018 um 16:32:
> Thomas Kellerer <spam_eater@gmx.net> writes:
>> Is this a known limitation?
> 
> Yes, unless somebody has done radical restructuring of the aggregation
> code while I wasn't looking.
> 
> agg(DISTINCT ...) is currently implemented inside the Agg plan node,
> so it's an indivisible black box to everything else.  That was a
> simple, minimum-code-footprint method for implementing the feature
> back when; but it's got lots of drawbacks, and one is that there's
> no reasonable way to parallelize.
> 
> I'd anticipate that before we could even start to think of parallelizing,
> we'd have to split out the distinct-ification processing into a separate
> plan node.
> 
> agg(... ORDER BY ...) has got the same problem, and it'd likely be
> advisable to fix that at the same time.

Thansk for the explanation.