PostgreSQL超100亿行表count(*)查询耗时25分钟排查求助

云上老周

2026-7-15

PostgreSQL超100亿行大表COUNT(*)查询耗时25分钟排查求助

我在AWS Serverless RDS PostgreSQL中对一张超100亿行的大表执行select count(*)，耗时超过25分钟才返回结果。执行计划显示查询采用Parallel Seq Scan，且已提前1小时执行过vacuum/analyze，AWS监控也未发现资源（如ACU）受限，但这个耗时明显不合理，求排查原因。

查询计划

QUERY PLAN                         
-----------------------------------------------------------
 [                                                        +
   {                                                      +
     "Plan": {                                            +
       "Node Type": "Aggregate",                          +
       "Strategy": "Plain",                               +
       "Partial Mode": "Finalize",                        +
       "Parallel Aware": false,                           +
       "Startup Cost": 222432514.88,                      +
       "Total Cost": 222432514.89,                        +
       "Plan Rows": 1,                                    +
       "Plan Width": 8,                                   +
       "Actual Startup Time": 1524678.987,                +
       "Actual Total Time": 1524798.515,                  +
       "Actual Rows": 1,                                  +
       "Actual Loops": 1,                                 +
       "Shared Hit Blocks": 2605348,                      +
       "Shared Read Blocks": 163231630,                   +
       "Shared Dirtied Blocks": 0,                        +
       "Shared Written Blocks": 0,                        +
       "Local Hit Blocks": 0,                             +
       "Local Read Blocks": 0,                            +
       "Local Dirtied Blocks": 0,                         +
       "Local Written Blocks": 0,                         +
       "Temp Read Blocks": 0,                             +
       "Temp Written Blocks": 0,                          +
       "I/O Read Time": 1044087497.561,                   +
       "I/O Write Time": 0.000,                           +
       "Plans": [                                         +
         {                                                +
           "Node Type": "Gather",                         +
           "Parent Relationship": "Outer",                +
           "Parallel Aware": false,                       +
           "Startup Cost": 222432514.67,                  +
           "Total Cost": 222432514.88,                    +
           "Plan Rows": 2,                                +
           "Plan Width": 8,                               +
           "Actual Startup Time": 1524678.978,            +
           "Actual Total Time": 1524798.508,              +
           "Actual Rows": 3,                              +
           "Actual Loops": 1,                             +
           "Workers Planned": 2,                          +
           "Workers Launched": 2,                         +
           "Single Copy": false,                          +
           "Shared Hit Blocks": 2605348,                  +
           "Shared Read Blocks": 163231630,               +
           "Shared Dirtied Blocks": 0,                    +
           "Shared Written Blocks": 0,                    +
           "Local Hit Blocks": 0,                         +
           "Local Read Blocks": 0,                        +
           "Local Dirtied Blocks": 0,                     +
           "Local Written Blocks": 0,                     +
           "Temp Read Blocks": 0,                         +
           "Temp Written Blocks": 0,                      +
           "I/O Read Time": 1044087497.561,               +
           "I/O Write Time": 0.000,                       +
           "Plans": [                                     +
             {                                            +
               "Node Type": "Aggregate",                  +
               "Strategy": "Plain",                       +
               "Partial Mode": "Partial",                 +
               "Parent Relationship": "Outer",            +
               "Parallel Aware": false,                   +
               "Startup Cost": 222431514.67,              +
               "Total Cost": 222431514.68,                +
               "Plan Rows": 1,                            +
               "Plan Width": 8,                           +
               "Actual Startup Time": 1524661.376,        +
               "Actual Total Time": 1524661.376,          +
               "Actual Rows": 1,                          +
               "Actual Loops": 3,                         +
               "Shared Hit Blocks": 2605348,              +
               "Shared Read Blocks": 163231630,           +
               "Shared Dirtied Blocks": 0,                +
               "Shared Written Blocks": 0,                +
               "Local Hit Blocks": 0,                     +
               "Local Read Blocks": 0,                    +
               "Local Dirtied Blocks": 0,                 +
               "Local Written Blocks": 0,                 +
               "Temp Read Blocks": 0,                     +
               "Temp Written Blocks": 0,                  +
               "I/O Read Time": 1044087497.561,           +
               "I/O Write Time": 0.000,                   +
               "Workers": [                               +
               ],                                         +
               "Plans": [                                 +
                 {                                        +
                   "Node Type": "Seq Scan",               +
                   "Parent Relationship": "Outer",        +
                   "Parallel Aware": true,                +
                   "Relation Name": "alf_node_properties",+
                   "Alias": "alf_node_properties",        +
                   "Startup Cost": 0.00,                  +
                   "Total Cost": 211112638.93,            +
                   "Plan Rows": 4527550293,               +
                   "Plan Width": 0,                       +
                   "Actual Startup Time": 1.430,          +
                   "Actual Total Time": 1238163.306,      +
                   "Actual Rows": 3624835814,             +
                   "Actual Loops": 3,                     +
                   "Shared Hit Blocks": 2605348,          +
                   "Shared Read Blocks": 163231630,       +
                   "Shared Dirtied Blocks": 0,            +
                   "Shared Written Blocks": 0,            +
                   "Local Hit Blocks": 0,                 +
                   "Local Read Blocks": 0,                +
                   "Local Dirtied Blocks": 0,             +
                   "Local Written Blocks": 0,             +
                   "Temp Read Blocks": 0,                 +
                   "Temp Written Blocks": 0,              +
                   "I/O Read Time": 1044087497.561,       +
                   "I/O Write Time": 0.000,               +
                   "Workers": [                           +
                   ]                                      +
                 }                                        +
               ]                                          +
             }                                            +
           ]                                              +
         }                                                +
       ]                                                  +
     },                                                   +
     "Planning": {                                        +
       "Shared Hit Blocks": 225,                          +
       "Shared Read Blocks": 0,                           +
       "Shared Dirtied Blocks": 0,                        +
       "Shared Written Blocks": 0,                        +
       "Local Hit Blocks": 0,                             +
       "Local Read Blocks": 0,                            +
       "Local Dirtied Blocks": 0,                         +
       "Local Written Blocks": 0,                         +
       "Temp Read Blocks": 0,                             +
       "Temp Written Blocks": 0,                          +
       "I/O Read Time": 0.000,                            +
       "I/O Write Time": 0.000                            +
     },                                                   +
     "Planning Time": 17.793,                             +
     "Triggers": [                                        +
     ],                                                   +
     "Execution Time": 1524798.554                        +
   }                                                      +
 ]
