Date: Wed, 28 Feb 2018 19:23:37 -0800
From: David Gould <daveg@sonic.net>
To: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org, Alina Alexeeva <alexeeva@adobe.com>,
        Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples
 inaccurate.
Message-ID: <20180228192337.67cc3f99@engels>
In-Reply-To: <fa8122c2-ea7a-ff38-49ac-e6616aec38ea@postgrespro.ru>
References: <20180117164916.3fdcf2e9@engels>
	<fa8122c2-ea7a-ff38-49ac-e6616aec38ea@postgrespro.ru>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="MP_/otEbDWxdSYnHOZDJ+EkQnU2"
Precedence: bulk

--MP_/otEbDWxdSYnHOZDJ+EkQnU2
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Wed, 28 Feb 2018 15:55:19 +0300
Alexander Kuzmenkov <a.kuzmenkov@postgrespro.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.gmail.com#BANLkTinL6QuAm_Xf8teRZboG2Mdy3dR_vw@mail.gmail.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@sonic.net
If simplicity worked, the world would be overrun with insects.

--MP_/otEbDWxdSYnHOZDJ+EkQnU2
Content-Type: application/x-awk
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=analyze_counts.awk

IyEvYmluL2F3awoKI3BzcWw6cmVsdHVwbGVzX2FuYWx5emVfYnVnLnNxbDo0NTogSU5GTzogIGFu
YWx5emluZyAicHVibGljLnJlbHR1cGxlc190ZXN0IgojcHNxbDpyZWx0dXBsZXNfYW5hbHl6ZV9i
dWcuc3FsOjQ1OiBJTkZPOiAgInJlbHR1cGxlc190ZXN0Ijogc2Nhbm5lZCAzMDAwIG9mIDI4NDI2
MiBwYWdlcywgY29udGFpbmluZyAxMDYyNjkgbGl2ZSByb3dzIGFuZCAxMDM3NDAgZGVhZCByb3dz
OyAzMDAwIHJvd3MgaW4gc2FtcGxlLCAxMDA2OTQxMyBlc3RpbWF0ZWQgdG90YWwgcm93cwoKZnVu
Y3Rpb24gdW5xdW90ZShzKSB7CiAgICByZXR1cm4gKHMgfiAiXlwiLipcIiQiKSA/IHN1YnN0cihz
LCAyLCBsZW5ndGgocyktMikgOiBzCn0KCkJFR0lOIHsKICAgIHByaW50ICIgICAgICAgICAgICAg
ICAvIGVzdGltYXRlZCByb3dzICAvICAgIC8gICBwYWdlcyAgIC8gICAvc2FtcGxlZCByb3dzLyIK
ICAgIHByaW50ICIgICByZWxuYW1lICAgICAgIGN1cnJlbnQgIHByb3Bvc2VkICAgIHRvdGFsIHNj
YW5uZWQgICAgbGl2ZSAgICBkZWFkIgp9CgojIGNhcHR1cmUgcmVzdWx0IGFuZCBtYXRjaCB3aXRo
IHNhdmVkIHNjaGVtYSBuYW1lCi9JTkZPOiAgIi4qIjogc2Nhbm5lZCAvIHsKICAgIHJlbG5hbWUg
PSB1bnF1b3RlKHN1YnN0cigkMywgMSwgbGVuZ3RoKCQzKS0xKSkKICAgIHNjaGVtYSA9IGFuYWx5
emluZ1tyZWxuYW1lXQogICAgc2Nhbm5lZCA9ICQ1CiAgICBwYWdlcyA9ICQ3CiAgICBsaXZlID0g
JDEwCiAgICBkZWFkID0gJDE0CiMgICBzYW1wbGVkID0gJDE3CiAgICBlc3RpbWF0ZWQgPSAkMjEK
ICAgIG5ld19lc3RpbWF0ZSA9IGludChwYWdlcy9zY2FubmVkICogbGl2ZSkKICAgIHByaW50Zigi
JXMlMTBkJTEwZCAgJTdkJTdkICU4ZCU4ZFxuIiwKICAgICAgICAgIHJlbG5hbWUsIGVzdGltYXRl
ZCwgbmV3X2VzdGltYXRlLCBwYWdlcywgc2Nhbm5lZCwgbGl2ZSwgZGVhZCkKfQo=

--MP_/otEbDWxdSYnHOZDJ+EkQnU2--