Re: Add min and max execute statement time in pg_stat_statement

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, KONDO Mitsumasa <kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com>, Mitsumasa KONDO <kondo(dot)mitsumasa(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Subject: Re: Add min and max execute statement time in pg_stat_statement
Date: 2015-01-06 23:15:21
Message-ID: 54AC6C89.9060701@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


On 12/21/2014 02:50 PM, Andrew Dunstan wrote:
>
> On 12/21/2014 02:12 PM, Tom Lane wrote:
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>> On 12/21/2014 01:23 PM, Alvaro Herrera wrote:
>>>> The point, I think, is that without atomic instructions you have to
>>>> hold
>>>> a lock while incrementing the counters.
>>> Hmm, do we do that now?
>> We already have a spinlock mutex around the counter adjustment code, so
>> I'm not sure why this discussion is being held.
>
> Because Peter suggested we might be able to use atomics. I'm a bit
> dubious that we can for min and max anyway.
>
>>
>>> I would like someone more versed in numerical analysis than me to
>>> tell me how safe using sum of squares actually is in our case.
>> That, on the other hand, might be a real issue. I'm afraid that
>> accumulating across a very long series of statements could lead
>> to severe roundoff error in the reported values, unless we use
>> a method chosen for numerical stability.
>>
>>
>
>
> Right.
>
> The next question along those lines is whether we need to keep a
> running mean or whether that can safely be calculated on the fly. The
> code at <http://www.johndcook.com/blog/standard_deviation/> does keep
> a running mean, and maybe that's required to prevent ill conditioned
> results, although I'm quite sure I see how it would. But this isn't my
> area of expertise.
>

I played it safe and kept a running mean, and since it's there and
useful in itself I exposed it via the function, so there are four new
columns, min_time, max_time, mean_time and stddev_time.

I'll add this to the upcoming commitfest.

cheers

andrew

Attachment Content-Type Size
pg_stat_statements-extrastats.patch text/x-patch 12.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2015-01-06 23:21:39 Re: Possible typo in create_policy.sgml
Previous Message Jim Nasby 2015-01-06 23:15:05 Re: Re: Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs