Postgres查询规划器未选用低成本复合索引的问题分析

问题概述 #

最初认为Postgres从10.21版本升级至11+版本后出现性能退化，实际根源是数据库统计信息更新导致部分查询变慢。涉事表events_test的last_analyze和last_autoanalyze均为NULL，升级过程会自动触发重新分析统计信息，进而引发该问题。

背景 #

生产环境运行PG 10.21，events_test表包含account（varchar(40)）和timestamp（timestamptz）字段，已创建三类索引：

• account单字段索引
• timestamp单字段索引
• account + timestamp复合索引

测试查询语句：

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM events_test 
WHERE account = '1234' AND TIMESTAMP >= (NOW() AT TIME ZONE 'EST'  - interval '1 month');

针对两类账号测试结果：

• account='1234'：返回数万行，生产环境耗时80-100ms
• account='5678'：返回数十行，生产环境耗时约1ms
  两类查询均默认使用复合索引events_test_account_timestamp_idx。

统计信息过时问题 #

生产环境中，返回大量数据的账号查询，规划器严重低估行数（预估约500行，实际约6万行），原因是表统计信息完全过时。手动执行ANALYZE events_test;或升级至PG11/12/14（升级后自动触发重新分析）会更新统计信息，行数预估修正为接近真实值（约2万行）。

查询规划器决策偏差 #

更新统计信息后：

• 小结果集查询（如account='5678'）仍正常使用复合索引
• 大结果集查询（如account='1234'）规划器改为先扫描events_test_timestamp_idx单索引，再过滤account字段，耗时升至400-500ms

已尝试的排查方案 #

• 调整统计目标：将default_statistics_target从100改为10/1000/10000，重新执行分析并重建索引，无效果
• 多版本验证：在PG11/12/14均复现该问题，PG10手动更新统计信息也会触发，排除版本特性问题
• 强制使用复合索引：临时删除timestamp单索引后，Postgres采用复合索引的位图扫描，耗时仍低于100ms，与原性能一致

查询执行计划对比 #

生产环境PG10高效计划（复合索引） #

Index Scan using events_test_account_timestamp_idx on events_test  (cost=0.11..1109.11 rows=579 width=206) (actual time=0.062..116.439 rows=71700 loops=1)
  Index Cond: (((account)::text = '1234'::text) AND ("timestamp" >= (timezone('EST'::text, now()) - '1 mon'::interval)))
  Buffers: shared hit=68619 read=1249
  I/O Timings: read=23.426
Planning time: 0.662 ms
Execution time: 119.339 ms

更新统计信息后PG14低效计划（timestamp单索引） #

Index Scan using events_test_timestamp_idx on events_test  (cost=0.11..31867.79 rows=18994 width=204) (actual time=0.164..311.960 rows=55583 loops=1)
  Index Cond: ("timestamp" >= ((now() AT TIME ZONE 'EST'::text) - '1 mon'::interval))
  Filter: ((account)::text = '1234'::text)
  Rows Removed by Filter: 462327
  Buffers: shared hit=492867
Planning:
  Buffers: shared hit=144 read=30
  I/O Timings: read=6.160
Planning Time: 7.021 ms
Execution Time: 314.676 ms

强制使用复合索引后的PG14计划（位图扫描） #

Bitmap Heap Scan on events_test  (cost=187.05..35867.12 rows=18992 width=204) (actual time=11.373..38.937 rows=55582 loops=1)
  Recheck Cond: (((account)::text = '1234'::text) AND ("timestamp" >= ((now() AT TIME ZONE 'EST'::text) - '1 mon'::interval)))
  Heap Blocks: exact=13586
  Buffers: shared hit=13803
  ->  Bitmap Index Scan on events_test_account_timestamp_idx  (cost=0.00..186.10 rows=18992 width=0) (actual time=9.376..9.376 rows=55582 loops=1)
        Index Cond: (((account)::text = '1234'::text) AND ("timestamp" >= ((now() AT TIME ZONE 'EST'::text) - '1 mon'::interval)))
        Buffers: shared hit=217
Planning:
  Buffers: shared hit=5
Planning Time: 0.316 ms
Execution Time: 41.446 ms

核心问题：Postgres基于准确统计信息预估「单索引扫描+过滤」的成本更低，但实际「复合索引扫描」的性能快近10倍。

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