PostgreSQL执行计划未选用复合索引i2的原因咨询

我创建了如下示例，无法理解查询优化器为何未选用索引i2执行查询。从pg_stats可知，uniqueIds列值唯一，fourOtherIds列仅含4种不同值。按我的理解，使用索引i2应该是最快的方式：只需在fourOtherIds的4个索引叶节点中查找uniqueIds？我对索引工作原理的理解哪里有误？为何优化器认为使用i1更合理，即便需要过滤333333行？我认为应该先通过i2找到uniqueIds=4000的行（或少量行，因无唯一约束），再过滤fourIds=1的条件。

测试环境与代码 #

create table t (fourIds int, uniqueIds int,fourOtherIds int);
insert into t ( select 1,*,5 from generate_series(1      ,1000000));
insert into t ( select 2,*,6 from generate_series(1000001,2000000));
insert into t ( select 3,*,7 from generate_series(2000001,3000000));
insert into t ( select 4,*,8 from generate_series(3000001,4000000));
create index i1 on t (fourIds);
create index i2 on t (fourOtherIds,uniqueIds);
analyze t;

统计信息查询结果 #

n_distinct|attname     |
----------+------------+
       4.0|fourids     |
      -1.0|uniqueids   |
       4.0|fourotherids|

查询执行计划 #

explain analyze select * from t where fourIds = 1 and uniqueIds = 4000;

执行计划输出：

QUERY PLAN                                                                                                                |
--------------------------------------------------------------------------------------------------------------------------+
Gather  (cost=1000.43..22599.09 rows=1 width=12) (actual time=0.667..46.818 rows=1 loops=1)                               |
  Workers Planned: 2                                                                                                      |
  Workers Launched: 2                                                                                                     |
  ->  Parallel Index Scan using i1 on t  (cost=0.43..21598.99 rows=1 width=12) (actual time=25.227..39.852 rows=0 loops=3)|
        Index Cond: (fourids = 1)                                                                                         |
        Filter: (uniqueids = 4000)                                                                                        |
        Rows Removed by Filter: 333333                                                                                    |
Planning Time: 0.107 ms                                                                                                   |
Execution Time: 46.859 ms                                                                                                 |

问题解析与解决方案 #

核心误区：对复合索引的使用逻辑理解错误 #

索引i2 (fourOtherIds, uniqueIds)是前缀优先的复合索引，PostgreSQL只能利用索引的前缀列来快速定位数据范围。你的查询没有指定fourOtherIds的过滤条件，因此无法通过i2直接定位uniqueIds=4000的行：

• 要通过i2查找目标数据，数据库必须遍历fourOtherIds的所有4个取值对应的索引分支，然后在每个分支里逐一扫描uniqueIds值，这个操作的IO成本远高于优化器预估的使用i1的成本。

优化器选择i1的原因 #

• 索引i1 (fourIds)能通过fourIds=1精准定位到100万行数据（占总数据量的25%）。优化器基于统计信息判断：从这100万行中过滤出1行的CPU+IO成本，低于遍历i2的4个索引分支查找目标值的成本。
• 这里优化器的预估存在偏差，是因为它不知道你数据中uniqueIds=4000仅存在于fourIds=1的分组中——它只能基于独立列的统计信息做概率判断，无法感知列之间的关联关系。

高效解决方案：创建匹配查询条件的复合索引 #

如果想让查询高效命中目标行，推荐创建以下两种复合索引之一：

• 方案1：以唯一列为前缀

  create index i3 on t (uniqueIds, fourIds);
  

  由于uniqueIds是唯一列，这个索引能直接定位到uniqueIds=4000的1行数据，再验证fourIds=1的条件，几乎是最优性能。
• 方案2：以过滤列为前缀+唯一列

  create index i4 on t (fourIds, uniqueIds);
  

  先通过fourIds=1定位到100万行数据，再通过uniqueIds=4000在索引中精准命中目标行，避免了全分组的过滤操作。

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