检查数据库日志
在日志中查看哪些可能是慢查询语句。
通过查询分析器(EXPLAIN)分析语句
看是否有顺序扫描(Seq Scan)等低效查询发生，如：
EXPLAIN
SELECT u.username, u.display_name, u.avatar_url, u.lang_tag, u.location, u.timezone, u.metadata, u.wallet,
u.email, u.facebook_id, u.google_id, u.gamecenter_id, u.steam_id, u.custom_id, u.edge_count,
u.create_time, u.update_time, u.verify_time, array(select ud.id from user_device ud where u.id = ud.user_id)
FROM users u
WHERE u.id = '4ee52024-e64d-4017-8807-897a1f7249e6';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using users_pkey on users u (cost=0.42..87.56 rows=1 width=3048)
Index Cond: (id = '4ee52024-e64d-4017-8807-897a1f7249e6'::uuid)
SubPlan 1
-> Seq Scan on user_device ud (cost=0.00..79.12 rows=1 width=36)
Filter: (u.id = user_id)
写脚本以固定时间间隔输出对pg_stat_activity的查询结果
- 查看耗时较长的查询
SELECT
pid,
NOW(),
NOW()-query_start AS query_duration,
query
FROM pg_stat_activity
WHERE datname = 'nakama'
AND pid != pg_backend_pid()
AND state != 'idle'
ORDER by query_duration DESC;
- 分析脚本定期输出的结果，找到慢查询语句。
