PostgreSQL多列索引选择逻辑：为何选c2_c1而非(c1,c2)主键索引

核心判定逻辑 #

PostgreSQL优化器选择多列索引时不会机械套用“等值列放前导位”的经验规则，而是基于统计信息对多个候选索引的执行成本做量化估算，选择总成本最低的方案，核心考量维度包括：

• 索引扫描的IO成本：区分顺序IO、随机IO的开销，结合索引列值与堆表物理存储的相关性做估算
• 排序开销：如果索引的天然排序顺序能匹配ORDER BY需求，可以省掉显式排序的内存/CPU开销
• 提前终止收益：查询带LIMIT子句时，会估算扫描多少条索引条目就能凑够需要的行数，扫描条目越少成本越低
• 索引条件下推效率：评估过滤条件能否直接在索引层面完成判断，减少不必要的回表操作

案例复现 #

表与索引定义 #

CREATE TABLE test
(
    c1 INTERVAL  NOT NULL,
    c2 TIMESTAMP NOT NULL,
    c3 VARCHAR   NOT NULL,
    PRIMARY KEY (c1, c2)
);

CREATE INDEX c2_c1_index ON test (c2, c1);

待执行查询 #

EXPLAIN ANALYSE
SELECT *
FROM test
WHERE c1 = '1 minute'::INTERVAL
  AND c2 > '2020-06-27 00:00:00.0'::TIMESTAMP AND c2 < '2022-06-27 00:00:00.0'
ORDER BY c2
LIMIT 10000

实际执行计划（PostgreSQL 13.3） #

Limit  (cost=0.43..1102.23 rows=10000 width=40) (actual time=2.127..15.670 rows=10000 loops=1)
  ->  Index Scan using c2_c1_index on test  (cost=0.43..110174.94 rows=999957 width=40) (actual time=2.126..14.715 rows=10000 loops=1)
        Index Cond: ((c2 > '2020-06-25 00:00:00'::timestamp without time zone) AND (c2 < '2022-06-25 00:00:00'::timestamp without time zone) AND (c1 = '00:01:00'::interval))
Planning Time: 0.290 ms
Execution Time: 16.201 ms

选择c2_c1_index而非主键索引的原因 #

不少人会默认(c1,c2)结构的主键索引更适配：c1是等值条件放前导列，同c1下c2有序，能匹配范围查询和排序需求，但优化器的成本计算中，c2_c1_index的综合成本更低，核心原因有两个：

1. 回表IO成本差异
   c2是时间戳类型，绝大多数业务场景下数据都是按时间顺序插入堆表，因此c2的取值顺序和堆表物理存储顺序相关性极高。c2_c1_index按c2排序，扫描时按索引顺序回表取c3列几乎是顺序IO，单条回表成本极低。
   而c1只有8个取值，插入时c1值随机分布在堆表中，主键索引(c1,c2)虽然在c1等值切片内c2有序，但这部分行在堆表里是离散存储的，回表时会产生大量随机IO，单条回表成本远高于顺序IO。PostgreSQL默认随机IO成本是顺序IO的4倍，这部分成本差足以覆盖多扫描少量索引条目的开销。
2. LIMIT提前终止的收益差没有想象中大
   c1只有8个取值，意味着平均每扫描8条c2_c1_index的索引条目，就能命中1条c1='1 minute'的符合条件行，凑够10000条结果只需要扫描约8万条索引条目。虽然比主键索引理论上扫描1万条索引条目更多，但索引扫描本身是内存/顺序读，开销极低，多扫7万条索引的成本，远低于随机回表和顺序回表的成本差。
   同时两个索引都天然匹配ORDER BY c2的排序需求，都不需要额外做排序操作，这部分成本没有差异。

注：“等值条件列放在多列索引前导位”只是通用场景下的经验优化建议，不是数据库优化器必须遵守的规则，当存在排序、LIMIT、列存储相关性、数据分布倾斜等因素时，优化器会基于实际统计信息选择成本最低的执行方案。

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