Message-ID: <7760d3e3-15cc-4abf-ab33-027008c17d53@iki.fi>
Date: Mon, 2 Sep 2024 23:58:25 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: PG17 optimizations to vacuum
To: Pavel Luzanov <p.luzanov@postgrespro.ru>,
 Melanie Plageman <melanieplageman@gmail.com>, Peter Geoghegan <pg@bowt.ie>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
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>
 <CAAKRu_YF9UeFnfsgDiFybAss2YQtmEHU+xjngqwmwwyQjCDvxQ@mail.gmail.com>
 <7bc8da54-fe4f-4ff2-aa4e-b54fc91df586@postgrespro.ru>
Content-Language: en-US
From: Heikki Linnakangas <hlinnaka@iki.fi>
In-Reply-To: <7bc8da54-fe4f-4ff2-aa4e-b54fc91df586@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/7760d3e3-15cc-4abf-ab33-027008c17d53%40iki.fi>
Precedence: bulk

On 02/09/2024 23:35, Pavel Luzanov wrote:
> On 02.09.2024 22:23, Melanie Plageman wrote:
>> For some reason I stopped being able to reproduce Pavel's case.

I also cannot reproduce this.

> I repeated the test on another computer, but compared master with v15.
> The results are the same. The test can be simplified as follows:
> 
> 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);
> VACUUM FREEZE t;
> UPDATE t SET id = id + 1;
> VACUUM FREEZE VERBOSE t;
> 
> My results (only line with WAL info from the last VACUUM command).
> 
> master:
> WAL usage: 119583 records, 37231 full page images, 272631468 bytes
> 
> v15:
> WAL usage: 96565 records, 47647 full page images, 217144602 bytes

Can you dump the stats with pg_waldump please. Something like:

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;
select pg_current_wal_insert_lsn();   -- <<< PRINT WAL POS BEFORE VACUUM
VACUUM FREEZE VERBOSE t;

And then:

pg_waldump -p data/pg_wal/ -s 1/F4474498 --stats=record

where "1/F4474498" is the position printed by the "SELECT 
pg_current_wal_insert_lsn()" above.


Do you have any non-default settings? "select name, 
current_setting(name), source  from pg_settings where setting <> 
boot_val;" would show that.

-- 
Heikki Linnakangas
Neon (https://neon.tech)