Track statement entry timestamp in contrib/pg_stat_statements

This patch adds 'stats_since' and 'minmax_stats_since' columns to the
pg_stat_statements view and pg_stat_statements() function.  The new min/max
reset mode for the pg_stat_stetments_reset() function is controlled by the
parameter minmax_only.

'stat_since' column is populated with the current timestamp when a new
statement is added to the pg_stat_statements hashtable.  It provides clean
information about statistics collection time intervals for each statement.
Besides it can be used by sampling solutions to detect situations when a
statement was evicted and stored again between samples.

Such a sampling solution could derive any pg_stat_statements statistic values
for an interval between two samples with the exception of all min/max
statistics. To address this issue this patch adds the ability to reset
min/max statistics independently of the statement reset using the new
minmax_only parameter of the pg_stat_statements_reset(userid oid, dbid oid,
queryid bigint, minmax_only boolean) function. The timestamp of such reset
is stored in the minmax_stats_since field for each statement.
pg_stat_statements_reset() function now returns the timestamp of a reset as the
result.

Discussion: https://postgr.es/m/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru
Author: Andrei Zubkov
Reviewed-by: Julien Rouhaud, Hayato Kuroda, Yuki Seino, Chengxi Sun
Reviewed-by: Anton Melnikov, Darren Rush, Michael Paquier, Sergei Kornilov
Reviewed-by: Alena Rybakina, Andrei Lepikhov

1 files changed, 60 insertions, 9 deletions

diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index b66dc7fa30a..acfacf828ce 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -140,9 +140,12 @@
        <structfield>min_plan_time</structfield> <type>double precision</type>
       </para>
       <para>
-       Minimum time spent planning the statement, in milliseconds
-       (if <varname>pg_stat_statements.track_planning</varname> is enabled,
-       otherwise zero)
+       Minimum time spent planning the statement, in milliseconds.
+       This field will be zero if <varname>pg_stat_statements.track_planning</varname>
+       is disabled, or if the counter has been reset using the
+       <function>pg_stat_statements_reset</function> function with the
+       <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
+       and never been planned since.
       </para></entry>
      </row>
 
@@ -151,9 +154,12 @@
        <structfield>max_plan_time</structfield> <type>double precision</type>
       </para>
       <para>
-       Maximum time spent planning the statement, in milliseconds
-       (if <varname>pg_stat_statements.track_planning</varname> is enabled,
-       otherwise zero)
+       Maximum time spent planning the statement, in milliseconds.
+       This field will be zero if <varname>pg_stat_statements.track_planning</varname>
+       is disabled, or if the counter has been reset using the
+       <function>pg_stat_statements_reset</function> function with the
+       <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
+       and never been planned since.
       </para></entry>
      </row>
 
@@ -203,7 +209,11 @@
        <structfield>min_exec_time</structfield> <type>double precision</type>
       </para>
       <para>
-       Minimum time spent executing the statement, in milliseconds
+       Minimum time spent executing the statement, in milliseconds,
+       this field will be zero until this statement
+       is executed first time after reset performed by the
+       <function>pg_stat_statements_reset</function> function with the
+       <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
       </para></entry>
      </row>
 
@@ -212,7 +222,11 @@
        <structfield>max_exec_time</structfield> <type>double precision</type>
       </para>
       <para>
-       Maximum time spent executing the statement, in milliseconds
+       Maximum time spent executing the statement, in milliseconds,
+       this field will be zero until this statement
+       is executed first time after reset performed by the
+       <function>pg_stat_statements_reset</function> function with the
+       <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
       </para></entry>
      </row>
 
@@ -512,6 +526,28 @@
        functions, in milliseconds
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>stats_since</structfield> <type>timestamp with time zone</type>
+      </para>
+      <para>
+       Time at which statistics gathering started for this statement
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>minmax_stats_since</structfield> <type>timestamp with time zone</type>
+      </para>
+      <para>
+       Time at which min/max statistics gathering started for this
+       statement (fields <structfield>min_plan_time</structfield>,
+       <structfield>max_plan_time</structfield>,
+       <structfield>min_exec_time</structfield> and
+       <structfield>max_exec_time</structfield>)
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -713,7 +749,8 @@
   <variablelist>
    <varlistentry>
     <term>
-     <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void</function>
+     <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid
+     bigint, minmax_only boolean) returns timestamp with time zone</function>
      <indexterm>
       <primary>pg_stat_statements_reset</primary>
      </indexterm>
@@ -732,6 +769,20 @@
       If all statistics in the <filename>pg_stat_statements</filename>
       view are discarded, it will also reset the statistics in the
       <structname>pg_stat_statements_info</structname> view.
+      When <structfield>minmax_only</structfield> is <literal>true</literal> only the
+      values of minimun and maximum planning and execution time will be reset (i.e.
+      <structfield>min_plan_time</structfield>, <structfield>max_plan_time</structfield>,
+      <structfield>min_exec_time</structfield> and <structfield>max_exec_time</structfield>
+      fields). The default value for <structfield>minmax_only</structfield> parameter is
+      <literal>false</literal>. Time of last min/max reset performed is shown in
+      <structfield>minmax_stats_since</structfield> field of the
+      <structname>pg_stat_statements</structname> view.
+      This function returns the time of a reset. This time is saved to
+      <structfield>stats_reset</structfield> field of
+      <structname>pg_stat_statements_info</structname> view or to
+      <structfield>minmax_stats_since</structfield> field of the
+      <structname>pg_stat_statements</structname> view if the corresponding reset was
+      actually performed.
       By default, this function can only be executed by superusers.
       Access may be granted to others using <command>GRANT</command>.
      </para>