MIME-Version: 1.0
References: <3bda0d10-0d60-42b7-9600-abe23d54bb16@postgrespro.ru>
 <CAH2-Wz=pCtsB3v42RB5dLnzEn3tQLUJ8fJMn+si-9A8s6v=B1A@mail.gmail.com>
 <CAAKRu_aBthDxatwzR-wQ8bWfYehuxYgAO0g6AMPFyVgdOxfS4g@mail.gmail.com> <CAH2-Wznqbnc8en8+B26obp-pmTKXi_HS_h_yRt4HVtqLOCKxLQ@mail.gmail.com>
In-Reply-To: <CAH2-Wznqbnc8en8+B26obp-pmTKXi_HS_h_yRt4HVtqLOCKxLQ@mail.gmail.com>
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Mon, 2 Sep 2024 15:23:13 -0400
Message-ID: <CAAKRu_YF9UeFnfsgDiFybAss2YQtmEHU+xjngqwmwwyQjCDvxQ@mail.gmail.com>
Subject: Re: PG17 optimizations to vacuum
To: Peter Geoghegan <pg@bowt.ie>
Cc: Pavel Luzanov <p.luzanov@postgrespro.ru>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>, Heikki Linnakangas <hlinnaka@iki.fi>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAAKRu_YF9UeFnfsgDiFybAss2YQtmEHU%2BxjngqwmwwyQjCDvxQ%40mail.gmail.com>
Precedence: bulk

On Mon, Sep 2, 2024 at 1:47=E2=80=AFPM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Sep 2, 2024 at 1:29=E2=80=AFPM Melanie Plageman
> <melanieplageman@gmail.com> wrote:
> > I'll investigate more tomorrow, but based on my initial investigation,
> > there appears to be some interaction related to how much of the
> > relation is in shared buffers after creating the table and updating
> > it. If you set shared_buffers sufficiently high and prewarm the table
> > after the update, master has fewer WAL records reported by vacuum
> > verbose.
>
> Fewer of what specific kind of WAL record?

I would have expected to see no freeze records (since they no longer
exist) and the same number of prune records. However, the overall
number of records that I get for 16 and master is pretty similar. For
some reason I stopped being able to reproduce Pavel's case. I'll work
more on it tomorrow.

This is roughly what I get for records by vacuum. Note that I prefixed
VACUUM with BTREE on master to indicate those records are from index
vacuuming. By default the headesc routine for records emitted by index
vacuuming prints just VACUUM -- perhaps it would be better to prefix
it.

Note that these add up to almost the same thing. I don't know yet why
the number PRUNE_VACUUM_SCAN is different than PRUNE on 16.
PRUNE_VACUUM_SCAN and PRUNE + FREEZE_PAGE on 16 are similar. So, there
must be pages that don't have items being pruned which are being
frozen. I'll need to investigate further.

master
--
 PRUNE_ON_ACCESS             | 6
 PRUNE_VACUUM_SCAN        | 30974
 PRUNE_VACUUM_CLEANUP | 14162
 BTREE_VACUUM                    | 19127


16
--
 PRUNE               | 15504
 FREEZE_PAGE  | 13257
 VACUUM             | 34527


- Melanie