On Mon, Oct 21, 2013 at 1:36 PM, Robert Haas <robertmh...@gmail.com> wrote:

> On Mon, Oct 21, 2013 at 4:01 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> > Gavin Flower <gavinflo...@archidevsys.co.nz> writes:
> >>>> If we're going to extend pg_stat_statements, even more than min and
> max
> >>>> I'd like to see the standard deviation in execution time.
> >
> >> How about the 'median', often a lot more useful than the 'arithmetic
> >> mean' (which most people call the 'average').
> >
> > AFAIK, median is impossible to calculate cheaply (in particular, with
> > a fixed amount of workspace).  So this apparently innocent request
> > is actually moving the goalposts a long way, because the space per
> > query table entry is a big concern for pg_stat_statements.
> Yeah, and I worry about min and max not being very usable - once they
> get pushed out to extreme values, there's nothing to drag them back
> toward normality except resetting the stats, and that's not something
> we want to encourage people to do frequently. Of course, averages over
> very long sampling intervals may not be too useful anyway, dunno.

I think the pg_stat_statements_reset() should be done every time you make a
change which you think (or hope) will push the system into a new regime,
which goes for either min/max or for average/stdev.

A histogram would be cool, but it doesn't seem very practical to implement.
 If I really needed that I'd probably set log_min_duration_statement = 0
and mine the log files.  But that means I'd have to wait to accumulate
enough logs once I made that change, then remember to turn it off.

What I'd like most in pg_stat_statements now is the ability to distinguish
which queries have a user grinding their teeth, versus which ones have a
cron job patiently doing a wait4.  I don't know the best way to figure that
out, other than stratify on application_name.  Or maybe a way to
selectively undo the query text normalization, so I could see which
parameters were causing the problem.



Reply via email to