From: | Konstantin Knizhnik <knizhnik(at)garret(dot)ru> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Parallel plan cost |
Date: | 2023-03-27 08:16:36 |
Message-ID: | 7951f4a3-a82d-1376-4b59-aa5270b0efa4@garret.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2023-03-27 08:18:20 | Re: generic plans and "initial" pruning |
Previous Message | Drouvot, Bertrand | 2023-03-27 08:11:21 | Re: Generate pg_stat_get_xact*() functions with Macros |