From 17e03282241c6ac58a714eb0c3b6a8018cf6167a Mon Sep 17 00:00:00 2001 From: Fujii Masao Date: Thu, 2 Apr 2020 11:20:19 +0900 Subject: Allow pg_stat_statements to track planning statistics. This commit makes pg_stat_statements support new GUC pg_stat_statements.track_planning. If this option is enabled, pg_stat_statements tracks the planning statistics of the statements, e.g., the number of times the statement was planned, the total time spent planning the statement, etc. This feature is useful to check the statements that it takes a long time to plan. Previously since pg_stat_statements tracked only the execution statistics, we could not use that for the purpose. The planning and execution statistics are stored at the end of each phase separately. So there are not always one-to-one relationship between them. For example, if the statement is successfully planned but fails in the execution phase, only its planning statistics are stored. This may cause the users to be able to see different pg_stat_statements results from the previous version. To avoid this, pg_stat_statements.track_planning needs to be disabled. This commit bumps the version of pg_stat_statements to 1.8 since it changes the definition of pg_stat_statements function. Author: Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao Reviewed-by: Sergei Kornilov, Tomas Vondra, Yoshikazu Imai, Haribabu Kommi, Tom Lane Discussion: https://postgr.es/m/CAHGQGwFx_=DO-Gu-MfPW3VQ4qC7TfVdH2zHmvZfrGv6fQ3D-Tw@mail.gmail.com Discussion: https://postgr.es/m/CAEepm=0e59Y_6Q_YXYCTHZkqOc6H2pJ54C_Xe=VFu50Aqqp_sA@mail.gmail.com Discussion: https://postgr.es/m/DB6PR0301MB21352F6210E3B11934B0DCC790B00@DB6PR0301MB2135.eurprd03.prod.outlook.com --- doc/src/sgml/pgstatstatements.sgml | 232 ++++++++++++++++++++++++------------- 1 file changed, 149 insertions(+), 83 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 26bb82da4a8..b4df84c60bb 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -9,7 +9,8 @@ The pg_stat_statements module provides a means for - tracking execution statistics of all SQL statements executed by a server. + tracking planning and execution statistics of all SQL statements executed by + a server. @@ -82,6 +83,48 @@ Text of a representative statement + + plans + bigint + + Number of times the statement was planned + + + + total_plan_time + double precision + + Total time spent planning the statement, in milliseconds + + + + min_plan_time + double precision + + Minimum time spent planning the statement, in milliseconds + + + + max_plan_time + double precision + + Maximum time spent planning the statement, in milliseconds + + + + mean_plan_time + double precision + + Mean time spent planning the statement, in milliseconds + + + + stddev_plan_time + double precision + + Population standard deviation of time spent planning the statement, in milliseconds + + calls bigint @@ -90,38 +133,38 @@ - total_time + total_exec_time double precision - Total time spent in the statement, in milliseconds + Total time spent executing the statement, in milliseconds - min_time + min_exec_time double precision - Minimum time spent in the statement, in milliseconds + Minimum time spent executing the statement, in milliseconds - max_time + max_exec_time double precision - Maximum time spent in the statement, in milliseconds + Maximum time spent executing the statement, in milliseconds - mean_time + mean_exec_time double precision - Mean time spent in the statement, in milliseconds + Mean time spent executing the statement, in milliseconds - stddev_time + stddev_exec_time double precision - Population standard deviation of time spent in the statement, in milliseconds + Population standard deviation of time spent executing the statement, in milliseconds @@ -448,6 +491,21 @@ + + + pg_stat_statements.track_planning (boolean) + + + + + pg_stat_statements.track_planning controls whether + planning operations and duration are tracked by the module. + The default value is on. + Only superusers can change this setting. + + + + pg_stat_statements.save (boolean) @@ -498,89 +556,97 @@ $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x -bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / +bench=# SELECT query, calls, total_exec_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 -calls | 3000 -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 | 20756.669379 -rows | 3000 -hit_percent | 100.0000000000000000 --[ RECORD 3 ]-------------------------------------------------------------------- -query | copy pgbench_accounts from stdin -calls | 1 -total_time | 291.865911 -rows | 100000 -hit_percent | 100.0000000000000000 --[ RECORD 4 ]-------------------------------------------------------------------- -query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 -calls | 3000 -total_time | 271.232977 -rows | 3000 -hit_percent | 98.5723926698852723 --[ RECORD 5 ]-------------------------------------------------------------------- -query | alter table pgbench_accounts add primary key (aid) -calls | 1 -total_time | 160.588563 -rows | 0 -hit_percent | 100.0000000000000000 + FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; +-[ RECORD 1 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 +calls | 3000 +total_exec_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_exec_time | 20756.669379 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 3 ]---+-------------------------------------------------------------------- +query | copy pgbench_accounts from stdin +calls | 1 +total_exec_time | 291.865911 +rows | 100000 +hit_percent | 100.0000000000000000 +-[ RECORD 4 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 +calls | 3000 +total_exec_time | 271.232977 +rows | 3000 +hit_percent | 98.8454011741682975 +-[ RECORD 5 ]---+-------------------------------------------------------------------- +query | alter table pgbench_accounts add primary key (aid) +calls | 1 +total_exec_time | 160.588563 +rows | 0 +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 / +bench=# SELECT query, calls, total_exec_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 + FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; +-[ RECORD 1 ]---+-------------------------------------------------------------------- +query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 +calls | 3000 +total_exec_time | 20756.669379 +rows | 3000 +hit_percent | 100.0000000000000000 +-[ RECORD 2 ]---+-------------------------------------------------------------------- +query | copy pgbench_accounts from stdin +calls | 1 +total_exec_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_exec_time | 271.232977 +rows | 3000 +hit_percent | 98.8454011741682975 +-[ RECORD 4 ]---+-------------------------------------------------------------------- +query | alter table pgbench_accounts add primary key (aid) +calls | 1 +total_exec_time | 160.588563 +rows | 0 +hit_percent | 100.0000000000000000 +-[ RECORD 5 ]---+-------------------------------------------------------------------- +query | vacuum analyze pgbench_accounts +calls | 1 +total_exec_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 / +bench=# SELECT query, calls, total_exec_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 | + FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; +-[ RECORD 1 ]---+----------------------------------------------------------------------------- +query | SELECT pg_stat_statements_reset(0,0,0) +calls | 1 +total_exec_time | 0.189497 +rows | 1 +hit_percent | +-[ RECORD 2 ]---+----------------------------------------------------------------------------- +query | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit / + + | nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+ + | FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3 +calls | 0 +total_exec_time | 0 +rows | 0 +hit_percent | -- cgit v1.2.3