From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
To: David Gould <daveg@sonic.net>
To: pgsql-hackers@lists.postgresql.org, Alina Alexeeva <alexeeva@adobe.com>
To: Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
Date: Wed, 28 Feb 2018 15:55:19 +0300
Message-ID: <fa8122c2-ea7a-ff38-49ac-e6616aec38ea@postgrespro.ru> (raw)
In-Reply-To: <20180117164916.3fdcf2e9@engels>
References: <20180117164916.3fdcf2e9@engels>
Hi David,
I was able to reproduce the problem using your script.
analyze_counts.awk is missing, though.
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#BANL...
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.
--
Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
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-hackers@postgresql.org
Cc: a.kuzmenkov@postgrespro.ru, daveg@sonic.net, alexeeva@adobe.com, lakkurra@adobe.com
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
In-Reply-To: <fa8122c2-ea7a-ff38-49ac-e6616aec38ea@postgrespro.ru>
* 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