MIME-Version: 1.0
References: <c20f6340eb26f3b736abc59471bfada8@oss.nttdata.com>
In-Reply-To: <c20f6340eb26f3b736abc59471bfada8@oss.nttdata.com>
From: vellaipandiyan sm <vellaipandiyan.sm@gmail.com>
Date: Thu, 21 May 2026 11:08:33 +0530
Message-ID: 
 <CAGXjcj=hnEZCCDDMRv06EQnDcv0mRe6+Ph1gv8=Hb7NEc51y5A@mail.gmail.com>
Subject: Re: RFC: Allow EXPLAIN to Output Page Fault Information
To: torikoshia <torikoshia@oss.nttdata.com>
Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000062b07906524d53b0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGXjcj%3DhnEZCCDDMRv06EQnDcv0mRe6%2BPh1gv8%3DHb7NEc51y5A%40mail.gmail.com>
Precedence: bulk

--00000000000062b07906524d53b0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello hackers,

I reviewed the EXPLAIN storage I/O patch and the overall direction seems
useful, especially for distinguishing shared-buffer hits from actual
storage reads during query analysis.

One concern that stood out to me from the later discussion is the
interaction with asynchronous I/O and worker-based I/O accounting.

Since the patch currently relies on per-process getrusage() statistics, it
seems possible that the reported values could become partial or misleading
once I/O is performed outside the backend process context. In particular,
worker-based AIO could undercount storage reads/writes while still
returning non-zero values, which may make the output appear more accurate
than it actually is.

I also wonder whether this should affect the semantics/documentation of the
feature itself =E2=80=94 i.e. whether the reported numbers should be descri=
bed more
explicitly as =E2=80=9Cbackend-observed synchronous storage I/O=E2=80=9D ra=
ther than total
query I/O.

That said, the examples showing differences between OS cache hits and
actual storage reads were quite compelling, especially for query
performance debugging.

Thanks for working on the patch.

Regards,
Vellaipandiyan

On Thu, May 21, 2026 at 11:07=E2=80=AFAM torikoshia <torikoshia@oss.nttdata=
.com>
wrote:

> Hi,
>
> When reading the output of EXPLAIN (ANALYZE) to diagnose slow queries
> for our customers, I often want to know how many page faults occurred,
> especially major page faults, which involve disk access.
>
> Currently, the BUFFERS option in EXPLAIN provides information on whether
> a page was found in the shared buffers, but it doesn't provide insight
> into whether the page was found in the OS cache or not and disk I/O
> occurred.
>
> Since page faults especially major one impact performance compared to
> shared buffer and OS cache hits, it would be helpful to track these
> events.
>
> I have attached a PoC patch that modifies EXPLAIN to include page fault
> information during both the planning and execution phases of a query.
> The output would look like this:
>
>    =3D# EXPLAIN (ANALYZE, PAGEFAULTS)
>            SELECT * FROM pgbench_branches b
>              JOIN pgbench_accounts a ON b.bid =3D a.bid ORDER BY a.aid;
>
>    QUERY PLAN
>
>
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
>      Nested Loop  (cost=3D0.58..335386.98 rows=3D4999917 width=3D197) (ac=
tual
> time=3D3.785..5590.294 rows=3D5000000 loops=3D1)
>
>        ... (omitted)
>
>      Planning:
>        Buffers: shared hit=3D50 read=3D48 dirtied=3D4 written=3D2
>        Page Faults: minor=3D30 major=3D19 <-ADDED
>      Planning Time: 22.080 ms
>      Execution:
>        Page Faults: minor=3D49 major=3D5 <-ADDED
>      Execution Time: 5794.356 ms
>
> The patch has not yet added this functionality to auto_explain, but I
> believe this feature would be more useful in auto_explain than in the
> EXPLAIN command itself. I plan to add it to auto_explain if there's
> interest in including page fault information in EXPLAIN.
>
> While GUCs like log_statement_stats allow logging of getrusage(2)
> information, including page faults, always enabling this can lead to
> excessive log output. It would be better to log this data only when
> queries are slow. Therefore, adding this feature to auto_explain seems
> like a good solution.
>
>
> The patch introduces a new option, PAGEFAULTS, but it may be more
> appropriate to include the page fault information in another option,
> such as SUMMARY, especially if there are other useful resources that can
> be obtained from getrusage(2).
>
>
> The patch cannot be applied to Windows because getrusage() in PostgreSQL
> ported for Windows currently only tracks CPU times. I'm not sure if
> information on major and minor page faults is accessible on Windows, but
> it might be acceptable to treat it similarly to log_statement_stats and
> exclude it from Windows support.
>
>
> I also tried to add page faults information for each plan node, similar
> to the BUFFERS option, but I decided against this approach due to the
> performance impact. The frequent calls to getrusage(2), i.e. each time
> getting one row, created significant overhead.
>
>
> Any feedback would be greatly appreciated.
>
>
> --
> Regards,
>
> --
> Atsushi Torikoshi
> Seconded from NTT DATA GROUP CORPORATION to SRA OSS K.K.

