pg_stat_kcache 插件使用手册

shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'
pg_stat_statements.track = all
pg_stat_kcache.track = top
pg_stat_kcache.track_planning = off

pg_ctl -D $PGDATA restart

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;

CREATE SCHEMA pgsk;
CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;

GRANT pg_monitor TO monitor_user;

SELECT substring(query, 1, 80) AS query,
       round((exec_user_time + exec_system_time)::numeric, 3) AS cpu_sec,
       round(exec_user_time::numeric, 3) AS user_sec,
       round(exec_system_time::numeric, 3) AS system_sec,
       rolname, datname
FROM pg_stat_kcache_detail
WHERE top IS TRUE
ORDER BY exec_user_time + exec_system_time DESC
LIMIT 10;

SELECT substring(query, 1, 80) AS query,
       pg_size_pretty(exec_reads) AS phys_read,
       pg_size_pretty(exec_writes) AS phys_write,
       exec_reads_blks, exec_writes_blks
FROM pg_stat_kcache_detail
WHERE top IS TRUE AND exec_reads > 0
ORDER BY exec_reads DESC
LIMIT 10;

SELECT substring(s.query, 1, 80) AS query,
       s.shared_blks_read AS shared_miss_blocks,
       pg_size_pretty(k.exec_reads) AS physical_read
FROM pg_stat_kcache() k
JOIN pg_stat_statements s
  ON s.queryid = k.queryid
 AND s.dbid = k.dbid
 AND s.userid = k.userid
 AND s.toplevel = k.top
WHERE k.top IS TRUE
ORDER BY k.exec_reads DESC
LIMIT 10;

SELECT datname,
       round((exec_user_time + exec_system_time)::numeric, 2) AS cpu_sec,
       pg_size_pretty(exec_reads) AS phys_read,
       pg_size_pretty(exec_writes) AS phys_write,
       stats_since
FROM pg_stat_kcache
ORDER BY exec_user_time + exec_system_time DESC;

SELECT substring(query, 1, 80) AS query,
       exec_minflts, exec_majflts, exec_nvcsws, exec_nivcsws
FROM pg_stat_kcache_detail
WHERE top IS TRUE
ORDER BY exec_majflts DESC, exec_nivcsws DESC
LIMIT 10;

SET pg_stat_kcache.track_planning = on;

DROP EXTENSION pg_stat_kcache;