PostgreSQL 9000万行cars表特定查询偶发耗时1分钟排查求助

• 当前仅有的index_on_garage_id单列索引不足以覆盖查询逻辑：执行查询时需要先通过该索引扫出所有garage_id=7777的行，再回表过滤type='hybrid'的条件，最后对所有符合条件的行按id倒排取第一条。你提供的正常执行计划里需要读取近4000个数据页，当这些页刚好不在PostgreSQL共享缓冲区、也不在OS页缓存中时，需要触发大量随机磁盘IO，就会出现偶发的耗时飙升，机械盘场景下这个问题会格外明显。
• 热数据被挤出缓存：如果实例上有其他大查询、或者副本执行了checkpoint、vacuum等操作，可能会把该查询依赖的热数据页从缓存中清理出去，首次查询时就需要从磁盘读取，导致耗时变长。
• 部分车库对应的数据量波动：如果某类garage_id关联的车辆数远高于平均值，需要扫描和过滤的行数会大幅增加，缓存未命中时耗时会进一步拉长到分钟级。

最优方案（从根本上解决问题） #

创建匹配查询逻辑的联合索引，避免回表过滤和排序操作：

-- 主库执行，CONCURRENTLY参数不会锁表，不影响线上写入
CREATE INDEX CONCURRENTLY idx_garage_type_id_desc ON cars (garage_id, type, id DESC);

该索引建立后，查询可以直接定位到garage_id=7777且type='hybrid'的行，且索引内已经按id倒序排列，直接取第一条即可，最多只需要读取个位数的索引页，哪怕缓存未命中也只会触发极少量IO，完全消除偶发慢查询的可能。

辅助优化措施 #

• 开启auto_explain插件记录慢查询的实际执行计划，确认慢查询发生时是否存在执行计划偏移、走错索引的情况。
• 合理调整shared_buffers参数，建议设置为系统内存的25%~40%，扩大缓存容量降低热数据被挤出的概率。
• 若当前使用机械盘存储，建议更换为SSD，大幅提升随机IO性能，缓解缓存未命中时的耗时涨幅。
• 定期更新表统计信息，避免统计信息不准导致执行计划异常：

ANALYZE VERBOSE cars;

内容的提问来源于stack exchange，提问作者ddd