PostgreSQL带复合主键的大表查询性能优化求助

表结构与环境 #

我们有一张1.8亿行、大小20GB的表，DDL如下：

create table app.table
(
    a_id    integer   not null,
    b_id    integer   not null,
    c_id    integer   not null,
    d_id    integer   not null,
    e_id    integer   not null,
    f_id    integer   not null,
    a_date  timestamp not null,
    date_added          timestamp,
    last_date_modified  timestamp default now()
);

值分布 #

• a_id范围：0-160,000,000
• b_id仅有一种取值（该表是分区表单个分区的副本，b_id为分区键）
• c_id范围：0-4
• d_id当前仅有一种取值
• e_id当前仅有一种取值

主键定义 #

alter table app.table add constraint table_pk primary key (a_id, b_id, c_id, d_id, e_id);

数据库环境 #

使用Aurora PostgreSQL v12.8的r6g.xlarge集群，单实例无其他流量，已执行ANALYZE和VACUUM ANALYZE，分析结果如下：

INFO:  "table": scanned 30000 of 1711284 pages, containing 3210000 live
 rows and 0 dead rows; 30000 rows in sample, 183107388 estimated total rows

查询性能问题 #

当shared_buffers处于冷状态时，以下查询耗时约9秒：

select a_id, b_id, c_id, d_id, a_date
from app.table ts
where a_id in ( <5000 values> )
and b_id = 34
and c_id in (2,3)
and d_id = 0

该查询的EXPLAIN输出：

Index Scan using table_pk on table ts  (cost=0.57..419134.91 rows=237802 width=24) (actual time=8.335..9803.424 rows=5726 loops=1)
"  Index Cond: ((a_id = ANY ('{66986803,90478329,...,121697593}'::integer[])) AND (b_id = 34))"
"  Filter: (c_id = ANY ('{2,3}'::integer[])))"
  Rows Removed by Filter: 3
  Buffers: shared hit=12610 read=10593
  I/O Timings: read=9706.055
Planning:
  Buffers: shared hit=112 read=29
  I/O Timings: read=29.227
Planning Time: 33.437 ms
Execution Time: 9806.271 ms

缓存命中时该查询仅需25ms，我们希望无需预缓存即可将性能优化至1-2秒。

已尝试的优化方案及效果 #

1. 添加覆盖索引 #

创建包含a_date的覆盖索引：

create unique index covering_idx on app.table (a_id, b_id, c_id, d_id, e_id) include (a_date)

冷缓存下的EXPLAIN结果：

Index Only Scan using covering_idx on table ts (cost=0.57..28438.58 rows=169286 width=24) (actual time=8.020..7028.442 rows=5658 loops=1)
  Index Cond: ((a_id = ANY ('{134952505,150112033,…,42959574}'::integer[])) AND (b_id = 34))
  Filter: ((e_id = ANY ('{0,0}'::integer[])) AND (c_id = ANY ('{2,3}'::integer[])))
  Rows Removed by Filter: 2
  Heap Fetches: 0
  Buffers: shared hit=12353 read=7733
  I/O Timings: read=6955.935
Planning:
  Buffers: shared hit=80 read=8
  I/O Timings: read=8.458
Planning Time: 11.930 ms
Execution Time: 7031.054 ms

2. 强制Bitmap Heap Scan #

使用pg_hint_plan添加/*+ BitmapScan(table) */提示后，EXPLAIN结果：

