To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: "Robert Haas" <robertmhaas@gmail.com>,
	"Florian Helmberger" <fh@25th-floor.com>,
	"Alvaro Herrera" <alvherre@commandprompt.com>,
	"Greg Stark" <gsstark@mit.edu>, pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by
	autovacuum/vacuum
In-reply-to: <4DDE7EB8020000250003DDA8@gw.wicourts.gov> 
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>
	<BANLkTi=jcKSU5zR+JG2BvfLNCtezC9sb+g@mail.gmail.com>
	<15043.1306423500@sss.pgh.pa.us>
	<BANLkTikrCaoZCVrda6SNCUQFiYvvZX75BA@mail.gmail.com>
	<25097.1306426982@sss.pgh.pa.us>
	<BANLkTikxM6V5DUsXnDEs84z=oRMbyeoOgA@mail.gmail.com>
	<4DDE475B020000250003DD63@gw.wicourts.gov>
	<BANLkTikTbZ8gibj+X++vkdqkxxJCU6trWw@mail.gmail.com>
	<4DDE5033020000250003DD71@gw.wicourts.gov>
	<BANLkTikGu30z1rwYeN0NwuO8jMFWHzus+Q@mail.gmail.com>
	<4DDE7EB8020000250003DDA8@gw.wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Thu, 26 May 2011 16:24:24 -0500"
Date: Thu, 26 May 2011 17:50:00 -0400
Message-ID: <9007.1306446600@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> When we prune or vacuum a page, I don't suppose we have enough
> information about that page's previous state to calculate a tuple
> count delta, do we?  That would allow a far more accurate number to
> be maintained than anything suggested so far, as long as we tweak
> autovacuum to count inserts toward the need to vacuum.

Well, that was the other direction that was suggested upthread: stop
relying on reltuples at all, but use the stats collector's counts.
That might be a good solution in the long run, but there are some
issues:

1. It's not clear how using a current count, as opposed to
time-of-last-vacuum count, would affect the behavior of the autovacuum
control logic.  At first glance I think it would break it, since the
basic logic there is "how much of the table changed since it was last
vacuumed?".  Even if the equations could be modified to still work,
I remember enough feedback control theory from undergrad EE to think that
this is something to be seriously scared of tweaking without extensive
testing.  IMO it is far more risky than what Robert is worried about.

2. You still have the problem that we're exposing inaccurate (or at
least less accurate than they could be) counts to the planner and to
onlooker clients.  We could change the planner to also depend on the
stats collector instead of reltuples, but at that point you just removed
the option for people to turn off the stats collector.  The implications
for plan stability might be unpleasant, too.

So that's not a direction I want to go without a significant amount
of work and testing.

			regards, tom lane