March 2nd, 2011 by depesz | Tags: , , , , , | Comments Off on Waiting for 9.1 – stats reset tracking
Did it help? If yes - maybe you can help me?

On 10th of February, Magnus Hagander committed patch:

Track last time for statistics reset on databases and bgwriter
 
Tracks one counter for each database, which is reset whenever
the statistics for any individual object inside the database is
reset, and one counter for the background writer.
 
Tomas Vondra, reviewed by Greg Smith

PostgreSQL has a lot of *stat* views, but the problem is that we can never be sure what is the time that they accumulated values since.

So, I can see that some table had 2.5 million index scans, but I don't know, just by looking at it, if it happened overnight, or it took 2 months.

Now, thanks to this patch, we have 2 simple fields, one in pg_stat_bgwriter, and one in pg_stat_database, which show when the stats were last reset:

$ select checkpoints_timed,stats_reset from pg_stat_bgwriter ;
 checkpoints_timed |          stats_reset
-------------------+-------------------------------
                18 | 2011-03-02 13:04:44.373104+01
(1 row)
 
$ select datname, stats_reset from pg_stat_database;
  datname   |          stats_reset
------------+-------------------------------
 template0  | [null]
 postgres   | 2011-03-02 13:04:45.074693+01
 depesz     | 2011-03-02 13:04:50.347928+01
 mailsystem | 2011-03-02 13:04:50.444534+01
 pgdba      | 2011-03-02 13:04:52.363636+01
 q          | 2011-03-02 13:04:56.899499+01
 regression | 2011-03-02 13:05:00.246407+01
 template1  | 2011-03-02 13:04:46.944505+01
(8 rows)

It still doesn't tell us the whole story, since table could have been dropped/recreated since the stats reset, but at the very least you will now have good ballpark when simply looking into existing databases.

Sorry, comments for this post are disabled.