Parallel plan cost

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.

Browse pgsql-hackers by date

  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