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

Hi,

Sorry for the late reply.

On Fri, Nov 19, 2021 at 7:24 AM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> 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

I think the I/O timing shown as shared/local is the time spent on disk
I/O so it doesn't matter if these disk I/O are for shared buffers or
local buffers.

>
> 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.

But if we do that, we end up using different terminology in "I/O
Timing" and "Buffers". I think it's better to use consistent words
used in them.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/