一个慢查询从 8 秒优化到 50 毫秒的过程。本文通过一个真实案例，讲解 EXPLAIN ANALYZE 的使用、索引策略以及常见的查询反模式。

EXPLAIN ANALYZE — 优化前
NodeRowsTimeMethod
Hash Join12,4508,142ms—
├─ Seq Scan on orders847,2033,210ms全表扫描
├─ Seq Scan on users125,0001,890ms全表扫描
└─ Sort12,4502,450msExternal Merge

一、问题背景

一个订单报表查询，关联 users 和 orders 两张表，按时间范围过滤并分页。数据量：users 12.5 万，orders 84.7 万。查询耗时 8 秒，用户投诉页面加载慢。

-- 原始慢查询
SELECT o.id, o.amount, o.created_at, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at BETWEEN '2026-01-01' AND '2026-03-31'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 20;

二、EXPLAIN ANALYZE 分析

执行 EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 后发现两个问题：

  1. orders 表全表扫描：没有对 created_at 建索引
  2. Sort 使用 External Merge：排序数据超出 work_mem，溢出到磁盘

三、优化过程

3.1 添加复合索引

最关键的优化：创建一个覆盖 WHERE 条件和 ORDER BY 的复合索引。

-- 复合索引：覆盖过滤 + 排序
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC)
WHERE status = 'completed';  -- 部分索引，更小更快

-- users 表主键已有索引，无需额外创建
💡 部分索引 (Partial Index)：只索引 status = 'completed' 的记录，索引体积从 ~85MB 缩小到 ~25MB，查询速度更快。适用于大部分查询只针对特定状态的场景。

3.2 调整 work_mem

-- 会话级别调整（针对这个查询）
SET work_mem = '64MB';

-- 或者全局调整（谨慎）
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

3.3 优化后的查询

-- 优化后：使用覆盖索引，避免回表
SELECT o.id, o.amount, o.created_at, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
  AND o.created_at BETWEEN '2026-01-01' AND '2026-03-31'
ORDER BY o.created_at DESC
LIMIT 20;
EXPLAIN ANALYZE — 优化后
NodeRowsTimeMethod
Nested Loop Join2048ms—
├─ Index Scan Backward202msidx_orders_status_created
└─ Index Scan on users_pkey201msB-Tree

四、优化效果

❌ 优化前
8,142
毫秒
✅ 优化后
48
毫秒

性能提升 169 倍。核心就两件事：加对索引 + 调 work_mem。

五、常用诊断命令

-- 查看慢查询（需要开启 pg_stat_statements）
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 查看缺失的索引建议
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND n_distinct > 100;

-- 查看表膨胀
SELECT pg_size_pretty(pg_total_relation_size('orders'));

-- 查看索引使用率
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders';

六、总结