PostgreSQL中匹配查询时间范围的部分索引未被查询计划器选用的问题

看起来你遇到了部分索引的典型坑——明明索引的过滤条件和查询范围对齐，但查询计划器就是不肯选用它对吧？我来帮你拆解下可能的原因和对应的排查解决办法：

先明确你的场景：你把全表的original_index替换成了仅包含key10 >= '2023-06-01 00:00:00+00'数据的部分索引constrained_index，但同样的查询在旧索引时能被计划器选用，新索引却不行。

可能的原因及排查步骤： #

• 查询条件与索引过滤规则不完全匹配
  这是最常见的诱因。比如你的查询用了动态时间条件（比如key10 >= NOW() - INTERVAL '1 year'），而索引里是硬编码的固定时间。PostgreSQL的查询计划器是静态规划的，它没法提前确定NOW() - 1年的结果是否完全落在索引覆盖的范围内（比如当前时间是2024-05的话，查询范围是2023-05，就会超出索引的2023-06起点）。哪怕实际运行时范围完全匹配，计划器也不敢冒险选用这个索引。
  测试方法：把查询里的时间条件改成和索引完全一致的硬编码值key10 >= '2023-06-01 00:00:00+00'，再跑EXPLAIN ANALYZE看看是否能用上索引。

• 统计信息过时
  新索引创建后，如果没更新表的统计信息，计划器可能不知道这个索引的实际大小、选择性这些关键数据，会误以为它的查询成本比顺序扫描更高。
  解决办法：执行ANALYZE table1;强制更新统计信息，之后再查看查询计划。

• 查询返回的数据量占比过高
  如果你的查询要返回的行数占索引覆盖数据的比例很高（比如超过30%），计划器会认为顺序扫描比索引扫描更高效——因为索引扫描需要先查索引再回表取数据，反而不如直接扫表来得快。
  你可以用EXPLAIN ANALYZE查看计划里的"Rows"预估数，对比表中符合key10 >= '2023-06-01'的实际行数，判断是不是这个情况。

• 时区或数据类型不匹配
  索引里用的是timestamp with time zone，如果查询条件里用的是不带时区的timestamp，会发生隐式类型转换，导致索引无法被匹配。比如查询里写key10 >= '2023-06-01'（不带时区），PostgreSQL会把它转换成当前时区的时间，可能和索引里的+00时区值不匹配，或者转换后无法利用索引。
  检查查询条件里的时间值是否和索引用的是同一种类型，最好显式指定时区，比如'2023-06-01 00:00:00+00'::timestamp with time zone。

• 索引本身存在问题
  可以尝试强制指定索引来测试：

  SELECT ... FROM table1 INDEX USING constrained_index WHERE ...;
  

  如果强制后能用上索引，说明索引本身没问题，只是计划器的成本估算有偏差；如果强制后还是不行，那可能索引创建时有错误（比如是不是把>=写成>了？）。

额外建议： #

如果你的业务确实只需要最近1年的数据，后续可以考虑用分区表替代部分索引：按时间分区，最近1年的分区单独建索引，查询时会自动扫描对应分区，效率更高，还不用手动更新索引的过滤条件。

备注：内容来源于stack exchange，提问作者Chamath