pg_stats_statements
-- pgMonitor Exporter (ccp_pg_stat_statements)
SELECT pg_get_userbyid(s.userid) as role, d.datname AS dbname, s.queryid,
btrim(replace(left(s.query, 40), '\n', '')) AS query, s.plans,
s.total_plan_time AS total_plan_time_ms, s.min_plan_time AS min_plan_time_ms,
s.max_plan_time AS max_plan_time_ms, s.mean_plan_time AS mean_plan_time_ms,
s.stddev_plan_time AS stddev_plan_time_ms, s.calls, s.total_exec_time AS total_exec_time_ms,
s.min_exec_time AS min_exec_time_ms, s.max_exec_time AS max_exec_time_ms,
s.mean_exec_time AS mean_exec_time_ms, s.stddev_exec_time AS stddev_exec_time_ms, s.rows,
s.shared_blks_hit, s.shared_blks_read, s.shared_blks_dirtied, s.shared_blks_written,
s.local_blks_hit, s.local_blks_read, s.local_blks_dirtied, s.local_blks_written,
s.temp_blks_read, s.temp_blks_written, s.blk_read_time AS blk_read_time_ms,
s.blk_write_time AS blk_write_time_ms, s.wal_records, s.wal_fpi, s.wal_bytes
FROM public.pg_stat_statements s
JOIN pg_catalog.pg_database d ON d.oid = s.dbid
Select (total_time / 1000 / 60) as total, (total_time/calls) as avg, query from pg_stat_statements order by 1 desc limit 100;
pg_stats_statements Reset
SELECT pg_stat_statements_reset()
Stat Statements Total
-- pgMonitor Exporter (ccp_pg_stat_statements_total)
SELECT pg_get_userbyid(s.userid) as role, d.datname AS dbname, sum(s.calls) AS calls_count,
sum(s.total_exec_time) AS exec_time_ms, avg(s.mean_exec_time) AS mean_exec_time_ms,
sum(s.rows) AS row_count
FROM public.pg_stat_statements s
JOIN pg_catalog.pg_database d ON d.oid = s.dbid
GROUP BY 1,2
Top Mean/Avg
-- pgMonitor Exporter (ccp_pg_stat_statements_top_mean)
SELECT pg_get_userbyid(s.userid) as role, d.datname AS dbname, s.queryid,
btrim(replace(left(s.query, 40), '\n', '')) AS query, max(s.mean_exec_time) exec_time_ms
FROM public.pg_stat_statements s
JOIN pg_catalog.pg_database d ON d.oid = s.dbid
GROUP BY 1,2,3,4
ORDER BY 5 DESC
LIMIT #PG_STAT_STATEMENTS_LIMIT#
Top Max
-- pgMonitor Exporter (ccp_pg_stat_statements_top_max)
SELECT pg_get_userbyid(s.userid) as role, d.datname AS dbname, s.queryid,
btrim(replace(left(s.query, 40), '\n', '')) AS query, s.max_exec_time AS exec_time_ms
FROM public.pg_stat_statements s
JOIN pg_catalog.pg_database d ON d.oid = s.dbid
ORDER BY 5 DESC
LIMIT #PG_STAT_STATEMENTS_LIMIT#
Top Total
-- Exporter (ccp_pg_stat_statements_top_total)
SELECT pg_get_userbyid(s.userid) as role, d.datname AS dbname, s.queryid,
btrim(replace(left(s.query, 40), '\n', '')) AS query, s.total_exec_time exec_time_ms
FROM public.pg_stat_statements s
JOIN pg_catalog.pg_database d ON d.oid = s.dbid
ORDER BY 5 DESC
LIMIT #PG_STAT_STATEMENTS_LIMIT#
