DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=ad4qjYJ6bI6EqObfkU5eyOSaAqMtNzOg9Y7vgBhalvR27XjHAsklEr9+crgoDvD4lq
	LFjLB4D25JTJsqvAFc/lALKrcf5JvHUMWnZfkmZ4XY27OSgnHGCb0s0vwGyZC8M2IyWU
	Vx0CoIy1PJFYU0acHU5H7YZv+Wx2ruiExETYg=
MIME-Version: 1.0
In-Reply-To: <22497.1306341699@sss.pgh.pa.us>
References: <4DD28F73.6010108@25th-floor.com> <25017.1306291669@sss.pgh.pa.us>
	<4DDCA801.70805@25th-floor.com> <19738.1306338472@sss.pgh.pa.us>
	<BANLkTi=973Arh-nXLO6YFP7_ZTCeNp-X7w@mail.gmail.com>
	<22497.1306341699@sss.pgh.pa.us>
Date: Wed, 25 May 2011 12:54:28 -0400
Message-ID: <BANLkTim7YNLBBDUT9qXZrO2biuwDZ3kjyg@mail.gmail.com>
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by
	autovacuum/vacuum
From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Florian Helmberger <fh@25th-floor.com>, pgsql-hackers@postgresql.org,
	Alvaro Herrera <alvherre@commandprompt.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, May 25, 2011 at 12:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yeah, I had been thinking about the latter point. =A0We could be
> conservative and just keep the reported tuple density the same (ie,
> update relpages to the new correct value, while setting reltuples so
> that the density ratio doesn't change). =A0But that has its own problems
> when the table contents *do* change. =A0What I'm currently imagining is
> to do a smoothed moving average, where we factor in the new density
> estimate with a weight dependent on the percentage of the table we did
> scan. =A0That is, the calculation goes something like
>
> old_density =3D old_reltuples / old_relpages
> new_density =3D counted_tuples / scanned_pages
> reliability =3D scanned_pages / new_relpages
> updated_density =3D old_density + (new_density - old_density) * reliabili=
ty
> new_reltuples =3D updated_density * new_relpages
>
> We could slow the moving-average convergence even further when
> reliability is small; for instance if you were really paranoid you might
> want to use the square of reliability in line 4. =A0That might be
> overdoing it, though.

I don't know.  That's maybe better, but I'd be willing to wager that
in some cases it will just slow down the rate at which we converge to
a completely incorrect value, while in other cases it'll fail to
update the data when it really has changed.

I am wondering, though, why we're not just inserting a special-purpose
hack for TOAST tables.  Your email seems to indicate that regular
tables are already handled well enough, and certainly if we only whack
around the TOAST behavior it's much less likely to fry anything.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company