PostgreSQL为何采用Index Scan Backward查询指定条件的最大ID？

问题背景 #

执行简单查询获取指定条件的最大ID：SELECT max(ID) FROM t WHERE m=345;，表t包含2000万条记录，m字段有2000个不同取值，ID是主键（带索引），m字段单独建了索引。但优化器选择对主键索引执行Index Scan Backward，而非扫描m字段的索引，查询耗时超10秒。

修改SQL为SELECT max(ID+0) FROM t WHERE m=345;后，优化器自动选择扫描m的索引，查询仅需数毫秒完成。我们定期执行vacuum/analyze，不想通过给所有查询添加+0的方式解决问题，原SQL逻辑简单，优化器理应选择最优执行计划。

表及索引DDL #

CREATE TABLE IF NOT EXISTS t
(
    id bigint NOT NULL DEFAULT nextval('t_seq'::regclass),
    c integer,
    m integer,
    b integer DEFAULT '-1'::integer,
    p integer,
    CONSTRAINT t_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;

CREATE INDEX t_m_index
    ON t USING btree
    (m ASC NULLS LAST)
    TABLESPACE pg_default;

两种SQL的执行计划 #

优化后SQL（SELECT MAX(id+0) FROM t WHERE m=345;） #

db=> explain (analyze, buffers, format text)
db=> select MAX(id+0) FROM t WHERE m=345;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19418.26..19418.27 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=1)
   Buffers: shared hit=6 read=2
   ->  Bitmap Heap Scan on t  (cost=211.19..19368.82 rows=9888 width=8) (actual time=0.039..0.042 rows=2 loops=1)
         Recheck Cond: (m = 345)
         Heap Blocks: exact=2
         Buffers: shared hit=6 read=2
         ->  Bitmap Index Scan on t_m_index  (cost=0.00..208.72 rows=9888 width=0) (actual time=0.033..0.034 rows=2 loops=1)
               Index Cond: (m = 345)
               Buffers: shared hit=4 read=2
 Planning Time: 0.898 ms
 Execution Time: 0.094 ms
(11 rows)

原SQL（SELECT MAX(id) FROM t WHERE m=345;） #

db=> explain (analyze, buffers, format text)
db=> select MAX(id) FROM t WHERE m=345;
                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=464.31..464.32 rows=1 width=8) (actual time=21627.948..21627.950 rows=1 loops=1)
   Buffers: shared hit=10978859 read=124309 dirtied=1584
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.56..464.31 rows=1 width=8) (actual time=21627.945..21627.946 rows=1 loops=1)
           Buffers: shared hit=10978859 read=124309 dirtied=1584
           ->  Index Scan Backward using t_pkey on t  (cost=0.56..4524305.43 rows=9756 width=8) (actual time=21627.944..21627.944 rows=1 loops=1)
                 Index Cond: (id IS NOT NULL)
                 Filter: (m = 345)
                 Rows Removed by Filter: 11745974
                 Buffers: shared hit=10978859 read=124309 dirtied=1584
 Planning Time: 0.582 ms
 Execution Time: 21627.964 ms
(12 rows)

问题原因 #

PostgreSQL优化器处理MAX(id)时，会优先利用主键索引的有序性——主键索引按id升序排列，反向扫描可快速获取最大id，但需要逐个验证m=345的条件。由于优化器错误预估了符合条件记录的位置，认为最大id附近就有目标记录，实际这类记录集中在较靠前的位置，导致扫描大量无关行，耗时剧增。

而MAX(id+0)的写法破坏了主键索引的直接可用性，优化器只能先通过m索引筛选符合条件的记录，再计算最大id，这才是高效路径。

解决方案 #

• 创建复合索引：这是最优方案，无需修改SQL，优化器可直接通过索引获取指定m下的最大id：

  CREATE INDEX t_m_id_idx ON t USING btree (m ASC, id DESC);
  

  该索引能直接定位到m=345对应的最大id，查询效率与id+0写法一致。

• 使用查询提示（需扩展支持）：若不想新建索引，可通过pg_hint_plan扩展强制优化器使用位图扫描：

  SELECT /*+ BitmapScan(t) */ max(id) FROM t WHERE m=345;
  

• 临时调整优化器参数：临时关闭主键索引扫描（不建议全局长期使用，仅用于验证）：

  SET enable_indexscan = off;
  

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