Re: RFC: Allow EXPLAIN to Output Page Fault Information

On Mon, Jun 1, 2026 at 10:10 PM Atsushi Torikoshi
<torikoshia(dot)tech(at)gmail(dot)com> wrote:
>
> On Tue, May 26, 2026 at 3:44 AM Lukas Fittl <lukas(at)fittl(dot)com> wrote:
>
> Thanks for your comment!
>
> > Maybe we should try to figure out what would be needed to do better
> > I/O tracking on the Linux side in a way that is compatible with I/O
> > workers?
>
> That may well be true.
> Unfortunately, I do not currently have a good idea of how to achieve
> this in a way that works with I/O workers.
> I'll keep thinking about whether there might be another approach.

I have not found a practical way to attribute storage I/O performed by
the shared I/O workers to individual backend queries.

One possible approach would be to call getrusage() before and after each
I/O request processed by a worker and accumulate the delta in counters
associated with the request owner. However, a worker may process
requests
from different backends in succession, and an individual request may be
as small as one block. This could therefore require two getrusage()
calls
per I/O request. In that case, the overhead of calling getrusage()
would
not be negligible.

I also considered probing page-cache availability with
preadv2(RWF_NOWAIT) before performing the actual read. However, this
does
not appear to provide reliable storage-I/O accounting: RWF_NOWAIT can
return EAGAIN for reasons other than fetching data from storage, and a
blocking read would still be required for data that is not immediately
available. It would also add another system call on that path.
Therefore,
I have not pursued this approach.

For now, I still think it is useful to provide this information for
io_method=sync and io_method=io_uring, while clearly documenting that it
is unavailable with io_method=worker. As Jelte previously commented:

On 2025-02-10 23:52:17 +0100, Jelte Fennema-Nio wrote:
> I think it would be a shame to make perfect be the enemy of good here
> (as often seems to happen with PG patches). I'd rather have this
> feature for some setups, than for no setups at all.

My impression is that io_uring is still in the process of gaining
broader
adoption in enterprise environments. However, there are also signs that
it is becoming a more practical option. For example, RHEL 10.2 has moved
io_uring out of Technology Preview and now supports it[1].

Some environments may still restrict io_uring for security or
operational
reasons, but I expect the number of environments where io_uring can be
used in practice to increase over time.

On Sun, May 24, 2026 at 6:50 PM Jelte Fennema-Nio
<postgres(at)jeltef(dot)nl> wrote:
>
> On Fri, 22 May 2026 at 18:07, Atsushi Torikoshi
> <torikoshia(dot)tech(at)gmail(dot)com> wrote:
> > Updated the patch to clarify that the reported values include not only
> > the backend process executing the query, but also any parallel query
> > worker processes involved in the query.
>
> For PG19 Tomas added an IO option to EXPLAIN. I think it would be
> better to have the Storage IO tracking be enabled by the IO option,
> rather than the BUFFERS option.

Changed this feature so that it is enabled by the IO option.

On Sat, May 30, 2026 at 6:45 PM Ilmar Y <tanswis42(at)gmail(dot)com> wrote:
> Should the execution Storage I/O section be gated on es->analyze,
> similar to Execution Time?

Moving the feature to the IO option also resolves this issue, because IO
requires ANALYZE. Therefore, an Execution Storage I/O section is no
longer
produced for a non-ANALYZE EXPLAIN.

In addition, added the following changes:

- Excluded I/O caused by generating the EXPLAIN output itself from the
execution statistics. I/O through ExecutorFinish() and during
ExecutorEnd() is still included, but I/O during plan formatting and
EXPLAIN hooks is not.
- Changed parallel-query instrumentation so that storage I/O counters
and the associated shared memory are used only when the IO option is
requested. This avoids unnecessary getrusage() calls and shared-memory
allocation otherwise.

[1]
https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/10.2_release_notes/revision-history

--
Thanks,

--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.