PostgreSQL 9.5.9为何选择不同索引执行计划？(btree/gist问题)

这是个很常见的优化器决策偏差问题，结合你的PG 9.5.9场景，我来拆解下背后的原因和解决思路：

核心原因：优化器的成本估算偏差 #

PostgreSQL的查询优化器是基于成本模型来选择执行计划的，当你的查询条件从column BETWEEN a AND b变成column = a（因为上下边界相同），优化器会重新评估所有可用索引的成本，而老版本的PG在这个场景下对GIST索引的成本估算可能出现偏差：

1. 统计信息不准确 #

PG 9.5的统计信息收集机制对GIST索引的支持不够完善，如果你的表数据分布比较特殊（比如某个值的重复率极高），优化器可能错误估算GIST索引扫描的行数和成本，误以为它比BTREE索引更高效。

2. 等值查询下的索引成本计算缺陷 #

对于等值查询，BTREE索引的成本计算是非常成熟的：优化器会根据索引的选择性估算需要扫描的索引页面数，加上回表的行数成本。但在PG 9.5中，GIST复合索引的成本计算逻辑可能存在漏洞——比如如果你的GIST索引包含了查询需要的所有列，优化器可能误以为可以避免回表（覆盖索引扫描），但实际上GIST索引的等值扫描效率远低于BTREE，这种错误的“覆盖索引”判断会让优化器倾向于选择GIST索引。

3. 老版本优化器的局限性 #

PG 9.5是一个已经停止维护的老版本（官方支持到2021年），在索引成本对比、尤其是GIST与BTREE的决策逻辑上，存在不少已知的优化器bug和不足，新版本（比如12+）已经修复了很多这类问题。

解决办法 #

针对你的场景，可以尝试以下几种方案：

• 更新统计信息：先执行ANALYZE VERBOSE your_table_name;，让优化器获得最准确的数据分布，很多时候这就能纠正错误的成本估算。
• 强制指定BTREE索引：PG 9.5本身没有官方的查询hint，但可以通过pg_hint_plan扩展来强制优化器使用指定索引，比如：

  /*+ IndexScan(your_table idx_hourts_btree) */
  SELECT ... FROM your_table WHERE hourts BETWEEN '2024-01-01' AND '2024-01-01';
  

• 调整索引成本参数：临时降低GIST索引的优先级，比如执行SET enable_indexscan = on; SET enable_gistscan = off;（注意这是会话级别的设置，不要全局修改），或者调整gist_index_scan_cost参数让优化器认为GIST索引的成本更高。
• 升级PG版本：这是长期来看最有效的方案，新版本的优化器在索引选择、成本计算上有极大的改进，能从根本上避免这类问题。
• 调整索引策略：如果GIST索引只用于特定的范围/地理查询，而不用于等值查询，可以考虑拆分索引，或者在等值查询的列上单独创建BTREE索引（如果还没有的话）。

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