Horton

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

Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>

View thread View on postgr.es
From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
To: David Gould <daveg@sonic.net>, pgsql-hackers@lists.postgresql.org, Alina Alexeeva <alexeeva@adobe.com>, Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Date: 2018-02-28T12:55:19Z
Lists: pgsql-hackers 
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#BANLkTinL6QuAm_Xf8teRZboG2Mdy3dR_vw@mail.gmail.com

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

Commits

  1. Sync up our various ways of estimating pg_class.reltuples.

  2. Fix tuple counting in SP-GiST index build.

  3. Fix errors in contrib/bloom index build.

  4. When updating reltuples after ANALYZE, just extrapolate from our sample.

  5. Avoid holding AutovacuumScheduleLock while rechecking table statistics.