Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Excerpts from Tom Lane's message of mi may 25 11:47:52 -0400 2011:
>> 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?

> It uses reltuples simply because it was what the original contrib code
> was using. Since pgstat was considerably weaker at the time, reltuples
> might have been the only thing available. It's certainly the case that
> pgstat has improved a lot since autovacuum got in, and some things have
> been revised but not this one.

On reflection I'm hesitant to do this, especially for a backpatched bug
fix, because it would be changing the feedback loop behavior for
autovacuum scheduling. That could have surprising consequences.

>> 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.

> Hmm, interesting idea. This would be done only for toast tables, or all
> tables?

I'm thinking just do it for all. The fact that these numbers don't
necessarily update after a vacuum is certainly surprising in and of
itself, and it did not work that way before the VM patch went in.
I'm concerned about other stuff besides AV not dealing well with
obsolete values.

> At this point I only wonder why we store tuples & pages rather than just
> live tuple density.

It's just for backwards compatibility. I've thought about doing that in
the past, but I don't know what client-side code might be looking at
relpages/reltuples. It's not like collapsing them into one field would
save much, anyway.

regards, tom lane