On Mon, Sep 6, 2021 at 9:21 AM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote: > #define BYPASS_THRESHOLD_PAGES 0.02 /* i.e. 2% of rel_pages */ > > So up to an additional 2% of all pages can have the all-visible bit > unset with "index_cleanup = auto". > > That is probably not worth worrying, right? I don't think it's worth worrying about. I would say that, since I chose the exact threshold myself. The threshold was a bit arbitrary, of course. Note that Daniel's example had a non-HOT update, even though it's the kind of update that we imagine can use HOT (because it didn't modify an indexed column). He could have ensured a HOT update by lowering heap fill factor, but why should that be necessary if updates are rare anyway? The bypass-index-vacuuming feature may have had a bit of a messaging problem. It was something we usually talked about as being about skipping index vacuuming, because that's what it actually does. However, the feature isn't really about doing less work during VACUUM. It's actually about doing *more* work during VACUUM -- more useful work. Especially setting visibility map bits. But also freezing. Now you can very aggressively tune VACUUM to do these things more often, with no fear of that being way too expensive because of index vacuuming that has only marginal value. The threshold is not so much about any one VACUUM operation -- you have to think about the aggregate effect on the table over time. Most individual tables will never have the new optimization kick in even once, because the workload just couldn't possibly allow it -- the 2% threshold is vastly exceeded every single time. The cases that it actually applies to are pretty much insert-only tables, perhaps with some HOT updates. 100% clean inserts are probably very rare in the real world. I believe that it's *vastly* more likely that such a table will have pages that are ~98%+ free of LP_DEAD line pointers in heap pages (i.e., the thing that BYPASS_THRESHOLD_PAGES applies to). To get to 100% you cannot allow even one single insert transaction to abort since the last VACUUM. If you assume that BYPASS_THRESHOLD_PAGES is actually too low for your workload (which is the opposite problem), then it doesn't matter very much. The feature as coded should still have the desired effect of skipping index vacuuming in *most* cases where it's unnecessary (however you happen to define "unnecessary") -- the number of pages with LP_DEAD items will naturally increase over time without index vacuuming, until the threshold is crossed. Maybe still-unnecessary index vacuuming will still take place in 1 out of 5 cases with the feature. This is still much better than 5 out of 5. More importantly, you can now aggressively tune vacuuming without noticeably increasing the number of individual vacuums that still have the problem of unnecessary index vacuuming. So if you go from 5 vacuums per day to 20 through tuning alone, the number of vacuum operations that do unnecessary index vacuuming doesn't increase at all (except perhaps due to rounding effects). -- Peter Geoghegan
