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