PostgreSQL添加received_at过滤后索引查询慢10倍问题排查

问题背景 #

为限制返回行数，在PostgreSQL 15的查询中添加了以下过滤条件：

...
where 
     received_at >= current_date - interval '2 years'
...

原本预期优化器会使用received_at字段上的索引，但实际查询耗时反而增加了10倍。对比执行计划发现，使用索引的过滤查询产生了大量IO。

原因分析 #

• 索引选择性不足：如果过滤条件匹配的数据占表总数据量的比例过高（通常超过20%-30%），索引扫描效率会远低于全表扫描。索引扫描需要先读索引条目，再随机读取对应数据块，产生大量随机IO；而全表扫描是顺序读取数据块，在数据占比高时，顺序IO的吞吐量远高于随机IO。
• 统计信息不准确：PostgreSQL优化器依赖表的统计信息判断执行计划。如果received_at字段的统计信息过时或未正确收集，优化器会错误估算过滤后的数据量，误以为适合用索引扫描，但实际数据量远超预期，导致大量IO开销。
• 索引类型不匹配：若当前使用B-tree索引，但表中received_at数据是按时间顺序插入的，此时BRIN索引更适合大范围时间过滤场景——BRIN索引体积小，扫描时IO开销远低于B-tree索引。

最佳下一步操作 #

• 验证数据占比：执行以下SQL计算过滤后数据占表总数据的比例：

  -- 获取过滤后的数据量
  SELECT COUNT(*) AS filtered_rows FROM 你的表名 WHERE received_at >= current_date - interval '2 years';
  -- 获取表总数据量
  SELECT COUNT(*) AS total_rows FROM 你的表名;
  

  如果占比超过20%-30%，说明索引选择性差，可临时关闭索引扫描测试全表扫描性能：SET enable_indexscan = off;，若性能提升，可考虑让优化器优先选择全表扫描。
• 更新统计信息：执行ANALYZE 你的表名;，让PostgreSQL重新收集表的最新数据分布统计，帮助优化器做出更合理的执行计划选择。
• 更换索引类型：如果表数据是按received_at顺序插入的，删除原B-tree索引，创建BRIN索引：

  DROP INDEX IF EXISTS idx_received_at;
  CREATE INDEX idx_received_at ON 你的表名 USING brin (received_at);
  

• 对比执行计划细节：重点查看两个执行计划的扫描行数、实际返回行数、IO耗时指标，确认是索引扫描的随机IO导致耗时激增。

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