摘要生产环境 PostgreSQL 查询慢到怀疑人生我亲测了 5 个优化手段把接口响应从 3 秒干到 30 毫秒。索引、执行计划、连接池、分区表、参数调优全是实战经验没有理论废话。一、开篇引入上周三凌晨 2 点我被报警电话吵醒生产环境某个核心接口响应时间飙到 3 秒用户投诉电话被打爆。赶到公司一看监控大盘一片红。数据库 CPU 常年 90%慢查询日志里全是同一个 SQLSELECT * 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 ANALYZEEXPLAIN 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 (cost0.00..185432.00 rows12000 width512) Actual Time2847.321..2847.321 rows12033 loops1 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 ANALYZEIndex Scan using idx_orders_user_status_created on orders (cost0.43..156.00 rows20 width512) Actual Time0.089..0.156 rows20 loops1 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_memeffective_cache_size告诉优化器有多少缓存可用影响执行计划选择四、技术选型建议什么时候用复合索引✅ 多字段联合查询且查询模式固定✅ 查询结果集小于表数据 5%❌ 查询条件灵活多变字段组合太多什么时候用分区表✅ 单表数据量 5000 万✅ 查询有明显的时间/地域边界✅ 需要快速归档历史数据❌ 查询经常跨分区❌ 外键约束跨分区不支持连接池选哪个方案适用场景复杂度PgBouncer通用场景轻量级低Pgpool-II需要读写分离/高可用中应用层连接池简单部署无需额外组件低我的建议90% 场景用 PgBouncer 就够了简单可靠。五、踩坑经验总结坑 1索引创建后不生效原因统计信息没更新优化器不知道有新索引。解决ANALYZE tablename;强制更新统计信息。坑 2COUNT(*)慢到离谱原因PostgreSQL 的COUNT(*)要扫描全表MVCC 机制导致无法简单统计。解决-- 近似计数快 100 倍 SELECT reltuples FROM pg_class WHERE relname orders; -- 或用缓存表 CREATE TABLE order_count_cache ( count BIGINT, updated_at TIMESTAMPTZ );坑 3IN条件走不了索引原因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;坑 4VACUUM导致业务抖动原因自动 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
