Re: Add last_vacuum_index_scans in pg_stat_all_tables

On 2022-Sep-16, Fujii Masao wrote:

> Could you tell me why the number of index scans should be tracked for
> each table? Instead, isn't it enough to have one global counter, to
> check whether the current setting of maintenance_work_mem is sufficient
> or not? That is, I'm thinking to have something like pg_stat_vacuum view
> that reports, for example, the number of vacuum runs, the total
> number of index scans, the maximum number of index scans by one
> vacuum run, the number of cancellation of vacuum because of
> lock conflicts, etc. If so, when these global counters are high or
> increasing, we can think that it may worth tuning maintenance_work_mem.

I think that there are going to be cases where some tables in a database
definitely require multiple index scans no matter what; but you
definitely want to know how many occurred for others, not so highly
trafficked tables. So I *think* a single counter across the whole
database might not be sufficient.

The way I imagine using this (and I haven't operated databases in quite
a while so this may be all wet) is that I would have a report of which
tables have the highest numbers of indexscans, then study the detailed
vacuum reports for those tables as a way to change autovacuum_work_mem.

On the other hand, we have an absolute high cap of 1 GB for autovacuum's
work_mem, and many systems are already using that as the configured
value. Maybe trying to fine-tune it is a waste of time. If a 1TB table
says that it had 4 index scans, what are you going to do about it? It's
a lost cause. It sounds like we need more code changes so that more
memory can be used; and also changes so that that memory is used more
efficiently. We had a patch for this, I don't know if that was
committed already.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)