PostgreSQL查询优化器为何为大表连接选Nested Loop Join？如何优化？

背景信息 #

需要连接两张大表：

live_inventory_calculation: 810万条记录
demand: 300万条记录

查询优化器当前默认选择Nested Loop Join，查询耗时超1分钟，且连接字段已创建索引。通过执行：

SET LOCAL enable_nestloop = False;

强制优化器使用Hash Join后，查询仅需12秒。

查询语句如下：

SELECT 
  live_inventory_calculation.csku_id,
  live_inventory_calculation.dc_code,
  live_inventory_calculation.date,
  count(*)
FROM live_inventory_calculation
INNER JOIN demand ON
  live_inventory_calculation.csku_id = demand.sku_id
  AND live_inventory_calculation.dc_code = demand.dc_code
  AND live_inventory_calculation.date = demand.date
GROUP BY 
  live_inventory_calculation.csku_id, 
  live_inventory_calculation.dc_code,
  live_inventory_calculation.production_week;

所有与live_inventory_calculation表的连接都存在这个问题，且已刷新统计信息，确保优化器拥有正确数据。

问题 #

1. 既然两张表都是大表，查询优化器为何仍选择Nested Loop Join？
2. 如何在不使用参数设置的情况下，让查询优化器选择Hash Join？

解答 #

问题1：优化器选择Nested Loop Join的原因 #

尽管两张表数据量都很大，优化器仍选Nested Loop Join的常见原因包括：

• 统计信息偏差：即使执行了ANALYZE，若连接字段的数据分布极度倾斜（比如某几个csku_id+dc_code+date组合占绝大多数数据），优化器可能错误估算嵌套循环的迭代次数和成本，认为其比Hash Join更高效。
• 成本参数设置不合理：PostgreSQL默认random_page_cost（索引随机读取成本）为4，若使用SSD存储（随机读取成本远低于机械盘），优化器会低估索引查找的实际耗时，从而更倾向于依赖索引的Nested Loop Join。
• 索引的误导：连接字段上的索引让优化器认为可以通过快速索引查找匹配内层表，忽略了大表场景下嵌套循环总迭代次数带来的高耗时。
• 结果集估算错误：优化器可能低估了连接后的中间结果集大小，误以为嵌套循环的单次查找成本低就能覆盖总开销，实际大表连接时Hash Join的批量处理效率更高。

问题2：无需参数强制的Hash Join引导方法 #

可以通过以下方式让优化器主动选择Hash Join：

1. 调整成本参数：
   提高random_page_cost的值（比如设为10），让优化器意识到索引随机读取的实际成本更高；或降低seq_page_cost，提升顺序扫描的竞争力，从而倾向于Hash Join。执行语句：

   SET LOCAL random_page_cost = 10;
   

   （建议先局部测试，如需全局生效可修改postgresql.conf）
2. 使用查询提示：
   PostgreSQL 12及以上版本支持查询提示，直接指定使用Hash Join：

   SELECT /*+ HashJoin(live_inventory_calculation demand) */
     live_inventory_calculation.csku_id,
     live_inventory_calculation.dc_code,
     live_inventory_calculation.date,
     count(*)
   FROM live_inventory_calculation
   INNER JOIN demand ON
     live_inventory_calculation.csku_id = demand.sku_id
     AND live_inventory_calculation.dc_code = demand.dc_code
     AND live_inventory_calculation.date = demand.date
   GROUP BY 
     live_inventory_calculation.csku_id, 
     live_inventory_calculation.dc_code,
     live_inventory_calculation.production_week;
   

3. 优化统计信息精度：
   针对连接字段提高统计信息采样率，让优化器更准确判断数据分布：

   ALTER TABLE live_inventory_calculation ALTER COLUMN csku_id SET STATISTICS 1000;
   ALTER TABLE live_inventory_calculation ALTER COLUMN dc_code SET STATISTICS 1000;
   ALTER TABLE live_inventory_calculation ALTER COLUMN date SET STATISTICS 1000;
   ALTER TABLE demand ALTER COLUMN sku_id SET STATISTICS 1000;
   ALTER TABLE demand ALTER COLUMN dc_code SET STATISTICS 1000;
   ALTER TABLE demand ALTER COLUMN date SET STATISTICS 1000;
   ANALYZE live_inventory_calculation;
   ANALYZE demand;
   

4. 调整连接顺序：
   通过子查询或CTE明确让较小的demand表作为Hash Join的构建侧（Hash Join用小表构建哈希表更高效），引导优化器调整连接策略：

   SELECT 
     lic.csku_id,
     lic.dc_code,
     lic.date,
     count(*)
   FROM live_inventory_calculation lic
   INNER JOIN (SELECT sku_id, dc_code, date FROM demand) d
     ON lic.csku_id = d.sku_id
     AND lic.dc_code = d.dc_code
     AND lic.date = d.date
   GROUP BY lic.csku_id, lic.dc_code, lic.production_week;
   

内容的提问来源于stack exchange，提问作者Mangat Rai Modi