PostgreSQL 性能调优：从 30 秒到 300 毫秒的优化之路
我们有一个报表查询，高峰期要跑 30 秒。用户投诉后，我开始系统性地优化。最终降到 300 毫秒。
第一步：找到慢查询
SQL
-- 开启慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 记录超过 1 秒的查询
SELECT pg_reload_conf();
-- 查看当前正在跑的查询
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;第二步：EXPLAIN ANALYZE
SQL
EXPLAIN ANALYZE
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01'
AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50;关键指标：
- `Seq Scan`（全表扫描）→ 需要加索引
- `cost` 越大越慢
- `actual time` 是真实执行时间
第三步：加索引
SQL
-- 复合索引，匹配查询条件
CREATE INDEX idx_orders_status_created
ON orders(status, created_at DESC);
-- 覆盖索引，避免回表
CREATE INDEX idx_orders_covered
ON orders(status, created_at DESC)
INCLUDE (total, user_id);
-- 部分索引，仅对活跃数据
CREATE INDEX idx_active_orders
ON orders(created_at DESC)
WHERE status IN ('pending', 'processing');第四步：优化查询
SQL
-- 优化前：子查询 + NOT IN
SELECT * FROM orders
WHERE user_id NOT IN (
SELECT id FROM blocked_users
);
-- 优化后：LEFT JOIN + IS NULL
SELECT o.* FROM orders o
LEFT JOIN blocked_users b ON o.user_id = b.id
WHERE b.id IS NULL;
-- 分页优化：用游标代替 OFFSET
-- 慢
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- 快
SELECT * FROM orders
WHERE id > 100000
ORDER BY id LIMIT 20;第五步：连接池
SQL
-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;
-- 合理配置连接池（应用层）
-- PgBouncer 或应用内连接池最终效果
| 指标 | 优化前 | 优化后 |
|------|--------|--------|
| 查询时间 | 30s | 300ms |
| CPU 使用率 | 85% | 30% |
| 连接数 | 120 | 20 |
核心思路：找到慢查询 → 分析执行计划 → 加索引 → 改写 SQL → 配置优化。
读者评论 3