PostgreSQL含公共列的索引方案性能对比及工作原理咨询

不存在脱离业务查询场景的绝对优化结论，方案2是否能提升性能完全由你的实际查询条件决定：

• 如果你业务中绝大多数涉及a、b字段的查询，都会同时携带c字段的等值过滤条件（典型场景比如c是租户ID、用户ID这类全局隔离字段，所有查询都要先匹配c的固定值），那方案2的性能会显著优于a、b单列索引的方案。
• 如果你业务中涉及a、b的查询基本不会带c字段的过滤条件，只会单独对a、b做匹配、范围查询或排序，那方案2不仅无法优化性能，反而会导致索引利用率极低，查询效率比单列索引更差。

PostgreSQL默认创建的是B树索引，不管是单列索引还是联合索引，核心都是基于排序规则构建的多路平衡搜索树结构，两种方案的索引运行逻辑差异如下：

联合索引的基础排序规则 #

你创建的(c,a)、(c,b)属于联合B树索引，索引条目排序遵循最左前缀规则：

• 对于(c,a)索引，所有索引条目会先按照c字段的值全局排序，c值完全相同的条目，再按照a字段的值做组内排序，叶子节点通过指针串成有序链表。(c,b)索引的排序逻辑同理。

带c等值过滤的查询场景（方案2更优的情况） #

当查询携带类似WHERE c = 'xxx' AND a = 1、WHERE c = 'xxx' AND b > 10 ORDER BY b这类条件时：

• 走(c,a)或(c,b)索引时，数据库可以先通过B树快速定位到所有符合c值的连续索引区间，这个区间内的a/b值本身就是提前排好序的，不管是做等值匹配、范围过滤还是排序操作，都只需要扫描这个连续区间的索引块即可，不需要额外做内存排序，回表取数据的随机IO也非常少，查询效率极高。
• 如果用a、b的单列索引，因为索引里没有存储c字段的信息，数据库要么扫描a/b的单列索引拿到所有符合a/b条件的行，再回表逐行过滤c的值，要么走效率更低的位图扫描，随机IO量会大很多，性能差距可以达到数倍到数十倍。

不带c过滤的查询场景（方案2更差的情况） #

当查询只针对a或b做过滤，没有c的匹配条件时：

• 因为(c,a)、(c,b)索引是先按c排序的，不同c值分组下的a/b值是分散存储的，没有全局有序性，数据库无法通过B树快速定位到符合a/b条件的索引位置，要么放弃索引走全表扫描，要么走效率极低的全索引扫描，性能远不如直接按a/b全局排序的单列索引。

补充：如果你的业务场景中c是区分度极低的字段（比如c只有0/1两个枚举值），哪怕查询带c的过滤，联合索引的收益也会非常有限，是否选择方案2需要结合c的区分度、查询携带c条件的比例综合判断。

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