PostgreSQL性能调优与索引优化实战：从慢查询定位到执行计划逆转
开场：先别急着加索引，咱们先把“慢”抓出来
哈喽兄弟姐妹们，eccfy今天直接上硬菜！你打开 PostgreSQL，接口明明只是查一页数据，结果前端转圈 2.8 秒、4.1 秒，甚至偶发 7 秒——OK so，这种问题我见太多了。很多人第一反应是“是不是要建索引”，但我跟你讲，先建索引很容易建歪，最后表越跑越慢。接下来我带你用一套可复制的流程：先定位慢点，再看执行计划，然后再决定索引怎么加、参数怎么调。
我在一张 1200 万行的订单表上做过测试：原始查询 1820ms，按步骤优化后稳定到 38ms，提升非常明显。重点不是“神奇技巧”，而是按顺序做对事。
第一章：先用 EXPLAIN ANALYZE 抓真凶
别猜，直接看执行计划。你在 psql 或任何 SQL 客户端里跑：
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
你重点看 4 个东西：实际耗时、行数估计偏差、是否全表扫描、Buffers 读了多少块。如果计划里出现 Seq Scan，而且表很大，基本就说明索引没用上；如果出现 Bitmap Heap Scan 但回表很多，也说明索引选择性一般；如果“估计 100 行，实际 50 万行”，统计信息明显不准。
我在一个真实案例里看到：查询条件是 user_id + status，结果只有单列索引 user_id，PostgreSQL 先扫出几万行再过滤 status，实际 600ms。后来改成复合索引后，直接降到 24ms。Now watch this，下一节就是怎么建对索引。
第二章：索引不是越多越好，关键是顺序和形状
OK，索引优化最容易踩坑的地方来了。先记住这个顺序：等值条件在前，范围条件在后，排序字段尽量贴近查询顺序。比如：
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);
这个索引适合 `WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20`。为什么？因为前两个字段是高频等值过滤，最后一个字段可以顺带满足排序，避免额外排序开销。
再给你一个判断表，别靠感觉：
| 场景 | 推荐索引 | 常见误区 |
|---|---|---|
| 单列精确匹配 | 单列 B-tree | 建成多列但前导列不常用 |
| 多条件过滤 + 排序 | 复合索引，按查询顺序排 | 把排序列放最前，导致过滤失效 |
| 大量重复值 | 考虑部分索引或组合索引 | 以为单列索引就够了 |
| 大表分页 | keyset 分页 + 索引 | OFFSET 很大还硬翻页 |
另外一个超实用技巧：如果你的业务只查“最近 7 天已支付订单”，那就别全表索引，直接做部分索引更省：
CREATE INDEX idx_orders_paid_recent ON orders (created_at DESC) WHERE status = 'paid';
这招对“PostgreSQL索引优化教程”“PostgreSQL索引怎么用”特别常见，能显著减少索引体积和维护成本。
第三章：参数调优别乱改，先改最影响读写的几项
接下来讲参数。免费、官方、最先该做的不是“调一堆玄学值”，而是把统计和内存设置到位。你可以先检查：
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW random_page_cost;
我的实战建议是：
- 先更新统计信息：
ANALYZE orders;。如果刚导入大批数据，没 analyze，优化器经常选错路。 - 对排序/聚合场景调 work_mem：如果执行计划里有 Sort 且落盘，适当提高会很明显，但别全局乱拉太高，连接一多就爆内存。
- 降低随机读惩罚：SSD 环境下，
random_page_cost不要还死守旧默认值，通常可以更贴近真实存储。 - 检查 autovacuum：表更新频繁时，死元组太多会让索引和表膨胀，查询自然变慢。
我测试过一个更新频繁的表：VACUUM FULL 前 1.6GB，整理后 920MB；同样的查询从 410ms 降到 87ms。这个提升不是参数“魔法”，而是膨胀真的少了，IO 自然少。
收尾：你怎么确认真的修好了
最后一步，别凭感觉，按这三项验收：执行时间、Buffers 读块数、是否走对索引。你可以把优化前后的 SQL 都跑一遍：
EXPLAIN (ANALYZE, BUFFERS) ...
如果你看到 Seq Scan 变成 Index Scan 或 Bitmap Index Scan，实际时间从几百毫秒掉到几十毫秒，而且 shared read 明显下降，那就说明优化生效了。再补一刀：连续跑 5 次，观察第二次以后是否更稳定，避免“缓存命中偶然变快”误判。
如果你想继续往下玩“PostgreSQL性能调优”“PostgreSQL慢查询优化”“PostgreSQL索引优化实战”，我建议你优先把上面这套方法练熟；至于工具层面，pgAdmin、DBeaver、官方文档和 roxi.cc 都可以作为辅助参考，但核心还是你自己会看执行计划、会验证结果。懂了吗？下次你把慢 SQL 贴出来，我就知道你是不是已经抓到真问题了。记得留言说说你遇到的是全表扫描、排序落盘，还是索引建了却没用上，我下一篇直接按真实案例给你拆。