如何让PostgreSQL选择效率高6个数量级的最优索引

优化器选错索引的核心原因是PostgreSQL默认统计信息的能力边界：

• 常规ANALYZE只会收集单列的统计数据，默认不会感知created和action_time两个字段的强关联关系——你业务里99.999%的场景下两个时间差值不超过数分钟，属于高度相关字段，但优化器会默认两个字段独立，把两个过滤条件的选择率直接相乘，错误估算两个索引的扫描成本。
• 实际场景里action_time < NOW() - INTERVAL '1 hour'的过滤性仅为0.01%，几乎等于不做过滤，但优化器不知道这个条件和created > NOW() - INTERVAL '25 hour'的绑定关系，错误认为两个条件叠加后可以过滤掉绝大多数数据，才会选择第二列为action_time的低效索引。
• 你执行计划里估算的符合条件行数约20万，刚好是单组织单日的全量数据量，也印证了优化器完全没算对两个时间条件的实际过滤效果，这和你是否每日执行VACUUM ANALYZE没有关系，默认统计机制本身不会自动识别跨列相关性。

1. 删除冗余索引（零成本，无副作用） #

你当前持有的两个联合索引属于完全冗余：

• 因为两个时间字段几乎同步，对于固定org值，created的有序性和action_time的有序性几乎完全一致，(org, created, action_time)索引完全可以覆盖所有需要按org + action_time过滤的查询场景，根本不需要单独维护(org, action_time, created)索引。
• 直接删除这个会被选错的索引，不仅能解决当前查询的性能问题，还能降低数据写入时的索引维护开销，没有任何业务副作用。

2. 若必须保留双索引，创建扩展统计信息修正估算 #

如果存在其他特殊查询必须保留(org, action_time, created)索引，直接创建跨列扩展统计信息，让优化器感知字段间的关联关系，这是PostgreSQL官方推荐的解决此类问题的标准方案：

-- 创建包含相关性、distinct值、高频值统计的跨列统计规则
CREATE STATISTICS st_action_org_time_corr (ndistinct, dependencies, mcv)
ON org, created, action_time FROM action;
-- 立即触发统计信息收集，无需等待定时任务
ANALYZE action;

统计信息收集完成后，优化器会自动计算两个时间字段的依赖度，正确识别(org, created, action_time)索引仅需扫描0.3%的索引条目，成本远低于另一个索引，会自动选择正确的执行路径，不需要修改任何业务SQL。

3. 定向SQL改写（应急场景使用） #

如果暂时不能调整索引、也不能修改统计配置，可以通过SQL改写加物化CTE的方式，人为设置优化器屏障，强制先过滤created条件：

WITH recent_data AS MATERIALIZED (
    SELECT created, action_time
    FROM action
    WHERE org = 10
      AND created > NOW() - INTERVAL '25 hour'
    OFFSET 0
)
SELECT min(created)
FROM recent_data
WHERE action_time < NOW() - INTERVAL '1 hour';

该写法会强制优化器优先扫描(org, created, action_time)索引取出近25小时的少量数据，再在内存中过滤action_time条件，性能和正确选择索引的效果一致，仅适合临时应急使用，不推荐作为长期方案。

4. 调高时间字段的统计精度 #

如果上述方案生效后仍偶发选错索引，可以调大两个时间字段的统计直方图桶数，提升时间范围选择率的估算精度：

-- 将字段统计桶数从默认的100调整为1000，亿级数据可调整到10000
ALTER TABLE action ALTER COLUMN created SET STATISTICS 1000;
ALTER TABLE action ALTER COLUMN action_time SET STATISTICS 1000;
ANALYZE action;

默认100个统计桶对于跨度1年、单org年数据量超7000万的时间字段来说粒度过粗，很容易出现小时间窗口的选择率估算偏差，调高桶数不会明显增加ANALYZE的开销，但可以大幅提升时间范围条件的估算准确率。

不需要寻找索引提示类的hack方案，PostgreSQL优化器出现这类索引选择错误，90%以上都是跨列关联未被统计信息覆盖、或统计粒度不足导致的，上述方案可以从根本上修正优化器的估算逻辑，不需要调整数据库全局参数，也不需要依赖第三方插件。

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