Re: reltuples decreasing with each autovacuum run

Dear David J,
Mr. Masao Fujii,

I apologize for the time that has passed,
but it would be helpful if you could update me on the current situation to determine the future operational policy.

I understand that this issue has a low priority and is unlikely to be addressed in the near future.

Could you please confirm if this understanding is correct?

Thank you very much.

Sincerely,
________________________________
差出人: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
送信日時: 2025年2月12日 20:19
宛先: 濱中 弘和 <hamanaka7767(dot)ita(at)al(dot)asahi-life(dot)co(dot)jp>; David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
CC: pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
件名: reltuples decreasing with each autovacuum run

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://urldefense.com/v3/__https://postgr.es/m/3dec196d-72a6-447f-ad2e-f2668f2907a9@oss.nttdata.com__;!!OxltQgZmDyZ3!V0u6Rb4ZtxYQzyQrjrR4Wboc3t01blOJyA3T4SoEDra5C29OpxGJ2Rrk3UlFnmGOn6UW3Z8D9OS7_2jfvE2eY137Ek6z3d9pmFT1bE47hQRc$

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
=======================================================================
このE-mailに含まれる情報は、このE-mailの名宛人のみが利用可能な限定された情報です。

万一、当方の誤送信等によりこのE-mailをお受け取りになった場合は、深くお詫び申し上げます。
誠に勝手ではございますが、このメールの印刷、コピー、転送その他
一切のご使用をお控えいただきますようお願いいたします。
お手数をお掛けいたしますが、直ちにこのE-mailを破棄して頂くとともに、誤送信である旨を送信者に通知いただきますようご協力をお願い申し上げます。
=======================================================================
[ex-al]