PostgreSQL嵌套循环中成本与行数估算不符问题排查

先看你提供的执行计划片段：

->  Nested Loop  (cost=0.57..933455.16 rows=11 width=122) (actual time=3.710..497.990 rows=86102 loops=1)
       ->  CTE Scan on last_outputs  (cost=0.00..5.02 rows=251 width=172) (actual time=3.675..333.800 rows=1773 loops=1)
       ->  Index Scan using i_batch_company_outputs_file_md5 on batch_company_outputs bco2  (cost=0.57..3718.91 rows=1 width=65) (actual time=0.019..0.080 rows=49 loops=1773)
             Index Cond: ((file_md5)::text = (last_outputs.file_md5)::text)
             Filter: (((service_id)::text = 'sheetbuilder'::text) AND ((file_name)::text = 'output.txt'::text) AND ((last_outputs.company_id)::text = ((company_id)::text || ''::text)))
             Rows Removed by Filter: 9

预估成本偏高的核心原因 #

PostgreSQL嵌套循环的成本计算公式为：
外层节点总成本 + 外层预估行数 × 内层节点单次执行成本

对应到你的执行计划：

• 外层CTE Scan的预估成本是5.02，预估行数251
• 内层Index Scan的单次预估成本是3718.91

计算后：5.02 + 251 × 3718.91 ≈ 933455.16，这就是高成本数值的来源。

实际执行时间远低于预期的原因是两个关键预估偏差：

1. 内层实际单次执行成本（0.019-0.080毫秒）远低于预估的3718.91——预估成本基于“每个外层行需扫描大量数据”的假设，但实际索引匹配效率极高，过滤开销极小。
2. 嵌套循环预估总行数（11）严重偏低，源于优化器对Filter条件的选择性预估错误，尤其是last_outputs.company_id = (company_id || '')这类涉及字符串拼接的条件，优化器无法准确计算匹配概率，导致预估内层每行仅返回1行，但实际返回49行。

进一步排查的具体方式 #

1. 更新并检查统计信息

   • 执行ANALYZE batch_company_outputs;更新表统计数据，重新生成执行计划观察预估是否改善
   • 查询字段统计详情，对比与实际数据分布的差异：

     SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs
     FROM pg_stats 
     WHERE tablename = 'batch_company_outputs' 
       AND attname IN ('file_md5', 'service_id', 'file_name', 'company_id');
     

2. 优化Filter条件的可预估性

   • 将last_outputs.company_id = (company_id || '')改为更直接的形式：若company_id是文本类型，直接用last_outputs.company_id = company_id；若为其他类型，显式转换为文本：last_outputs.company_id::text = company_id::text，避免空字符串拼接，帮助优化器准确计算条件选择性。
   • 手动计算Filter条件的真实选择性，比如统计service_id = 'sheetbuilder' AND file_name = 'output.txt'的行数占表总行数的比例，与优化器预估对比。

3. 查看完整执行计划

   • 执行EXPLAIN (ANALYZE, VERBOSE)获取包含CTElast_outputs内部逻辑的完整计划，排查CTE预估行数（251）与实际行数（1773）偏差的原因。

4. 检查索引定义与有效性

   • 查询索引具体定义：

     SELECT tablename, indexname, indexdef 
     FROM pg_indexes 
     WHERE indexname = 'i_batch_company_outputs_file_md5';
     

     若索引仅包含file_md5，扫描后需回表获取其他字段会拉高预估成本，可考虑创建包含service_id、file_name、company_id的覆盖索引，降低预估与实际成本。

5. 跟踪优化器决策过程

   • 临时开启log_planner_stats = on（修改postgresql.conf后重新加载配置），重新执行查询，查看日志中优化器计算统计信息、选择性、成本的过程，定位预估偏差的具体环节。

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