Re: Parallel Bitmap Heap Scan reports per-worker stats in EXPLAIN ANALYZE

public inbox for [email protected]  
help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: David Geier <[email protected]>
Cc: PostgreSQL Developers <[email protected]>
Subject: Re: Parallel Bitmap Heap Scan reports per-worker stats in EXPLAIN ANALYZE
Date: Tue, 9 Jul 2024 11:51:23 +1200
Message-ID: <CAApHDvoom6ABLgyNW3FtqtQxFB4Hz-6Y=zMy1EobPEOOMBDfUg@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvqFtd-9DYH70sbjD7iB-Eq-xSip1LPr=nayfpPd1pkZVw@mail.gmail.com>
References: <[email protected]>
	<CAFiTN-v1yDvU=X+hwfJ+55=sbgDj=_kuvbduEG-F7=BjpWcnuw@mail.gmail.com>
	<[email protected]>
	<CAApHDvqFtd-9DYH70sbjD7iB-Eq-xSip1LPr=nayfpPd1pkZVw@mail.gmail.com>

On Mon, 8 Jul 2024 at 15:43, David Rowley <[email protected]> wrote:
>
> On Sun, 18 Feb 2024 at 11:31, Tomas Vondra
> <[email protected]> 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;


Attachments:

  [text/plain] explain_analyze_parallel_stats_analysis.txt (3.7K, 2-explain_analyze_parallel_stats_analysis.txt)
  download | inline:
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;

view thread (23+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Parallel Bitmap Heap Scan reports per-worker stats in EXPLAIN ANALYZE
  In-Reply-To: <CAApHDvoom6ABLgyNW3FtqtQxFB4Hz-6Y=zMy1EobPEOOMBDfUg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox