create extension pg_stat_statements;
show shared_preload_libraries;
Useful commands
-- Flush data from pg_stat_statements:
> pg_stat_statements_reset() function will reset data from pg_stat_statements table.
-- To flush data of a particular database:( feature available from postgres 12 onwards only)
-- Find the dbid for the database
select pg_stat_statements.dbid,datname,count(*) from pg_stat_statements join
pg_database on pg_stat_statements.dbid=pg_database.oid group by pg_stat_statements.dbid,datname;
dbid | datname | count
-------+----------+-------
15846 | edb | 3
15845 | postgres | 18 <<<<<<<<<<<<<<<<<<<<<<<<<<
(2 rows)
select pg_stat_statements_reset(0, 15845, 0);
pg_stat_statements_reset
--------------------------
(1 row)
-- To flush a particular query:( Available from postgres 12 onwards only)
-- Find the queryid
select userid::regrole, dbid, queryid,query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
limit 10;
-[ RECORD 1 ]------+---
userid | enterprisedb
dbid | 15846
queryid | 123573657 <<<<<<<<<<<<<<<<<<<<<<<<<<<<
query | insert into test select * from test
-[ RECORD 2 ]------+-----
select pg_stat_statements_reset(0, 0, 123573657);
pg_stat_statements_reset
--------------------------
(1 row)
-- Monitoring using pg_stat_statements:
SELECT substring(query, 1, 50) AS query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time /
sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
query. | total_time | calls | mean | percentage_cpu
----------------------------------------------------+------------+-------+------+----------------
select * from pg_stat_statements | 0.85 | 1 | 0.85 | 38.77
SELECT e.extname AS "Name", e.extversion AS "Versi | 0.55 | 1 | 0.55 | 24.95
WITH released_messages AS ( UPDATE sys.callback_qu | 0.17 | 2 | 0.08 | 7.57
WITH released_messages AS ( UPDATE sys.callback_qu | 0.16 | 2 | 0.08 | 7.11
show shared_preload_libraries | 0.10 | 1 | 0.10 | 4.56
WITH released_messages AS ( UPDATE sys.callback_qu | 0.09 | 2 | 0.04 | 3.87
WITH messages AS ( SELECT msgid FROM sys.callbac | 0.04 | 2 | 0.02 | 1.71
DELETE FROM sys.callback_queue_table qt WHERE qt. | 0.04 | 2 | 0.02 | 1.60
SELECT MIN(qt.next_event_time) FROM sys.callback | 0.04 | 2 | 0.02 | 1.67
-- sql queries having high i/o activity
select userid::regrole, dbid, query,queryid,mean_time/1000 as mean_time_seconds
from pg_stat_statements
order by (blk_read_time+blk_write_time) desc
limit 10;
-- Top time consuming queries
select userid::regrole, dbid, query ,calls, total_time/1000 as total_time_seconds ,min_time/1000 as min_time_seconds,max_time/1000 as max_time_seconds,mean_time/1000 as mean_time_seconds
from pg_stat_statements
order by mean_time desc
limit 10;
-[ RECORD 1 ]------+----
userid | enterprisedb
dbid | 15846
query | insert into test select * from test
calls | 9
total_time_seconds | 2.722928186
min_time_seconds | 0.003885998
max_time_seconds | 1.395848226
mean_time_seconds | 0.302547576222222
-[ RECORD 2 ]------+--------
userid | enterprisedb
dbid | 15846
query | insert into test select * from pg_tables
calls | 5
total_time_seconds | 0.003757356
min_time_seconds | 0.00065117
max_time_seconds | 0.001032883
mean_time_seconds | 0.0007514712
-- with high memory usage:
select userid::regrole, dbid, queryid,query from pg_stat_statements
order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
