数据库慢了，加索引就行？没那么简单。本文从 EXPLAIN 读图、索引设计、常见反模式三个层面，系统讲解 PostgreSQL 查询优化的核心方法论。
为什么要学慢查询优化？
几乎所有后端服务的性能瓶颈，最终都指向数据库。在 PostgreSQL 生态里，"加个索引"是最常见的处方——但就像吃药一样，不对症的索引不仅治不好病，还可能拖慢写入、浪费存储。
真正的优化，从读懂执行计划开始。
第一步：用 EXPLAIN ANALYZE 读懂查询计划
基本用法
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
AND o.status = 'completed';
ANALYZE 让 PostgreSQL 真正执行查询（注意写操作要套在事务里再 ROLLBACK），BUFFERS 显示 I/O 统计。
执行计划关键指标
| 指标 | 含义 | 关注点 |
|---|---|---|
cost=0.00..123.45 |
估算成本（启动成本..总成本） | 总成本越大越慢 |
rows=1000 |
预估返回行数 | 与 actual rows 差距大 → 统计信息过时 |
actual time=0.1..5.2 |
真实耗时（ms） | 每个节点花了多久 |
Buffers: shared hit=100 read=50 |
缓存命中/磁盘读取 | read 多 → 内存不够或数据集太大 |
loops=100 |
该节点被执行次数 | 嵌套循环里 loops 高要警惕 |
识别问题节点
一份执行计划就是一棵倒置的树，从叶子节点往根节点读。重点找：
- Seq Scan on 大表：全表扫描，通常意味着缺索引
- Nested Loop + 高 loops：驱动表行数多时效率极低
- Sort + 高 cost：缺排序索引或
work_mem太小导致磁盘排序 - Hash Join + Batches > 1：哈希表溢出到磁盘
-- 典型问题计划：全表扫描 + 嵌套循环
Nested Loop (cost=0.00..892345.00 rows=500 ...)
-> Seq Scan on orders (cost=0.00..45000.00 rows=100000 ...)
Filter: (status = 'completed')
Rows Removed by Filter: 900000 ← 过滤了 90% 的行！
-> Index Scan on users (cost=0.29..8.31 rows=1 ...)
这个计划告诉我们：orders.status 上缺索引，导致扫描了 100 万行只为取 10 万行。
第二步：索引设计的核心原则
1. 选择性（Selectivity）决定索引价值
选择性 = 不重复值数 / 总行数。选择性越接近 1，索引越有效。
-- 查看列的选择性
SELECT
attname,
n_distinct,
CASE
WHEN n_distinct > 0 THEN n_distinct / reltuples
ELSE abs(n_distinct)
END AS selectivity
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY selectivity DESC;
经验法则：
- 选择性 > 0.1（如 user_id、email）→ B-tree 索引非常有效
- 选择性 < 0.01（如 status、gender）→ 单独建索引几乎没用
- 低选择性列 + 高选择性列组合 → 复合索引
2. 复合索引的列顺序
复合索引遵循最左前缀原则。列的排列顺序直接影响能否被利用：
-- 创建复合索引
CREATE INDEX idx_orders_status_user ON orders(status, user_id, created_at);
-- ✅ 能用：匹配最左前缀
WHERE status = 'completed' AND user_id = 42
WHERE status = 'completed'
-- ❌ 不能用：跳过了 status
WHERE user_id = 42
-- ⚠️ 部分利用：用到 status，但 created_at 的范围条件无法传递
WHERE status = 'completed' AND created_at > '2025-01-01'
设计口诀：等值条件在前，范围条件在后，排序列收尾。
-- 优化后的索引设计
CREATE INDEX idx_orders_optimized
ON orders(status, user_id, created_at DESC);
3. 覆盖索引（Index-Only Scan）
如果查询需要的列全部在索引里，PostgreSQL 可以完全不碰表数据：
-- 查询只需 user_id 和 total
CREATE INDEX idx_orders_covering
ON orders(status, user_id) INCLUDE (total);
-- 这个查询可以触发 Index Only Scan
SELECT user_id, total
FROM orders
WHERE status = 'completed';
INCLUDE 子句把 total 放进索引叶节点但不参与排序，既提供覆盖能力又不增加索引深度。
4. 部分索引（Partial Index）
只索引你关心的行，体积小、维护成本低：
-- 只索引未完成的订单（假设 95% 已完成）
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status IN ('pending', 'processing');
对于状态机模型的表，部分索引是利器——活跃数据通常只占很小比例。
第三步：常见反模式与修复
反模式 1：隐式类型转换杀死索引
-- phone 列是 varchar，但传入的是数字
-- PostgreSQL 会对每行做 phone::numeric 比较 → Seq Scan
SELECT * FROM users WHERE phone = 13800138000;
-- 修复：保持类型一致
SELECT * FROM users WHERE phone = '13800138000';
反模式 2：函数包裹索引列
-- created_at 有索引，但 DATE() 函数让索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2025-04-17';
-- 修复方案 A：改为范围查询
SELECT * FROM orders
WHERE created_at >= '2025-04-17'
AND created_at < '2025-04-18';
-- 修复方案 B：表达式索引
CREATE INDEX idx_orders_date ON orders(DATE(created_at));
反模式 3：SELECT * 阻止覆盖索引
-- 即使有完美的索引，SELECT * 也得回表
SELECT * FROM orders WHERE status = 'pending';
-- 只取需要的列，配合覆盖索引
SELECT id, user_id, total FROM orders WHERE status = 'pending';
反模式 4：OR 条件导致索引失效
-- OR 通常让优化器放弃索引
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '138';
-- 修复：用 UNION 拆分
SELECT * FROM users WHERE email = 'a@b.com'
UNION
SELECT * FROM users WHERE phone = '138';
反模式 5：大 OFFSET 分页
-- 第 10000 页，PG 需要扫描并丢弃前 100000 行
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- 修复：游标分页（keyset pagination）
SELECT * FROM orders
WHERE id > 上一页最后一条的id
ORDER BY id LIMIT 10;
第四步：统计信息与配置调优
保持统计信息新鲜
-- 手动更新统计信息（大批量写入后）
ANALYZE orders;
-- 查看自动 ANALYZE 的执行情况
SELECT relname, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
统计信息过时 → 估算行数偏差大 → 优化器选错计划。如果 rows 估算和 actual rows 差 10 倍以上，先跑 ANALYZE。
关键内存参数
# postgresql.conf
# 每个查询的排序/哈希内存（默认 4MB 太小）
work_mem = 64MB
# 共享缓冲区（推荐物理内存的 25%）
shared_buffers = 4GB
# 告诉优化器系统可用内存（推荐物理内存的 50-75%）
effective_cache_size = 12GB
# 随机读成本（SSD 设为 1.1，HDD 保持 4.0）
random_page_cost = 1.1
random_page_cost 对 SSD 尤其重要——默认值 4.0 是 HDD 时代的设定，会让优化器过度偏好 Seq Scan。
第五步：实战诊断流程
把上面的知识串起来，我日常的慢查询诊断流程是：
1. 开启 pg_stat_statements 或 auto_explain 捕获慢查询
↓
2. EXPLAIN (ANALYZE, BUFFERS) 拿到执行计划
↓
3. 检查估算 vs 实际行数差距 → 需要 ANALYZE？
↓
4. 定位最耗时的节点：
- Seq Scan → 缺索引？选择性够不够？
- Sort → work_mem 够不够？能加排序索引？
- Nested Loop × 高 loops → 能改 Hash Join？
↓
5. 设计索引方案，用 EXPLAIN 验证
↓
6. 生产部署：CREATE INDEX CONCURRENTLY（不锁表）
一个真实案例
某 SaaS 系统的订单查询从 3 秒优化到 15 毫秒：
-- 原始查询：3.2s
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.tenant_id = 42
AND o.status = 'completed'
AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;
-- 诊断发现：
-- 1. orders 只有 (id) 的主键索引
-- 2. 全表扫描 200 万行，过滤到 8000 行
-- 3. 再排序、取 20 行
-- 修复：一个精准的复合索引
CREATE INDEX CONCURRENTLY idx_orders_tenant_query
ON orders(tenant_id, status, created_at DESC)
INCLUDE (total, user_id);
-- 优化后：15ms，Index Only Scan
关键不是"加索引"，而是加对索引——列顺序、排序方向、INCLUDE 列，每个细节都影响最终效果。
总结
PostgreSQL 查询优化的核心方法论可以浓缩为：
- EXPLAIN 先行：不猜测，用数据说话
- 选择性驱动：索引不是越多越好，高选择性列优先
- 复合索引设计：等值在前、范围在后、排序收尾
- 消除反模式：类型转换、函数包裹、SELECT *、大 OFFSET
- 统计信息 + 配置：ANALYZE 保鲜，work_mem / random_page_cost 别用默认值
数据库调优没有银弹，但掌握了 EXPLAIN 和索引设计的基本功，90% 的慢查询都能在 30 分钟内搞定。
本文基于 PostgreSQL 16，部分语法在 PG 11+ 兼容。