前面我测试了oracle迁移到postgresql的两个性能问题，not in时不能转换为Antijoin, 和join view的谓词不是关连列时，存在无法JOIN Predicate Pushdown，下面测试一下在另外2款集中式数据库达梦和崖山的表现。
概念区分
开始可能会把join filter和Predicate Pushdown 概念搞混，广义上Join Filter 属于谓词下推的范畴，尤其是 Join 相关的谓词优化。下面做个简单的区分。狭义上不是所有谓词下推都是 Join Filter（普通 WHERE 过滤下推到 Scan 就不涉及 Join）,实际两者目标一致——尽早过滤，减少 Join 的输入数据量，性能提升非常显著。
- 谓词下推（Predicate Pushdown / 谓词推入）：
核心优化思想是把过滤条件（WHERE / HAVING 中的 predicate）尽可能早地往下推，靠近数据源（Scan 层）或 Join 之前执行，减少后续处理的数据量。
- Join Filter： 特指与 Join 操作相关的过滤。
常见场景包括：
- Join 条件本身（ON 子句的等值条件）。
- 通过 Join 推导出来的过滤条件（例如 inner join 后可以把一侧的过滤推到另一侧）。
- Runtime Filter / Dynamic Filter（运行时 Join Filter）：在大表 Join 小表时，用小表构建 Bloom Filter / Hash Set 等，在扫描大表时就过滤掉不可能匹配的行（这属于Join 谓词下推的进阶形式）。
达梦
构建测试数据
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 7.366(ms)
disql V8
SQL>
SQL> create table tobj1 as select * from dba_objects;
executed successfully
used time: 66.824(ms). Execute id is 60105.
SQL> create table tobj2 as select * from dba_objects;
executed successfully
used time: 63.160(ms). Execute id is 60106.
SQL> insert into tobj2 select * from tobj2;
affect rows 1015
used time: 6.149(ms). Execute id is 60107.
SQL> /
affect rows 2030
used time: 9.378(ms). Execute id is 60108.
SQL> /
affect rows 4060
...
SQL> select count(*) from tobj2;
LINEID COUNT(*)
---------- --------------------
1 2078720
Not in场景
SQL> select count(*) from tobj1 where object_id not in(select object_id from tobj2 where object_id is not null) ;
LINEID COUNT(*)
---------- --------------------
1 0
used time: 439.006(ms). Execute id is 60120.
SQL> explain select count(*) from tobj1 where object_id not in(select object_id from tobj2 where object_id is not null) ;
1 #NSET2: [384, 1, 30]
2 #PRJT2: [384, 1, 30]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [384, 1, 30]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #HASH LEFT SEMI JOIN2: [384, 1, 30]; (ANTI),KEY_NUM(1); KEY(TOBJ1.OBJECT_ID=DMTEMPVIEW_889214295.colname) KEY_NULL_EQU(0)
5 #CSCN2: [1, 1013, 30]; INDEX33555549(TOBJ1)
6 #PRJT2: [250, 1974784, 30]; exp_num(1), is_atom(FALSE)
7 #SLCT2: [250, 1974784, 30]; NOT(TOBJ2.OBJECT_ID IS NULL)
8 #CSCN2: [250, 2078720, 30]; INDEX33555550(TOBJ2)
used time: 1.432(ms). Execute id is 0.
SQL> select count(*) from tobj1 where not exists(select 1 from tobj2 where tobj1.object_id=tobj2.object_id);
LINEID COUNT(*)
---------- --------------------
1 0
used time: 382.377(ms). Execute id is 60121.
SQL> explain select count(*) from tobj1 where not exists(select 1 from tobj2 where tobj1.object_id=tobj2.object_id);
1 #NSET2: [371, 1, 42]
2 #PRJT2: [371, 1, 42]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [371, 1, 42]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #HASH LEFT SEMI JOIN2: [371, 1, 42]; (ANTI),KEY_NUM(1); KEY(TOBJ1.OBJECT_ID=TOBJ2.OBJECT_ID) KEY_NULL_EQU(0)
5 #CSCN2: [1, 1013, 42]; INDEX33555549(TOBJ1)
6 #CSCN2: [230, 2078720, 30]; INDEX33555550(TOBJ2)
Note: 在达梦中not in 和not exists一样都使用了 ANTI JOIN，而非逐行执行子查询. 便是注意使用not in时子查询是先被完整的物化再与外层匹配。 而not exists使用了更优的反连接（ANTI JOIN）优化，并且避免了子查询全物化。虽然两个计划最终都是 HASH ANTI JOIN，但 NOT EXISTS 版本省去了 PRJT2 和 SLCT2 两层操作，减少了数据复制和内存开销。
| 对比项 | NOT IN 版本 | NOT EXISTS 版本 |
|---|---|---|
| 子查询处理 | 先全扫描 + 过滤 NULL，生成中间结果（第6-8行有 PRJT2 + SLCT2） | 直接扫描，无额外投影层 |
| 中间结果集 | 需要物化投影后的 197 万行 | 无需物化，直接参与 HASH 连接 |
| 执行计划层级 | 3 层（子查询投影 → 过滤 → 反连接） | 2 层（扫描 → 反连接） |
| 性能 | 差（大量内存/临时表消耗） | 好（流式处理） |
NOT EXISTS 而非 NOT IN，即使 NOT IN 写了 IS NOT NULLJOIN VIEW 场景
SQL> create view vobj2 as select object_id,max(object_name) object_name,count(*) cnt from tobj2 group by object_id;
executed successfully
used time: 17.226(ms). Execute id is 60122.
SQL> EXPLAIN select * from tobj1 left join vobj2 on tobj1.object_id=vobj2.object_id where tobj1.object_id=40;
1 #NSET2: [128510, 13160, 675]
2 #PRJT2: [128510, 13160, 675]; exp_num(18), is_atom(FALSE)
3 #NEST LOOP LEFT JOIN2: [128510, 13160, 675]; partition_keys_num(0) ret_null(0)
4 #SLCT2: [1, 25, 597]; TOBJ1.OBJECT_ID = var1
5 #CSCN2: [1, 1013, 597]; INDEX33555549(TOBJ1)
6 #SLCT2: [404, 519, 78]; VOBJ2.OBJECT_ID = var2
7 #PRJT2: [404, 20787, 78]; exp_num(3), is_atom(FALSE)
8 #HAGR2: [404, 20787, 78]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(TOBJ2.OBJECT_ID)
9 #CSCN2: [251, 2078720, 78]; INDEX33555550(TOBJ2)
used time: 1.844(ms). Execute id is 0.
SQL> create index idx_tobj1_oid on tobj1(object_id);
executed successfully
used time: 449.781(ms). Execute id is 60123.
SQL> create index idx_tobj2_oid on tobj2(object_id);
executed successfully
used time: 00:00:06.532. Execute id is 60124.
SQL> EXPLAIN select * from tobj1 left join vobj2 on tobj1.object_id=vobj2.object_id where tobj1.object_id=40;
1 #NSET2: [128507, 13160, 675]
2 #PRJT2: [128507, 13160, 675]; exp_num(18), is_atom(FALSE)
3 #NEST LOOP LEFT JOIN2: [128507, 13160, 675]; partition_keys_num(0) ret_null(0)
4 #BLKUP2: [1, 25, 597]; IDX_TOBJ1_OID(TOBJ1)
5 #SSEK2: [1, 25, 597]; scan_type(ASC), IDX_TOBJ1_OID(TOBJ1), scan_range[exp_cast(40),exp_cast(40)]
6 #SLCT2: [404, 519, 78]; VOBJ2.OBJECT_ID = var1
7 #PRJT2: [404, 20787, 78]; exp_num(3), is_atom(FALSE)
8 #HAGR2: [404, 20787, 78]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(TOBJ2.OBJECT_ID)
9 #CSCN2: [251, 2078720, 78]; INDEX33555550(TOBJ2)
used time: 2.541(ms). Execute id is 0.
使用Join 条件谓词时，在达梦8时发生了视图谓词推进（View Predicate Pushdown）。
- 第 6 行：
#SLCT2: [404, 519, 78]; VOBJ2.OBJECT_ID = var1- 这说明在扫描视图
VOBJ2时，已经应用了一个过滤条件VOBJ2.OBJECT_ID = var1。 - 这里的
var1是从左表TOBJ1传来的绑定变量值（具体为40）。
- 这说明在扫描视图
吐槽，执行计划关键字真难看。
SQL> EXPLAIN select * from tobj1 left join vobj2 on tobj1.object_id=vobj2.object_id where tobj1.object_name='tobj';
1 #NSET2: [406, 75, 675]
2 #PRJT2: [406, 75, 675]; exp_num(18), is_atom(FALSE)
3 #HASH LEFT JOIN2: [406, 75, 675]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TOBJ1.OBJECT_ID=VOBJ2.OBJECT_ID)
4 #SLCT2: [1, 25, 597]; TOBJ1.OBJECT_NAME = 'tobj'
5 #CSCN2: [1, 1013, 597]; INDEX33555549(TOBJ1)
6 #PRJT2: [404, 20787, 78]; exp_num(3), is_atom(FALSE)
7 #HAGR2: [404, 20787, 78]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(TOBJ2.OBJECT_ID)
8 #CSCN2: [251, 2078720, 78]; INDEX33555550(TOBJ2)
used time: 2.093(ms). Execute id is 0.
NOte: 在使用 非join 列谓词时，并没有发生视图谓词推进
- 第4-5行： 从
TOBJ1中筛选object_name='tobj'的记录（扫描索引INDEX33555549）。索引组织表机构，使用了内部的索引。 - 第6-8行：独立、完整地执行视图
vobj2：- 全表扫描
TOBJ2（预估 2,078,720 行，见第8行） - 进行哈希聚合
HAGR2（按OBJECT_ID分组） - 生成完整的视图结果集
- 全表扫描
| 特征 | 使用join列谓词 | 使用 非join列谓词 |
|---|---|---|
| 连接方式 | NEST LOOP LEFT JOIN | HASH LEFT JOIN |
| 视图侧过滤 | #SLCT2: VOBJ2.OBJECT_ID = var1 （有参数传递） | 无此条件 |
| 视图扫描 | 虽然有CSCN2，但外层传入了绑定变量 | 视图独立全扫，无外部条件 |
崖山数据库
构建测试数据
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL>
SQL> create table tobj1 as select * from dba_objects;
Succeed.
SQL> select count(*) from tobj1;
COUNT(*)
---------------------
2333
1 row fetched.
SQL> create table tobj2 as select * from dba_objects;
Succeed.
SQL> insert into tobj2 select * from tobj2;
2334 rows affected.
SQL> /
4668 rows affected.
...
SQL> select count(*) from tobj2;
COUNT(*)
---------------------
1195008not in 场景
SQL> explain select count(*) from tobj1 where object_id not in(select object_id from tobj2 where object_id is not null) ;
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 72379594
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | AGGREGATE | | | 1| 280( 0)| |
|* 2 | HASH JOIN ANTI NA | | | 99900| 279( 0)| |
| 3 | TABLE ACCESS FULL | TOBJ1 | SYS | 100000| 136( 0)| |
| 4 | HASH GROUP | | | 1000| 141( 0)| |
|* 5 | TABLE ACCESS FULL | TOBJ2 | SYS | 96000| 136( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
2 - Predicate : access("TOBJ1"."OBJECT_ID" = "TOBJ2"."OBJECT_ID")
4 - Group Expression: ("TOBJ2"."OBJECT_ID")
5 - Predicate : filter("TOBJ2"."OBJECT_ID" IS NOT NULL)
SQL> explain select count(*) from tobj1 where not exists(select 1 from tobj2 where tobj1.object_id=tobj2.object_id);
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 1231899452
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | AGGREGATE | | | 1| 280( 0)| |
|* 2 | HASH JOIN ANTI | | | 99900| 279( 0)| |
| 3 | TABLE ACCESS FULL | TOBJ1 | SYS | 100000| 136( 0)| |
| 4 | HASH GROUP | | | 1000| 141( 0)| |
| 5 | TABLE ACCESS FULL | TOBJ2 | SYS | 100000| 136( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
2 - Predicate : access("TOBJ1"."OBJECT_ID" = "TOBJ2"."OBJECT_ID")
4 - Group Expression: ("TOBJ2"."OBJECT_ID")
19 rows fetched.
Note: 这执行计划看的很熟悉，和oracle一样使用了hash join anti join。
JOIN VIEW 场景
SQL> create view vobj2 as select object_id,max(object_name) object_name,count(*) cnt from tobj2 group by object_id;
SQL> create index idx_tobj1_oid on tobj1(object_id);
Succeed.
SQL> create index idx_tobj2_oid on tobj2(object_id);
Succeed.
SQL> explain select * from tobj1 left join vobj2 on tobj1.object_id=vobj2.object_id where tobj1.object_id=40;
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 3480004066
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN LEFT OUTER | | | 101| 1( 0)| |
| 2 | TABLE ACCESS BY INDEX ROWID | TOBJ1 | SYS | 101| 1( 0)| |
|* 3 | INDEX RANGE SCAN | IDX_TOBJ1_OID | SYS | 101| 1( 0)| |
| 4 | VIEW | | | 101| 1( 0)| |
| 5 | GROUP | | | 101| 1( 0)| |
| 6 | TABLE ACCESS BY INDEX ROWID| TOBJ2 | SYS | 101| 1( 0)| |
|* 7 | INDEX RANGE SCAN | IDX_TOBJ2_OID | SYS | 101| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : access("TOBJ1"."OBJECT_ID" = "TOBJ2"."OBJECT_ID")
3 - Predicate : access("TOBJ1"."OBJECT_ID" = 40)
5 - Group Expression: (40)
7 - Predicate : access("TOBJ2"."OBJECT_ID" = 40)
23 rows fetched.
Note: 在使用join 列谓词时， 尤其是常量谓词，优化器最喜欢这种，直接可以建立等价，#7能看到,优化器做了传递推导（transitive predicate generation）, #5也能看到说明已经做了过滤，而不是“Group Expression:(“TOBJ2″.”OBJECT_ID”)”， 这是典型的Predicate Pushdown。
虽然这里用的是hash join 不是nested join， 是因为2连的估算行数都很小。hash join cost=1. 没有必要NL。
SQL> explain select * from tobj1 left join vobj2 on tobj1.object_id=vobj2.object_id where tobj1.object_name='tobj';
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 1184278721
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN RIGHT OUTER | | | 101| 293( 0)| |
| 2 | JOIN FILTER USE | | | 1000| 150( 0)| |
| 3 | VIEW | | | 1000| 150( 0)| |
| 4 | HASH GROUP | | | 1000| 150( 0)| |
|* 5 | TABLE ACCESS FULL | TOBJ2 | SYS | 100000| 136( 0)| |
|* 6 | JOIN FILTER CREATE | | | 101| 141( 0)| |
|* 7 | TABLE ACCESS FULL | TOBJ1 | SYS | 101| 141( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : access("TOBJ2"."OBJECT_ID" = "TOBJ1"."OBJECT_ID")
4 - Group Expression: ("TOBJ2"."OBJECT_ID")
5 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "TOBJ2"."OBJECT_ID")
6 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "TOBJ1"."OBJECT_ID")
7 - Predicate : filter("TOBJ1"."OBJECT_NAME" = 'tobj')
Note: 崖山的效果更好，对于非Join列谓词时，崖山数据库在这里使用了运行时过滤（Runtime Filter）优化，避免了视图的完全物化。
这里使用的是JOIN FILTER，其实不是 Join Predicate Pushdown, Bloom Filter（运行时过滤）, Tobj2仍然是FULL SCAN， 虽然还是全扫，但因为用了Bloom Filter, 很多行在扫描阶段就被丢弃了。
执行顺序（从下往上）：
1. 第7行：扫描 TOBJ1，过滤 object_name='tobj' → 预估 101 行
2. 第6行：JOIN FILTER CREATE - 用这 101 行的 OBJECT_ID 动态创建一个过滤布隆过滤器
3. 第2行：JOIN FILTER USE - 将过滤器下推到 TOBJ2 的扫描中
4. 第5行：扫描 TOBJ2 时，用过滤器提前过滤掉不符合条件的行
5. 第4行：对过滤后的结果做 GROUP BY
6. 第1行：HASH JOIN 完成最终连接
在请教同事后，其实yashan也可以做Join Predicate Pushdown， 需要增加hint.
SQL> explain select /*+use_nl(tobj1,tobj2)*/ * from tobj1 left join vobj2 on tobj1.object_id=vobj2.object_id where tobj1.object_name='tobj';
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2737420841
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS LEFT OUTER | | | 512| 50( 0)| |
|* 2 | TABLE ACCESS FULL | TOBJ1 | SYS | 1| 25( 0)| |
| 3 | VIEW | | | 512| 24( 0)| |
| 4 | GROUP | | | 512| 24( 0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| TOBJ2 | SYS | 512| 24( 0)| |
|* 6 | INDEX RANGE SCAN | IDX_TOBJ2_OID | SYS | 1024| 3( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Nest Loop Join NDV Expression: ("TOBJ1"."OBJECT_ID")
2 - Predicate : filter("TOBJ1"."OBJECT_NAME" = 'tobj')
4 - Group Expression: ("TOBJ2"."OBJECT_ID")
6 - Predicate : access("TOBJ2"."OBJECT_ID" = "TOBJ1"."OBJECT_ID")
22 rows fetched.
Note:这里已经发生了某种程度的 Join Predicate Pushdown。
最关键一行 #6 – access(“TOBJ2″.”OBJECT_ID”=”TOBJ1″.”OBJECT_ID”), 这里不是HASH JOIN， 而是NL, 如果对于NL， 那就是可以访问tobj2时使用object_id 做为访问条件传递给索引减少扫描范围。或者叫Index Join Predicate Pushdown
和前面的 Bloom Filter（Join fiter） 有什么区别：
Join Filter: 扫描tobj1, 构建bloom filter， 全扫tobj2，过滤，HASH JOIN
hint后的Join Predicate Pushdown： 扫描tobj1 ，得到object_id, 传递给TOBJ2的索引idx_obj2_oid, 使用index 扫描
小结
对于前两篇 PostgreSQL 兼容性测试中涉及的两个典型优化场景，可以总结如下：
1. NOT IN 子查询优化
在能够保证子查询返回列不存在 NULL 值的前提下，达梦数据库和崖山数据库均能够将 NOT IN 子查询转换为 Anti Join 执行，从而避免逐行子查询计算，提高执行效率。
不过，从执行计划来看，达梦数据库在处理 NOT IN 时仍会引入一层全雾化（Full Materialization）过程，增加了额外的执行开销。因此，从兼容性和执行效率角度考虑，仍建议优先使用 NOT EXISTS 替代 NOT IN，以获得更加稳定的执行计划和性能表现。
2. JOIN VIEW 谓词下推优化
在视图关联场景下，当过滤条件作用于 JOIN 关联列时，达梦数据库和崖山数据库均能够实现谓词下推（Predicate Pushdown），将过滤条件传递到视图内部，从而减少参与连接的数据量。
而当过滤条件作用于非 JOIN 列时，两者表现存在差异：
- 达梦数据库未能将该类谓词进一步推进至视图内部，导致视图侧数据仍需较大范围扫描后再参与连接。
- 崖山数据库（YashanDB）默认采用 Join Filter 运行时过滤机制，即利用 Bloom Filter 在连接过程中对数据进行提前过滤，以降低无效数据参与连接的成本。
此外，若希望崖山数据库进一步采用 Join Predicate Pushdown（连接谓词下推） 策略，可通过指定 USE_NL Hint 强制优化器选择 Nested Loop Join。此时，连接条件能够作为访问条件传递至被驱动表，从而实现更深层次的谓词下推和索引访问优化。