Search Postgresql Archives

Using distinct in an aggregate prevents parallel execution?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux