Обсуждение: Parallel plan cost
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:
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.
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.