Bitmap Heap Scan on table ts (cost=22912.96..60160.79 rows=9842 width=24) (actual time=3972.237..4063.417 rows=5657 loops=1)
  Recheck Cond: ((a_id = ANY ('{24933126,19612702,27100661,73628268,...,150482461}'::integer[])) AND (b_id = 34))
  Filter: ((d_id = ANY ('{0,0}'::integer[])) AND (c_id = ANY ('{2,3}'::integer[])))
 Rows Removed by Filter: 4
  Heap Blocks: exact=5644
  Buffers: shared hit=14526 read=11136
  I/O Timings: read=22507.527
  ->  Bitmap Index Scan on table_pk (cost=0.00..22898.00 rows=9842 width=0) (actual time=3969.920..3969.920 rows=5661 loops=1)
       Index Cond: ((a_id = ANY ('{24933126,19612702,27100661,,150482461}'::integer[])) AND (b_id = 34))
       Buffers: shared hit=14505 read=5513
       I/O Timings: read=3923.878
Planning:
  Buffers: shared hit=6718
Planning Time: 21.493 ms
Execution Time: 4066.582 ms

疑问与诉求 #

目前我们考虑在生产环境强制使用Bitmap Heap Scan执行计划，但希望了解为何查询规划器会选择性能更差的Index Scan/Index Only Scan计划。我们已设置default_statistics_target为1000并重新执行了VACUUM ANALYZE。

解答 #

为什么规划器选择了更慢的执行计划？

1. 统计信息偏差：即便设置了default_statistics_target=1000并执行了VACUUM ANALYZE，PostgreSQL对包含5000个值的IN列表的行数估算仍可能存在误差。规划器估算返回237802行，远高于实际的5726行，这会让它认为Index Scan的顺序读取更高效——因为它假设需要读取大量连续数据，Bitmap Scan的位图构建和合并开销会更高。

2. 单值列的统计局限性：b_id、d_id、e_id当前均为单值，但规划器可能未完全识别这种极端分布。主键索引顺序为(a_id, b_id, c_id, d_id, e_id)，当b_id固定时，索引实际按a_id排序，但规划器可能未将b_id的单值特性纳入成本计算，导致它认为逐个查找a_id的开销低于Bitmap Scan的位图操作。

3. I/O成本估算偏差：Aurora SSD的实际I/O延迟与PostgreSQL默认成本参数不匹配。PostgreSQL默认的random_page_cost和seq_page_cost基于传统磁盘，而Aurora随机I/O性能远高于传统磁盘，但规划器仍按默认值估算成本，导致它认为随机读取的Index Scan成本更低，Bitmap Scan的随机读取成本更高。

进一步优化建议

1. 调整成本参数：

   • 降低random_page_cost（比如设为1.1-1.5，接近seq_page_cost=1），让规划器更准确评估Aurora SSD的随机I/O性能，从而更倾向于选择Bitmap Scan。可执行SET random_page_cost = 1.2;测试效果。

2. 优化索引结构：

   • 针对查询特征创建专用索引：create index optimized_idx on app.table (a_id, c_id) include (b_id, d_id, a_date);。该索引更小，可直接在索引中过滤c_id，减少后续Filter操作，同时覆盖所有所需列，实现无额外过滤的Index Only Scan。

3. 预处理IN列表：

   • 将5000个a_id存入临时表，通过JOIN替代IN子句：

     CREATE TEMP TABLE temp_a_ids (a_id integer PRIMARY KEY);
     INSERT INTO temp_a_ids VALUES (66986803), (90478329), ...; -- 5000个值
     select ts.a_id, ts.b_id, ts.c_id, ts.d_id, ts.a_date
     from app.table ts
     join temp_a_ids t on ts.a_id = t.a_id
     where ts.b_id = 34
       and ts.c_id in (2,3)
       and ts.d_id = 0;
     

     这种方式规划器更容易估算行数，且可利用临时表索引高效JOIN，减少IN列表带来的统计误差。

4. 验证统计信息：

   • 执行SELECT * FROM pg_stats WHERE tablename = 'table' AND attname IN ('a_id', 'b_id', 'c_id');，查看统计信息是否准确反映列分布，尤其是b_id的ndistinct是否为1。若不是，执行ALTER TABLE app.table ALTER COLUMN b_id SET STATISTICS 1000;后重新ANALYZE。

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