PostgreSQL中同一查询出现不同执行计划的原因排查

背景 #

测试使用的表结构如下：

CREATE TABLE IF NOT EXISTS Queue(
   Id             BIGSERIAL        NOT NULL PRIMARY KEY,
   SendAt         TIMESTAMP(3)     NOT NULL,
   Payload        TEXT             NOT NULL,
   KeyId          BIGINT               NULL
);

CREATE INDEX IF NOT EXISTS Queue_sendAt_ch_idx ON Queue (SendAt, KeyId);

在Docker环境的PostgreSQL 16.3-bullseye中，执行以下事务进行测试：

BEGIN;

INSERT INTO Queue 
(id, sendat, payload, KeyId)
SELECT "id", now()-("id"||'ms')::INTERVAL, 'random payload', ("id" * 0.8)::bigint
FROM generate_series(1,200000) id;

analyze Queue;

EXPLAIN (ANALYZE, buffers)
SELECT Id
       FROM Queue
     WHERE KeyId           = 15
       AND SendAt         <= now()
       ORDER BY SendAt ASC
       LIMIT 1;
ROLLBACK;

测试中发现同一查询会生成两种不同的执行计划：

索引扫描执行计划 #

Limit  (cost=0.42..3518.43 rows=1 width=16) (actual time=27.851..27.852 rows=1 loops=1)
  Buffers: shared hit=1373
  ->  Index Scan using queue_sendat_ch_idx on queue  (cost=0.42..3518.43 rows=1 width=16) (actual time=27.849..27.849 rows=1 loops=1)
        Index Cond: ((sendat <= now()) AND (keyid = 15))
        Buffers: shared hit=1373
Planning:
  Buffers: shared hit=52 read=1
Planning Time: 0.408 ms
Execution Time: 27.872 ms

并行顺序扫描执行计划 #

Limit  (cost=5792.44..5792.45 rows=1 width=16) (actual time=19.846..23.651 rows=1 loops=1)
  Buffers: shared hit=3334
  ->  Sort  (cost=5792.44..5792.45 rows=1 width=16) (actual time=19.844..23.649 rows=1 loops=1)
        Sort Key: sendat
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=3334
        ->  Gather  (cost=1000.00..5792.43 rows=1 width=16) (actual time=4.843..23.642 rows=1 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=3334
              ->  Parallel Seq Scan on queue  (cost=0.00..4792.33 rows=1 width=16) (actual time=5.451..10.427 rows=0 loops=3)
                    Filter: ((keyid = 15) AND (sendat <= now()))
                    Rows Removed by Filter: 66666
                    Buffers: shared hit=3334
Planning:
  Buffers: shared hit=26
Planning Time: 0.392 ms
Execution Time: 23.673 ms

核心原因分析 #

• 成本估算的临界波动：优化器计算两种执行计划的成本非常接近，从实际执行时间看，并行扫描耗时甚至略低于索引扫描。由于表中符合KeyId=15的记录仅1条，优化器认为两种方案的成本差异极小，导致偶尔切换执行计划。
• 索引结构适配性不足：当前索引(SendAt, KeyId)的排序顺序与查询过滤逻辑不匹配——查询先过滤KeyId=15再筛选SendAt <= now()，但索引先按SendAt排序，这意味着索引扫描需要遍历所有SendAt <= now()的条目才能找到目标记录，实际扫描的索引页数较多，拉高了索引扫描的成本。
• 统计信息的近似性偏差：尽管执行了ANALYZE，PostgreSQL的统计信息是抽样估算的，对于这种极端少数据的过滤场景，估算结果的微小偏差就可能影响优化器的选择。加上now()是动态值，每次查询的SendAt <= now()范围略有变化，进一步干扰成本计算。
• 缓存状态的实时影响：两次执行的缓存命中情况不同，并行扫描时表数据更多处于缓存中，实际IO成本更低，优化器可能根据缓存的实时状态调整了成本判断。

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