To: Florian Helmberger <fh@25th-floor.com>
cc: pgsql-hackers@postgreSQL.org, Alvaro Herrera <alvherre@commandprompt.com>
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by
	autovacuum/vacuum
In-reply-to: <4DDCA801.70805@25th-floor.com> 
References: <4DD28F73.6010108@25th-floor.com> <25017.1306291669@sss.pgh.pa.us>
	<4DDCA801.70805@25th-floor.com>
Comments: In-reply-to Florian Helmberger <fh@25th-floor.com>
	message dated "Wed, 25 May 2011 08:56:01 +0200"
Date: Wed, 25 May 2011 11:47:52 -0400
Message-ID: <19738.1306338472@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Florian Helmberger <fh@25th-floor.com> writes:
> On 25.05.11 04:47, Tom Lane wrote:
>> Florian Helmberger<fh@25th-floor.com>  writes:
>>> I'm running a production database with PostgreSQL 9.0.3 (64-bit) on
>>> Debian 5.0.4 and have an issue with a TOAST table and far to frequent
>>> autovacuum runs.
>>> 
>>> I think I've pinned the problem down to the values pg_class holds for
>>> the affected TOAST table:
>>> 
>>> relpages  | 433596
>>> reltuples | 1868538
>>> 
>>> These values are significantly too low. Interestingly, the autovacuum
>>> logout reports the correct values:
>>> 
>>> pages: 0 removed, 34788136 remain
>>> tuples: 932487 removed, 69599038 remain
>>> 
>>> but these aren't stored in pg_class after each run.

>> That's exceedingly weird.  Do the pg_stat_all_tables columns update
>> after autovacuums on that table?

> Yes they do:

I think I see what must be going on here: that toast table must contain
a long run of all-visible-according-to-the-VM pages (which is hardly a
surprising situation).  This results in VACUUM choosing not to update
the pg_class entry:

    /*
     * Update statistics in pg_class.  But only if we didn't skip any pages;
     * the tuple count only includes tuples from the pages we've visited, and
     * we haven't frozen tuples in unvisited pages either.  The page count is
     * accurate in any case, but because we use the reltuples / relpages ratio
     * in the planner, it's better to not update relpages either if we can't
     * update reltuples.
     */
    if (vacrelstats->scanned_all)
        vac_update_relstats(onerel,
                            vacrelstats->rel_pages, vacrelstats->rel_tuples,
                            vacrelstats->hasindex,
                            FreezeLimit);

For an ordinary table this wouldn't be fatal because we'll still do an
ANALYZE from time to time, and that will update the entries with new
(approximate) values.  But we never run ANALYZE on toast tables.

And this would *still* be okay, because as noted in the comment, the
planner only depends on the ratio being roughly correct, not on either
individual value being current.  But autovacuum didn't get the memo;
it thinks it can use reltuples to make decisions.

I can see two basic approaches we might take here:

1. Modify autovacuum to use something from the stats collector, rather
than reltuples, to make its decisions.  I'm not too clear on why
reltuples is being used there anyway; is there some good algorithmic or
statistical reason why AV should be looking at a number from the last
vacuum?

2. Revise the vacuum code so that it doesn't skip updating the pg_class
entries.  We could have it count the number of pages it skipped, rather
than just keeping a bool, and then scale up the rel_tuples count to be
approximately right by assuming the skipped pages have tuple density
similar to the scanned ones.

Thoughts?

			regards, tom lane