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.