复合索引列顺序为何导致PostgreSQL执行计划差异？

场景背景 #

我有一张150万行的users表，包含last_name（varchar类型，随机值）和status（integer枚举类型）列：

enum status: { active: 0, archived: 1, blocked: 2, inactive: 3, part_active: 4, disabled: 5 }

我测试了两种复合索引顺序：

1. 索引1：

add_index :users, [:last_name, :status]

执行查询：

User.where(status: 'active', last_name: 'Anderson').explain(:analyze)

得到执行计划：

Bitmap Heap Scan on users  (cost=4.68..102.86 rows=25 width=108) (actual time=0.559..1.446 rows=489 loops=1)
   Recheck Cond: (((last_name)::text = 'Anderson'::text) AND (status = 0))
   Heap Blocks: exact=484
   ->  Bitmap Index Scan on index_users_on_last_name_and_status  (cost=0.00..4.68 rows=25 width=0) (actual time=0.091..0.092 rows=489 loops=1)
         Index Cond: (((last_name)::text = 'Anderson'::text) AND (status = 0))
 Planning Time: 0.133 ms
 Execution Time: 1.535 ms

2. 索引2：

add_index :users, [:status, :last_name]

执行相同查询，得到执行计划：

Index Scan using index_users_on_status_and_last_name on users  (cost=0.43..103.41 rows=25 width=108) (actual time=0.065..0.755 rows=489 loops=1)
   Index Cond: ((status = 0) AND ((last_name)::text = 'Anderson'::text))
 Planning Time: 0.152 ms
 Execution Time: 0.813 ms

使用PostgreSQL 13，疑惑：仅用等值匹配时，索引列顺序为何仍会导致执行计划不同？原本认为只有范围查询才受数据基数影响。

问题解答 #

核心原因：基数差异与数据分布决定执行计划 #

即使是纯等值查询，复合索引的列顺序依然会通过列基数（distinct值数量）和数据物理分布影响PostgreSQL优化器的选择，进而带来性能差异。

1. 列基数的影响

你的表中：

• last_name是高基数列：随机生成意味着不同姓氏的数量极多，单个姓氏对应的行数占比很低
• status是低基数列：仅6个枚举值，单个状态（比如active）对应的行数占比可能很高

2. 两种索引的执行逻辑差异

索引(last_name, status)：Bitmap扫描

当以高基数列last_name作为索引前缀时，查询先筛选出last_name='Anderson'的行。由于姓氏是随机插入的，这些行在磁盘堆表中的分布极其分散（执行计划里Heap Blocks: exact=484，489行几乎对应484个不同磁盘块）。

PostgreSQL选择Bitmap扫描组合：

• 先用Bitmap Index Scan标记所有符合条件的行位置
• 再用Bitmap Heap Scan批量读取这些分散的磁盘块，避免重复读取同一块，降低IO开销。但这种方式需要额外的Bitmap构建和处理开销。

索引(status, last_name)：Index扫描

当以低基数列status作为索引前缀时，索引先按status分组，每组内再按last_name排序。查询时，先定位到status=0的分组，再在这个分组里快速找到last_name='Anderson'的连续索引条目。

由于这些条目在索引中是连续的，对应的堆表数据物理分布也相对集中，PostgreSQL直接选择Index Scan：

• 遍历索引中符合条件的连续条目，逐个读取堆表数据。这种方式省去了Bitmap构建的额外开销，IO效率更高，所以实际执行时间更短。

3. 纠正误区

你之前认为“只有范围查询才受基数影响”是错误的：

• 基数不仅影响范围查询，还会通过改变数据分布、索引筛选效率，直接影响等值查询的执行计划选择。
• PostgreSQL优化器会根据表的统计信息（比如列的distinct值数量、数据分布），评估不同扫描方式的CPU和IO成本，最终选择预计开销最低的方案。

内容的提问来源于stack exchange，提问作者Jarosław Kowalewski