Re: VACUUM and ANALYZE disagreeing on what reltuples means

On 7/25/17 5:04 PM, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)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