Re: 9.6beta, parallel execution and cpu_tuple_cost

On Fri, 27 May 2016, 9:26 p.m. Thomas Kellerer, <spam_eater(at)gmx(dot)net> wrote:

> Hello,
>
> while playing around with the parallel aggregates and seq scan in 9.6beta
> I noticed that Postgres will stop using parallel plans when cpu_tuple_cost
> is set to a very small number.
>
> When using the defaults and max_parallel_degree = 4, the following (test)
> query will be executed with 4 workers
>
> explain (analyze, verbose)
> select o.customer_id,
> count(*) num_orders,
> sum(ol.price) as total_price,
> sum(p.purchase_price) as total_purchase_price
> from orders o
> join order_line ol on o.id = ol.order_id
> join product p ON ol.product_id = p.id
> group by o.customer_id;
>
> The execution plan is: https://explain.depesz.com/s/C7g
>
> After setting cpu_tuple_cost to something small:
>
> set cpu_tuple_cost = 0.0001;
>
> No parallel wokers are used: https://explain.depesz.com/s/q1zb
>
>
> I am not sure I understand why this is happening. Why would lowering the
> CPU cost for a tuple result in not using a parallel plan?
>
> Is this an oversight, a bug or intended?
>
>

This is expected. You have modified cost of processing the tuple by CPu but
you have not modified the cost of parallel tuple processing
(parallel_tuple_cost, if I recall it right). That forces optimizer to
evaluate parallel processing of tuples to be more costly, hence chooses a
plan without parallel worker.

Perhaps if you reduce the both parameters by same factor/margin, you can
see that parallel execution will be preffered when it is of lower cost.

> Regards
> Thomas
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com