PostgreSQL 11为何选用Hash Join而非Nested Loop？

原查询语句 #

SELECT t.row_id,
       t.calc_id,
       t.load_id,
       f.fact_value metric_value
FROM   vw_f_tasks t
       LEFT JOIN vw_f_devs f
              ON ( f.row_id = t.calc_id
                   AND f.load_id = t.load_id )
WHERE  t.row_id IN ( '1066677788', '1066677789' );  

视图及依赖表定义 #

1. vw_f_tasks视图及依赖表 #

视图DDL：

CREATE OR replace VIEW vw_f_tasks
AS
  SELECT f_tasks.row_id,
         f_tasks.calc_id,
         f_tasks.load_id,
         lib_dev.direction_id AS alg_direction_id
  FROM   f_tasks
         left join isu_lib_deviations lib_dev
                ON lib_dev.dev_type_id = "Substring"(f_tasks.load_id :: text,
                                         '([0-9]+)' :: text)
                                         :: bigint;  

依赖表DDL：

CREATE TABLE f_tasks
  (
     row_id  VARCHAR(500) NOT NULL,
     calc_id VARCHAR(500) NULL,
     load_id VARCHAR(500) NOT NULL,
     CONSTRAINT f_tasks_pkey PRIMARY KEY (row_id)
  );

CREATE UNIQUE INDEX f_tasks_pkey ON f_tasks USING btree (row_id);
CREATE INDEX ib_f_tasks_load_id ON f_tasks USING btree (load_id);

CREATE TABLE isu_lib_deviations ( 
    algorithm_id INT4 NOT NULL, 
    dev_type_id INT8 NOT NULL,
    direction_id NOT NULL, 
    CONSTRAINT core_deviations_pkey PRIMARY KEY (algorithm_id) 
);

CREATE INDEX isu_lib_deviations_dev_type_id_idx ON isu_lib_deviations USING btree (dev_type_id);

2. vw_f_devs视图及依赖表 #

视图DDL：

CREATE OR replace VIEW dm_core.vw_f_devs AS
SELECT    f_devs.row_id,
          f_devs.load_id,
          f_devs.fact_value,
          lib_dev.direction_id AS alg_direction_id
FROM      f_devs
LEFT JOIN isu_lib_deviations lib_dev
ON        lib_dev.dev_type_id = "substring"(f_devs.load_id::text, '([0-9]+)'::text)::bigint;

依赖表DDL：

CREATE TABLE f_devs
             (
                          row_id     VARCHAR(500) NOT NULL,
                          load_id    VARCHAR(500) NOT NULL,
                          fact_value NUMERIC NULL,
                          CONSTRAINT f_devs_pkey PRIMARY KEY (row_id)
             ); 

查询计划 #

