From: Noah Misch <noah@leadboat.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: VACUUM and ANALYZE disagreeing on what reltuples means
Date: Wed, 2 Aug 2017 02:01:15 +0000
Message-ID: <20170802020115.GE2611764@rfd.leadboat.com> (raw)
In-Reply-To: <9b594258-57b1-f2cf-f25c-d2caa0a1bc8d@2ndquadrant.com>
References: <16db4468-edfa-830a-f921-39a50498e77e@2ndquadrant.com>
<19990.1500936945@sss.pgh.pa.us>
<3d250597-384e-c5f4-7533-b513e366888f@2ndquadrant.com>
<7087.1500995043@sss.pgh.pa.us>
<9b594258-57b1-f2cf-f25c-d2caa0a1bc8d@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On Tue, Jul 25, 2017 at 07:02:28PM +0200, Tomas Vondra wrote:
> On 7/25/17 5:04 PM, Tom Lane wrote:
> >Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> >>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.
> FWIW I personally see this as a fairly annoying bug, and would vote to
> backpatch it, although I understand people might object.
I tend to agree. If you have a setup that somehow never uses ANALYZE or never
uses VACUUM on a particular table, you might prefer today's (accidental)
behavior. However, the discrepancy arises only on a table that gets dead
tuples, and a table that gets dead tuples will see both VACUUM and ANALYZE
soon enough. It does seem like quite a stretch to imagine someone wanting
plans to depend on which of VACUUM or ANALYZE ran most recently.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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: noah@leadboat.com, tomas.vondra@2ndquadrant.com, tgl@sss.pgh.pa.us
Subject: Re: VACUUM and ANALYZE disagreeing on what reltuples means
In-Reply-To: <20170802020115.GE2611764@rfd.leadboat.com>
* 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