Re: DB Tuning Notes for comment...

Scott Shattuck <ss(at)technicalpursuit(dot)com> writes:
> Willing to learn here but skipping a vacuum full has caused some issues
> for us. Here's some data from a recent 3 day test run that was done with
> regular vacuums but not vacuum fulls. When running with vacuum full the
> indexes remain in line:

> nsuite-10=# select relname, relpages, reltuples from pg_class where
> relname not like 'pg_%' order by reltuples desc;
> -[ RECORD 1 ]------------------------------
> relname | directory_fullpath_ix
> relpages | 96012
> reltuples | 1.38114e+06
> -[ RECORD 2 ]------------------------------
> relname | directory_pkey
> relpages | 16618
> reltuples | 1.38114e+06
> -[ RECORD 3 ]------------------------------
> relname | directory
> relpages | 23924
> reltuples | 59578
> <snip>

<<blink>> There's no way that the index and table tuple counts should
get that far out of line; in the absence of any concurrent updates,
they should be *equal* (or index < table, if you have a partial index,
which I assume these are not). I would credit the recorded index count
exceeding the recorded table count by the number of tuples inserted/
updated while a (plain) VACUUM is in process on that table --- but this
doesn't look like it meets that situation.

There was a bug a long time ago wherein vacuum would forget to update
pg_class.reltuples for indexes in some cases, but according to the CVS
logs that was fixed before 7.2 release. What version are you running
exactly?

In any case, you seem to be dealing with some kind of bug here. It
might be helpful to look at the output of "vacuum verbose directory"
if you still have it available.

regards, tom lane