Message-ID: <a05495b4-3e35-49ca-9b0b-b60a698f441e@oss.nttdata.com>
Date: Wed, 12 Feb 2025 20:19:27 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: reltuples decreasing with each autovacuum run
To: =?UTF-8?B?5r+x5LitIOW8mOWSjA==?= <hamanaka7767.ita@al.asahi-life.co.jp>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
References: 
 <OS3P286MB2727C88B1E05D1785A1B9344C5F22@OS3P286MB2727.JPNP286.PROD.OUTLOOK.COM>
 <CAKFQuwb+wCqGOnx_UdJSSsD5c1n9EkLj14GXfVwwzqbHVS1KfQ@mail.gmail.com>
 <OS3P286MB2727CE0FBFFDB1E4FA9CD97FC5F22@OS3P286MB2727.JPNP286.PROD.OUTLOOK.COM>
Content-Language: en-US
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: 
 <OS3P286MB2727CE0FBFFDB1E4FA9CD97FC5F22@OS3P286MB2727.JPNP286.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/a05495b4-3e35-49ca-9b0b-b60a698f441e%40oss.nttdata.com>
Precedence: bulk


On 2025/02/10 18:47, 濱中 弘和 wrote:
> Dear David J.,
> 
> Thank you very much for your kind words.
> 
> I have created a new bug report.

I’ve updated the email subject to an English version for broader understanding.

> ④ The skipped autovacuum was triggered every 30 seconds. Each time it triggered, pg_class.reltuples decreased.
>    After about 20 triggers, pg_class.reltuples became approximately 500.

As mentioned in the original thread [1], I believe this issue comes from
the logic in vac_estimate_reltuples(). This function estimates reltuples
by summing the number of live tuples found in scanned pages with
an estimate for unscanned pages. While this seems to work well when live tuples
are evenly distributed, it can underestimate reltuples if unscanned pages
have a higher density of live tuples than scanned ones.

Commit 74388a1ac3 seems attempt to address this by keeping the old reltuples
if VACUUM scans <=2% of total pages. However, when VACUUM scans more than 2%,
the estimation may still be inaccurate, leading to a gradual decrease in reltuples.

One idea for this issue is to raise the threshold from 2% to, say, 10%,
though this would only be a partial improvement...

Alternatively, if VACUUM is likely to set incorrect reltuples, another just idea
is to introduce a storage parameter to prevent VACUUM from updating it,
allowing only ANALYZE to make changes.

Regards,

[1] https://postgr.es/m/3dec196d-72a6-447f-ad2e-f2668f2907a9@oss.nttdata.com

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION