冷缓存下PostgreSQL Count查询性能过慢问题排查

问题背景 #

当大部分数据未缓存时，执行以下查询耗时42秒：

EXPLAIN (ANALYZE, BUFFERS) select count(*) from packages where company_id = 178381;

查询计划：

Aggregate  (cost=395914.63..395914.63 rows=1 width=8) (actual time=42411.940..42411.942 rows=1 loops=1)
   Buffers: shared hit=21775 read=94888
   I/O Timings: read=39723.315
   ->  Bitmap Heap Scan on packages  (cost=1053.07..395761.41 rows=306442 width=0) (actual time=83.104..42336.765 rows=322432 loops=1)
         Recheck Cond: (company_id = 178381)
         Heap Blocks: exact=116385
         Buffers: shared hit=21775 read=94888
         I/O Timings: read=39723.315
         ->  Bitmap Index Scan on packages_company_id_index  (cost=0.00..1037.75 rows=306442 width=0) (actual time=45.846..45.847 rows=325795 loops=1)
               Index Cond: (company_id = 178381)
               Buffers: shared hit=1 read=277
               I/O Timings: read=7.090
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.237 ms
 Execution Time: 42413.042 ms

缓存预热后再次执行，耗时降至418ms：

distru_prod=> EXPLAIN (ANALYZE, BUFFERS) select count(*) from packages where company_id = 178381;

查询计划：

Aggregate  (cost=395914.63..395914.63 rows=1 width=8) (actual time=416.943..416.957 rows=1 loops=1)
   Buffers: shared hit=116589
   ->  Bitmap Heap Scan on packages  (cost=1053.07..395761.41 rows=306442 width=0) (actual time=78.925..395.495 rows=322432 loops=1)
         Recheck Cond: (company_id = 178381)
         Heap Blocks: exact=116308
         Buffers: shared hit=116589
         ->  Bitmap Index Scan on packages_company_id_index  (cost=0.00..1037.75 rows=306442 width=0) (actual time=46.359..46.360 rows=325351 loops=1)
               Index Cond: (company_id = 178381)
               Buffers: shared hit=281
 Planning:
   Buffers: shared hit=448
 Planning Time: 1.375 ms
 Execution Time: 418.321 ms

环境与表信息 #

• PostgreSQL 14.12
• GCP环境：8 vCPUs，50GB内存，SSD存储
• 查询时CPU使用率低于25%
• 表与索引统计：

-- 不同company_id数量
select count(distinct company_id) from packages;
-- 结果：691

-- 表总行数
select count(*) from packages;
-- 结果：10764441

-- 目标company_id的行数
select count(*) from packages where company_id = 178381;
-- 结果：322432

-- 表总大小
select pg_size_pretty(pg_total_relation_size('packages'));
-- 结果：12 GB

-- 索引总大小
select pg_size_pretty(pg_total_relation_size('packages_company_id_index'));
-- 结果：79 MB

• 索引定义：

CREATE INDEX packages_company_id_index ON public.packages USING btree (company_id);

强制Index Only Scan的结果 #

使用pg_hint_plan强制执行Index Only Scan后，buffers使用量仍与Bitmap Heap Scan相当：

/*+ IndexOnlyScan(packages packages_company_id_index) */ EXPLAIN (ANALYZE, BUFFERS) select count(*) from packages where company_id = 178381;

查询计划：

Aggregate  (cost=517401.31..517401.32 rows=1 width=8) (actual time=172.448..172.450 rows=1 loops=1)
   Buffers: shared hit=116586
   ->  Index Only Scan using packages_company_id_index on packages  (cost=0.09..517248.09 rows=306442 width=0) (actual time=0.034..150.510 rows=322432 loops=1)
         Index Cond: (company_id = 178381)
         Heap Fetches: 325351
         Buffers: shared hit=116586
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.238 ms
 Execution Time: 172.546 ms

疑问解答 #

1. 为何Index Only Scan仍需与原查询相当的buffers，尽管索引仅占79MB磁盘空间？ #

执行计划中Heap Fetches: 325351，几乎等于返回行数，说明所有匹配的索引条目都需要回表检查行的可见性。
PostgreSQL的Index Only Scan依赖可见性映射（VM）快速判断堆行是否对当前事务可见：如果VM标记了某个堆块的所有行都可见，则无需回表；否则必须读取堆块验证行的可见性。当前场景下，可见性映射未有效标记目标company_id对应的堆块，导致Index Only Scan本质上还是要读取所有相关堆数据块，因此buffer使用量和Bitmap Heap Scan几乎一致。

2. 为何PostgreSQL默认不执行Index Only Scan，明明该查询无需访问packages表？ #

优化器会根据成本估算选择执行计划：

• 强制Index Only Scan的成本（cost=517401.31）远高于Bitmap Heap Scan的成本（cost=395914.63）。
• 优化器通过统计信息判断，执行Index Only Scan需要大量回表验证可见性（Heap Fetches），实际开销比Bitmap Heap Scan更高，因此默认选择后者。

3. 原查询在1000万行表中统计32.2万行耗时42秒，即使存在匹配索引： #

a. 即使考虑冷缓存，该性能是否符合预期？

不符合预期。从查询计划看，I/O耗时占比超过93%（39723ms），读取94888个8KB块（约741MB）耗时近40秒，平均读速仅约18MB/s，远低于GCP SSD的常规随机读性能（通常可达数百MB/s）。

b. 若不符合预期，可能存在哪些问题？

• 表碎片化严重：目标company_id对应32万行，但需要读取11.6万个堆块，平均每个堆块仅存储2-3行，说明表存在大量碎片化，导致需要读取更多磁盘块。
• 存储性能瓶颈：可能使用了GCP标准SSD（而非高性能SSD），或磁盘IOPS/吞吐量达到限制；也可能存在存储层的其他性能问题。
• 可见性映射未更新：未定期执行VACUUM ANALYZE，导致可见性映射失效，Index Only Scan无法发挥作用，同时Bitmap Heap Scan也无法利用VM减少验证开销。
• work_mem不足：如果work_mem设置过小，Bitmap Heap Scan生成的位图可能溢出到磁盘，增加额外I/O开销（当前计划未体现，但可作为排查方向）。

c. 有无无需预热缓存即可提升冷缓存下查询性能的方法？

针对当前场景，可通过以下方式优化：

• 执行VACUUM ANALYZE packages;：更新可见性映射，让Index Only Scan无需大量回表，同时优化统计信息帮助优化器选择更优计划。
• 优化表存储：使用CLUSTER packages USING packages_company_id_index;或pg_repack重构表，减少碎片化，让目标company_id的行集中存储，减少磁盘读取块数。
• 使用物化视图预计算：创建物化视图存储每个company_id的行数，定期刷新，查询时直接读取物化视图：

  CREATE MATERIALIZED VIEW mv_package_company_count AS
  SELECT company_id, count(*) AS total
  FROM packages
  GROUP BY company_id;
  CREATE UNIQUE INDEX idx_mv_package_company ON mv_package_company_count (company_id);
  

• 提升存储性能：切换到GCP高性能SSD（如Persistent SSD Premium），调整磁盘IOPS配额。
• 调整并行查询参数：适当提高max_parallel_workers_per_gather（如设置为4），利用空闲CPU并行读取数据，减少I/O等待时间。

内容的提问来源于stack exchange，提问作者Jordi Chacán