Hello,
On Sun, 2022-01-02 at 13:28 -0800, Andres Freund wrote:
> Hi,
>
> This fails with an assertion failure:
> https://cirrus-ci.com/task/5567540742062080?logs=cores#L55
>
>
Andres, thank you for your test! I've missed it. Fixed in attached
patch v5.
On Wed, 2021-12-22 at 04:25 +0300, Anton A. Melnikov wrote:
>
>
> I completely agree that creating a separate view for these new fields
> is
> the most correct thing to do.
Anton,
I've created a new view named pg_stat_statements_aux. But for now both
views are using the same function pg_stat_statements which returns all
fields. It seems reasonable to me - if sampling solution will need all
values it can query the function.
> Also it might be better to use the term 'auxiliary' and use the same
> approach as for existent similar vars.
Agreed, renamed to auxiliary term.
> So internally it might look something like this:
>
> double aux_min_time[PGSS_NUMKIND];
> double aux_max_time[PGSS_NUMKIND];
> TimestampTz aux_stats_reset;
>
> And at the view level:
> aux_min_plan_time float8,
> aux_max_plan_time float8,
> aux_min_exec_time float8,
> aux_max_exec_time float8,
> aux_stats_reset timestamp with time zone
>
> Functions names might be pg_stat_statements_aux() and
> pg_stat_statements_aux_reset().
>
But it seems "stats_reset" term is not quite correct in this case. The
"stats_since" field holds the timestamp of hashtable entry, but not the
reset time. The same applies to aux_stats_since - for new statement it
holds its entry time, but in case of reset it will hold the reset time.
"stats_reset" name seems a little bit confusing to me.
Attached patch v5
--
Andrei Zubkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From e3140e136bab9d44d7818ed86e1c8ff119936532 Mon Sep 17 00:00:00 2001
From: Andrei Zubkov <zub...@moonset.ru>
Date: Fri, 14 Jan 2022 18:04:53 +0300
Subject: [PATCH] pg_stat_statements: Track statement entry timestamp
This patch adds stats_since column to the pg_stat_statements view. This 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 interval for each statement. Besides it can be used
by sampling solutions to detect situations when a statement was evicted and
returned back between samples.
Such sampling solution could derive any pg_stat_statements statistic value for
an interval between two samples with except of all min/max statistics. To
address this issue this patch adds auxiliary statistics to the pg_stat_statements
function and a new view named pg_stat_statements_aux:
View "public.pg_stat_statements_aux"
Column | Type | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
toplevel | boolean | | |
queryid | bigint | | |
query | text | | |
aux_min_plan_time | double precision | | |
aux_max_plan_time | double precision | | |
aux_min_exec_time | double precision | | |
aux_max_exec_time | double precision | | |
stats_since | timestamp with time zone | | |
aux_stats_since | timestamp with time zone | | |
These auxiliary statistics are resettable independently of statement reset by
the new function pg_stat_statements_aux_reset(userid oid, dbid oid, queryid bigint)
Discussion: https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru
---
contrib/pg_stat_statements/Makefile | 3 +-
.../expected/pg_stat_statements.out | 90 ++++++++
.../pg_stat_statements--1.9--1.10.sql | 122 +++++++++++
.../pg_stat_statements/pg_stat_statements.c | 203 +++++++++++++++++-
.../pg_stat_statements.control | 2 +-
.../sql/pg_stat_statements.sql | 50 +++++
doc/src/sgml/pgstatstatements.sgml | 184 +++++++++++++++-
7 files changed, 640 insertions(+), 14 deletions(-)
create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 7fabd96f38d..edc40c8bbfb 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,8 @@ OBJS = \
pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.8--1.9.sql \
+DATA = pg_stat_statements--1.4.sql \
+ pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0abe34bb6a..34d65bb521c 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -1077,4 +1077,94 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
2
(1 row)
+--
+-- statement timestamps
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT 1 AS "STMTTS1";
+ STMTTS1
+---------
+ 1
+(1 row)
+
+SELECT now() AS ref_ts \gset
+SELECT 1,2 AS "STMTTS2";
+ ?column? | STMTTS2
+----------+---------
+ 1 | 2
+(1 row)
+
+SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+GROUP BY stats_since >= :'ref_ts'
+ORDER BY stats_since >= :'ref_ts';
+ ?column? | count
+----------+-------
+ f | 1
+ t | 1
+(2 rows)
+
+-- auxiliary statistics reset
+-- Is there any statements with different regular and auxiliary values?
+SELECT count(*) > 0 FROM pg_stat_statements(true)
+WHERE
+ ROW(min_plan_time, max_plan_time, min_exec_time, max_exec_time)
+ IS DISTINCT FROM
+ ROW(aux_min_plan_time, aux_max_plan_time, aux_min_exec_time,
+ aux_max_exec_time);
+ ?column?
+----------
+ f
+(1 row)
+
+-- Move reference point
+SELECT now() AS ref_ts \gset
+-- Testing stats_since and aux_stats_since values before and after reset
+SELECT
+ /*pgss_aux_reset_test_query*/
+ bool_and(stats_since <= :'ref_ts') AS all_stats_before_ref,
+ bool_and(aux_stats_since >= :'ref_ts') AS all_aux_stats_after_ref
+FROM pg_stat_statements_aux
+WHERE query NOT LIKE '%pgss_aux_reset_test_query%';
+ all_stats_before_ref | all_aux_stats_after_ref
+----------------------+-------------------------
+ t | f
+(1 row)
+
+-- perform auxiliary statistics reset
+SELECT /*pgss_aux_reset_test_query*/ pg_stat_statements_aux_reset();
+ pg_stat_statements_aux_reset
+------------------------------
+
+(1 row)
+
+SELECT
+ /*pgss_aux_reset_test_query*/
+ bool_and(stats_since <= :'ref_ts') AS all_stats_before_ref,
+ bool_and(aux_stats_since >= :'ref_ts') AS all_aux_stats_after_ref
+FROM pg_stat_statements_aux
+WHERE query NOT LIKE '%pgss_aux_reset_test_query%';
+ all_stats_before_ref | all_aux_stats_after_ref
+----------------------+-------------------------
+ t | t
+(1 row)
+
+-- Is there any statements with different regular and auxiliary
+-- values after aux reset?
+SELECT count(*) > 0 FROM pg_stat_statements(true)
+WHERE
+ ROW(min_plan_time, max_plan_time, min_exec_time, max_exec_time)
+ IS DISTINCT FROM
+ ROW(aux_min_plan_time, aux_max_plan_time, aux_min_exec_time,
+ aux_max_exec_time);
+ ?column?
+----------
+ t
+(1 row)
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
new file mode 100644
index 00000000000..cf59108dc92
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
@@ -0,0 +1,122 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'" to load this file. \quit
+
+/* We need to redefine a view and a function */
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT toplevel bool,
+ OUT queryid bigint,
+ OUT query text,
+ OUT plans int8,
+ OUT total_plan_time float8,
+ OUT min_plan_time float8,
+ OUT max_plan_time float8,
+ OUT mean_plan_time float8,
+ OUT stddev_plan_time float8,
+ OUT aux_min_plan_time float8,
+ OUT aux_max_plan_time float8,
+ OUT calls int8,
+ OUT total_exec_time float8,
+ OUT min_exec_time float8,
+ OUT max_exec_time float8,
+ OUT mean_exec_time float8,
+ OUT stddev_exec_time float8,
+ OUT aux_min_exec_time float8,
+ OUT aux_max_exec_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8,
+ OUT wal_records int8,
+ OUT wal_fpi int8,
+ OUT wal_bytes numeric,
+ OUT stats_since timestamp with time zone,
+ OUT aux_stats_since timestamp with time zone
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT
+ userid,
+ dbid,
+ toplevel,
+ queryid,
+ query,
+ plans,
+ total_plan_time,
+ min_plan_time,
+ max_plan_time,
+ mean_plan_time,
+ stddev_plan_time,
+ calls,
+ total_exec_time,
+ min_exec_time,
+ max_exec_time,
+ mean_exec_time,
+ stddev_exec_time,
+ rows,
+ shared_blks_hit,
+ shared_blks_read,
+ shared_blks_dirtied,
+ shared_blks_written,
+ local_blks_hit,
+ local_blks_read,
+ local_blks_dirtied,
+ local_blks_written,
+ temp_blks_read,
+ temp_blks_written,
+ blk_read_time,
+ blk_write_time,
+ wal_records,
+ wal_fpi,
+ wal_bytes,
+ stats_since
+ FROM pg_stat_statements(true);
+
+CREATE VIEW pg_stat_statements_aux AS
+ SELECT
+ userid,
+ dbid,
+ toplevel,
+ queryid,
+ query,
+ aux_min_plan_time,
+ aux_max_plan_time,
+ aux_min_exec_time,
+ aux_max_exec_time,
+ stats_since,
+ aux_stats_since
+ FROM pg_stat_statements(true);
+
+CREATE FUNCTION pg_stat_statements_aux_reset(IN userid Oid DEFAULT 0,
+ IN dbid Oid DEFAULT 0,
+ IN queryid bigint DEFAULT 0
+)
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_stat_statements_aux_reset_1_10'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+GRANT SELECT ON pg_stat_statements, pg_stat_statements_aux TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 082bfa8f77f..5ad90bad5e8 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -88,7 +88,7 @@ PG_MODULE_MAGIC;
#define PGSS_TEXT_FILE PG_STAT_TMP_DIR "/pgss_query_texts.stat"
/* Magic number identifying the stats file format */
-static const uint32 PGSS_FILE_HEADER = 0x20201227;
+static const uint32 PGSS_FILE_HEADER = 0x20210322;
/* PostgreSQL major version number, changes in which invalidate all entries */
static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
@@ -121,7 +121,8 @@ typedef enum pgssVersion
PGSS_V1_2,
PGSS_V1_3,
PGSS_V1_8,
- PGSS_V1_9
+ PGSS_V1_9,
+ PGSS_V1_10
} pgssVersion;
typedef enum pgssStoreKind
@@ -168,6 +169,10 @@ typedef struct Counters
* msec */
double max_time[PGSS_NUMKIND]; /* maximum planning/execution time in
* msec */
+ double aux_min_time[PGSS_NUMKIND]; /* minimum planning/execution time
+ * since aux reset in msec */
+ double aux_max_time[PGSS_NUMKIND]; /* maximum planning/execution time
+ * since aux reset in msec */
double mean_time[PGSS_NUMKIND]; /* mean planning/execution time in
* msec */
double sum_var_time[PGSS_NUMKIND]; /* sum of variances in
@@ -209,12 +214,14 @@ typedef struct pgssGlobalStats
*/
typedef struct pgssEntry
{
- pgssHashKey key; /* hash key of entry - MUST BE FIRST */
- Counters counters; /* the statistics for this query */
- Size query_offset; /* query text offset in external file */
- int query_len; /* # of valid bytes in query string, or -1 */
- int encoding; /* query text encoding */
- slock_t mutex; /* protects the counters only */
+ pgssHashKey key; /* hash key of entry - MUST BE FIRST */
+ Counters counters; /* the statistics for this query */
+ Size query_offset; /* query text offset in external file */
+ int query_len; /* # of valid bytes in query string, or -1 */
+ int encoding; /* query text encoding */
+ TimestampTz stats_since; /* timestamp of entry allocation moment */
+ TimestampTz aux_stats_since; /* timestamp of last auxiliary statistics reset */
+ slock_t mutex; /* protects the counters only */
} pgssEntry;
/*
@@ -298,10 +305,12 @@ void _PG_fini(void);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7);
+PG_FUNCTION_INFO_V1(pg_stat_statements_aux_reset_1_10);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_8);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
@@ -345,6 +354,7 @@ static char *qtext_fetch(Size query_offset, int query_len,
char *buffer, Size buffer_size);
static bool need_gc_qtexts(void);
static void gc_qtexts(void);
+static void entry_reset_aux(Oid userid, Oid dbid, uint64 queryid);
static void entry_reset(Oid userid, Oid dbid, uint64 queryid);
static char *generate_normalized_query(JumbleState *jstate, const char *query,
int query_loc, int *query_len_p);
@@ -649,6 +659,8 @@ pgss_shmem_startup(void)
/* copy in the actual stats */
entry->counters = temp.counters;
+ entry->stats_since = temp.stats_since;
+ entry->aux_stats_since = temp.aux_stats_since;
}
/* Read global statistics for pg_stat_statements */
@@ -1336,6 +1348,8 @@ pgss_store(const char *query, uint64 queryId,
e->counters.min_time[kind] = total_time;
e->counters.max_time[kind] = total_time;
e->counters.mean_time[kind] = total_time;
+ e->counters.aux_min_time[kind] = total_time;
+ e->counters.aux_max_time[kind] = total_time;
}
else
{
@@ -1355,6 +1369,24 @@ pgss_store(const char *query, uint64 queryId,
e->counters.min_time[kind] = total_time;
if (e->counters.max_time[kind] < total_time)
e->counters.max_time[kind] = total_time;
+
+ /*
+ * Calculate auxiliary min and max time. aux_min == aux_max == 0
+ * means that auxiliary stats reset was happen
+ */
+ if (e->counters.aux_min_time[kind] == e->counters.aux_max_time[kind]
+ && e->counters.aux_max_time[kind] == 0)
+ {
+ e->counters.aux_min_time[kind] = total_time;
+ e->counters.aux_max_time[kind] = total_time;
+ }
+ else
+ {
+ if (e->counters.aux_min_time[kind] > total_time)
+ e->counters.aux_min_time[kind] = total_time;
+ if (e->counters.aux_max_time[kind] < total_time)
+ e->counters.aux_max_time[kind] = total_time;
+ }
}
e->counters.rows += rows;
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
@@ -1385,6 +1417,25 @@ done:
pfree(norm_query);
}
+/*
+ * Reset auxiliary statement statistics corresponding to userid, dbid, and queryid.
+ */
+Datum
+pg_stat_statements_aux_reset_1_10(PG_FUNCTION_ARGS)
+{
+ Oid userid;
+ Oid dbid;
+ uint64 queryid;
+
+ userid = PG_GETARG_OID(0);
+ dbid = PG_GETARG_OID(1);
+ queryid = (uint64) PG_GETARG_INT64(2);
+
+ entry_reset_aux(userid, dbid, queryid);
+
+ PG_RETURN_VOID();
+}
+
/*
* Reset statement statistics corresponding to userid, dbid, and queryid.
*/
@@ -1422,7 +1473,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_3 23
#define PG_STAT_STATEMENTS_COLS_V1_8 32
#define PG_STAT_STATEMENTS_COLS_V1_9 33
-#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_10 39
+#define PG_STAT_STATEMENTS_COLS 39 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1434,6 +1486,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* expected API version is identified by embedding it in the C name of the
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
+Datum
+pg_stat_statements_1_10(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_9(PG_FUNCTION_ARGS)
{
@@ -1567,6 +1629,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_9)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_10:
+ if (api_version != PGSS_V1_10)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1652,6 +1718,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
Counters tmp;
double stddev;
int64 queryid = entry->key.queryid;
+ TimestampTz stats_since;
+ TimestampTz aux_stats_since;
memset(values, 0, sizeof(values));
memset(nulls, 0, sizeof(nulls));
@@ -1720,6 +1788,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
SpinLockAcquire(&e->mutex);
tmp = e->counters;
+ stats_since = e->stats_since;
+ aux_stats_since = e->aux_stats_since;
SpinLockRelease(&e->mutex);
}
@@ -1754,6 +1824,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
else
stddev = 0.0;
values[i++] = Float8GetDatumFast(stddev);
+
+ if (api_version >= PGSS_V1_10) {
+ values[i++] = Float8GetDatumFast(tmp.aux_min_time[kind]);
+ values[i++] = Float8GetDatumFast(tmp.aux_max_time[kind]);
+ }
}
}
values[i++] = Int64GetDatumFast(tmp.rows);
@@ -1791,6 +1866,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
Int32GetDatum(-1));
values[i++] = wal_bytes;
}
+ if (api_version >= PGSS_V1_10)
+ {
+ values[i++] = TimestampTzGetDatum(stats_since);
+ values[i++] = TimestampTzGetDatum(aux_stats_since);
+ }
Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
@@ -1798,6 +1878,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 :
api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 :
+ api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -1913,6 +1994,8 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
entry->query_offset = query_offset;
entry->query_len = query_len;
entry->encoding = encoding;
+ entry->stats_since = GetCurrentTimestamp();
+ entry->aux_stats_since = entry->stats_since;
}
return entry;
@@ -2459,6 +2542,106 @@ gc_fail:
record_gc_qtexts();
}
+/*
+ * Reset auxiliary statistic values of specified entries
+ */
+static void
+entry_reset_aux(Oid userid, Oid dbid, uint64 queryid)
+{
+ HASH_SEQ_STATUS hash_seq;
+ pgssEntry *entry;
+ Counters *entry_counters;
+ pgssHashKey key;
+ TimestampTz aux_stats_reset;
+
+ if (!pgss || !pgss_hash)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_stat_statements must be loaded via shared_preload_libraries")));
+
+ LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
+
+ aux_stats_reset = GetCurrentTimestamp();
+
+ if (userid != 0 && dbid != 0 && queryid != UINT64CONST(0))
+ {
+ /* If all the parameters are available, use the fast path. */
+ memset(&key, 0, sizeof(pgssHashKey));
+ key.userid = userid;
+ key.dbid = dbid;
+ key.queryid = queryid;
+
+ /*
+ * Reset aux stats, starting with the nested-level entry
+ * For min/max values reset sign is min = max = 0
+ */
+ key.toplevel = false;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
+ if (entry) /* found */
+ {
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->aux_max_time[kind] = 0;
+ entry_counters->aux_min_time[kind] = 0;
+ }
+ entry->aux_stats_since = aux_stats_reset;
+ }
+
+ /* Reset aux stats for top level statements */
+ key.toplevel = true;
+
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
+ if (entry) /* found */
+ {
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->aux_max_time[kind] = 0;
+ entry_counters->aux_min_time[kind] = 0;
+ }
+ entry->aux_stats_since = aux_stats_reset;
+ }
+ }
+ else if (userid != 0 || dbid != 0 || queryid != UINT64CONST(0))
+ {
+ /* Reset aux stats for entries corresponding to valid parameters. */
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ {
+ if ((!userid || entry->key.userid == userid) &&
+ (!dbid || entry->key.dbid == dbid) &&
+ (!queryid || entry->key.queryid == queryid))
+ {
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->aux_max_time[kind] = 0;
+ entry_counters->aux_min_time[kind] = 0;
+ }
+ entry->aux_stats_since = aux_stats_reset;
+ }
+ }
+ }
+ else
+ {
+ /* Reset aux stats for all entries. */
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ {
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->aux_max_time[kind] = 0;
+ entry_counters->aux_min_time[kind] = 0;
+ }
+ entry->aux_stats_since = aux_stats_reset;
+ }
+ }
+
+ LWLockRelease(pgss->lock);
+}
+
/*
* Release entries corresponding to parameters passed.
*/
@@ -2488,7 +2671,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
key.dbid = dbid;
key.queryid = queryid;
- /* Remove the key if it exists, starting with the top-level entry */
+ /* Remove the key if it exists, starting with the nested-level entry */
key.toplevel = false;
entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_REMOVE, NULL);
if (entry) /* found */
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 2f1ce6ed507..0747e481383 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track planning and execution statistics of all SQL statements executed'
-default_version = '1.9'
+default_version = '1.10'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index dffd2c8c187..603b4207375 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -442,4 +442,54 @@ SELECT (
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
+--
+-- statement timestamps
+--
+SELECT pg_stat_statements_reset();
+SELECT 1 AS "STMTTS1";
+SELECT now() AS ref_ts \gset
+SELECT 1,2 AS "STMTTS2";
+SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+GROUP BY stats_since >= :'ref_ts'
+ORDER BY stats_since >= :'ref_ts';
+
+-- auxiliary statistics reset
+-- Is there any statements with different regular and auxiliary values?
+SELECT count(*) > 0 FROM pg_stat_statements(true)
+WHERE
+ ROW(min_plan_time, max_plan_time, min_exec_time, max_exec_time)
+ IS DISTINCT FROM
+ ROW(aux_min_plan_time, aux_max_plan_time, aux_min_exec_time,
+ aux_max_exec_time);
+
+-- Move reference point
+SELECT now() AS ref_ts \gset
+
+-- Testing stats_since and aux_stats_since values before and after reset
+SELECT
+ /*pgss_aux_reset_test_query*/
+ bool_and(stats_since <= :'ref_ts') AS all_stats_before_ref,
+ bool_and(aux_stats_since >= :'ref_ts') AS all_aux_stats_after_ref
+FROM pg_stat_statements_aux
+WHERE query NOT LIKE '%pgss_aux_reset_test_query%';
+-- perform auxiliary statistics reset
+SELECT /*pgss_aux_reset_test_query*/ pg_stat_statements_aux_reset();
+
+SELECT
+ /*pgss_aux_reset_test_query*/
+ bool_and(stats_since <= :'ref_ts') AS all_stats_before_ref,
+ bool_and(aux_stats_since >= :'ref_ts') AS all_aux_stats_after_ref
+FROM pg_stat_statements_aux
+WHERE query NOT LIKE '%pgss_aux_reset_test_query%';
+
+-- Is there any statements with different regular and auxiliary
+-- values after aux reset?
+SELECT count(*) > 0 FROM pg_stat_statements(true)
+WHERE
+ ROW(min_plan_time, max_plan_time, min_exec_time, max_exec_time)
+ IS DISTINCT FROM
+ ROW(aux_min_plan_time, aux_max_plan_time, aux_min_exec_time,
+ aux_max_exec_time);
+
DROP EXTENSION pg_stat_statements;
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index bc9d5bdbe3b..0797f324593 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -28,9 +28,11 @@
When <filename>pg_stat_statements</filename> is active, it tracks
statistics across all databases of the server. To access and manipulate
these statistics, the module provides views
- <structname>pg_stat_statements</structname> and
+ <structname>pg_stat_statements</structname>,
+ <structname>pg_stat_statements_aux</structname> and
<structname>pg_stat_statements_info</structname>,
- and the utility functions <function>pg_stat_statements_reset</function> and
+ and the utility functions <function>pg_stat_statements_reset</function>,
+ <function>pg_stat_statements_reset_aux</function> and
<function>pg_stat_statements</function>. These are not available globally but
can be enabled for a specific database with
<command>CREATE EXTENSION pg_stat_statements</command>.
@@ -379,6 +381,15 @@
Total amount of WAL generated by the statement in bytes
</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>
+ Timestamp of statistics gathering start for the statement
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -508,6 +519,154 @@
</para>
</sect2>
+ <sect2>
+ <title>The <structname>pg_stat_statements_aux</structname> View</title>
+
+ <para>
+ The view named <structname>pg_stat_statements_aux</structname> privodes access to
+ auxiliary statistics. These statistics can be reset by the function named
+ <function>pg_stat_statements_reset_aux()</function> independently of whole
+ statistics reset. Auxiliary statistics makes it possible to get accurate
+ min/max statistic values for sampling solutions avoiding overall reset. This
+ view contains one row for each distinct combination of database ID, user ID,
+ query ID and whether it's a top-level statement or not (up to the maximum
+ number of distinct statements that the module can track). The columns of the
+ view are shown in <xref linkend="pgstatstatementsaux-columns"/>.
+ </para>
+
+ <table id="pgstatstatementsaux-columns">
+ <title><structname>pg_stat_statements_aux</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>userid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of user who executed the statement
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dbid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of database in which the statement was executed
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>toplevel</structfield> <type>bool</type>
+ </para>
+ <para>
+ True if the query was executed as a top-level statement
+ (always true if <varname>pg_stat_statements.track</varname> is set to
+ <literal>top</literal>)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>queryid</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Hash code to identify identical normalized queries.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>query</structfield> <type>text</type>
+ </para>
+ <para>
+ Text of a representative statement
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>aux_min_plan_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Minimum time spent planning the statement since
+ <structfield>aux_stats_since</structfield>, in milliseconds
+ (if <varname>pg_stat_statements.track_planning</varname> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>aux_max_plan_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Maximum time spent planning the statement since
+ <structfield>aux_stats_since</structfield>, in milliseconds
+ (if <varname>pg_stat_statements.track_planning</varname> is enabled,
+ otherwise zero)
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>aux_min_exec_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Minimum time spent executing the statement since
+ <structfield>aux_stats_since</structfield>, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>aux_max_exec_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Maximum time spent executing the statement since
+ <structfield>aux_stats_since</structfield>, 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>
+ Timestamp of statistics gathering start for the statement
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>aux_stats_since</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Timestamp of auxiliary statistics gathering start for the statement. In
+ case of a new statement entry value of this field is the same as
+ <structfield>stats_since</structfield>, but after the auxiliary
+ statistics reset this field holds the timestamp of a reset
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2>
<title>The <structname>pg_stat_statements_info</structname> View</title>
@@ -595,6 +754,27 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>pg_stat_statements_reset_aux(userid Oid, dbid Oid, queryid bigint) returns void</function>
+ <indexterm>
+ <primary>pg_stat_statements_reset_aux</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pg_stat_statements_reset_aux</function> function discards only
+ the auxiliary statistics available in
+ <structname>pg_stat_statements_aux</structname> view. This function will
+ update the value of <structfield>aux_stats_since</structfield> with the
+ current timestamp. By default, this function can only be executed by
+ superusers.
+ Access may be granted to others using <command>GRANT</command>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term>
<function>pg_stat_statements(showtext boolean) returns setof record</function>
--
2.30.2
