慢查询

慢查询指的是：执行时间超过预期或阈值的 SQL 语句，通常意味着索引缺失、执行计划错误、I/O 过高、锁等待等问题。

比如，你设定阈值为 1 秒：

log_min_duration_statement = 1000   # 超过1000毫秒的SQL会记录日志

如果有 SQL 花了 2.5 秒执行，PostgreSQL 就会在日志中自动记录：

duration: 2500.321 ms  statement: SELECT * FROM orders WHERE user_id = 10000;

这类 SQL 就属于 “慢查询”。

常见原因

类型	常见原因	举例
索引问题	没有合适索引、索引未被使用	WHERE col LIKE '%abc' 无法用索引
执行计划不合理	统计信息过期导致选择了错误的 JOIN/SCAN 方式	该用 index scan 却用了 seq scan
数据量膨胀	表/索引太大，I/O 过重	表有上千万行
锁等待	SQL 在等其他事务释放锁	update/delete 被阻塞
配置不当	work_mem, shared_buffers 太小	排序/哈希落盘
网络与并发	客户端等待时间长	连接池未优化

慢查询的检测

PostgreSQL 提供三种主要手段：

日常检测

在 postgresql.conf 中开启：

logging_collector = on
log_min_duration_statement = 500   # 超过0.5秒的SQL记录下来
log_line_prefix = '%t [%p]: user=%u,db=%d '

查看日志：

duration: 1200.45 ms  statement: SELECT ...;

实时统计：pg_stat_statements

启用扩展：

CREATE EXTENSION pg_stat_statements;

查看最慢的 SQL：

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 5;

这能看到：执行最慢的 SQL、被执行次数、平均耗时、总耗时占比。

EXPLAIN / EXPLAIN ANALYZE

分析单条 SQL 的执行计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 12345;

输出示例：

Index Scan using idx_orders_user on orders  (cost=0.42..8.44 rows=1 width=48)
(actual time=0.031..0.033 rows=1 loops=1)
Buffers: shared hit=3

含义：

• Index Scan：使用了索引

• cost：估算开销

• actual time：实际耗时

• rows：返回行数

如果实际时间比预估高很多，说明执行计划有问题。

慢查询的优化

1️⃣ 开启 log_min_duration_statement
2️⃣ 日志中定位最慢 SQL
3️⃣ 用 EXPLAIN ANALYZE 查看执行计划
4️⃣ 检查是否使用索引、连接顺序、过滤条件
5️⃣ 根据结果调整 SQL / 索引 / 配置
6️⃣ 重新验证性能

常见优化思路：

问题类型	优化方向
没有用索引	建立合适索引（B-Tree、GIN、HASH）
全表扫描	改写 SQL 逻辑或增加索引
JOIN 很慢	优化连接条件或拆分查询
排序/聚合慢	增大 work_mem 或使用物化表
频繁执行相同SQL	开启 prepare statement
统计信息不准	手动执行 ANALYZE
锁等待	优化事务逻辑、减少长事务
频繁I/O	调整 shared_buffers, effective_cache_size