From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Geoghegan <pg@bowt.ie>
cc: postgresql@taljaren.se, pgsql-bugs@lists.postgresql.org,
        Michael Paquier <michael@paquier.xyz>
Subject: Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and
 possible memory issue)
In-reply-to: 
 <CAH2-WzkXmYt-K+pQqMqjFgwU7jiykD3amngd5-jkqQF=gST7Og@mail.gmail.com>
References: <17717-6c50eb1c7d23a886@postgresql.org>
 <3439037.1671130590@sss.pgh.pa.us>
 <CAH2-Wzne4Wgaaruq-VSY2qWNhxcPRqWUbdwVQPkM81TpaHY_kQ@mail.gmail.com>
 <3544447.1671141436@sss.pgh.pa.us>
 <CAH2-WzkXmYt-K+pQqMqjFgwU7jiykD3amngd5-jkqQF=gST7Og@mail.gmail.com>
Comments: In-reply-to Peter Geoghegan <pg@bowt.ie>
	message dated "Thu, 15 Dec 2022 20:39:54 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3672942.1671202149.1@sss.pgh.pa.us>
Date: Fri, 16 Dec 2022 09:49:09 -0500
Message-ID: <3672943.1671202149@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3672943.1671202149%40sss.pgh.pa.us>
Precedence: bulk

Peter Geoghegan <pg@bowt.ie> writes:
> On Thu, Dec 15, 2022 at 1:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Or maybe we could modify things so that "autovacuum = off" doesn't prevent
>> occasional cycles of vac_update_datfrozenxid-and-nothing-else?

> That's what I was thinking of. It seems like a more natural approach
> to me, at least offhand.

Seems worth looking into.  But I suppose the launch frequency would
have to be more often than the current behavior for autovacuum = off,
so it would complicate the logic in that area.

> I have to imagine that the vast majority of individual calls to
> vac_update_datfrozenxid have just about zero chance of updating
> datfrozenxid or datminmxid as things stand.

That is a really good point.  How about teaching VACUUM to track
the oldest original relfrozenxid and relminmxid among the table(s)
it processed, and skip vac_update_datfrozenxid unless at least one
of those matches the database's values?  For extra credit, also
skip if we didn't successfully advance the source rel's value.

This might lead to a fix that solves the OP's problem while not
changing anything fundamental, which would make it reasonable
to back-patch.

			regards, tom lane