帮你快速理解、总结文档立即下载
文档中心>云数据库 PostgreSQL>内核能力介绍>自研内核功能介绍>连接谓词下推到子查询

连接谓词下推到子查询

最近更新时间：2026-07-17 20:15:00

我的收藏

本页目录：

功能概述

针对形如：
SELECT ...
FROM outer_tab o,
     (SELECT k, agg(x) FROM big_tab GROUP BY k) s
WHERE o.k = s.k AND o.filter_col < ...;

PostgreSQL 主干默认无法把等值连接谓词 o.k = s.k 推到子查询内部（因为子查询含 GROUP BY），只能先把 big_tab 整表聚合物化，再做 Nested Loop / Hash Join，导致 IO 放大。
云数据库 PostgreSQL 实现了 Push Down Join Clause to Subquery 优化，新增 GUC tencentdb_enable_push_pred。打开后，优化器会尝试生成一条参数化（parameterised）SubqueryScan 路径。内层子查询由此可以走 Index Scan (Index Cond: c1 = t1.c1) 而不是全表扫 + Hash / Sort。

前提条件

PostgreSQL 18，版本 ≥ v18.4_r1.10。

使用限制

说明：
只有等值连接谓词（= 且 hashable）会被下推。
存在 LIMIT / OFFSET / DISTINCT ON 等 volatile 顶层修饰的子查询会被拒绝。
该功能默认关闭。

参数说明

参数名
类型
默认值
上下文
说明
tencentdb_enable_push_pred
bool
off
USERSET
是否允许把等值连接谓词下推到子查询 RTE，生成参数化 SubqueryScan 路径。

启用方法

SET tencentdb_enable_push_pred = on;
或写入 postgresql.conf：
tencentdb_enable_push_pred = on

使用示例

准备数据：
CREATE TABLE pushpred_t1(c1 int, c2 int, c3 int, c4 int);
CREATE TABLE pushpred_t2(c1 int, c2 int, c3 int, c4 int);
CREATE TABLE pushpred_t3(c1 int, c2 int, c3 int, c4 int);

INSERT INTO pushpred_t1 SELECT i,i,i,i FROM generate_series(1,10000) i;
INSERT INTO pushpred_t2 SELECT i,i,i,i FROM generate_series(1,10000) i;
INSERT INTO pushpred_t3 SELECT i,i,i,i FROM generate_series(1,10000) i;

CREATE INDEX pushpred_t1_c1_idx ON pushpred_t1(c1);
CREATE INDEX pushpred_t2_c1_idx ON pushpred_t2(c1);
CREATE INDEX pushpred_t3_c1_idx ON pushpred_t3(c1);

ANALYZE pushpred_t1; ANALYZE pushpred_t2; ANALYZE pushpred_t3;

SET max_parallel_workers TO 0;
SET max_parallel_workers_per_gather TO 0;
SET enable_hashjoin TO off;
SET enable_mergejoin TO off;
SET enable_bitmapscan TO off;

基线：子查询按聚合结果整体连接

SET tencentdb_enable_push_pred TO off;

EXPLAIN (COSTS OFF)
SELECT * FROM pushpred_t1 t1,
              (SELECT c1, sum(c2) FROM pushpred_t2 GROUP BY c1) st2
 WHERE t1.c1 = st2.c1 AND t1.c4 < 5;
计划形态（简化）：
 Nested Loop
   Join Filter: (t1.c1 = pushpred_t2.c1)
   ->  HashAggregate
         Group Key: pushpred_t2.c1
         ->  Seq Scan on pushpred_t2
   ->  Materialize
         ->  Seq Scan on pushpred_t1 t1
               Filter: (c4 < 5)
pushpred_t2 整表被聚合后再连接，IO 与 pushpred_t2 表规模成正比。

启用后：参数化 SubqueryScan + 内部索引扫描

SET tencentdb_enable_push_pred TO on;

EXPLAIN (COSTS OFF)
SELECT * FROM pushpred_t1 t1,
              (SELECT c1, sum(c2) FROM pushpred_t2 GROUP BY c1) st2
 WHERE t1.c1 = st2.c1 AND t1.c4 < 5;
计划形态：
 Nested Loop
   ->  Seq Scan on pushpred_t1 t1
         Filter: (c4 < 5)
   ->  Subquery Scan on st2
         Filter: (t1.c1 = st2.c1)
         ->  GroupAggregate
               ->  Index Scan using pushpred_t2_c1_idx on pushpred_t2
                     Index Cond: (c1 = t1.c1)
c1 = t1.c1 已经作为相关谓词下推进子查询体内，从而走 pushpred_t2_c1_idx 索引，每次外层元组只回读命中的一小组行。

UNION ALL 子查询

对 UNION ALL 组合子查询，下推同样生效：
SET tencentdb_enable_push_pred TO on;

EXPLAIN (COSTS OFF)
SELECT * FROM pushpred_t1 t1,
       (SELECT c1, sum(c2) FROM pushpred_t2 GROUP BY c1
        UNION ALL
        SELECT c1, sum(c2) FROM pushpred_t3 GROUP BY c1) st2
 WHERE t1.c1 = st2.c1 AND t1.c4 < 5;
每个 UNION 分支各自拿到相关谓词，各自走自己表的 c1 索引。
 Nested Loop
   ->  Seq Scan on pushpred_t1 t1
         Filter: (c4 < 5)
   ->  Append
         ->  Subquery Scan on "*SELECT* 1"
               Filter: (t1.c1 = "*SELECT* 1".c1)
               ->  GroupAggregate
                     ->  Index Scan using pushpred_t2_c1_idx on pushpred_t2
                           Index Cond: (c1 = t1.c1)
         ->  Subquery Scan on "*SELECT* 2"
               Filter: (t1.c1 = "*SELECT* 2".c1)
               ->  GroupAggregate
                     ->  Index Scan using pushpred_t3_c1_idx on pushpred_t3
                           Index Cond: (c1 = t1.c1)
中国站