Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.

On Wed, 28 Feb 2018 15:55:19 +0300
Alexander Kuzmenkov <a(dot)kuzmenkov(at)postgrespro(dot)ru> wrote:

> Hi David,
>
> I was able to reproduce the problem using your script.
> analyze_counts.awk is missing, though.

Attached now I hope. I think I also added it to the commitfest page.

> The idea of using the result of ANALYZE as-is, without additional
> averaging, was discussed when vac_estimate_reltuples() was introduced
> originally. Ultimately, it was decided not to do so. You can find the
> discussion in this thread:
> https://www.postgresql.org/message-id/flat/BANLkTinL6QuAm_Xf8teRZboG2Mdy3dR_vw%40mail(dot)gmail(dot)com#BANLkTinL6QuAm_Xf8teRZboG2Mdy3dR_vw(at)mail(dot)gmail(dot)com

Well that was a long discussion. I'm not sure I would agree that there was a
firm conclusion on what to do about ANALYZE results. There was some
recognition that the case of ANALYZE is different than VACUUM and that is
reflected in the original code comments too. However the actual code ended up
being the same for both ANALYZE and VACUUM. This patch is about that.

See messages:
https://www.postgresql.org/message-id/BANLkTimVhdO_bKQagRsH0OLp7MxgJZDryg%40mail.gmail.com
https://www.postgresql.org/message-id/BANLkTimaDj950K-298JW09RrmG0eJ_C%3DqQ%40mail.gmail.com
https://www.postgresql.org/message-id/28116.1306609295%40sss.pgh.pa.us

> The core problem here seems to be that this calculation of moving
> average does not converge in your scenario. It can be shown that when
> the number of live tuples is constant and the number of pages grows, the
> estimated number of tuples will increase at each step. Do you think we
> can use some other formula that would converge in this scenario, but
> still filter the noise in ANALYZE results? I couldn't think of one yet.

Besides the test data generated with the script I have parsed the analyze
verbose output for several large production systems running complex
applications and have found that for tables larger than the statistics
sample size (300*default_statistics_target) the row count you can caculate
from (pages/sample_pages) * live_rows is pretty accurate, within a few
percent of the value from count(*).

In theory the sample pages analyze uses should represent the whole table
fairly well. We rely on this to generate pg_statistic and it is a key
input to the planner. Why should we not believe in it as much only for
reltuples? If the analyze sampling does not work, the fix would be to improve
that, not to disregard it piecemeal.

My motivation is that I have seen large systems fighting mysterious run-away
bloat for years no matter how aggressively autovacuum is tuned. The fact that
an inflated reltuples can cause autovacuum to simply ignore tables forever
seems worth fixing.

-dg

--
David Gould daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.