PostgreSQL同查询改参数后分别走seq scan与index scan原因咨询

该现象是数据库优化器基于表统计信息的执行计划代价估算差异导致的。

• 你创建的(period_id, day_id, value_type, indicator_id)联合B树索引完全匹配两个查询的WHERE条件前缀，理论上两个查询都可以走索引扫描，最终执行计划的差异和查询匹配的行数预估直接相关。
• 数据库优化器选择执行计划的核心逻辑是比较不同执行路径的总IO代价：
  • 顺序扫描的代价为全表所有数据块的连续读取总开销，连续IO的单位代价很低
  • 索引扫描的代价为索引块随机读取开销 + 回表读取完整数据行的随机IO开销，随机IO的单位代价远高于连续IO
• 当day_id=30时，数据库统计信息记录的该条件组合的匹配行数占比很高：比如2021年4月30日是月末，交易数据量占全月总量的比例超过阈值（通常在20%~30%左右），此时索引扫描需要执行大量随机IO回表取数，总代价高于一次性顺序扫描全表，优化器就会选择顺序扫描。
• 当day_id=20时，该条件组合的匹配行数占比很低，索引扫描+少量随机回表的总代价低于全表扫描，优化器就会选择索引扫描。

• 你可以执行EXPLAIN ANALYZE分别运行两个查询，对比优化器预估的匹配行数和实际返回行数，如果两者偏差较大，说明表统计信息过时，可以执行ANALYZE transaction;更新统计信息后再验证执行计划。
• 如果把查询语句的select *改为仅查询索引包含的4个列，会触发索引仅扫描（不需要回表），无论匹配行数多少，优化器都会优先选择走索引。

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