AWS RDS Postgres同数据同查询执行差异问题求助

针对你遇到的AWS RDS Postgres 13.7中foo表查询挂起、克隆表bar却性能正常的问题，结合你已排查的信息，给出以下针对性排查和解决步骤：

• 强制更新foo表统计信息
  尽管执行计划显示一致，但foo表的统计信息可能因频繁写入/更新而过时，导致优化器对自连接的行数估计偏差，实际运行时产生远超预期的中间数据。执行ANALYZE foo;强制更新统计，新克隆的bar表默认会生成新鲜统计，这可能是两者性能差异的核心原因之一。

• 排查并发负载与锁冲突
  仅下午时段查询正常，说明其他时段foo表存在竞争资源或锁的情况：

  • 用SELECT * FROM pg_locks WHERE relation = 'foo'::regclass;查看表级锁持有情况
  • 用SELECT * FROM pg_stat_activity WHERE query LIKE '%foo%';查看针对foo的活跃查询
    非下午时段的批量写入、大更新或其他长查询可能占用了CPU/IO资源，或持有锁阻塞了你的查询。

• 消除foo表存储碎片
  即使执行过VACUUM，频繁更新/删除的foo表可能存在大量物理存储碎片，自连接时IO开销远大于连续存储的bar表。在业务低峰期执行VACUUM FULL foo;（此操作会锁表），重新整理表的物理存储，降低IO延迟。

• 对比自连接的实际执行数据
  用EXPLAIN ANALYZE对比两表自连接的真实运行指标：

  -- foo表自连接分析
  EXPLAIN ANALYZE
  WITH distinct_cte AS (SELECT DISTINCT col1, col2 FROM foo)
  SELECT * FROM distinct_cte d1 JOIN distinct_cte d2 ON d1.col1 = d2.col2;
  
  -- bar表自连接分析
  EXPLAIN ANALYZE
  WITH distinct_cte AS (SELECT DISTINCT col1, col2 FROM bar)
  SELECT * FROM distinct_cte d1 JOIN distinct_cte d2 ON d1.col1 = d2.col2;
  

  重点关注实际返回行数、循环次数、磁盘读写耗时，可能foo表的CTE结果无法完全缓存到内存，导致自连接时频繁磁盘IO。

• 排查RDS资源瓶颈
  查看RDS监控面板的CPU使用率、内存使用率、磁盘IOPS、吞吐量指标：

  • 如果其他时段资源使用率接近峰值，说明查询因资源不足被阻塞，可临时升级实例规格或调整参数组（比如增大work_mem，提升排序和连接操作的内存分配）。

• 重建foo表索引
  尽管克隆表复制了索引，但foo表的索引可能存在碎片或隐性损坏，导致自连接时索引扫描效率低下。执行REINDEX TABLE foo;重建所有索引，验证性能是否改善。

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