Re: Add min and max execute statement time in pg_stat_statement

On 04/07/2014 04:19 PM, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> I just noticed that this patch not only adds min,max,stddev, but it also
>> adds the ability to reset an entry's counters.  This hasn't been
>> mentioned in this thread at all; there has been no discussion on whether
>> this is something we want to have, nor on whether this is the right API.
>> What it does is add a new function pg_stat_statements_reset_time() which
>> resets the min and max values from all function's entries, without
>> touching the stddev state variables nor the number of calls.  Note
>> there's no ability to reset the values for a single function.
> That seems pretty bizarre.  At this late date, the best thing would
> probably be to strip out the undiscussed functionality.  It can get
> resubmitted for 9.5 if there's a real use-case for it.
>



This seems to have fallen through the slats completely. I'd like to 
revive it for 9.5, without the extra function. If someone wants to be 
able to reset stats on a finer grained basis that should probably be a 
separate patch.

One thing that bothered me slightly is that the stddev calculation 
appears to use a rather naive "sum of squares" method of calculation, 
which is known to give ill conditioned or impossible results under some 
pathological conditions: see 
<http://www.johndcook.com/blog/standard_deviation> for some details. I 
don't know if our results are likely to be so skewed as to produce 
pathological results, but ISTM we should probably take the trouble to be 
correct and use Welford's method anyway.

On my blog Peter Geoghegan mentioned something about "atomic 
fetch-and-add" being useful here, but I'm not quite sure what that's 
referring to. Perhaps someone can give me a pointer.

Thoughts?

cheers

andrew