Re: PG17 optimizations to vacuum

On Mon, Sep 2, 2024 at 1:47 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Sep 2, 2024 at 1:29 PM 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