一、功能概述
针对形如：
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。
三、使用限制
注意：说明：
四、参数说明
| 参数名 | 类型 | 默认值 | 上下文 | 说明 |
| 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)