Обсуждение: Autovacuum and pg_stat_reset()

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello

We are playing around with different statistics provided by postgresql
to get a better overview of our systems.

Until postgresql 8.2, and the presence of
'stats_reset_on_server_start=on' + pg_postmaster_start_time(), we could
calculate very easy many average values/sec. for our statistics.

With 8.3 and later, it is not possible to do this anymore. But we are
thinking that if we run pg_stat_reset() when the postgres server is
startet we could achieve the same.

Our question is:

Does the use of pg_stat_reset() affects the statistics autovacuum uses
to find out what to do and when this should be done? Can the use of
pg_stat_reset() affect performance in any way?

regards,
- --
 Rafael Martinez, <r.m.guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFKXvU+BhuKQurGihQRAjz6AJ4r7i0aBZU17/u4xw/21q0fOQrWuQCfRj1h
gfe9Z6yT2eZ1cqxfYet19og=
=uf0D
-----END PGP SIGNATURE-----

Rafael Martinez wrote:

> Does the use of pg_stat_reset() affects the statistics autovacuum uses
> to find out what to do and when this should be done?

Yes.  You should do a manual ANALYZE after resetting stats to keep
autovacuum in sync with reality.  (In principle ANALYZE is only
concerned with updating the unrelated stats in pg_statistic, but it also
sends dead/live tuple counts to pgstats which autovacuum relies on.)

> Can the use of pg_stat_reset() affect performance in any way?

Hmm, not sure.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support