Message-Id: <4DDE7EB8020000250003DDA8@gw.wicourts.gov>
Date: Thu, 26 May 2011 16:24:24 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Robert Haas" <robertmhaas@gmail.com>
Cc: "Florian Helmberger" <fh@25th-floor.com>,
	"Alvaro Herrera" <alvherre@commandprompt.com>,
	"Greg Stark" <gsstark@mit.edu>,
	<pgsql-hackers@postgresql.org>,"Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated
	by autovacuum/vacuum
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>
In-Reply-To: <BANLkTikGu30z1rwYeN0NwuO8jMFWHzus+Q@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Robert Haas <robertmhaas@gmail.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
 
>> By storing the ratio and one count you make changes to the
>> other count implied and less visible.  It seems more
>> understandable and less prone to error (to me, anyway) to keep
>> the two "raw" numbers and calculate the ratio -- and when you
>> observe a change in one raw number which you believe should force
>> an adjustment to the other raw number before its next actual
>> value is observed, to comment on why that's a good idea, and do
>> the trivial arithmetic at that time.
> 
> Except that's not how it works.  At least in the case of ANALYZE,
> we *aren't* counting all the tuples in the table.  We're selecting
> a random sample of pages and inferring a tuple density, which we
> then extrapolate to the whole table and store.  Then when we pull
> it back out of the table, we convert it back to a tuple density. 
> The real value we are computing and using almost everywhere is
> tuple density; storing a total number of tuples in the table
> appears to be just confusing the issue.
 
Well, if tuple density is the number which is most heavily used, it
might shave a few nanoseconds doing the arithmetic in enough places
to justify the change, but I'm skeptical.  Basically I'm with Tom on
the fact that this change would store neither more nor less
information (and for that matter would not really change what
information you can easily retrieve); and slightly changing the
manner in which it is stored doesn't solve any of the problems you
assert that it does.
 
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.  (It seems to
me I saw a post giving some reason that would have benefits anyway.)
Except for the full pass during transaction wrap-around protection,
where it could just set a new actual count, autovacuum would be
skipping pages where the bit is set to indicate that all tuples are
visible, right?
 
-Kevin