From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
cc: David Gould <daveg@sonic.net>, 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.
In-reply-to: <4988c54a-1709-a978-9a43-65f8a1e09e94@postgrespro.ru>
References: <20180117164916.3fdcf2e9@engels>
 <fa8122c2-ea7a-ff38-49ac-e6616aec38ea@postgrespro.ru>
 <20180228192337.67cc3f99@engels>
 <4988c54a-1709-a978-9a43-65f8a1e09e94@postgrespro.ru>
Comments: In-reply-to Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
	message dated "Thu, 01 Mar 2018 17:25:09 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <18521.1519916974.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 01 Mar 2018 10:09:34 -0500
Message-ID: <18522.1519916974@sss.pgh.pa.us>
Precedence: bulk

Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru> writes:
> On 01.03.2018 06:23, David Gould wrote:
>> In theory the sample pages analyze uses should represent the whole tabl=
e
>> 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 i=
mprove
>> that, not to disregard it piecemeal.

> Well, that sounds reasonable. But the problem with the moving average =

> calculation remains. Suppose you run vacuum and not analyze. If the =

> updates are random enough, vacuum won't be able to reclaim all the =

> pages, so the number of pages will grow. Again, we'll have the same =

> thing where the number of pages grows, the real number of live tuples =

> stays constant, and the estimated reltuples grows after each vacuum run.

You claimed that before, with no more evidence than this time, and I still
don't follow your argument.  The number of pages may indeed bloat but the
number of live tuples per page will fall.  Ideally, at least, the estimate
would remain on-target.  If it doesn't, there's some other effect that
you haven't explained.  It doesn't seem to me that the use of a moving
average would prevent that from happening.  What it *would* do is smooth
out errors from the inevitable sampling bias in any one vacuum or analyze
run, and that seems like a good thing.

> I did some more calculations on paper to try to understand this. If we =

> average reltuples directly, instead of averaging tuple density, it =

> converges like it should. The error with this density calculation seems =

> to be that we're effectively multiplying the old density by the new =

> number of pages. I'm not sure why we even work with tuple density. We =

> could just estimate the number of tuples based on analyze/vacuum, and =

> then apply moving average to it. The calculations would be shorter, too.=
 =

> What do you think?

I think you're reinventing the way we used to do it.  Perhaps consulting
the git history in the vicinity of this code would be enlightening.

			regards, tom lane