On Mon, 16 Nov 2020 at 22:53, Masahiko Sawada <sawada.m...@gmail.com> wrote: > On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <si...@2ndquadrant.com> wrote:
> I don't think the doc is wrong. If DISABLE_PAGE_SKIPPING is specified, > we not only set aggressive = true but also skip checking visibility > map. For instance, see line 905 and line 963, lazy_scan_heap(). OK, so you're saying that the docs illustrate the true intention of the patch, which I immediately accept since I know you were the author. Forgive me for not discussing it with you first, I thought this was a clear cut case. But that then highlights another area where the docs are wrong... > On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <si...@2ndquadrant.com> wrote: > > The docs do correctly say "Pages where all tuples are known to be > > frozen can always be skipped". Checking the code, lazy_scan_heap() > > comments say > > "we can still skip pages that are all-frozen, since such pages do not > > need freezing". The docs say this: "Pages where all tuples are known to be frozen can always be skipped." Why bother to say that if the feature then ignores that point and scans them anyway? May I submit a patch to remove that sentence? Anyway, we're back to where I started: looking for a user-initiated command option that allows a table to scanned aggressively so that relfrozenxid can be moved forward as quickly as possible. This is what I thought that you were trying to achieve with DISABLE_PAGE_SKIPPING option, my bad. Now David J, above, says this would be VACUUM FREEZE, but I don't think that is right. Setting VACUUM FREEZE has these effects: 1) makes a vacuum aggressive, but it also 2) moves the freeze limit so high that it freezes mostly everything. (1) allows the vacuum to reset relfrozenxid, but (2) actually slows down the scan by making it freeze more blocks than it would do normally. So we have 3 ways to reset relfrozenxid by a user action: VACUUM (DISABLE_PAGE_SKIPPING ON) - scans all blocks, deliberately ignoring the ones it could have skipped. This certainly slows it down. VACUUM (FREEZE ON) - freezes everything in its path, slowing down the scan by writing too many blocks. VACUUM (FULL on) - rewrites table and rebuilds index, so very slow What I think we need is a 4th option which aims to move relfrozenxid forwards as quickly as possible * initiates an aggressive scan, so it does not skip blocks because of busy buffer pins * skip pages that are all-frozen, as we are allowed to do * uses normal freeze limits, so we avoid writing to blocks if possible If we do all 3 of those things, the scan will complete as quickly as possible and reset relfrozenxid quickly. It would make sense to use that in conjunction with index_cleanup=off As an additional optimization, if we do find a row that needs freezing on a data block, we should simply freeze *all* row versions on the page, not just the ones below the selected cutoff. This is justified since writing the block is the biggest cost and it doesn't make much sense to leave a few rows unfrozen on a block that we are dirtying. Thoughts? -- Simon Riggs http://www.EnterpriseDB.com/