--00000000000062b07906524d53b0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello hackers,<br><br>I reviewed the EXPLAIN storage I/O p=
atch and the overall direction seems useful, especially for distinguishing =
shared-buffer hits from actual storage reads during query analysis.<br><br>=
One concern that stood out to me from the later discussion is the interacti=
on with asynchronous I/O and worker-based I/O accounting.<br><br>Since the =
patch currently relies on per-process getrusage() statistics, it seems poss=
ible that the reported values could become partial or misleading once I/O i=
s performed outside the backend process context. In particular, worker-base=
d AIO could undercount storage reads/writes while still returning non-zero =
values, which may make the output appear more accurate than it actually is.=
<br><br>I also wonder whether this should affect the semantics/documentatio=
n of the feature itself =E2=80=94 i.e. whether the reported numbers should =
be described more explicitly as =E2=80=9Cbackend-observed synchronous stora=
ge I/O=E2=80=9D rather than total query I/O.<br><br>That said, the examples=
 showing differences between OS cache hits and actual storage reads were qu=
ite compelling, especially for query performance debugging.<br><br>Thanks f=
or working on the patch.<br><br>Regards,<br>Vellaipandiyan</div><br><div cl=
ass=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_a=
ttr">On Thu, May 21, 2026 at 11:07=E2=80=AFAM torikoshia &lt;<a href=3D"mai=
lto:torikoshia@oss.nttdata.com">torikoshia@oss.nttdata.com</a>&gt; wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
When reading the output of EXPLAIN (ANALYZE) to diagnose slow queries <br>
for our customers, I often want to know how many page faults occurred, <br>
especially major page faults, which involve disk access.<br>
<br>
Currently, the BUFFERS option in EXPLAIN provides information on whether <b=
r>
a page was found in the shared buffers, but it doesn&#39;t provide insight =
<br>
into whether the page was found in the OS cache or not and disk I/O <br>
occurred.<br>
<br>
Since page faults especially major one impact performance compared to <br>
shared buffer and OS cache hits, it would be helpful to track these <br>
events.<br>
<br>
I have attached a PoC patch that modifies EXPLAIN to include page fault <br=
>
information during both the planning and execution phases of a query. <br>
The output would look like this:<br>
<br>
=C2=A0 =C2=A0=3D# EXPLAIN (ANALYZE, PAGEFAULTS)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT * FROM pgbench_branches b<b=
r>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0JOIN pgbench_accounts a ON =
b.bid =3D a.bid ORDER BY a.aid;<br>
<br>
=C2=A0 =C2=A0QUERY PLAN<br>
<br>
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------<br>
=C2=A0 =C2=A0 =C2=A0Nested Loop=C2=A0 (cost=3D0.58..335386.98 rows=3D499991=
7 width=3D197) (actual <br>
time=3D3.785..5590.294 rows=3D5000000 loops=3D1)<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0... (omitted)<br>
<br>
=C2=A0 =C2=A0 =C2=A0Planning:<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D50 read=3D48 dirtied=3D4 w=
ritten=3D2<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0Page Faults: minor=3D30 major=3D19 &lt;-ADDED<br=
>
=C2=A0 =C2=A0 =C2=A0Planning Time: 22.080 ms<br>
=C2=A0 =C2=A0 =C2=A0Execution:<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0Page Faults: minor=3D49 major=3D5 &lt;-ADDED<br>
=C2=A0 =C2=A0 =C2=A0Execution Time: 5794.356 ms<br>
<br>
The patch has not yet added this functionality to auto_explain, but I <br>
believe this feature would be more useful in auto_explain than in the <br>
EXPLAIN command itself. I plan to add it to auto_explain if there&#39;s <br=
>
interest in including page fault information in EXPLAIN.<br>
<br>
While GUCs like log_statement_stats allow logging of getrusage(2) <br>
information, including page faults, always enabling this can lead to <br>
excessive log output. It would be better to log this data only when <br>
queries are slow. Therefore, adding this feature to auto_explain seems <br>
like a good solution.<br>
<br>
<br>
The patch introduces a new option, PAGEFAULTS, but it may be more <br>
appropriate to include the page fault information in another option, <br>
such as SUMMARY, especially if there are other useful resources that can <b=
r>
be obtained from getrusage(2).<br>
<br>
<br>
The patch cannot be applied to Windows because getrusage() in PostgreSQL <b=
r>
ported for Windows currently only tracks CPU times. I&#39;m not sure if <br=
>
information on major and minor page faults is accessible on Windows, but <b=
r>
it might be acceptable to treat it similarly to log_statement_stats and <br=
>
exclude it from Windows support.<br>
<br>
<br>
I also tried to add page faults information for each plan node, similar <br=
>
to the BUFFERS option, but I decided against this approach due to the <br>
performance impact. The frequent calls to getrusage(2), i.e. each time <br>
getting one row, created significant overhead.<br>
<br>
<br>
Any feedback would be greatly appreciated.<br>
<br>
<br>
-- <br>
Regards,<br>
<br>
--<br>
Atsushi Torikoshi<br>
Seconded from NTT DATA GROUP CORPORATION to SRA OSS K.K.</blockquote></div>

--00000000000062b07906524d53b0--