PG17 optimizations to vacuum - Mailing list pgsql-general

Hello,

While playing with optimizations to vacuum in v17 I can't understand
how to measure this one:
"Allow vacuum to more efficiently remove and freeze tuples".

My test script and results:

CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id);
CREATE INDEX t_id ON t(id);
SET maintenance_work_mem = '1MB';
UPDATE t SET id = id + 1;
VACUUM FREEZE VERBOSE t;


v16.4
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 21
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable
removable cutoff: 1675, which was 0 XIDs old when operation ended
new relfrozenxid: 1675, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 473.207 MB/s, avg write rate: 92.511 MB/s
buffer usage: 212718 hits, 267930 misses, 52380 dirtied
WAL usage: 96585 records, 42819 full page images, 198029405 bytes
system usage: CPU: user: 3.17 s, system: 0.48 s, elapsed: 4.42 s
VACUUM

master
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 1
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable
removable cutoff: 950, which was 0 XIDs old when operation ended
new relfrozenxid: 950, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 101.121 MB/s, avg write rate: 120.530 MB/s
buffer usage: 48900 hits, 47749 reads, 56914 dirtied
WAL usage: 125391 records, 46626 full page images, 330547751 bytes
system usage: CPU: user: 2.90 s, system: 0.27 s, elapsed: 3.68 s
VACUUM


I see a perfectly working TID-store optimization.
With reduced maintenance_work_mem it used only one 'vacuuming indexes'
phase instead of 21 in v16.
But I also expected to see a reduction in the number of WAL records
and the total size of the WAL. Instead, WAL numbers have significantly
degraded.

What am I doing wrong?

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com