PostgreSQL组合索引仅用首列查询性能骤降原因及索引方案咨询

现有索引 #

• index_buyer_suppliers_on_buyer_id：btree 索引，仅包含 buyer_id
• index_buyer_suppliers_on_supplier_id_and_buyer_id：唯一btree索引，包含 supplier_id, buyer_id

执行的查询 #

@current_suppliers = BuyerSupplier.where("buyer_id = ?", @entity.id).length
@current_buyers    = BuyerSupplier.where("supplier_id = ?", @entity.id).length

性能差异 #

通过EXPLAIN ANALYZE BUFFERS得到的执行计划显示：

• 使用单独buyer_id索引的查询耗时约0.399ms
• 使用组合索引首列supplier_id的查询耗时约39.806ms，性能差距达100倍

疑问：

1. 理论上组合索引的首列可以单独被利用，为什么此处性能差距这么大？
2. 是否需要创建四个索引：buyer_id、supplier_id、[buyer_id, supplier_id]、[supplier_id, buyer_id]？这看起来冗余，但是否有必要？

1. 数据分布天差地别 #

这是最核心的原因：buyer_id和supplier_id对应的数据量完全不在一个量级。比如你查询的这个buyer_id可能仅关联几条记录，单独索引一搜就能定位；但这个supplier_id可能是头部大供应商，关联了几万甚至几十万条记录——哪怕用组合索引的首列，数据库也得扫描海量索引条目，耗时自然飙升。

2. 索引的“轻量性”不同 #

单独的buyer_id索引只存储buyer_id和主键，统计行数时数据库可以直接扫描索引获取结果（即索引覆盖扫描），无需回表查询原数据。而组合索引(supplier_id, buyer_id)每条条目包含两个字段，体积更大，扫描相同数量的条目时效率更低；如果数据库还需要回表验证数据，耗时会进一步增加。

3. 缓存命中率差异 #

如果buyer_id的查询平时执行频率高，相关的索引页早就被加载到内存缓存中，查询速度自然快；而supplier_id的查询可能很少执行，需要从磁盘读取索引数据，这也会导致耗时大幅增加。

完全没必要创建四个索引，纯冗余，按以下方式调整即可：

1. 添加单独的supplier_id btree索引：
   针对WHERE supplier_id = ?的单条件查询，单独的supplier_id索引比组合索引更高效——它的索引条目更小，扫描速度更快，也更容易被缓存，刚好解决当前的慢查询问题。
2. 保留现有两个索引：
   • index_buyer_suppliers_on_buyer_id：继续服务buyer_id的单条件查询
   • index_buyer_suppliers_on_supplier_id_and_buyer_id：作为唯一约束保证supplier_id + buyer_id的唯一性，同时可服务supplier_id和buyer_id同时作为条件的复合查询
3. 无需创建[buyer_id, supplier_id]索引：
   一来你当前没有用到该顺序的复合查询场景，二来即便有，现有组合索引也能满足需求，重复创建只会浪费存储和增加写入时的索引维护成本。

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