On Mon, Mar 1, 2021 at 8:06 PM Masahiko Sawada <sawada.m...@gmail.com> wrote: > I think that removing vacuum_cleanup_index_scale_factor in the back > branches would affect the existing installation much. It would be > better to have btree indexes not use this parameter while not changing > the contents of meta page. That is, just remove the check related to > vacuum_cleanup_index_scale_factor from _bt_vacuum_needs_cleanup().
That's really what I meant -- we cannot just remove a GUC or storage param in the backbranches, of course (it breaks postgresql.conf, stuff like that). But we can disable GUCs at the code level. > And > I personally prefer to fix the "IndexVacuumInfo.num_heap_tuples is > inaccurate outside of btvacuumcleanup-only VACUUMs" bug separately. I have not decided on my own position on the backbranches. Hopefully there will be clear guidance from other hackers. > Yeah, this argument makes sense to me. The default values of > autovacuum_vacuum_insert_scale_factor/threshold are 0.2 and 1000 > respectively whereas one of vacuum_cleanup_index_scale_factor is 0.1. > It means that in insert-only workload with default settings, > autovacuums triggered by autovacuum_vacuum_insert_scale_factor always > scan the all btree index to update the index statistics. I think most > users would not expect this behavior. As I mentioned above, I think we > can have nbtree not use this parameter or increase the default value > of vacuum_cleanup_index_scale_factor in back branches. It's not just a problem when autovacuum_vacuum_insert_scale_factor triggers a cleanup-only VACUUM in all indexes. It's also a problem with cases where there is a small number of dead tuples by an autovacuum VACUUM triggered by autovacuum_vacuum_insert_scale_factor. It will get index scans done by btbulkdeletes() -- which are more expensive than a VACUUM that only calls btvacuumcleanup(). Of course this is exactly what the patch you're working on for Postgres 14 helps with. It's actually not very different (1 dead tuple and 0 dead tuples are not very different). So it makes sense that we ended up here -- vacuumlazy.c alone should be in control of this stuff, because only vacuumlazy.c has the authority to see that 1 dead tuple and 0 dead tuples should be considered the same thing (or almost the same). So...maybe we can only truly fix the problem in Postgres 14 anyway, and should just accept that? OTOH scanning the indexes for no reason when autovacuum_vacuum_insert_scale_factor triggers an autovacuum VACUUM does seem *particularly* silly. So I don't know what to think. -- Peter Geoghegan
