PostgreSQL为何优先选择全索引而非Canceled专属部分索引？

问题背景 #

在PostgreSQL 17.2环境下操作postgres_air数据库，执行以下查询：

SELECT status
FROM postgres_air.flight
WHERE status = 'Canceled';

已创建两个索引：

-- 全字段B树索引
CREATE INDEX flight_status_index ON flight(status);

-- 仅包含`status='Canceled'`记录的部分索引
CREATE INDEX flight_canceled ON flight(status)
WHERE status = 'Canceled';

根据《PostgreSQL Query Optimization（第二版）》第90页内容，优化器应优先选择部分索引，但实际测试中优化器预估全索引扫描成本更低。而实际执行时，部分索引（执行时间0.066ms）比全索引（0.080ms）更快，且降级至书中对应版本后问题依旧。

优化器选择全索引的核心原因 #

1. 统计信息失真 #

PostgreSQL优化器完全依赖表和索引的统计信息估算成本。如果flight表的统计信息未及时更新，优化器无法精准感知部分索引的实际体积和扫描开销：

• 执行ANALYZE flight;更新统计信息后，重新生成执行计划，观察预估成本是否修正。
• 通过pg_stat_user_indexes、pg_index系统视图对比两个索引的实际行数、磁盘占用，确认与优化器预估的rows、cost是否匹配。

2. 缓存状态干扰 #

若全索引已被加载至shared_buffers内存缓存，而部分索引仍存储在磁盘，优化器会预估全索引的IO成本更低：

• 实际执行时部分索引更快，可能是测试过程中缓存状态发生变化，或是部分索引的缓存命中率在实际执行中更高。
• 查看pg_stat_user_indexes中的idx_scan、idx_tup_read、idx_tup_fetch字段，分析索引的缓存和使用情况。

3. 成本模型的系数偏差 #

PostgreSQL的成本计算依赖预设系数（如random_page_cost、seq_page_cost），部分索引的WHERE过滤逻辑可能被优化器高估开销：

• 部分索引的条件过滤本身几乎无开销，但优化器的成本模型可能为这一步分配了额外权重，导致预估成本高于全索引。
• 对于极小结果集（仅171条记录），成本计算的细微偏差会被放大，使优化器做出不符合实际执行效率的选择。

4. 小数据量的边际差异 #

当查询返回结果集极小时，全索引与部分索引的扫描开销差距本身处于微秒级，优化器的成本模型无法精准区分两者的差异：

• 实际执行时间的差距（0.080ms vs 0.066ms）在大多数业务场景下可忽略，优化器可能判定两种方案的成本差异不足以触发部分索引的选择。

验证与调整建议 #

• 强制指定索引测试：执行SET enable_seqscan = off; SET enable_indexscan = off; SET enable_bitmapscan = off;后重新执行查询，确认部分索引的执行效率。
• 精细化统计信息：执行ANALYZE VERBOSE flight;，查看输出中status字段及两个索引的统计数据是否准确反映实际情况。
• 调整成本参数：临时降低random_page_cost（如SET random_page_cost = 1.1;）模拟内存缓存充足场景，观察优化器是否切换至部分索引。

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