To: Robert Haas <robertmhaas@gmail.com>
cc: Greg Stark <gsstark@mit.edu>, Florian Helmberger <fh@25th-floor.com>,
	pgsql-hackers@postgresql.org, Alvaro Herrera <alvherre@commandprompt.com>
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by
	autovacuum/vacuum
In-reply-to: <BANLkTikxM6V5DUsXnDEs84z=oRMbyeoOgA@mail.gmail.com> 
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>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Thu, 26 May 2011 13:08:46 -0400"
Date: Thu, 26 May 2011 16:37:45 -0400
Message-ID: <28973.1306442265@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, May 26, 2011 at 12:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Another thought: Couldn't relation_needs_vacanalyze() just scale up
>>> reltuples by the ratio of the current number of pages in the relation
>>> to relpages, just as the query planner does?

>> Hmm ... that would fix Florian's immediate issue, and it does seem like
>> a good change on its own merits.  But it does nothing for the problem
>> that we're failing to put the best available information into pg_class.
>> 
>> Possibly we could compromise on doing just that much in the back
>> branches, and the larger change for 9.1?

> Do you think we need to worry about the extra overhead of determining
> the current size of every relation as we sweep through pg_class?  It's
> not a lot, but OTOH I think we'd be doing it once a minute... not sure
> what would happen if there were tons of tables.

Ugh ... that is a mighty good point, since the RelationGetNumberOfBlocks
call would have to happen for each table, even the ones we then decide
not to vacuum.  We've already seen people complain about the cost of the
AV launcher once they have a lot of databases, and this would probably
increase it quite a bit.

> Going back to your thought upthread, I think we should really consider
> replacing reltuples with reltupledensity at some point.  I continue to
> be afraid that using a decaying average in this case is going to end
> up overweighting the values from some portion of the table that's
> getting scanned repeatedly, at the expense of other portions of the
> table that are not getting scanned at all.

Changing the representation of the information would change that issue
not in the slightest.  The fundamental point here is that we have new,
possibly partial, information which we ought to somehow merge with the
old, also possibly partial, information.  Storing the data a little bit
differently doesn't magically eliminate that issue.

			regards, tom lane