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: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Gould <daveg@sonic.net>, pgsql-hackers@lists.postgresql.org, Alina Alexeeva <alexeeva@adobe.com>, Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Date: 2018-03-01T16:56:20Z
Lists: pgsql-hackers

Attachments

On 01.03.2018 18:09, Tom Lane wrote:

> Ideally, at least, the estimate would remain on-target.

The test shows that under this particular scenario the estimated number 
of tuples grows after each ANALYZE. I tried to explain how this happens 
in the attached pdf. The direct averaging of the number of tuples, not 
using the density, doesn't have this problem, so I suppose it could help.

> 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.

I see that before vac_estimate_reltuples was introduced, the results of 
analyze and vacuum were used directly, without averaging. What I am 
suggesting is to use a different way of averaging, not to remove it.

-- 
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.