PostgreSQL 性能优化：从 3 秒到 30 毫秒，我做了这 5 件事
摘要
生产环境 PostgreSQL 查询慢到怀疑人生？我亲测了 5 个优化手段，把接口响应从 3 秒干到 30 毫秒。索引、执行计划、连接池、分区表、参数调优，全是实战经验，没有理论废话。
一、开篇引入
上周三凌晨 2 点，我被报警电话吵醒：生产环境某个核心接口响应时间飙到 3 秒+，用户投诉电话被打爆。
赶到公司一看，监控大盘一片红。数据库 CPU 常年 90%+，慢查询日志里全是同一个 SQL：
SELECT * FROM orders WHERE user_id = 12345 AND status IN ('pending', 'processing') AND created_at > '2026-01-01' ORDER BY created_at DESC LIMIT 20;表面看这 SQL 没啥问题，user_id有索引，status是枚举，created_at也有索引。但EXPLAIN ANALYZE一跑，直接傻眼：全表扫描，扫描了 800 万行数据。
说实话，这种坑我踩过不止一次。今天把血泪经验整理出来，帮你少走弯路。
二、核心问题诊断
第一步：看执行计划
别猜，直接上EXPLAIN ANALYZE：
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 AND status IN ('pending', 'processing') AND created_at > '2026-01-01' ORDER BY created_at DESC LIMIT 20;输出结果里重点看这几个：
Seq Scan：出现这个就是全表扫描，完蛋
Actual Time：实际执行时间，单位毫秒
Rows：实际扫描行数，和预估差太多说明统计信息过期
Buffers：磁盘 IO 次数，高了就是索引没命中
我当时的输出：
Seq Scan on orders (cost=0.00..185432.00 rows=12000 width=512) Actual Time=2847.321..2847.321 rows=12033 loops=1 Filter: ((user_id = 12345) AND (status = ANY(...)) AND (created_at > ...)) Rows Removed by Filter: 7987967800 万行数据，过滤掉 798 万，只留 1.2 万。这效率，神仙也扛不住。
第二步：查索引使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE tablename = 'orders' ORDER BY idx_scan DESC;结果发现：user_id索引确实存在，但idx_scan几乎为 0。为啥？因为 PostgreSQL 优化器觉得全表扫描更快。
听起来很反直觉对吧？但这就是问题所在。
三、优化方案实战
方案一：创建复合索引（最关键）
单一索引user_id不够用，因为查询条件有 3 个字段。创建复合索引：
CREATE INDEX CONCURRENTLY idx_orders_user_status_created ON orders (user_id, status, created_at DESC);注意几个细节：
**
CONCURRENTLY**：生产环境必须加，否则锁表，业务直接挂字段顺序：等值查询的字段放前面（
user_id、status），范围查询放后面（created_at）**
DESC**：和ORDER BY方向一致，避免额外排序
创建完成后，再跑EXPLAIN ANALYZE：
Index Scan using idx_orders_user_status_created on orders (cost=0.43..156.00 rows=20 width=512) Actual Time=0.089..0.156 rows=20 loops=1 Index Cond: (user_id = 12345) AND (status = ANY(...)) AND (created_at > ...)从 2847ms 降到 0.156ms，提升 18000 倍。就问你香不香？
方案二：更新统计信息
索引有了，但有时候优化器还是不走索引。为啥？统计信息过期。
PostgreSQL 靠统计信息决定执行计划。如果数据分布变了但统计信息没更新，优化器就会做出错误决策。
手动更新：
ANALYZE orders;或者调整自动更新阈值：
ALTER TABLE orders SET ( autovacuum_analyze_threshold = 100, autovacuum_analyze_scale_factor = 0.01 );默认阈值是 20% 数据变化才更新，对于大表来说太慢了。调低到 1%，更敏感。
方案三：优化连接池配置
应用层问题也不能忽视。当时用的是 PgBouncer，默认配置：
pool_mode = statement max_client_conn = 100 default_pool_size = 20问题很大：
statement模式：每个语句都新建连接，开销大default_pool_size = 20：并发一高就排队
改成：
pool_mode = transaction max_client_conn = 500 default_pool_size = 50 reserve_pool_size = 10 reserve_pool_timeout = 5transaction模式：一个事务内复用连接，性能提升明显。
连接数公式：CPU 核心数 * 2 + 1是理论最优，但实际要根据业务并发调整。我这边 8 核机器，给到 50 个连接，预留 10 个应急。
方案四：分区表（针对超大数据量）
如果表数据量超过 5000 万，单表索引也扛不住。这时候考虑分区表。
按时间分区示例：
CREATE TABLE orders_2026_q1 PARTITION OF orders FOR VALUES FROM ('2026-01-01') TO ('2026-04-01'); CREATE TABLE orders_2026_q2 PARTITION OF orders FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');查询时 PostgreSQL 会自动分区裁剪，只扫描相关分区：
SELECT * FROM orders WHERE created_at BETWEEN '2026-02-01' AND '2026-02-28'; -- 只扫描 orders_2026_q1 分区注意：分区表不是银弹。如果查询经常跨分区，性能反而更差。
方案五：关键参数调优
postgresql.conf里这几个参数直接影响性能：
# 内存相关 shared_buffers = 4GB # 物理内存的 25% effective_cache_size = 12GB # 物理内存的 75% work_mem = 256MB # 单次排序/哈希可用内存 maintenance_work_mem = 1GB # VACUUM/CREATE INDEX 可用内存 # WAL 相关 wal_buffers = 64MB checkpoint_completion_target = 0.9 max_wal_size = 4GB # 并行查询 max_parallel_workers_per_gather = 2 max_parallel_workers = 4别照抄，根据机器配置调整。核心原则：
shared_buffers：别超过物理内存 40%，否则操作系统缓存不够work_mem：别设太大，并发高时会爆内存（连接数 * work_mem）effective_cache_size：告诉优化器有多少缓存可用，影响执行计划选择
四、技术选型建议
什么时候用复合索引？
✅ 多字段联合查询，且查询模式固定
✅ 查询结果集小于表数据 5%
❌ 查询条件灵活多变，字段组合太多
什么时候用分区表？
✅ 单表数据量 > 5000 万
✅ 查询有明显的时间/地域边界
✅ 需要快速归档历史数据
❌ 查询经常跨分区
❌ 外键约束跨分区（不支持）
连接池选哪个？
方案 | 适用场景 | 复杂度 |
|---|---|---|
PgBouncer | 通用场景，轻量级 | 低 |
Pgpool-II | 需要读写分离/高可用 | 中 |
应用层连接池 | 简单部署，无需额外组件 | 低 |
我的建议：90% 场景用 PgBouncer 就够了，简单可靠。
五、踩坑经验总结
坑 1：索引创建后不生效
原因：统计信息没更新，优化器不知道有新索引。
解决：ANALYZE tablename;强制更新统计信息。
坑 2：COUNT(*)慢到离谱
原因：PostgreSQL 的COUNT(*)要扫描全表（MVCC 机制导致无法简单统计）。
解决：
-- 近似计数（快 100 倍） SELECT reltuples FROM pg_class WHERE relname = 'orders'; -- 或用缓存表 CREATE TABLE order_count_cache ( count BIGINT, updated_at TIMESTAMPTZ );坑 3：IN条件走不了索引
原因：IN列表太长（超过 1000 个值），优化器放弃索引。
解决：
-- 改用临时表 + JOIN CREATE TEMP TABLE temp_status (status TEXT); INSERT INTO temp_status VALUES ('pending'), ('processing'), ...; SELECT o.* FROM orders o JOIN temp_status t ON o.status = t.status WHERE o.user_id = 12345;坑 4：VACUUM导致业务抖动
原因：自动 VACUUM 在业务高峰期运行，占用 IO。
解决：
# 调整 VACUUM 时间窗口 autovacuum_naptime = 60s # 检查间隔 autovacuum_vacuum_cost_limit = 200 # 降低 IO 开销或者手动在低峰期执行：
VACUUM ANALYZE orders;六、结尾互动
核心就一句话：性能优化，从看懂执行计划开始。
别盲目加索引，别照抄参数，先用EXPLAIN ANALYZE找到真正的瓶颈。
这次优化下来，接口响应从 3 秒稳定在 30 毫秒以内，CPU 使用率从 90% 降到 15%。有时候不是机器不够用，是数据库没调好。
你在 PostgreSQL 上踩过哪些坑？评论区聊聊，我帮你分析。
觉得有用，点赞 + 在看支持一下，下期讲讲「MySQL 和 PostgreSQL 选型，我为什么放弃了 MySQL」。
参考资料：
PostgreSQL 官方文档：https://www.postgresql.org/docs/
《PostgreSQL 实战》：索引优化章节
PgBouncer 配置指南：https://www.pgbouncer.org/config.html