Re: Clearing global statistics

2010/1/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> writes:
>> Is there any chance of implementing a way of knowing when was the last
>> time statistics delivered via pg_stat_* were reset?
>
> Actually, that brings up a more general question: what's with the
> enthusiasm for clearing statistics *at all*?  ISTM that's something
> you should do only in dire emergencies, like the collector went
> haywire and has now got a bunch of garbage numbers.  The notion of
> resetting subsets of the stats seems even more dubious, because now
> you have numbers that aren't mutually comparable.  So I fail to
> understand why the desire to expend valuable development time on
> any of this.

s/collector/application/ and you've got one reason.

Example, that I hit the other day. Examining pg_stat_user_functions
shows one function taking much longer than you'd expect. Called about
6 million times, total time about 7 days spent. Reason turned out to
be a missing index. Without clearing the stats, it'll take a *long*
time before the average goes down enough to make it possible to use
the simple SELECT self_time/calls FROM pg_stat_user_functions WHERE...
to monitor. Sure, if you have a system that graphs it, it'll update
properly, but for the quick manual checks, that view suddenly becomes
a lot less ueful.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/