Horton

Re: VACUUM and ANALYZE disagreeing on what reltuples means

Tomas Vondra <tomas.vondra@2ndquadrant.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Date: 2017-07-25T17:02:28Z
Lists: pgsql-hackers 


On 7/25/17 5:04 PM, Tom Lane wrote:
> Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
>> On 7/25/17 12:55 AM, Tom Lane wrote:
>>> I think the planner basically assumes that reltuples is the live 
>>> tuple count, so maybe we'd better change VACUUM to get in step.
> 
>> Attached is a patch that (I think) does just that. The disagreement
>> was caused by VACUUM treating recently dead tuples as live, while
>> ANALYZE treats both of those as dead.
> 
>> At first I was worried that this will negatively affect plans in
>> the long-running transaction, as it will get underestimates (due
>> to reltuples not including rows it can see). But that's a problem
>> we already have anyway, you just need to run ANALYZE in the other
>> session.
> 
> This definitely will have some impact on plans, at least in cases
> where there's a significant number of unvacuumable dead tuples. So I
> think it's a bit late for v10, and I wouldn't want to back-patch at
> all. Please add to the next commitfest.
> 

I dare to disagree here, for two reasons.

Firstly, the impact *is* already there, it only takes running ANALYZE. 
Or VACUUM ANALYZE. In both those cases we already end up with 
reltuples=n_live_tup.

Secondly, I personally strongly prefer stable predictable behavior over 
intermittent oscillations between two values. That's a major PITA on 
production, both to investigate and fix.

So people already have this issue, although it only strikes randomly. 
And no way to fix it (well, except for fixing the cleanup, but that may 
not be possible).

It is true we tend to run VACUUM more often than ANALYZE, particularly 
in situations where the cleanup can't proceed - ANALYZE will do it's 
work and VACUUM will be triggered over and over again, so it "wins" this 
way. But I'm not sure that's something we should rely on.


FWIW I personally see this as a fairly annoying bug, and would vote to 
backpatch it, although I understand people might object. But I don't 
quite see a reason not to fix this in v10.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.