On Tue, Mar 2, 2021 at 1:42 PM Peter Geoghegan <p...@bowt.ie> wrote: > > 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.
Oh ok, I misunderstood. > > > 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. +1 > > > 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? Yeah, I think that's right. Perhaps we can do something so that autovacuums triggered by autovacuum_vacuum_insert_scale_factor are triggered on only a true insert-only case (e.g., by checking if n_dead_tup is 0). > > OTOH scanning the indexes for no reason when > autovacuum_vacuum_insert_scale_factor triggers an autovacuum VACUUM > does seem *particularly* silly. Agreed. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