(1 row)

核心原因分析

从执行计划的关键数据可直接定位问题：

磁盘I/O是最大瓶颈：I/O Read Time高达约17400分钟（1044087497毫秒），占总执行时间的99%以上，说明几乎所有耗时都在等待磁盘读取。
缓存命中率极低：Shared Hit Blocks仅260万，Shared Read Blocks却有1.63亿，仅约1.6%的数据在内存缓存中，剩下的全要从磁盘加载。
并行度不足：仅启动2个并行worker，对于100亿行的大表来说，并行进程数太少，未充分利用Serverless的弹性计算资源。

排查与优化建议

1. 优化缓存利用率

检查RDS参数组中的shared_buffers配置，Serverless实例默认值可能偏低，可尝试适当调高（需符合Serverless的参数限制）。
若需要频繁执行COUNT查询，可提前预热缓存：执行一次全表扫描（如SELECT count(1) FROM alf_node_properties），让数据加载到shared_buffers中，后续COUNT查询会大幅提速。

2. 提升并行查询能力

调整max_parallel_workers_per_gather参数，将并行worker数量提高到4-8（根据实例规格调整，避免资源过载），让更多进程同时读取磁盘数据。
降低parallel_setup_cost和parallel_tuple_cost参数，减少并行查询的启动成本，让优化器更倾向于使用更高的并行度。

3. 替代COUNT(*)的高效方案

用统计信息近似计数：如果不需要精确值，直接查询pg_class中的近似行数，速度极快：
```
SELECT reltuples::bigint FROM pg_class WHERE relname = 'alf_node_properties';
```
注意：该值是VACUUM/ANALYZE后更新的近似值，数据变动频繁时误差会增大。
创建覆盖索引：创建一个基于非空字段（如主键）的索引，PostgreSQL可以通过索引扫描来计数，比全表扫描快数倍：
```
CREATE INDEX idx_alf_node_properties_count ON alf_node_properties (id); -- id为非空字段或主键
```
缺点：会增加存储空间和写入开销，需根据业务场景权衡。
分区表拆分统计：如果表是分区表，可分别统计每个分区的行数再求和，利用并行扫描每个分区提升效率。

4. 检查存储层性能

排查AWS EBS的IOPS和吞吐量是否达到瓶颈，虽然监控未显示资源受限，但EBS的突发IOPS可能被耗尽，导致持续低性能。
考虑升级存储类型，将gp2切换为gp3，gp3提供更高的基线IOPS和吞吐量，更适合大表扫描场景。

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

PostgreSQL超100亿行表count(*)查询耗时25分钟排查求助

查询计划 #

核心原因分析 #

排查与优化建议 #

1. 优化缓存利用率 #

2. 提升并行查询能力 #

3. 替代COUNT(*)的高效方案 #

4. 检查存储层性能 #