Thread: 9.6beta, parallel execution and cpu_tuple_cost
Hello,
while playing around with the parallel aggregates and seq scan in 9.6beta I noticed that Postgres will stop using
parallelplans 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
parallelplan?
Is this an oversight, a bug or intended?
Regards
Thomas
On Fri, 27 May 2016, 9:26 p.m. Thomas Kellerer, <spam_eater@gmx.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@postgresql.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
Thomas Kellerer <spam_eater@gmx.net> writes:
> while playing around with the parallel aggregates and seq scan in 9.6beta I noticed that Postgres will stop using
parallelplans when cpu_tuple_cost is set to a very small number.
If you don't reduce the parallel-plan cost factors proportionally,
it's not very surprising that reducing that would tend to bias the
planner away from using parallel plans. See parallel_setup_cost and
parallel_tuple_cost.
regards, tom lane
Tom Lane schrieb am 27.05.2016 um 15:48: > Thomas Kellerer <spam_eater@gmx.net> writes: >> 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. > > If you don't reduce the parallel-plan cost factors proportionally, > it's not very surprising that reducing that would tend to bias the > planner away from using parallel plans. See parallel_setup_cost and > parallel_tuple_cost. Ah, thanks. That makes sense. The low value for cpu_tuple_cost was actually a typo. Adjusting parallel_tuple_cost does bring back the parallel plan. Thomas