PostgreSQL复合索引咨询：单字段索引必要性与多条件查询收益

背景信息 #

表结构 #

ID | first_name | last_name    | class      | position |
--------------------------------------------------------
 1 | Teemo      | Shroomer     | Specialist | Top      |
 2 | Cecil      | Heimerdinger | Specialist | Mid      |
 3 | Annie      | Hastur       | Mage       | Mid      |
 4 | Fiora      | Laurent      | Slayer     | Top      |
 5 | Garen      | Crownguard   | Fighter    | Top      |
 6 | Malcolm    | Graves       | Specialist | ADC      |
 7 | Irelia     | Lito         | Figher     | Top      |
 8 | Janna      | Windforce    | Controller | Support  | 

查询使用的WHERE子句 #

WHERE first_name = 'Annie'
WHERE first_name = 'Annie' AND class = 'Mage'
WHERE first_name = 'Janna' AND class = 'Controller' AND position = 'Support'

已创建的索引 #

CREATE INDEX first_name_index ON users (first_name);
CREATE INDEX first_name_class_index ON users (first_name, class);

问题与解答 #

问题1：已创建first_name+class复合索引，是否还需要单独的first_name单字段索引？ #

你的理解完全正确——仅按first_name查询时，first_name_class_index复合索引可以正常发挥作用，不需要保留单独的单字段索引。

PostgreSQL遵循复合索引的最左前缀匹配原则，当查询条件只用到复合索引的第一个字段时，数据库可以直接利用这个复合索引快速定位目标记录，效果和单字段索引基本一致。甚至如果你的查询只需要返回first_name和class字段，复合索引还能实现“索引覆盖”，避免回表查询原数据，比单字段索引更高效。因此单独的first_name_index属于冗余索引，建议删除以减少维护成本。

问题2：使用第三个含三个字段的WHERE子句时，first_name_class_index复合索引是否能带来收益？ #

是的，这个复合索引能带来明显收益，你的思路也正确。

PostgreSQL会先通过复合索引的first_name+class前缀，快速筛选出符合前两个条件的记录；由于经过这两步筛选后，剩余的记录数量已经非常少，数据库只需要在这个小范围内检查position字段是否匹配即可，整体查询效率远高于全表扫描。

如果想要进一步优化这个三字段查询，可以考虑创建包含position的三字段复合索引：

CREATE INDEX first_name_class_position_index ON users (first_name, class, position);

这样数据库可以直接通过索引完成所有条件的筛选，不需要回表检查position字段，效率会更高。

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