本地与云PostgreSQL查询执行耗时差异巨大的原因及复现调试需求

问题背景 #

我有一条统计指定Database对应Record数量的PostgreSQL查询：

select count(*) from store_record where database_id='a0b0dffb-4e5a-40d7-8f81-d129ffe35bbc';

本地与云端数据量相近，但耗时差异极大：

• 本地：3500万条Record，目标Database对应1170万条，查询耗时3秒
• 云端：3900万条Record，目标Database对应1200万条，查询耗时约2分钟

两者查询计划如下：

本地查询计划 #

Finalize Aggregate  (cost=849219.70..849219.71 rows=1 width=8) (actual time=2905.165..2915.472 rows=1 loops=1)
  ->  Gather  (cost=849219.58..849219.69 rows=1 width=8) (actual time=2904.858..2915.456 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Partial Aggregate  (cost=848219.58..848219.59 rows=1 width=8) (actual time=2871.121..2871.122 rows=1 loops=2)
              ->  Parallel Seq Scan on store_record  (cost=0.00..831132.87 rows=6834686 width=0) (actual time=55.204..2597.471 rows=5835078 loops=2)
                    Filter: (database_id = 'a0b0dffb-4e5a-40d7-8f81-d129ffe35bbc'::uuid)
                    Rows Removed by Filter: 11665094
Planning Time: 0.765 ms
JIT:
  Functions: 10
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 3.757 ms, Inlining 52.540 ms, Optimization 36.494 ms, Emission 19.836 ms, Total 112.626 ms"
Execution Time: 2918.714 ms

云端查询计划 #

Finalize Aggregate  (cost=2736538.10..2736538.10 rows=1 width=8) (actual time=126638.968..126675.865 rows=1 loops=1)
  ->  Gather  (cost=2736538.00..2736538.10 rows=1 width=8) (actual time=126638.828..126675.853 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Partial Aggregate  (cost=2735538.00..2735538.00 rows=1 width=8) (actual time=126612.325..126612.326 rows=1 loops=2)
              ->  Parallel Seq Scan on store_record  (cost=0.00..2731015.70 rows=9044601 width=0) (actual time=117.924..126072.782 rows=7658330 loops=2)
                    Filter: (database_id = 'a0b0dffb-4e5a-40d7-8f81-d129ffe35bbc'::uuid)
                    Rows Removed by Filter: 12357700
Planning Time: 5.079 ms
JIT:
  Functions: 10
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 1.249 ms, Inlining 153.195 ms, Optimization 45.848 ms, Emission 34.744 ms, Total 235.036 ms"
Execution Time: 126740.456 ms

耗时差异的可能原因 #

1. 硬件资源差异 #

两者均采用并行全表扫描，磁盘IO是核心瓶颈。本地机器的磁盘IO性能（如NVMe SSD）远高于云端存储（如普通云硬盘），从实际扫描时间看：云端扫描耗时126072ms，是本地2597ms的近50倍，说明磁盘吞吐量差距极大。

2. 数据缓存差异 #

本地数据可能因频繁访问被大量缓存到内存（PostgreSQL的shared_buffers或操作系统缓存），而云端数据未被预热，需要从磁盘全量读取，导致耗时剧增。

3. 数据库配置与资源限制 #

• 云端可能存在CPU核心数限制，导致并行扫描的实际执行效率低下；
• 虽然JIT编译耗时占比极低，但云端JIT总耗时是本地的2倍多，也可能受CPU资源限制影响；
• 统计信息偏差：本地预估返回行数与实际偏差约15%，云端偏差约15%，对全表扫描影响有限，但如果云端统计信息长期未更新，可能间接影响其他计划选择。

4. 数据存储布局差异 #

云端表可能存在较多数据碎片，导致扫描时需要读取更多磁盘块，进一步放大IO瓶颈的影响。

本地复现慢查询的方法 #

1. 模拟低IO性能 #

• 将本地数据库数据目录迁移到低速存储（如机械硬盘、USB硬盘）；
• 使用工具限制磁盘IO带宽（Linux下可使用trickle，Windows下可使用第三方限速工具）。

2. 清空缓存强制冷读 #

• 重启PostgreSQL服务，同时清空操作系统缓存：
  Linux执行：sync; echo 3 > /proc/sys/vm/drop_caches
  macOS执行：sudo purge
• 避免执行pg_prewarm预热表数据，确保查询从磁盘全量读取。

3. 限制CPU资源 #

• 使用cgroups（Linux）或容器（如Docker）限制数据库进程的CPU核心数和使用率，模拟云端的CPU资源约束。

4. 制造数据碎片 #

• 对本地store_record表执行多次批量删除、插入操作，然后不执行VACUUM ANALYZE，模拟云端的表碎片状态。

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