RFC: Allow EXPLAIN to Output Page Fault Information

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:

=# EXPLAIN (ANALYZE, PAGEFAULTS)
SELECT * FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.58..335386.98 rows=4999917 width=197) (actual
time=3.785..5590.294 rows=5000000 loops=1)

... (omitted)

Planning:
Buffers: shared hit=50 read=48 dirtied=4 written=2
Page Faults: minor=30 major=19 <-ADDED
Planning Time: 22.080 ms
Execution:
Page Faults: minor=49 major=5 <-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.