Hi hackers,
I wonder why while calculating cost of parallel scan we divide by
parallel_divisor only CPU run cost,
but not storage access cost? So we do not take in account that reading
pages is also performed in parallel.
Actually I observed strange behavior when increasing work_mem disables
parallel plan even with parallel-friendly tuning:
set parallel_tuple_cost = 0;
set parallel_setup_cost = 0;
set max_parallel_workers = 16;
set max_parallel_workers_per_gather = 16;
set min_parallel_table_scan_size = '16kB';
postgres=# set work_mem = '32MB';
SET
postgres=# explain select sum(payload) from sp where p <@
'((0.2,0.2),(0.3,0.3))'::box;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=3427210.71..3427210.72 rows=1 width=8)
-> Gather (cost=3427210.67..3427210.68 rows=12 width=8)
Workers Planned: 12
-> Partial Aggregate (cost=3427210.67..3427210.68 rows=1 width=8)
-> Parallel Bitmap Heap Scan on sp (cost=31994.55..3427002.34
rows=83333 width=4)
Recheck Cond: (p <@ '(0.3,0.3),(0.2,0.2)'::box)
-> Bitmap Index Scan on sp_p_idx (cost=0.00..31744.55
rows=1000000 width=0)
Index Cond: (p <@ '(0.3,0.3),(0.2,0.2)'::box)
(8 rows)
postgres=# set work_mem = '64MB';
SET
postgres=# explain select sum(payload) from sp where p <@
'((0.2,0.2),(0.3,0.3))'::box;
QUERY PLAN
---------------------------------------------------------------------------------------
Aggregate (cost=2694543.52..2694543.53 rows=1 width=8)
-> Bitmap Heap Scan on sp (cost=31994.55..2692043.52 rows=1000000 width=4)
Recheck Cond: (p <@ '(0.3,0.3),(0.2,0.2)'::box)
-> Bitmap Index Scan on sp_p_idx (cost=0.00..31744.55 rows=1000000
width=0)
Index Cond: (p <@ '(0.3,0.3),(0.2,0.2)'::box)
(5 rows)
In theory, with zero parallel setup cost we should always prefer
parallel plan with maximal possible number of workers.
But right now it is not true.