pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
To: David Gould <daveg@sonic.net>
Cc: pgsql-hackers@lists.postgresql.org, Alina Alexeeva <alexeeva@adobe.com>
Cc: Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
Date: Thu, 1 Mar 2018 17:25:09 +0300
Message-ID: <4988c54a-1709-a978-9a43-65f8a1e09e94@postgrespro.ru> (raw)
In-Reply-To: <20180228192337.67cc3f99@engels>
References: <20180117164916.3fdcf2e9@engels>
	<fa8122c2-ea7a-ff38-49ac-e6616aec38ea@postgrespro.ru>
	<20180228192337.67cc3f99@engels>

On 01.03.2018 06:23, David Gould wrote:
> 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.

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.

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?

-- 
Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company





view thread (33+ messages)  latest in thread

Message-ID: <4988c54a-1709-a978-9a43-65f8a1e09e94@postgrespro.ru>
Permalink:  ../4988c54a-1709-a978-9a43-65f8a1e09e94@postgrespro.ru/
Also on:    postgresql.org/message-id/4988c54a-1709-a978-9a43-65f8a1e09e94@postgrespro.ru

 · 

reply
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: <4988c54a-1709-a978-9a43-65f8a1e09e94@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