From 43cbedab8ff1eef4088807ffc1a64a107de67af6 Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Fri, 11 Jan 2019 08:50:09 +0530 Subject: Extend pg_stat_statements_reset to reset statistics specific to a particular user/db/query. The function pg_stat_statements_reset() is extended to accept userid, dbid, and queryid as input parameters. Now, it can discard the statistics gathered so far by pg_stat_statements corresponding to the specified userid, dbid, and queryid. If no parameter is specified or all the specified parameters have default value aka 0, it will discard all statistics as per the old behavior. The new behavior is useful to get the fresh statistics for a specific user/database/query without resetting all the existing statistics. Author: Haribabu Kommi, with few additional changes by me Reviewed-by: Michael Paquier, Amit Kapila and Fujii Masao Discussion: https://postgr.es/m/CAJrrPGcyh-gkFswyc6C661K6cknL0XkNqVT0sQt2mFNMR4HRKA@mail.gmail.com --- doc/src/sgml/pgstatstatements.sgml | 106 ++++++++++++++++++++++++++++--------- 1 file changed, 82 insertions(+), 24 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index c0217ed485f..26bb82da4a8 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -336,7 +336,7 @@ - pg_stat_statements_reset() returns void + pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void pg_stat_statements_reset @@ -344,9 +344,16 @@ - pg_stat_statements_reset discards all statistics - gathered so far by pg_stat_statements. - By default, this function can only be executed by superusers. + pg_stat_statements_reset discards statistics + gathered so far by pg_stat_statements corresponding + to the specified userid, dbid + and queryid. If any of the parameters are not + specified, the default value 0(invalid) is used for + each of them and the statistics that match with other parameters will be + reset. If no parameter is specified or all the specified parameters are + 0(invalid), it will discard all statistics. By + default, this function can only be executed by superusers. Access may be + granted to others using GRANT. @@ -494,36 +501,87 @@ bench=# \x bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; --[ RECORD 1 ]--------------------------------------------------------------------- -query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; +-[ RECORD 1 ]-------------------------------------------------------------------- +query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 calls | 3000 -total_time | 9609.00100000002 -rows | 2836 -hit_percent | 99.9778970000200936 --[ RECORD 2 ]--------------------------------------------------------------------- -query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; +total_time | 25565.855387 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 2 ]-------------------------------------------------------------------- +query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 calls | 3000 -total_time | 8015.156 -rows | 2990 -hit_percent | 99.9731126579631345 --[ RECORD 3 ]--------------------------------------------------------------------- +total_time | 20756.669379 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 3 ]-------------------------------------------------------------------- query | copy pgbench_accounts from stdin calls | 1 -total_time | 310.624 +total_time | 291.865911 rows | 100000 -hit_percent | 0.30395136778115501520 --[ RECORD 4 ]--------------------------------------------------------------------- -query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; +hit_percent | 100.0000000000000000 +-[ RECORD 4 ]-------------------------------------------------------------------- +query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 calls | 3000 -total_time | 271.741999999997 +total_time | 271.232977 rows | 3000 -hit_percent | 93.7968855088209426 --[ RECORD 5 ]--------------------------------------------------------------------- +hit_percent | 98.5723926698852723 +-[ RECORD 5 ]-------------------------------------------------------------------- query | alter table pgbench_accounts add primary key (aid) calls | 1 -total_time | 81.42 +total_time | 160.588563 rows | 0 -hit_percent | 34.4947735191637631 +hit_percent | 100.0000000000000000 + + +bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s + WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2'; + +bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / + nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent + FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; +-[ RECORD 1 ]-------------------------------------------------------------------- +query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 +calls | 3000 +total_time | 20756.669379 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 2 ]-------------------------------------------------------------------- +query | copy pgbench_accounts from stdin +calls | 1 +total_time | 291.865911 +rows | 100000 +hit_percent | 100.0000000000000000 +-[ RECORD 3 ]-------------------------------------------------------------------- +query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 +calls | 3000 +total_time | 271.232977 +rows | 3000 +hit_percent | 98.5723926698852723 +-[ RECORD 4 ]-------------------------------------------------------------------- +query | alter table pgbench_accounts add primary key (aid) +calls | 1 +total_time | 160.588563 +rows | 0 +hit_percent | 100.0000000000000000 +-[ RECORD 5 ]-------------------------------------------------------------------- +query | vacuum analyze pgbench_accounts +calls | 1 +total_time | 136.448116 +rows | 0 +hit_percent | 99.9201915403032721 + +bench=# SELECT pg_stat_statements_reset(0,0,0); + +bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / + nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent + FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; +-[ RECORD 1 ]--------------------------------------- +query | SELECT pg_stat_statements_reset(0,0,0) +calls | 1 +total_time | 0.189497 +rows | 1 +hit_percent | + -- cgit v1.2.3