Re: [Patch] Add a reset_computed_values function in pg_stat_statements

Em dom, 1 de set de 2019 às 06:04, Pierre Ducroquet
<p(dot)psql(at)pinaraf(dot)info> escreveu:
>
> Hello
>
> pg_stat_statements is a great tool to track performance issue in live
> databases, especially when adding interfaces like PoWA on top of it.
> But so far, tools like PoWA can not track the min_time, max_time, mean_time
> and sum_var_time of queries : these statistics are cumulated over time,
> fetching points in time would be of little to no use, especially when looking
> at the impact of a DDL change.
> This patch thus introduces a simple pg_stat_statements_reset_computed_values
> function that reset the computed statistics, leaving all other information
> alive, thus allowing the aforementioned scenario.
>
Pierre, I don't understand why you want to reset part of the statement
statistics. If you want the minimal query time every week, reset all
statistics of that statement (v12 or later). Postgres provides
histogram metrics (min, max, mean, stddev). AFAICS you want a metric
type called timer (combination of histogram and meter). For example,
calls, sum, min, max, mean, standard deviation will be calculated each
hour. If I were to add a new metric to pg_stat_statements, it would be
last_time. Compare histogram metrics with the last statement time is
useful to check if a certain optimization was effective.

Talking about your patch, math is wrong. If you don't reset
total_time, all computed values will be incorrect. You are changing
the actual meaning of those metrics and I think it is unacceptable
because it will break applications. Instead, you should provide (i)
new counters or (ii) add GUC to control this behavior. It is a
trade-off between incompatibility and too many metrics. Though I
wouldn't like to break compatibility (as I said you can always reset
all statement statistics). Why don't you provide a
pg_stat_statements_reset_computed_values(userid Oid, dbid Oid, queryid
bigint)? You forgot to provide documentation for the new function. You
should revoke pg_stat_statements_reset_computed_values from PUBLIC.

Regards,

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento