Re: VACUUM (DISABLE_PAGE_SKIPPING on) - Mailing list pgsql-hackers

On Mon, 16 Nov 2020 at 22:53, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <simon@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 <simon@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/