PostgreSQL查找慢查询

2026-01-10 Linux YY.K Permalink

统计CPU

ps -u postgres -o %cpu | awk '{s+=$1} END {print "Postgres Total CPU: " s "%"}'

查看活跃查询

SELECT pid, now() - query_start AS duration, query, state FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;
SELECT pid, now() - query_start AS duration, query, state FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC LIMIT 5;

查看连接IP和查询语句

SELECT pid, now() - query_start AS 执行时间, usename AS 用户名, 
client_addr AS 连接IP, state AS 状态, query AS 查询语句         
FROM pg_stat_activity WHERE
 pid <> pg_backend_pid() ORDER BY 执行时间 DESC;

查看扫表

SELECT 
    relname AS 表名,
    seq_scan AS 全表扫描次数,
    seq_tup_read AS 全表扫描读取行数,
    idx_scan AS 索引扫描次数,
    idx_tup_fetch AS 索引提取行数
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

启用插件

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

 重置自动增续ID

SELECT setval('tag_id_seq', (SELECT MAX(id) FROM tag));
PostgreSQL