On Mon, 8 Jul 2024 at 15:43, David Rowley <dgrowle...@gmail.com> wrote: > > On Sun, 18 Feb 2024 at 11:31, Tomas Vondra > <tomas.von...@enterprisedb.com> wrote: > > 2) Leader vs. worker counters > > > > It seems to me this does nothing to add the per-worker values from "Heap > > Blocks" into the leader, which means we get stuff like this: > > > > Heap Blocks: exact=102 lossy=10995 > > Worker 0: actual time=50.559..209.773 rows=215253 loops=1 > > Heap Blocks: exact=207 lossy=19354 > > Worker 1: actual time=50.543..211.387 rows=162934 loops=1 > > Heap Blocks: exact=161 lossy=14636 > > > > I think this is wrong / confusing, and inconsistent with what we do for > > other nodes. > > Are you able to share which other nodes that you mean here?
I did the analysis on this and out of the node types that have parallel instrumentation (per ExecParallelRetrieveInstrumentation()), Parallel Hash is the only node that does anything different from the others. Looking at the loop inside show_hash_info(), you can see it takes the Max() of each property. There's some discussion in [1] about why this came about. In particular [2]. I see no reason to copy the odd one out here, so I'm planning on going ahead with the patch that has Bitmap Heap Scan copy what the majority of other nodes do. I think we should consider aligning Parallel Hash with the other Parallel node behaviour. I've attached the (roughly done) schema and queries I used to obtain the plans to do this analysis. David [1] https://www.postgresql.org/message-id/flat/20200323165059.GA24950%40alvherre.pgsql [2] https://www.postgresql.org/message-id/31321.1586549487%40sss.pgh.pa.us
create table mill (a int); create index on mill(a); insert into mill select x%1000 from generate_Series(1,10000000)x; vacuum analyze mill; create table big (a int primary key); insert into big select x from generate_series(1,10000000)x; create table probe (a int); insert into probe select 1 from generate_Series(1,1000000); analyze big,probe; create table agg (a int, b int); insert into agg select a%1000,a from generate_Series(1,1000000)a; create index on agg(a); set parallel_tuple_cost=0; set parallel_setup_cost=0; set enable_indexscan=0; -- each includes "Worker N:" with stats for the operation. explain (analyze) select * from mill where a < 100; -- Bitmap Heap Scan explain (analyze) select * from big b inner join probe p on b.a=p.a; -- Memoize explain (analyze) select * from probe order by a; -- Sort set enable_nestloop=0; explain (analyze) select a,count(b) from agg group by a; -- HashAgg ODD ONE OUT. GETS MAX DETAILS FROM WORKERS. explain (analyze) select * from big b inner join probe p on b.a=p.a; -- Hash reset enable_nestloop; set enable_indexscan=1; set enable_bitmapscan=0; set enable_seqscan=0; explain (analyze) select a,b from agg where a > 10 order by a,b; -- Incremental Sort set enable_indexscan=0; set enable_bitmapscan=1; set enable_seqscan=1; -- each includes "Worker N:" with stats for the operation -- also includes actual time and rows for each worker. explain (analyze, verbose) select * from mill where a < 100; -- Bitmap Heap Scan explain (analyze, verbose) select * from big b inner join probe p on b.a=p.a; -- Memoize explain (analyze, verbose) select * from probe order by a; -- Sort set enable_nestloop=0; explain (analyze, verbose) select a,count(b) from agg group by a; -- HashAgg ODD ONE OUT. GETS MAX DETAILS FROM WORKERS. explain (analyze, verbose) select * from big b inner join probe p on b.a=p.a; -- Hash reset enable_nestloop; set enable_indexscan=1; set enable_bitmapscan=0; set enable_seqscan=0; explain (analyze, verbose) select a,b from agg where a > 10 order by a,b; -- Incremental Sort set enable_indexscan=0; set enable_bitmapscan=1; set enable_seqscan=1; -- each includes "Worker N:" with stats for the operation -- shows a single total buffers which includes leader and worker buffers. explain (analyze, buffers) select * from mill where a < 100; -- Bitmap Heap Scan explain (analyze, buffers) select * from big b inner join probe p on b.a=p.a; -- Memoize explain (analyze, buffers) select * from probe order by a; -- Sort set enable_nestloop=0; explain (analyze, buffers) select a,count(b) from agg group by a; -- HashAgg ODD ONE OUT. GETS MAX DETAILS FROM WORKERS. reset enable_nestloop; set enable_indexscan=1; set enable_bitmapscan=0; set enable_seqscan=0; explain (analyze, buffers) select a,b from agg where a > 10 order by a,b; -- Incremental Sort set enable_indexscan=0; set enable_bitmapscan=1; set enable_seqscan=1; -- each includes "Worker N:" with stats for the operation -- also includes actual time and rows for each worker. -- shows a single total buffers which includes leader and worker buffers. -- shows buffer counts for each worker process explain (analyze, buffers, verbose) select * from mill where a < 100; -- Bitmap Heap Scan explain (analyze, buffers, verbose) select * from big b inner join probe p on b.a=p.a; explain (analyze, buffers, verbose) select * from probe order by a; -- Sort set enable_nestloop=0; explain (analyze, buffers, verbose) select a,count(b) from agg group by a; -- HashAgg ODD ONE OUT. GETS MAX DETAILS FROM WORKERS. reset enable_nestloop; set enable_indexscan=1; set enable_bitmapscan=0; set enable_seqscan=0; explain (analyze, buffers, verbose) select a,b from agg where a > 10 order by a,b; -- Incremental Sort set enable_indexscan=0; set enable_bitmapscan=1; set enable_seqscan=1;
