PostgreSQL 14查询pg_stat_database缓慢的原因及优化咨询

问题背景 #

使用PostgreSQL 14.0版本，执行以下SQL语句：

explain analyse select xact_commit from pg_stat_database;

得到查询计划：

QUERY PLAN:
Subquery Scan on d (cost=0.00..1.11 rows=3 width=8) (actual time=21.502..21.547 rows=8 loops=1)
    -> Append (cost=0.00..1.07 rows=3 width=68) (actual time=0.007..0.046 rows=8 loops=1)
        -> Subquery Scan on "*SELECT* 1" (cost=0.00..0.02 rows=1 width=68) (actual time=0.006..0.008 rows=1 loops=1)
              -> Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=1)
        -> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=68) (actual time=0.028..0.032 rows=7 loops=1)
Planning Time: 0.556 ms
Execution Time: 21.686 ms

该查询耗时远超用户表及pg_class等系统表，且未直接使用SeqScan，需分析原因并给出优化方案。

缓慢原因 #

1. pg_stat_database是动态生成的视图：它并非物理表，底层通过调用pg_stat_get_db_stat()函数实时计算统计数据。查询计划中的Append节点包含两部分逻辑：
   • Result节点生成所有数据库的汇总统计行（调用pg_stat_get_db_stat(0)）
   • 对pg_database的Seq Scan会为每个数据库调用一次pg_stat_get_db_stat(d.oid)，从共享内存中提取该数据库的事务、连接等实时统计
2. 共享内存访问的额外开销：pg_stat_get_db_stat()需要访问PostgreSQL的共享内存统计区域，这涉及跨进程数据读取，系统负载较高时还可能存在锁竞争，这类开销远大于普通系统表的磁盘扫描操作。
3. 执行时间分布验证：从计划可以看到，Append节点实际耗时仅0.046ms，而外层Subquery Scan耗时达21.5ms，说明真正的耗时集中在视图函数的计算与共享内存读取阶段，而非表扫描。

优化方案 #

• 跳过汇总行，直接查询单库统计：如果不需要全局汇总数据，直接关联pg_database与统计函数，避免额外的汇总计算：

  SELECT (pg_stat_get_db_stat(d.oid)).xact_commit
  FROM pg_database d;
  

• 使用物化视图缓存结果：若需频繁查询该视图数据，创建物化视图定期刷新，替代每次查询的实时计算：

  CREATE MATERIALIZED VIEW mv_pg_stat_database AS
  SELECT * FROM pg_stat_database;
  
  -- 可通过定时任务定期刷新，例如每分钟执行一次
  REFRESH MATERIALIZED VIEW mv_pg_stat_database;
  

• 缩小查询范围：仅查询目标数据库的统计数据，减少函数调用次数：

  SELECT xact_commit FROM pg_stat_database WHERE datname = 'your_target_database';
  

• 优化统计存储（可选）：将stats_temp_directory配置为内存文件系统（如Linux下的tmpfs），减少统计数据的磁盘IO开销，适合统计数据量大的场景。

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