Re: Showing I/O timings spent reading/writing temp buffers in EXPLAIN

On Sun, Aug 22, 2021 at 9:47 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Thu, Aug 19, 2021 at 10:52 PM Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> wrote:
> >
> > Em qui., 19 de ago. de 2021 às 09:21, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> escreveu:
>
> > The presentation seems a little confusing, wouldn't it be better?
> >
> > I/O Timings: shared/local read=xxxx write=xxx temp read=0.487 write=2.073
>
> Yeah, it looks better to add "shared/local".

Using the patch, I do feel like the EXPLAIN format of

shared/local xxx, temp xxx

is a bit confusing. If temp is going to be its own EXPLAIN IO timing
category (as opposed to being summed with relation data block IO from
local and shared buffers), then it seems like local and shared should
be separated as well.

shared xxx, local xxx, temp xxx

With your patch applied, below is the top of the EXPLAIN output for a
query joining a temporary table (so using local buffers) to a regular
table (using shared buffers) and spilling the hash join (temp files).

Aggregate (actual rows=1 loops=1)
Buffers: shared read=4425, local read=4425 dirtied=4425
written=4423, temp read=5963 written=5963
I/O Timings: shared/local read=23.546, temp read=13.309 write=74.198

I found that using the same terminology as the "EXPLAIN BUFFERS" output
but not using the same categories was kind of confusing.

If it is only meaningful to distinguish between relation data IO and
query temp file IO, then maybe the words used in I/O Timings in EXPLAIN
should be "rel data" and "temp" or something like that.

- Melanie