Postgres简单查询选错索引问题：未使用匹配的IDX_epic索引

my_table的核心字段及索引如下：

user_id                | character varying           |           | not null |
epic_id                | text                        |           | not null |
status                 | integer                     |           | not null |

"IDX_user" UNIQUE, btree ("user_id") WHERE status < 101
"IDX_epic" UNIQUE, btree ("epic_id") WHERE status < 101 

执行如下查询时，存在完全匹配查询条件的IDX_epic索引，但执行计划选择了IDX_user索引，需要遍历数百行后再过滤数据：

EXPLAIN ANALYZE SELECT * FROM my_table WHERE "epic_id" = 'asdf' and "status" < 101 LIMIT 1;

执行计划输出：

Limit  (cost=0.28..8.29 rows=1 width=276) (actual time=0.230..0.231 rows=0 loops=1)
   ->  Index Scan using "IDX_user" on my_table  (cost=0.28..8.29 rows=1 width=276) (actual time=0.229..0.230 rows=0 loops=1)
         Filter: ("epic_id" = 'asdf'::text)
         Rows Removed by Filter: 273
 Planning Time: 0.122 ms
 Execution Time: 0.248 ms

同时该查询在事务中执行时可能产生不必要的锁。

• SET random_page_cost=1无改善效果，不符合通用优化经验的预期结论
• 本地环境执行该查询会使用正确的IDX_epic索引，本地满足status < 101的行仅有90条
• 当执行"epic_id" = table.random_column的内连接查询时，执行计划会正确使用IDX_epic索引
• "user_id"与"epic_id"类型不同，但text与character varying的性能差异几乎可以忽略，不是核心诱因
• 查询pg_stat_all_indexes可知，IDX_epic的idx_scan值为9，证实除测试场景外该索引几乎未被使用

索引选择错误的核心原因 #

1. 部分索引统计信息偏差
   两个索引均为带WHERE status < 101条件的部分索引，PostgreSQL对部分索引的统计信息收集粒度默认低于普通索引，加上IDX_epic历史扫描次数极少，优化器没有足够的运行时数据判断该索引的过滤效率，反而倾向于选择扫描次数更多、统计信息可信度更高的IDX_user索引。同时LIMIT 1的存在放大了该误判：优化器估算两个索引的单条查询成本相近，优先选择使用频率更高的索引。

2. 常量匹配的估算误差
   直接用常量值'asdf'匹配epic_id时，优化器对该常量值的选择性估算出现偏差；而关联查询时需要用其他表的字段匹配epic_id，优化器会触发更准确的索引选择性计算逻辑，因此会正确选择IDX_epic索引。

3. 环境差异
   本地环境满足status < 101的行仅有90条，不管选择哪个索引成本差异极小，且测试过程中IDX_epic的扫描次数足够，统计信息更准确，因此能走正确的索引。

不必要锁的成因 #

如果该查询带有FOR UPDATE/FOR SHARE等锁修饰符，PostgreSQL会给索引扫描过程中访问到的所有行加锁，哪怕这些行后续被epic_id条件过滤掉。本次执行计划扫描了273行不符合条件的行，这些行都会被额外加锁，大幅提升锁冲突概率，延长事务持有锁的时间。

1. 刷新统计信息：执行ANALYZE my_table;，如果效果不佳可以调高epic_id字段的统计采样粒度后再次刷新：

ALTER TABLE my_table ALTER COLUMN epic_id SET STATISTICS 1000;
ANALYZE my_table;

2. 强制指定索引：如果统计信息更新后仍无法解决，可以在查询中显式指定使用IDX_epic：

SELECT * FROM my_table WHERE "epic_id" = 'asdf' and "status" < 101 LIMIT 1 USING INDEX "IDX_epic";

3. 清理冗余索引：如果业务中IDX_user使用率极低，可直接删除该索引，强制优化器选择正确的索引。

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