Hi, I've rebased this patch so that it can be applied after 57d6aea00fc.
v14 attached -- regards, Andrei
From 6c541f3001d952e72e5d865fde09de3fb4f36d10 Mon Sep 17 00:00:00 2001 From: Andrei Zubkov <zub...@moonset.ru> Date: Fri, 8 Apr 2022 23:12:55 +0300 Subject: [PATCH] pg_stat_statements: Track statement entry timestamp 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 interval 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 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. 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 a result. Discussion: https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru --- .../expected/oldextversions.out | 118 +++--- .../expected/pg_stat_statements.out | 361 +++++++++++++----- .../pg_stat_statements--1.9--1.10.sql | 16 +- .../pg_stat_statements/pg_stat_statements.c | 128 +++++-- .../pg_stat_statements/sql/oldextversions.sql | 7 +- .../sql/pg_stat_statements.sql | 149 +++++++- doc/src/sgml/pgstatstatements.sgml | 66 +++- 7 files changed, 637 insertions(+), 208 deletions(-) diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out index efb2049ecff..0634d73bc03 100644 --- a/contrib/pg_stat_statements/expected/oldextversions.out +++ b/contrib/pg_stat_statements/expected/oldextversions.out @@ -138,7 +138,7 @@ SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc); -- New function pg_stat_statement_info, and new function -- and view for pg_stat_statements introduced in 1.9 -AlTER EXTENSION pg_stat_statements UPDATE TO '1.9'; +ALTER EXTENSION pg_stat_statements UPDATE TO '1.9'; SELECT pg_get_functiondef('pg_stat_statements_info'::regproc); pg_get_functiondef ------------------------------------------------------------------------------------------------------------------------- @@ -194,55 +194,79 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements; t (1 row) +\d pg_stat_statements_info + View "public.pg_stat_statements_info" + Column | Type | Collation | Nullable | Default +-------------+--------------------------+-----------+----------+--------- + dealloc | bigint | | | + stats_reset | timestamp with time zone | | | + +SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc); + pg_get_functiondef +-------------------------------------------------------------------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0)+ + RETURNS void + + LANGUAGE c + + PARALLEL SAFE STRICT + + AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_7$function$ + + +(1 row) + +SET SESSION AUTHORIZATION pg_read_all_stats; +SELECT pg_stat_statements_reset(); +ERROR: permission denied for function pg_stat_statements_reset +RESET SESSION AUTHORIZATION; -- New functions and views for pg_stat_statements in 1.10 AlTER EXTENSION pg_stat_statements UPDATE TO '1.10'; \d pg_stat_statements - View "public.pg_stat_statements" - Column | Type | Collation | Nullable | Default -------------------------+------------------+-----------+----------+--------- - userid | oid | | | - dbid | oid | | | - toplevel | boolean | | | - queryid | bigint | | | - query | text | | | - plans | bigint | | | - total_plan_time | double precision | | | - min_plan_time | double precision | | | - max_plan_time | double precision | | | - mean_plan_time | double precision | | | - stddev_plan_time | double precision | | | - calls | bigint | | | - total_exec_time | double precision | | | - min_exec_time | double precision | | | - max_exec_time | double precision | | | - mean_exec_time | double precision | | | - stddev_exec_time | double precision | | | - rows | bigint | | | - shared_blks_hit | bigint | | | - shared_blks_read | bigint | | | - shared_blks_dirtied | bigint | | | - shared_blks_written | bigint | | | - local_blks_hit | bigint | | | - local_blks_read | bigint | | | - local_blks_dirtied | bigint | | | - local_blks_written | bigint | | | - temp_blks_read | bigint | | | - temp_blks_written | bigint | | | - blk_read_time | double precision | | | - blk_write_time | double precision | | | - temp_blk_read_time | double precision | | | - temp_blk_write_time | double precision | | | - wal_records | bigint | | | - wal_fpi | bigint | | | - wal_bytes | numeric | | | - jit_functions | bigint | | | - jit_generation_time | double precision | | | - jit_inlining_count | bigint | | | - jit_inlining_time | double precision | | | - jit_optimization_count | bigint | | | - jit_optimization_time | double precision | | | - jit_emission_count | bigint | | | - jit_emission_time | double precision | | | + View "public.pg_stat_statements" + Column | Type | Collation | Nullable | Default +------------------------+--------------------------+-----------+----------+--------- + userid | oid | | | + dbid | oid | | | + toplevel | boolean | | | + queryid | bigint | | | + query | text | | | + plans | bigint | | | + total_plan_time | double precision | | | + min_plan_time | double precision | | | + max_plan_time | double precision | | | + mean_plan_time | double precision | | | + stddev_plan_time | double precision | | | + calls | bigint | | | + total_exec_time | double precision | | | + min_exec_time | double precision | | | + max_exec_time | double precision | | | + mean_exec_time | double precision | | | + stddev_exec_time | double precision | | | + rows | bigint | | | + shared_blks_hit | bigint | | | + shared_blks_read | bigint | | | + shared_blks_dirtied | bigint | | | + shared_blks_written | bigint | | | + local_blks_hit | bigint | | | + local_blks_read | bigint | | | + local_blks_dirtied | bigint | | | + local_blks_written | bigint | | | + temp_blks_read | bigint | | | + temp_blks_written | bigint | | | + blk_read_time | double precision | | | + blk_write_time | double precision | | | + temp_blk_read_time | double precision | | | + temp_blk_write_time | double precision | | | + wal_records | bigint | | | + wal_fpi | bigint | | | + wal_bytes | numeric | | | + jit_functions | bigint | | | + jit_generation_time | double precision | | | + jit_inlining_count | bigint | | | + jit_inlining_time | double precision | | | + jit_optimization_count | bigint | | | + jit_optimization_time | double precision | | | + jit_emission_count | bigint | | | + jit_emission_time | double precision | | | + stats_since | timestamp with time zone | | | + minmax_stats_since | timestamp with time zone | | | SELECT count(*) > 0 AS has_data FROM pg_stat_statements; has_data diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index 8f7f93172a2..177e3eb7836 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -4,10 +4,10 @@ CREATE EXTENSION pg_stat_statements; -- SET pg_stat_statements.track_utility = FALSE; SET pg_stat_statements.track_planning = TRUE; -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) SELECT 1 AS "int"; @@ -134,7 +134,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT $1 AS "int" | 2 | 2 SELECT $1 AS i UNION SELECT $2 ORDER BY i | 1 | 2 SELECT $1 || $2 | 1 | 1 - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 WITH t(f) AS ( +| 1 | 2 VALUES ($1), ($2) +| | @@ -146,10 +146,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- CRUD: INSERT SELECT UPDATE DELETE on test table -- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) -- utility "create table" should not be shown @@ -231,7 +231,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT * FROM test ORDER BY a | 1 | 12 SELECT * FROM test WHERE a > $1 ORDER BY a | 2 | 4 SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5) | 1 | 8 - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 UPDATE test SET b = $1 WHERE a = $2 | 6 | 6 UPDATE test SET b = $1 WHERE a > $2 | 1 | 3 @@ -240,10 +240,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics -- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) -- utility "create table" should not be shown @@ -266,7 +266,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C"; DELETE FROM pgss_test WHERE a > $1 | 1 | 1 | t | t | t DROP TABLE pgss_test | 1 | 0 | t | t | f INSERT INTO pgss_test VALUES(generate_series($1, $2), $3) | 1 | 10 | t | t | t - SELECT pg_stat_statements_reset() | 1 | 1 | f | f | f + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 | f | f | f SELECT query, calls, rows, +| 0 | 0 | f | f | t wal_bytes > $1 as wal_bytes_generated, +| | | | | wal_records > $2 as wal_records_generated, +| | | | | @@ -280,10 +280,10 @@ FROM pg_stat_statements ORDER BY query COLLATE "C"; -- pg_stat_statements.track = none -- SET pg_stat_statements.track = 'none'; -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) SELECT 1 AS "one"; @@ -307,10 +307,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- pg_stat_statements.track = top -- SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) DO LANGUAGE plpgsql $$ @@ -360,7 +360,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT $1::TEXT | 1 | 1 SELECT PLUS_ONE($1) | 2 | 2 SELECT PLUS_TWO($1) | 2 | 2 - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 (5 rows) @@ -368,10 +368,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- pg_stat_statements.track = all -- SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) -- we drop and recreate the functions to avoid any caching funnies @@ -419,7 +419,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT (i + $2)::INTEGER LIMIT $3 | 2 | 2 SELECT PLUS_ONE($1) | 2 | 2 SELECT PLUS_TWO($1) | 2 | 2 - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 (6 rows) @@ -428,10 +428,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- CREATE TABLE pgss_a (id integer PRIMARY KEY); CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a); -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) -- control query @@ -507,7 +507,7 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a 1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" - 1 | SELECT pg_stat_statements_reset() + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (12 rows) DROP TABLE pgss_a, pgss_b CASCADE; @@ -515,10 +515,10 @@ DROP TABLE pgss_a, pgss_b CASCADE; -- utility commands -- SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) SELECT 1; @@ -549,7 +549,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; DROP TABLE IF EXISTS test | 3 | 0 DROP TABLE test | 1 | 0 SELECT $1 | 1 | 1 - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 (9 rows) @@ -558,10 +558,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW, -- REFRESH MATERIALIZED VIEW and SELECT INTO -- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a; @@ -614,17 +614,17 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE FETCH NEXT pgss_cursor | 0 | 1 | 1 REFRESH MATERIALIZED VIEW pgss_matv | 0 | 1 | 13 SELECT generate_series(1, 10) c INTO pgss_select_into | 0 | 1 | 10 - SELECT pg_stat_statements_reset() | 0 | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 0 | 1 | 1 SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0 (13 rows) -- -- Track user activity and reset them -- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) CREATE ROLE regress_stats_user1; @@ -667,7 +667,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT $1 AS "ONE" | 1 | 1 SELECT $1+$2 AS "TWO" | 1 | 1 SELECT $1+$2 AS "TWO" | 1 | 1 - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 SET ROLE regress_stats_user1 | 1 | 0 SET ROLE regress_stats_user2 | 1 | 0 @@ -676,10 +676,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- Don't reset anything if any of the parameter is NULL -- -SELECT pg_stat_statements_reset(NULL); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset(NULL) IS NOT NULL AS t; + t +--- + f (1 row) SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; @@ -692,8 +692,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT $1 AS "ONE" | 1 | 1 SELECT $1+$2 AS "TWO" | 1 | 1 SELECT $1+$2 AS "TWO" | 1 | 1 - SELECT pg_stat_statements_reset($1) | 1 | 1 - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 11 SET ROLE regress_stats_user1 | 1 | 0 SET ROLE regress_stats_user2 | 1 | 0 @@ -707,10 +707,10 @@ SELECT pg_stat_statements_reset( (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'), (SELECT d.oid FROM pg_database As d where datname = current_database()), (SELECT s.queryid FROM pg_stat_statements AS s - WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1)); - pg_stat_statements_reset --------------------------- - + WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1)) IS NOT NULL AS t; + t +--- + t (1 row) SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; @@ -726,9 +726,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| | (SELECT d.oid FROM pg_database As d where datname = current_database()),+| | (SELECT s.queryid FROM pg_stat_statements AS s +| | - WHERE s.query = $2 LIMIT $3)) | | - SELECT pg_stat_statements_reset($1) | 1 | 1 - SELECT pg_stat_statements_reset() | 1 | 1 + WHERE s.query = $2 LIMIT $3)) IS NOT NULL AS t | | + SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 2 | 23 SET ROLE regress_stats_user1 | 1 | 0 SET ROLE regress_stats_user2 | 1 | 0 @@ -737,12 +737,12 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- remove query ('SELECT $1 AS "ONE"') executed by two users -- -SELECT pg_stat_statements_reset(0,0,s.queryid) +SELECT pg_stat_statements_reset(0,0,s.queryid) IS NOT NULL AS t FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"'; - pg_stat_statements_reset --------------------------- - - + t +--- + t + t (2 rows) SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; @@ -756,11 +756,11 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| | (SELECT d.oid FROM pg_database As d where datname = current_database()),+| | (SELECT s.queryid FROM pg_stat_statements AS s +| | - WHERE s.query = $2 LIMIT $3)) | | - SELECT pg_stat_statements_reset($1) | 1 | 1 - SELECT pg_stat_statements_reset($1,$2,s.queryid) +| 1 | 2 + WHERE s.query = $2 LIMIT $3)) IS NOT NULL AS t | | + SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1 + SELECT pg_stat_statements_reset($1,$2,s.queryid) IS NOT NULL AS t +| 1 | 2 FROM pg_stat_statements AS s WHERE s.query = $3 | | - SELECT pg_stat_statements_reset() | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 3 | 35 SET ROLE regress_stats_user1 | 1 | 0 SET ROLE regress_stats_user2 | 1 | 0 @@ -769,11 +769,11 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- remove query of a user (regress_stats_user1) -- -SELECT pg_stat_statements_reset(r.oid) +SELECT pg_stat_statements_reset(r.oid) IS NOT NULL AS t FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1'; - pg_stat_statements_reset --------------------------- - + t +--- + t (1 row) SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; @@ -786,12 +786,12 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| | (SELECT d.oid FROM pg_database As d where datname = current_database()),+| | (SELECT s.queryid FROM pg_stat_statements AS s +| | - WHERE s.query = $2 LIMIT $3)) | | - SELECT pg_stat_statements_reset($1) | 1 | 1 - SELECT pg_stat_statements_reset($1,$2,s.queryid) +| 1 | 2 + WHERE s.query = $2 LIMIT $3)) IS NOT NULL AS t | | + SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1 + SELECT pg_stat_statements_reset($1,$2,s.queryid) IS NOT NULL AS t +| 1 | 2 FROM pg_stat_statements AS s WHERE s.query = $3 | | - SELECT pg_stat_statements_reset() | 1 | 1 - SELECT pg_stat_statements_reset(r.oid) +| 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 + SELECT pg_stat_statements_reset(r.oid) IS NOT NULL AS t +| 1 | 1 FROM pg_roles AS r WHERE r.rolname = $1 | | SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 4 | 46 SET ROLE regress_stats_user2 | 1 | 0 @@ -800,16 +800,16 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- reset all -- -SELECT pg_stat_statements_reset(0,0,0); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset(0,0,0) IS NOT NULL AS t; + t +--- + t (1 row) SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls | rows ------------------------------------------------------------------------------+-------+------ - SELECT pg_stat_statements_reset(0,0,0) | 1 | 1 + SELECT pg_stat_statements_reset(0,0,0) IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 (2 rows) @@ -824,10 +824,10 @@ DROP TABLE pgss_select_into; -- -- [re]plan counting -- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) CREATE TABLE test (); @@ -882,7 +882,7 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ALTER TABLE test ADD COLUMN x int | 0 | 1 | 0 CREATE TABLE test () | 0 | 1 | 0 SELECT $1 | 3 | 3 | 3 - SELECT pg_stat_statements_reset() | 0 | 1 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 0 | 1 | 1 SELECT query, plans, calls, rows FROM pg_stat_statements+| 1 | 0 | 0 WHERE query NOT LIKE $1 ORDER BY query COLLATE "C" | | | (5 rows) @@ -899,16 +899,25 @@ SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_sta -- -- access to pg_stat_statements_info view -- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT now() AS ref_ts \gset +SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info; + dealloc | reset_after_ref +---------+----------------- + 0 | f (1 row) -SELECT dealloc FROM pg_stat_statements_info; - dealloc ---------- - 0 +SELECT pg_stat_statements_reset() AS stats_reset_ts \gset +SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info; + dealloc | reset_after_ref +---------+----------------- + 0 | t +(1 row) + +-- check stats_reset timestamp +SELECT stats_reset = :'stats_reset_ts' AS reset_ts_match FROM pg_stat_statements_info; + reset_ts_match +---------------- + t (1 row) -- @@ -1102,4 +1111,160 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; 2 (1 row) +-- +-- statement timestamps +-- +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(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) + +SELECT now() AS ref_ts \gset +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_stats_since_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref +-------+------------------+------------------+------------------------------+----------------------- + 2 | 0 | 0 | 0 | 0 +(1 row) + +-- Perform single min/max reset +SELECT pg_stat_statements_reset(0, 0, queryid, true) AS minmax_reset_ts +FROM pg_stat_statements +WHERE query LIKE '%STMTTS1%' \gset +-- check +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_stats_since_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref +-------+------------------+------------------+------------------------------+----------------------- + 2 | 1 | 1 | 1 | 0 +(1 row) + +-- check minmax reset timestamps +SELECT +query, minmax_stats_since = :'minmax_reset_ts' AS reset_ts_match +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%' +ORDER BY query COLLATE "C"; + query | reset_ts_match +---------------------------+---------------- + SELECT $1 AS "STMTTS1" | t + SELECT $1,$2 AS "STMTTS2" | f +(2 rows) + +-- check that minmax reset does not set stats_reset +SELECT +stats_reset = :'minmax_reset_ts' AS stats_reset_ts_match +FROM pg_stat_statements_info; + stats_reset_ts_match +---------------------- + f +(1 row) + +-- Perform common min/max reset +SELECT pg_stat_statements_reset(0, 0, 0, true) AS minmax_reset_ts \gset +-- check again +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_ts_after_ref, + count(*) FILTER ( + WHERE minmax_stats_since = :'minmax_reset_ts' + ) as minmax_ts_match, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_plan_zero | minmax_exec_zero | minmax_ts_after_ref | minmax_ts_match | stats_since_after_ref +-------+------------------+------------------+---------------------+-----------------+----------------------- + 2 | 2 | 2 | 2 | 2 | 0 +(1 row) + +-- Execute first query once more to check stats update +SELECT 1 AS "STMTTS1"; + STMTTS1 +--------- + 1 +(1 row) + +-- check +-- we don't check planing times here to be independent of +-- plan caching approach +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_ts_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + total | minmax_exec_zero | minmax_ts_after_ref | stats_since_after_ref +-------+------------------+---------------------+----------------------- + 2 | 1 | 2 | 0 +(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 index 811813c4915..7ca8d42f831 100644 --- 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 @@ -6,10 +6,13 @@ /* 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); +ALTER EXTENSION pg_stat_statements DROP FUNCTION + pg_stat_statements_reset(Oid, Oid, bigint); /* Then we can drop them */ DROP VIEW pg_stat_statements; DROP FUNCTION pg_stat_statements(boolean); +DROP FUNCTION pg_stat_statements_reset(Oid, Oid, bigint); /* Now redefine */ CREATE FUNCTION pg_stat_statements(IN showtext boolean, @@ -55,7 +58,9 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean, OUT jit_optimization_count int8, OUT jit_optimization_time float8, OUT jit_emission_count int8, - OUT jit_emission_time float8 + OUT jit_emission_time float8, + OUT stats_since timestamp with time zone, + OUT minmax_stats_since timestamp with time zone ) RETURNS SETOF record AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10' @@ -64,4 +69,13 @@ LANGUAGE C STRICT VOLATILE PARALLEL SAFE; CREATE VIEW pg_stat_statements AS SELECT * FROM pg_stat_statements(true); +CREATE FUNCTION pg_stat_statements_reset(IN userid Oid DEFAULT 0, + IN dbid Oid DEFAULT 0, + IN queryid bigint DEFAULT 0, + IN minmax_only boolean DEFAULT false +) +RETURNS timestamp with time zone +AS 'MODULE_PATHNAME', 'pg_stat_statements_reset_1_10' +LANGUAGE C STRICT PARALLEL SAFE; + GRANT SELECT ON pg_stat_statements TO PUBLIC; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 1ca67ef6234..d5ebade01aa 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -162,9 +162,9 @@ typedef struct Counters double total_time[PGSS_NUMKIND]; /* total planning/execution time, * in msec */ double min_time[PGSS_NUMKIND]; /* minimum planning/execution time in - * msec */ + * msec since min/max reset */ double max_time[PGSS_NUMKIND]; /* maximum planning/execution time in - * msec */ + * msec since min/max reset */ double mean_time[PGSS_NUMKIND]; /* mean planning/execution time in * msec */ double sum_var_time[PGSS_NUMKIND]; /* sum of variances in @@ -220,12 +220,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 */ + TimestampTz minmax_stats_since; /* timestamp of last min/max values reset */ + slock_t mutex; /* protects the counters only */ } pgssEntry; /* @@ -309,6 +311,7 @@ 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_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); @@ -358,7 +361,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(Oid userid, Oid dbid, uint64 queryid); +static TimestampTz entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only); static char *generate_normalized_query(JumbleState *jstate, const char *query, int query_loc, int *query_len_p); static void fill_in_constant_lengths(JumbleState *jstate, const char *query, @@ -662,6 +665,8 @@ pgss_shmem_startup(void) /* copy in the actual stats */ entry->counters = temp.counters; + entry->stats_since = temp.stats_since; + entry->minmax_stats_since = temp.minmax_stats_since; } /* Read global statistics for pg_stat_statements */ @@ -1368,11 +1373,23 @@ pgss_store(const char *query, uint64 queryId, e->counters.sum_var_time[kind] += (total_time - old_mean) * (total_time - e->counters.mean_time[kind]); - /* calculate min and max time */ - if (e->counters.min_time[kind] > total_time) + /* + * Calculate min and max time. min = 0 and max = 0 + * means that the min/max statistics were reset + */ + if (e->counters.min_time[kind] == 0 + && e->counters.max_time[kind] == 0) + { e->counters.min_time[kind] = total_time; - if (e->counters.max_time[kind] < total_time) e->counters.max_time[kind] = total_time; + } + else + { + if (e->counters.min_time[kind] > total_time) + e->counters.min_time[kind] = total_time; + if (e->counters.max_time[kind] < total_time) + e->counters.max_time[kind] = total_time; + } } e->counters.rows += rows; e->counters.shared_blks_hit += bufusage->shared_blks_hit; @@ -1436,18 +1453,34 @@ pg_stat_statements_reset_1_7(PG_FUNCTION_ARGS) dbid = PG_GETARG_OID(1); queryid = (uint64) PG_GETARG_INT64(2); - entry_reset(userid, dbid, queryid); + entry_reset(userid, dbid, queryid, false); PG_RETURN_VOID(); } +Datum +pg_stat_statements_reset_1_10(PG_FUNCTION_ARGS) +{ + Oid userid; + Oid dbid; + uint64 queryid; + bool minmax_only; + + userid = PG_GETARG_OID(0); + dbid = PG_GETARG_OID(1); + queryid = (uint64) PG_GETARG_INT64(2); + minmax_only = PG_GETARG_BOOL(3); + + PG_RETURN_TIMESTAMPTZ(entry_reset(userid, dbid, queryid, minmax_only)); +} + /* * Reset statement statistics. */ Datum pg_stat_statements_reset(PG_FUNCTION_ARGS) { - entry_reset(0, 0, 0); + entry_reset(0, 0, 0, false); PG_RETURN_VOID(); } @@ -1459,8 +1492,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_V1_10 43 -#define PG_STAT_STATEMENTS_COLS 43 /* maximum of above */ +#define PG_STAT_STATEMENTS_COLS_V1_10 45 +#define PG_STAT_STATEMENTS_COLS 45 /* maximum of above */ /* * Retrieve statement statistics. @@ -1677,6 +1710,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, Counters tmp; double stddev; int64 queryid = entry->key.queryid; + TimestampTz stats_since; + TimestampTz minmax_stats_since; memset(values, 0, sizeof(values)); memset(nulls, 0, sizeof(nulls)); @@ -1745,6 +1780,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, SpinLockAcquire(&e->mutex); tmp = e->counters; + stats_since = e->stats_since; + minmax_stats_since = e->minmax_stats_since; SpinLockRelease(&e->mutex); } @@ -1831,6 +1868,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, values[i++] = Float8GetDatumFast(tmp.jit_optimization_time); values[i++] = Int64GetDatumFast(tmp.jit_emission_count); values[i++] = Float8GetDatumFast(tmp.jit_emission_time); + values[i++] = TimestampTzGetDatum(stats_since); + values[i++] = TimestampTzGetDatum(minmax_stats_since); } Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 : @@ -1953,6 +1992,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->minmax_stats_since = entry->stats_since; } return entry; @@ -2499,11 +2540,30 @@ gc_fail: record_gc_qtexts(); } +#define SINGLE_ENTRY_RESET(e) \ +if (e) { \ + if (minmax_only) { \ + /* When requested reset only min/max statistics of an entry */ \ + for (int kind = 0; kind < PGSS_NUMKIND; kind++) \ + { \ + e->counters.max_time[kind] = 0; \ + e->counters.min_time[kind] = 0; \ + } \ + e->minmax_stats_since = stats_reset; \ + } \ + else \ + { \ + /* Remove the key otherwise */ \ + hash_search(pgss_hash, &e->key, HASH_REMOVE, NULL); \ + num_remove++; \ + } \ +} + /* - * Release entries corresponding to parameters passed. + * Reset entries corresponding to parameters passed. */ -static void -entry_reset(Oid userid, Oid dbid, uint64 queryid) +static TimestampTz +entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only) { HASH_SEQ_STATUS hash_seq; pgssEntry *entry; @@ -2511,6 +2571,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid) long num_entries; long num_remove = 0; pgssHashKey key; + TimestampTz stats_reset; if (!pgss || !pgss_hash) ereport(ERROR, @@ -2520,6 +2581,8 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid) LWLockAcquire(pgss->lock, LW_EXCLUSIVE); num_entries = hash_get_num_entries(pgss_hash); + stats_reset = GetCurrentTimestamp(); + if (userid != 0 && dbid != 0 && queryid != UINT64CONST(0)) { /* If all the parameters are available, use the fast path. */ @@ -2528,23 +2591,21 @@ 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 */ + /* Reset is started from nested-level */ key.toplevel = false; - entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_REMOVE, NULL); - if (entry) /* found */ - num_remove++; + entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL); + + SINGLE_ENTRY_RESET(entry); - /* Also remove entries for top level statements */ + /* Reset entries for top level statements */ key.toplevel = true; + entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL); - /* Remove the key if exists */ - entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_REMOVE, NULL); - if (entry) /* found */ - num_remove++; + SINGLE_ENTRY_RESET(entry); } else if (userid != 0 || dbid != 0 || queryid != UINT64CONST(0)) { - /* Remove entries corresponding to valid parameters. */ + /* Reset entries corresponding to valid parameters. */ hash_seq_init(&hash_seq, pgss_hash); while ((entry = hash_seq_search(&hash_seq)) != NULL) { @@ -2552,8 +2613,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid) (!dbid || entry->key.dbid == dbid) && (!queryid || entry->key.queryid == queryid)) { - hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL); - num_remove++; + SINGLE_ENTRY_RESET(entry); } } } @@ -2563,8 +2623,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid) hash_seq_init(&hash_seq, pgss_hash); while ((entry = hash_seq_search(&hash_seq)) != NULL) { - hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL); - num_remove++; + SINGLE_ENTRY_RESET(entry); } } @@ -2578,7 +2637,6 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid) */ { volatile pgssSharedState *s = (volatile pgssSharedState *) pgss; - TimestampTz stats_reset = GetCurrentTimestamp(); SpinLockAcquire(&s->mutex); s->stats.dealloc = 0; @@ -2616,6 +2674,8 @@ done: release_lock: LWLockRelease(pgss->lock); + + return stats_reset; } /* diff --git a/contrib/pg_stat_statements/sql/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql index e2a83106d4c..662ee59cb66 100644 --- a/contrib/pg_stat_statements/sql/oldextversions.sql +++ b/contrib/pg_stat_statements/sql/oldextversions.sql @@ -38,10 +38,15 @@ SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc); -- New function pg_stat_statement_info, and new function -- and view for pg_stat_statements introduced in 1.9 -AlTER EXTENSION pg_stat_statements UPDATE TO '1.9'; +ALTER EXTENSION pg_stat_statements UPDATE TO '1.9'; SELECT pg_get_functiondef('pg_stat_statements_info'::regproc); \d pg_stat_statements SELECT count(*) > 0 AS has_data FROM pg_stat_statements; +\d pg_stat_statements_info +SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc); +SET SESSION AUTHORIZATION pg_read_all_stats; +SELECT pg_stat_statements_reset(); +RESET SESSION AUTHORIZATION; -- New functions and views for pg_stat_statements in 1.10 AlTER EXTENSION pg_stat_statements UPDATE TO '1.10'; diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql index dffd2c8c187..9d294a053ac 100644 --- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql +++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql @@ -5,7 +5,7 @@ CREATE EXTENSION pg_stat_statements; -- SET pg_stat_statements.track_utility = FALSE; SET pg_stat_statements.track_planning = TRUE; -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT 1 AS "int"; @@ -57,7 +57,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- CRUD: INSERT SELECT UPDATE DELETE on test table -- -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; -- utility "create table" should not be shown CREATE TEMP TABLE test (a int, b char(20)); @@ -105,7 +105,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics -- -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; -- utility "create table" should not be shown CREATE TABLE pgss_test (a int, b char(20)); @@ -129,7 +129,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C"; -- pg_stat_statements.track = none -- SET pg_stat_statements.track = 'none'; -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT 1 AS "one"; SELECT 1 + 1 AS "two"; @@ -140,7 +140,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- pg_stat_statements.track = top -- SET pg_stat_statements.track = 'top'; -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; DO LANGUAGE plpgsql $$ BEGIN @@ -174,7 +174,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- pg_stat_statements.track = all -- SET pg_stat_statements.track = 'all'; -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; -- we drop and recreate the functions to avoid any caching funnies DROP FUNCTION PLUS_ONE(INTEGER); @@ -207,7 +207,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; CREATE TABLE pgss_a (id integer PRIMARY KEY); CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a); -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; -- control query SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id; @@ -236,7 +236,7 @@ DROP TABLE pgss_a, pgss_b CASCADE; -- utility commands -- SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT 1; CREATE INDEX test_b ON test(b); @@ -255,7 +255,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW, -- REFRESH MATERIALIZED VIEW and SELECT INTO -- -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a; SELECT generate_series(1, 10) c INTO pgss_select_into; @@ -278,7 +278,7 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE -- -- Track user activity and reset them -- -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; CREATE ROLE regress_stats_user1; CREATE ROLE regress_stats_user2; @@ -299,7 +299,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- Don't reset anything if any of the parameter is NULL -- -SELECT pg_stat_statements_reset(NULL); +SELECT pg_stat_statements_reset(NULL) IS NOT NULL AS t; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- @@ -310,27 +310,27 @@ SELECT pg_stat_statements_reset( (SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'), (SELECT d.oid FROM pg_database As d where datname = current_database()), (SELECT s.queryid FROM pg_stat_statements AS s - WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1)); + WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1)) IS NOT NULL AS t; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- remove query ('SELECT $1 AS "ONE"') executed by two users -- -SELECT pg_stat_statements_reset(0,0,s.queryid) +SELECT pg_stat_statements_reset(0,0,s.queryid) IS NOT NULL AS t FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"'; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- remove query of a user (regress_stats_user1) -- -SELECT pg_stat_statements_reset(r.oid) +SELECT pg_stat_statements_reset(r.oid) IS NOT NULL AS t FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1'; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- -- reset all -- -SELECT pg_stat_statements_reset(0,0,0); +SELECT pg_stat_statements_reset(0,0,0) IS NOT NULL AS t; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- @@ -345,7 +345,7 @@ DROP TABLE pgss_select_into; -- -- [re]plan counting -- -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; CREATE TABLE test (); PREPARE prep1 AS SELECT COUNT(*) FROM test; EXECUTE prep1; @@ -366,8 +366,12 @@ SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_sta -- -- access to pg_stat_statements_info view -- -SELECT pg_stat_statements_reset(); -SELECT dealloc FROM pg_stat_statements_info; +SELECT now() AS ref_ts \gset +SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info; +SELECT pg_stat_statements_reset() AS stats_reset_ts \gset +SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info; +-- check stats_reset timestamp +SELECT stats_reset = :'stats_reset_ts' AS reset_ts_match FROM pg_stat_statements_info; -- -- top level handling @@ -442,4 +446,113 @@ SELECT ( SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; +-- +-- statement timestamps +-- +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +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'; + +SELECT now() AS ref_ts \gset +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_stats_since_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + +-- Perform single min/max reset +SELECT pg_stat_statements_reset(0, 0, queryid, true) AS minmax_reset_ts +FROM pg_stat_statements +WHERE query LIKE '%STMTTS1%' \gset + +-- check +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_stats_since_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + +-- check minmax reset timestamps +SELECT +query, minmax_stats_since = :'minmax_reset_ts' AS reset_ts_match +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%' +ORDER BY query COLLATE "C"; + +-- check that minmax reset does not set stats_reset +SELECT +stats_reset = :'minmax_reset_ts' AS stats_reset_ts_match +FROM pg_stat_statements_info; + +-- Perform common min/max reset +SELECT pg_stat_statements_reset(0, 0, 0, true) AS minmax_reset_ts \gset + +-- check again +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_plan_time + max_plan_time = 0 + ) as minmax_plan_zero, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_ts_after_ref, + count(*) FILTER ( + WHERE minmax_stats_since = :'minmax_reset_ts' + ) as minmax_ts_match, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + +-- Execute first query once more to check stats update +SELECT 1 AS "STMTTS1"; + +-- check +-- we don't check planing times here to be independent of +-- plan caching approach +SELECT + count(*) as total, + count(*) FILTER ( + WHERE min_exec_time + max_exec_time = 0 + ) as minmax_exec_zero, + count(*) FILTER ( + WHERE minmax_stats_since >= :'ref_ts' + ) as minmax_ts_after_ref, + count(*) FILTER ( + WHERE stats_since >= :'ref_ts' + ) as stats_since_after_ref +FROM pg_stat_statements +WHERE query LIKE '%STMTTS%'; + DROP EXTENSION pg_stat_statements; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 45e720e995d..b6481e45b37 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> @@ -473,6 +487,25 @@ Total time spent by the statement on emitting code, 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 + </para></entry> + </row> </tbody> </tgroup> </table> @@ -664,7 +697,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> @@ -683,6 +717,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> -- 2.31.1
