Content-Type: multipart/alternative;
 boundary="------------WVdevr0MSlGu0Nd0zwn6nLwu"
Message-ID: <7bc8da54-fe4f-4ff2-aa4e-b54fc91df586@postgrespro.ru>
Date: Mon, 2 Sep 2024 23:35:04 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: PG17 optimizations to vacuum
To: Melanie Plageman <melanieplageman@gmail.com>, Peter Geoghegan <pg@bowt.ie>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>,
 Heikki Linnakangas <hlinnaka@iki.fi>
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>
Content-Language: en-US, ru-RU
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <CAAKRu_YF9UeFnfsgDiFybAss2YQtmEHU+xjngqwmwwyQjCDvxQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/7bc8da54-fe4f-4ff2-aa4e-b54fc91df586%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------WVdevr0MSlGu0Nd0zwn6nLwu
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

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


If it helps, without creating index on id column, the numbers will be
much closer:

master:
WAL usage: 78502 records, 22090 full page images, 196215494 bytes

v15:
WAL usage: 77437 records, 30872 full page images, 152080268 bytes

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

--------------WVdevr0MSlGu0Nd0zwn6nLwu
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 02.09.2024 22:23, Melanie Plageman wrote:<br>
    <blockquote type="cite"
cite="mid:CAAKRu_YF9UeFnfsgDiFybAss2YQtmEHU+xjngqwmwwyQjCDvxQ@mail.gmail.com"><span
      style="white-space: pre-wrap">
</span>
      <pre class="moz-quote-pre" wrap="">For some reason I stopped being able to reproduce Pavel's case.</pre>
    </blockquote>
    <pre>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


If it helps, without creating index on id column, the numbers will be
much closer:

master:
WAL usage: 78502 records, 22090 full page images, 196215494 bytes

v15:
WAL usage: 77437 records, 30872 full page images, 152080268 bytes
</pre>
    <pre class="moz-signature" cols="72">-- 
Pavel Luzanov
Postgres Professional: <a class="moz-txt-link-freetext" href="https://postgrespro.com">https://postgrespro.com</a></pre>
  </body>
</html>

--------------WVdevr0MSlGu0Nd0zwn6nLwu--