Postgres 14查询缓慢求助：DigitalOcean托管库较本地慢20倍

问题背景 #

将Postgres 14数据库从独立托管环境迁移到DigitalOcean托管库后，查询性能出现显著下降。以entries表（470万行，数据库总大小约1.1GB）的select count(*) from entries为例：

• DigitalOcean环境耗时约19秒
• 本地导入后仅需896ms

已尝试对entries表执行ANALYZE，无明显改善。

执行计划对比 #

本地环境explain输出 #

Finalize Aggregate  (cost=65547.91..65547.92 rows=1 width=8)
  ->  Gather  (cost=65547.70..65547.91 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=64547.70..64547.71 rows=1 width=8)
              ->  Parallel Index Only Scan using index_entries_on_discarded_at on entries  (cost=0.43..59600.43 rows=1978909 width=0)

DigitalOcean环境explain输出 #

Finalize Aggregate  (cost=340527.64..340527.65 rows=1 width=8)
  ->  Gather  (cost=340527.43..340527.64 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=339527.43..339527.44 rows=1 width=8)
              ->  Parallel Seq Scan on entries  (cost=0.00..334579.74 rows=1979074 width=0)
JIT:
  Functions: 5
  Options: Inlining false, Optimization false, Expressions true, Deforming true

DigitalOcean环境explain(analyze, verbose, buffers, settings)输出 #

Finalize Aggregate  (cost=340527.64..340527.65 rows=1 width=8) (actual time=21958.167..22251.122 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=19544 read=295245
  ->  Gather  (cost=340527.43..340527.64 rows=2 width=8) (actual time=21925.670..22239.158 rows=3 loops=1)
        Output: (PARTIAL count(*))
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=19544 read=295245
        ->  Partial Aggregate  (cost=339527.43..339527.44 rows=1 width=8) (actual time=21667.850..21667.880 rows=1 loops=3)
              Output: PARTIAL count(*)
              Buffers: shared hit=19544 read=295245
              Worker 0:  actual time=21539.121..21539.141 rows=1 loops=1
                JIT:
                  Functions: 3
                  Options: Inlining false, Optimization false, Expressions true, Deforming true
                  Timing: Generation 0.375 ms, Inlining 0.000 ms, Optimization 0.351 ms, Emission 74.197 ms, Total 74.923 ms
                Buffers: shared hit=6507 read=102324
              Worker 1:  actual time=21546.859..21546.874 rows=1 loops=1
                JIT:
                  Functions: 3
                  Options: Inlining false, Optimization false, Expressions true, Deforming true
                  Timing: Generation 0.351 ms, Inlining 0.000 ms, Optimization 0.289 ms, Emission 82.109 ms, Total 82.749 ms
                Buffers: shared hit=6352 read=91199
              ->  Parallel Seq Scan on public.entries  (cost=0.00..334579.74 rows=1979074 width=0) (actual time=153.347..21059.836 rows=1581283 loops=3)
                    Buffers: shared hit=19544 read=295245
                    Worker 0:  actual time=40.834..20943.050 rows=1638108 loops=1
                      Buffers: shared hit=6507 read=102324
                    Worker 1:  actual time=44.950..20923.007 rows=1471456 loops=1
                      Buffers: shared hit=6352 read=91199
"Settings: effective_cache_size = '568MB', effective_io_concurrency = '2', random_page_cost = '1', work_mem = '1751kB'"
Query Identifier: 1230802159253045228
Planning Time: 0.641 ms
JIT:
  Functions: 11
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 26.149 ms, Inlining 0.000 ms, Optimization 76.294 ms, Emission 515.557 ms, Total 618.000 ms
Execution Time: 22290.983 ms

排查建议 #

• 检查索引状态
  本地环境使用index_entries_on_discarded_at索引执行Index Only Scan，先在DigitalOcean环境执行\d entries确认该索引是否存在；若缺失则重新创建：CREATE INDEX index_entries_on_discarded_at ON entries(discarded_at);；若索引存在，执行REINDEX INDEX index_entries_on_discarded_at;后再跑ANALYZE entries;。

• 优化统计信息
  执行ANALYZE VERBOSE entries;确保Postgres获取最新表统计数据；通过SELECT * FROM pg_stat_activity WHERE query LIKE '%autovacuum%';确认autovacuum是否正常运行，避免统计数据过期。

• 调整配置参数

  • effective_cache_size：当前设为568MB，建议调整为服务器内存的70%-80%（如2GB内存的Droplet可设为1400MB），让优化器更倾向于选择索引扫描。
  • work_mem：当前1751kB，可临时调至4MB，避免并行扫描时的内存操作溢出到磁盘。
  • 临时关闭JIT测试：执行SET jit = off;后重新运行count查询，排除JIT编译的影响。

• 验证存储性能
  测试DigitalOcean存储IO速度：
  写入测试：dd if=/dev/zero of=test bs=1G count=1 oflag=direct
  读取测试：dd if=test of=/dev/null bs=1G count=1 iflag=direct
  先执行SELECT * FROM entries LIMIT 1;预热缓存，再运行count查询看性能是否改善。

• 清理表膨胀
  执行VACUUM ANALYZE entries;清理死元组、优化表物理结构；通过SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='entries';查看是否存在大量死元组导致表膨胀。

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