PostgreSQL索引填充因子如何影响查询计划与索引使用决策？

核心原理：填充因子通过影响索引成本估算改变查询计划 #

PostgreSQL的查询优化器会依据索引的物理页面数量、I/O开销来对比索引扫描和顺序扫描的成本。填充因子（fillfactor）是控制索引页面预留空闲空间的参数：

• 填充因子越低，索引创建时每个页面存储的条目越少，总页面数就越多，优化器估算的索引扫描I/O成本越高
• 当索引扫描的估算成本超过全表顺序扫描的成本时，优化器就会放弃索引，选择顺序扫描

你遇到的现象具体原因 #

1. 填充因子75时不使用索引：设置75的填充因子后，每个索引页面仅使用75%的空间，剩余25%留作后续更新插入。这直接导致索引总页面数远高于填充因子80的索引，优化器计算出的索引扫描成本超过了顺序扫描，因此选择了全表扫描。
2. 插入数据后恢复使用索引：插入新数据时，索引页面的预留空闲空间被逐步占用，实际填充率超过了75%，索引的总页面数随之减少。优化器重新估算后，认为索引扫描的成本低于顺序扫描，于是再次选用索引。

频繁增删+定期VACUUM场景的填充因子选择 #

需要结合读写比例、数据变化频率综合权衡：

• 读为主、增删少的场景：用默认填充因子（B-tree索引默认90）即可，最大化索引存储密度，降低I/O成本，让优化器更倾向于选择索引。
• 频繁增删且定期VACUUM的场景：
  • 不能设太高：过高的填充因子会导致更新索引时频繁触发页面分裂，加剧索引碎片化，反而降低性能。
  • 不能设太低：过低的填充因子会让索引页面数量过多，容易被优化器判定为成本过高，放弃使用索引。
  • 建议测试80-90区间的填充因子，观察查询计划是否稳定选用索引，同时通过pg_stat_user_indexes、pg_indexes_size监控索引碎片化程度和大小，找到平衡点。
• 定期VACUUM能清理索引中的无效条目，缓解碎片化，但无法抵消填充因子设置不合理带来的索引页面数量膨胀问题，仍需合理设置参数。

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