PostgreSQL CPU占用高分析，查询IO消耗最高的SQL，查询耗时最长的SQL，查询被锁住的session会话 转发 https://blog.csdn.net/sunny_day_day

查看当前活跃的DB session 正在运行的SQL语句（运行时间超过10秒）

SELECT now() - query_start as "runtime", usename,application_name, client_hostname, datname,  state, query
	    FROM  pg_stat_activity
	    WHERE now() - query_start > '10 seconds'::interval
	       and state!='idle'
	   ORDER BY runtime DESC;

按 total_time 列出查询，并查看哪个查询在数据库中花费的时间最多

SELECT round(total_time*1000)/1000 AS total_time,query
	FROM pg_stat_statements
	ORDER BY total_time DESC limit 5;

查看哪些table未及时做vacuum，以及未及时收集统计信息

SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
	to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date,
	to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
	FROM pg_stat_all_tables
	ORDER BY last_autovacuum;

查看有没有被锁的session

select pid,
	usename,
	pg_blocking_pids(pid) as blocked_by,
	query as blocked_query
	from pg_stat_activity
	where cardinality(pg_blocking_pids(pid)) > 0;

推荐创建如下session

pg_stat_tables

CREATE OR REPLACE VIEW pg_stat_tables
	AS
	WITH s AS (
	SELECT *, cast((n_tup_ins + n_tup_upd + n_tup_del) AS numeric) AS total
	       FROM pg_stat_user_tables
	)
	SELECT s.schemaname,       s.relname,       s.relid,
	       s.seq_scan,         s.idx_scan,
	       CASE WHEN s.seq_scan + s.idx_scan = 0 THEN 'NaN'::double precision
	            ELSE round(100 * s.idx_scan/(s.seq_scan+s.idx_scan),2)  END AS idx_scan_ratio,

	       s.seq_tup_read,       s.idx_tup_fetch,

	       sio.heap_blks_read,       sio.heap_blks_hit,
	       CASE WHEN sio.heap_blks_read = 0 THEN 0.00
	            ELSE round(100*sio.heap_blks_hit/(sio.heap_blks_read+sio.heap_blks_hit),2)  END AS hit_ratio,

	       n_tup_ins,       n_tup_upd,       n_tup_del,
	       CASE WHEN s.total = 0 THEN 0.00
	            ELSE round((100*cast(s.n_tup_ins AS numeric)/s.total) ,2) END AS ins_ratio,
	       CASE WHEN s.total = 0 THEN 0.00
	            ELSE round((100*cast(s.n_tup_upd AS numeric)/s.total) ,2) END AS upd_ratio,
	       CASE WHEN s.total = 0 THEN 0.00
	            ELSE round((100*cast(s.n_tup_del AS numeric)/s.total) ,2) END AS del_ratio,

	       s.n_tup_hot_upd,
	       CASE WHEN s.n_tup_upd = 0 THEN 'NaN'::double precision
	            ELSE round(100*cast(cast(n_tup_hot_upd as numeric)/n_tup_upd as numeric), 2) END AS hot_upd_ratio,

	       pg_size_pretty(pg_relation_size(sio.relid)) AS "table_size",
	       pg_size_pretty(pg_total_relation_size(sio.relid)) AS "total_size",

	       s.last_vacuum,       s.last_autovacuum,
	       s.vacuum_count,      s.autovacuum_count,
	       s.last_analyze,      s.last_autoanalyze,
	       s.analyze_count,     s.autoanalyze_count
	FROM s, pg_statio_user_tables AS sio WHERE s.relid = sio.relid ORDER BY relname;

pg_stat_indexes

AS
	SELECT s.schemaname,       s.relname,       s.indexrelname,       s.relid,
	       s.idx_scan,       s.idx_tup_read,       s.idx_tup_fetch,
	       sio.idx_blks_read,       sio.idx_blks_hit,
	       CASE WHEN sio.idx_blks_read  + sio.idx_blks_hit = 0 THEN 'NaN'::double precision
	       ELSE round(100 * sio.idx_blks_hit/(sio.idx_blks_read + sio.idx_blks_hit), 2) END AS idx_hit_ratio,
	       pg_size_pretty(pg_relation_size(s.indexrelid)) AS "index_size"
	FROM pg_stat_user_indexes AS s, pg_statio_user_indexes AS sio
	WHERE s.relid = sio.relid ORDER BY relname;

pg_stat_users

CREATE OR REPLACE VIEW pg_stat_users
	AS
	SELECT datname,       usename,       pid,       backend_start, 
	       (current_timestamp - backend_start)::interval(3) AS "login_time"
	FROM pg_stat_activity;
	pg_stat_queries
	CREATE OR REPLACE VIEW pg_stat_queries 
	AS
	SELECT datname,       usename,       pid,
	       (current_timestamp - xact_start)::interval(3) AS duration, 
	       waiting,       query
	FROM pg_stat_activity WHERE pid != pg_backend_pid();

pg_stat_long_trx

CREATE OR REPLACE VIEW pg_stat_long_trx 
	AS
	SELECT pid,        waiting,
	    (current_timestamp - xact_start)::interval(3) AS duration, query
	FROM pg_stat_activity
	WHERE pid <> pg_backend_pid();

pg_stat_waiting_locks

CREATE OR REPLACE VIEW pg_stat_waiting_locks
	AS
	SELECT l.locktype,       c.relname,       l.pid,       l.mode,
	       substring(a.query, 1, 6) AS query,
	       (current_timestamp - xact_start)::interval(3) AS duration
	FROM pg_locks AS l
	  LEFT OUTER JOIN pg_stat_activity AS a ON l.pid = a.pid
	  LEFT OUTER JOIN pg_class AS c ON l.relation = c.oid 
	WHERE  NOT l.granted ORDER BY l.pid;

要启用 pg_stat_statements，请修改现有的自定义参数组并设置以下值：

shared_preload_libraries = pg_stat_statements
track_activity_query_size = 2048
pg_stat_statements.track = ALL
pg_stat_statements.max = 10000

选择立即应用，然后重启数据库实例。然后，在要监控的数据库上运行与以下内容类似的命令：

注意：以下示例在“demo”数据库中安装扩展。

demo=> select current_database();
current_database
------------------
demo
(1 row)

demo=> CREATE EXTENSION pg_stat_statements;

设置 pg_stat_statements 后，您可以使用以下方法之一监控输出：

按 total_time 列出查询，并查看哪个查询在数据库中花费的时间最多：

SELECT round(total_time*1000)/1000 AS total_time,query
FROM pg_stat_statements
ORDER BY total_time DESC limit 2;

列出查询及调用总数、总行数和返回的行数：

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;

基于每次执行列出查询，以便随时间对查询进行抽样：

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,
temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY total_time DESC LIMIT 5;

确定导致高 CPU 使用率的查询后，可以使用以下方法进一步降低 CPU 使用率：

如果有机会进行调整，请使用 EXPLAIN 和 EXPLAIN ANALYZE 来识别警告。有关更多信息，请参阅有关 EXPLAIN 的 PostgreSQL 文档。
如果有重复运行的查询，请使用准备的语句来降低 CPU 的压力。