PostgreSQL索引选择疑问：带location和id条件的查询未使用对应组合索引反而选用主键索引的原因及相关咨询

嗨，我来帮你理清这个索引选择的问题，其实PostgreSQL的查询优化器这么做是有它的道理的，咱们一步步拆解：

为什么优化器选了主键索引而非(id, location)组合索引？ #

• 首先看你的主键结构：(id, location, store)，这是一个B-tree索引，而你额外创建的(id, location)组合索引也是B-tree。当你执行WHERE id = '1' AND location = '1'时，这两个索引的前缀匹配能力完全一致——主键索引的前两列正好是id和location，优化器可以通过主键索引快速定位到符合条件的行，效率和(id, location)组合索引几乎没有差别。
• 优化器做选择时会综合考虑索引的维护成本、大小、统计信息等。主键索引是数据库自动创建的，它的统计信息通常更完善，而且因为是唯一索引，优化器对它的选择性判断会更准确。如果两个索引的查询成本相差不大，优化器会倾向于选择已经存在的、更“可靠”的主键索引，避免额外的索引查找开销。
• 还有一种可能是你的表数据量比较小，这时候优化器可能觉得直接通过主键索引查找，甚至全表扫描，都比单独调用(id, location)索引更划算——毕竟索引本身也需要IO开销，数据量小时这种开销占比会更高。

如何让查询用到(id, location)组合索引？ #

• 临时测试的话，可以用索引提示强制指定索引（注意：生产环境不要随便用，除非你确定自己比优化器更清楚数据分布）：

  EXPLAIN ANALYZE SELECT * FROM my_table WHERE location = '1' AND id = '1' INDEX idx_id_location;
  

  这里的idx_id_location是你给(id, location)索引起的名字，如果没手动起名，可以通过SELECT indexname FROM pg_indexes WHERE tablename = 'my_table';查询到它的系统默认名称。
• 更合理的场景是让这个索引成为覆盖索引：如果你的查询只需要id和location这两个字段，而不是SELECT *，优化器会优先选择(id, location)索引，因为它本身就包含了需要返回的所有数据，不需要回表到堆表中读取其他列，成本更低。比如：

  EXPLAIN ANALYZE SELECT id, location FROM my_table WHERE location = '1' AND id = '1';
  

  这个查询大概率会用到你创建的(id, location)组合索引。

额外建议 #

• 先更新一下表的统计信息，确保优化器有准确的数据分布参考：

  ANALYZE my_table;
  

• 如果你觉得(id, location)索引是多余的，其实可以考虑删掉它——因为主键索引已经能覆盖这个查询的前缀条件，额外维护这个索引只会增加写入时的开销（比如INSERT/UPDATE/DELETE时要同时更新两个索引）。

内容来源于stack exchange