Opportunistic pruning is lost under direct io, and nothing shows it

Hi hackers,

On access pruning gives up without a word when it cannot get a cleanup lock
on a page. That happens when some other session holds a pin on it. The dead
tuples stay, vacuum has to deal with them later, and not one counter moves.
I think we should be able to see this. The attached patch makes it visible.

I started looking because on access pruning now does more than it used to.
It can set a page all visible and it keeps the free space map up to date. So
when a scan walks away from a page, we lose more than a few dead tuples.

Here is what I measured. One table of 42 MB, shared_buffers of 8 MB,
autovacuum off, five clients, four of them counting rows in the table and
one doing single row updates, twenty seconds per run.

With direct io off, the share of prune attempts lost to a pin was

4.1%
2.6%
4.5%

With debug_io_direct set to data, same workload and about the same rate of
work done, it was

35.0%
44.0%
70.2%

With direct io still on and effective_io_concurrency raised to 64, it was

82.3%
83.0%

So under direct io most opportunistic pruning simply does not happen, and
right now there is no way to find that out. The reason is plain once you
look at it. Without direct io a buffer miss is often just a copy out of the
kernel cache, so the pin is held for a moment. With direct io the pin is
held across a real read from the device, and any other scan that wanted to
prune that page gives up.

Please treat these numbers with care. This is a laptop running macOS, where
debug_io_direct uses fcntl with F_NOCACHE, which is only advice to the
kernel and is not the same thing as O_DIRECT. io_method was worker. The
ratio of table size to shared_buffers is small on purpose. I would like
someone to repeat this on Linux with io_uring.

The patch adds four columns to pg_stat_all_tables.

prune_onaccess counts pages that a scan pruned.

prune_onaccess_missed counts prune attempts dropped because the page was
pinned. This is the number that goes up under direct io.

pages_all_visible_onaccess counts pages that on access pruning marked all
visible.

vacuum_missed_dead_pages counts pages vacuum could not clean up for the same
reason. Vacuum already counts these, it just prints them in the log and
forgets them, so you cannot follow the number over time.

Thanks,
Shihao