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

Hi Melanie,

> This setup is contrived enough that I do not anticipate seeing
> anything like this in real workloads.

You were right, that first setup was not worth anything. With 1024 buffers,
MaxProportionalPins leaves each backend about 9 pins, so the read stream
never looked ahead and my reading of the numbers was wrong. I shrank the
buffer pool to force reads instead of growing the data. That was the wrong
trade.

Here it is again. Stock pgbench at scale 1000, so a 13 GB table.
shared_buffers of 2 GB. autovacuum on. 8 clients on the built in pgbench
script and 4 clients running one aggregate over the whole table. 120 seconds
per run.

Prune attempts dropped because the page was pinned.

direct io off, 4.6% and 3.2%
direct io on, 66.7% and 54.1%

Then I ran the same thing with the 4 scanning clients taken out, so plain
pgbench and nothing else. 4 missed prunes in 120 seconds. That was true with
direct io on and with it off.

So there are two conditions. The table has to be getting scans and updates
at
the same time, and direct io has to be on. Plain pgbench with no scans shows
nothing at all.

I would not call that a corner case. This is what we are running into in our
benchmark environment, and it is why I started looking.

> Adding four columns to pg_stat_all_tables is not worth it for this.

You are right about the shape too. A cumulative sum does not tell you when
it
happened. I would rather find the right shape than push four columns. Vacuum
at least has a log line for the pages it could not clean up. On access
pruning has no counter and no log line, so when it stops working there is
nothing to find. I would like to know whether something there is worth
doing,
or whether you think this does not need to be visible at all.

Thanks,
Shihao