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)
