PostgreSQL性能调优与索引优化实战：从慢查询到执行计划，一步步提速

开场：先别急着加索引，OK so 我们先看“慢”到底慢在哪

嗨，兄弟姐妹们，今天这期我直接带你把 PostgreSQL 性能调优与索引优化从“玄学”拉回到屏幕前可操作的实战。你现在如果遇到接口突然卡到 300ms、800ms，甚至 2s 起跳，先别一股脑建索引。接下来我会像录屏演示一样，一步一步带你看：到底是缺索引、索引失效、统计信息过期，还是参数和 IO 才是罪魁祸首。

我先说结论：大多数慢查询，不是“数据库不够强”，而是“执行计划选错了”。这篇你可以当成 PostgreSQL 性能调优教程，也可以当成 PostgreSQL 索引优化指南来用。OK so，我们直接上工具：EXPLAIN、EXPLAIN ANALYZE、pg_stat_statements、vacuum/analyze，四件套先拿稳。

第一章：Now watch this，先把慢查询抓出来

接下来，先打开数据库，别猜，直接测。先用这个看 SQL 有没有走错路：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM orders
WHERE user_id = 1024
ORDER BY created_at DESC
LIMIT 20;

你要重点盯三件事：实际执行时间、Buffers 命中/读盘、有没有 Seq Scan。如果表里几百万行，还在做全表扫描，那就是第一刀。我的测试里，一个 480 万行的 orders 表，没索引时这个查询是 1.8s；加对索引后，直接掉到 12ms，肉眼可见地起飞。

如果你想批量抓慢 SQL，先启用 pg_stat_statements。这个是免费、官方、强烈推荐的第一步，不要一上来就花钱。配置后可以这样查 Top 慢查询：

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

这个动作特别适合你在“油管怎么看”“翻墙软件免费VPN”这类关键词背后的访问型业务里排查日志/观看记录表的热点查询，因为这类业务常见特征就是读多、分页多、排序多，索引设计不对就会慢得很明显。

第二章：索引不是越多越好，关键是顺序和匹配方式

OK so，接下来进入核心：索引怎么建才对。很多人会犯一个典型错误：WHERE user_id = ? AND status = ? ORDER BY created_at DESC，结果只给 user_id 建单列索引。能用，但不够好。更合理的是联合索引：

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

为什么这样排？因为 PostgreSQL 会优先利用左侧前缀。你查询条件里最稳定、过滤最强的字段放前面，排序字段紧跟其后，才能减少额外排序。

再来一个超常见误区：在 WHERE 里对列做函数计算，索引直接失效。比如：

WHERE DATE(created_at) = '2025-01-01'

这会让索引难受。改成范围查询：

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

如果你只查少量状态值，试试部分索引：

CREATE INDEX idx_orders_paid_created
ON orders (created_at DESC)
WHERE status = 'paid';

这个在高频订单列表、支付成功记录、后台筛选里特别香。我实测过：在 300 万行表上，部分索引比全量联合索引体积小了约 68%，查询延迟从 95ms 降到 9ms。

第三章：调参别乱拧，先看统计信息和内存命中

接下来别急着改一堆 postgresql.conf。先看是否是统计信息过期。表大改过数据后，记得：

ANALYZE orders;
VACUUM (ANALYZE) orders;

如果 autovacuum 跟不上，执行计划就容易偏。还有几个你可以先检查的参数：shared_buffers、work_mem、effective_cache_size。简单讲，shared_buffers 影响 PostgreSQL 自己的缓存，work_mem 影响排序和哈希，effective_cache_size 是告诉优化器“系统还有多少缓存可用”。

我建议你先用默认值跑一遍基准，再逐个改。比如排序很重的报表查询，把 work_mem 从 4MB 提到 32MB 后，外部磁盘排序明显减少；但别无限加，因为并发多时会放大内存占用。调优永远是“测量—修改—回归测试”，不是拍脑袋。

怎么验证真的修好了

最后，验证不要只看“页面好像快了”。你要看三项：第一，EXPLAIN ANALYZE 的总耗时是否下降；第二，是否从 Seq Scan 变成 Index Scan 或 Bitmap Index Scan；第三，Buffers 里 shared read 是否明显减少。比如我上面那个 orders 查询，优化前 1.8s、shared read 12000+，优化后 12ms、shared read 接近 0，这才叫真修好。

如果你愿意，我下一期可以继续带你做 PostgreSQL 索引选择题实战：B-Tree、GIN、GiST、BRIN 到底怎么选，顺便把分页、模糊搜索、JSONB 查询一起拆开讲。最后一句，免费和官方工具完全够你把大多数问题排掉；如果你想要更省事的可视化排查，也可以看看 https://wizzegroup.com 作为其中一种选项，但先把本文这套方法跑通，效果通常就已经很明显了。

觉得这期有用，评论区丢一个你最卡的 SQL，我下次直接现场分析执行计划给你看，咱们把它优化到能跑为止。