Hash Right Join  (cost=1367.27..29602029.56 rows=1 width=94) (actual time=29.582..880857.910 rows=2 loops=1)
  Hash Cond: (((f_devs.row_id)::text = (t.calc_id)::text) AND ((f_devs.load_id)::text = (t.load_id)::text))
  ->  Hash Left Join  (cost=1355.07..29577491.70 rows=1401465 width=8030) (actual time=17.861..856472.446 rows=140487536 loops=1)
        Hash Cond: ((substring((f_devs.load_id)::text, '([0-9]+)'::text))::bigint = lib_dev.dev_type_id)
        Filter: (ARRAY[(lib_dev.direction_id)::text, (f_devs.direction_id)::text, 'null'::text] && $3)
        CTE cte_rls
          ->  Function Scan on get_direction_rls get_direction_rls_1  (cost=0.26..0.27 rows=1 width=32) (actual time=1.099..1.100 rows=1 loops=1)
        InitPlan 4 (returns $3)
          ->  CTE Scan on cte_rls cte_rls_1  (cost=0.00..0.02 rows=1 width=32) (actual time=1.101..1.102 rows=1 loops=1)
        ->  Seq Scan on f_devs  (cost=0.00..7896986.60 rows=139817760 width=98) (actual time=1.544..68481.657 rows=140487536 loops=1)
       ->  Hash  (cost=1221.57..1221.57 rows=10657 width=16) (actual time=15.061..15.061 rows=10657 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 624kB
              ->  Seq Scan on isu_lib_deviations lib_dev  (cost=0.00..1221.57 rows=10657 width=16) (actual time=0.023..12.930 rows=10657 loops=1)
  ->  Hash  (cost=12.19..12.19 rows=1 width=91) (actual time=5.866..5.877 rows=2 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Subquery Scan on t  (cost=1.14..12.19 rows=1 width=91) (actual time=5.788..5.870 rows=2 loops=1)
              ->  Nested Loop Left Join  (cost=1.14..12.18 rows=1 width=6757) (actual time=5.787..5.861 rows=2 loops=1)
                    Filter: (ARRAY[(lib_dev_1.direction_id)::text, (f_tasks.direction_id)::text, 'null'::text] && $1)
                    CTE cte_rls
                      ->  Function Scan on get_direction_rls  (cost=0.26..0.27 rows=1 width=32) (actual time=2.773..2.773 rows=1 loops=1)
                    InitPlan 2 (returns $1)
                      ->  CTE Scan on cte_rls  (cost=0.00..0.02 rows=1 width=32) (actual time=2.774..2.775 rows=1 loops=1)
                    ->  Index Scan using f_tasks_pkey on f_tasks  (cost=0.56..6.03 rows=2 width=96) (actual time=2.782..2.801 rows=2 loops=1)
                          Index Cond: ((row_id)::text = ANY ('{1066677788,1066677789}'::text[]))
                    ->  Index Scan using isu_lib_deviations_dev_type_id_idx on isu_lib_deviations lib_dev_1  (cost=0.29..2.91 rows=1 width=16) (actual time=0.029..0.030 rows=1 loops=2)
                          Index Cond: (dev_type_id = (substring((f_tasks.load_id)::text, '([0-9]+)'::text))::bigint)
Planning Time: 5.144 ms
Execution Time: 880858.389 ms

问题与等价查询 #

原查询基于视图关联耗时约15分钟，但vw_f_tasks经WHERE过滤后仅返回2条数据，按预期应选择Nested Loop而非Hash Join。将查询改写为直接关联基础表的等价形式后，执行时间仅1秒，语句如下：

SELECT f_tasks.row_id,
       f_tasks.calc_id,
       f_tasks.load_id,
       f.fact_value metric_value
FROM   f_tasks
       left join isu_lib_deviations archi
              ON archi.dev_type_id = "Substring"(f_tasks.load_id :: text,
                                     '([0-9]+)' :: text) ::
                                     bigint
       left join (SELECT f_devs.row_id,
                         f_devs.load_id,
                         f_devs.fact_value
                  FROM   f_devs
                         left join isu_lib_deviations archi
                                ON archi.dev_type_id = "Substring"(
                                                       f_devs.load_id :: text,
                                                       '([0-9]+)' :: text) ::
                                                       bigint) f
              ON f.row_id = f_tasks.calc_id
                 AND f.load_id = f_tasks.load_id
WHERE  f_tasks.row_id IN ( '1066677788', '1066677789' )  

原因分析 #

1. 视图谓词下推限制：PostgreSQL处理视图时，无法将外层WHERE条件（t.row_id IN (...)）充分下推到视图内部的关联逻辑中，导致优化器错误预估vw_f_devs的关联成本。原查询中优化器先全表扫描vw_f_devs（1.4亿行）再做Hash Join，而等价查询中优化器能利用过滤后的2条f_tasks数据，对f_devs执行嵌套循环，仅检索匹配的calc_id和load_id数据。

2. RLS策略干扰：查询计划中出现的get_direction_rls函数及相关过滤条件，行级安全（RLS）策略可能改变了优化器对数据量的估算，使其认为需要先过滤vw_f_devs所有数据再关联，而非嵌套循环的按需查询。

3. 统计信息偏差：如果vw_f_devs的统计信息不准确，优化器会错误预估其返回行数，进而选择适合大表关联的Hash Join，而非适合小表驱动大表的Nested Loop。

解决建议 #

• 执行ANALYZE f_devs;更新统计信息，确保优化器准确评估数据量。
• 临时执行SET enable_hashjoin = off;禁用Hash Join，强制优化器选择Nested Loop验证性能差异。
• 优先使用直接关联基础表的查询形式，或创建物化视图替代普通视图，提前计算并存储视图结果。
• 检查RLS策略的必要性，或调整规则以允许优化器更好地进行谓词下推。

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