Re: Vacuuming anything zeroes shared table stats

On Thu, Jun 07, 2007 at 11:41:56AM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > The attached patch fixes this.
>
> Wouldn't it be easier to just special-case the shared DB in
> pgstat_vacuum_tabstat?

Thanks; I'll test these patches when I get a chance.

> >> Additionally, in 8.3devel doing anything that queries or modifies a
> >> shared table seems to zero the statistics for all shared tables.
>
> > I'm not sure if this is fixed by the patch; can you verify, or provide a
> > more specific description of the problem?
>
> Seems unlikely that this bug would explain a behavior like that.

Further investigation shows that what really seems to be happening
in 8.3devel is that the statistics for shared tables are reset every
15 seconds when autovacuum is enabled, which it is by default. I
don't observe this phenomenon when autovacuum is disabled. Here's
a test case:

select * from pg_pltemplate;

select seq_scan, idx_scan, now()
from pg_stat_all_tables where relname in ('pg_pltemplate');

Repeat the second select until the statistics for pg_pltemplate
become zero, which should be within 15 seconds. Repeating the
experiment should reveal a 15-second cycle of statistics resets.

In case this behavior is platform-dependent I'm testing on Solaris 9
sparc.

--
Michael Fuhr