From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: 濱中 弘和 <hamanaka7767.ita@al.asahi-life.co.jp>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: reltuples decreasing with each autovacuum run
Date: Wed, 12 Feb 2025 20:19:27 +0900
Message-ID: <a05495b4-3e35-49ca-9b0b-b60a698f441e@oss.nttdata.com> (raw)
In-Reply-To: <OS3P286MB2727CE0FBFFDB1E4FA9CD97FC5F22@OS3P286MB2727.JPNP286.PROD.OUTLOOK.COM>
References: <OS3P286MB2727C88B1E05D1785A1B9344C5F22@OS3P286MB2727.JPNP286.PROD.OUTLOOK.COM>
<CAKFQuwb+wCqGOnx_UdJSSsD5c1n9EkLj14GXfVwwzqbHVS1KfQ@mail.gmail.com>
<OS3P286MB2727CE0FBFFDB1E4FA9CD97FC5F22@OS3P286MB2727.JPNP286.PROD.OUTLOOK.COM>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: masao.fujii@oss.nttdata.com, hamanaka7767.ita@al.asahi-life.co.jp, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: reltuples decreasing with each autovacuum run
In-Reply-To: <a05495b4-3e35-49ca-9b0b-b60a698f441e@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox