18 lines
644 B
Go
18 lines
644 B
Go
|
-- 查看当前活跃的 SQL（排查慢查询）
|
|||
|
|
SELECT pid, now() - query_start AS duration, query
|
|||
|
|
FROM pg_stat_activity
|
|||
|
|
WHERE state = 'active' AND now() - query_start > '5 seconds'::interval;
|
|||
|
|
|
|||
|
|
-- 查看表的访问统计（找出热点表）
|
|||
|
|
SELECT relname, seq_scan, idx_scan, n_live_tup
|
|||
|
|
FROM pg_stat_user_tables
|
|||
|
|
ORDER BY seq_scan DESC LIMIT 10;
|
|||
|
|
|
|||
|
|
-- 查看索引使用情况（找出未使用的索引）
|
|||
|
|
SELECT relname AS table_name, indexrelname AS index_name, idx_scan
|
|||
|
|
FROM pg_stat_user_indexes
|
|||
|
|
WHERE idx_scan = 0;
|
|||
|
|
|
|||
|
|
-- 更新统计信息（当执行计划不准时）
|
|||
|
|
ANALYZE users; -- 单表更新
|
|||
|
|
ANALYZE; -- 全库更新
|