convert_in_exists

用在子查询中，确定子查询使用 [NOT] IN 与 [NOT] EXISTS 间互相改写规则。

举例:

--  清理环境
drop table t1 cascade;
drop table t2 cascade;
drop table t3 cascade;

--  关闭 [NOT] IN 与 [NOT] EXISTS 间等价改写参数
set ENABLE_IN_EXISTS_MUTUAL_CONVERT = FALSE;
--  创建表
create table t1(a int);
create table t2(a int);
create table t3(a int);
insert into t1 select random() from generate_series(1, 1000);
insert into t2 select random() from generate_series(1, 10);
insert into t3 select random() from generate_series(1, 10000);
analyze t1;
analyze t2;
analyze t3;


--  使用convert_in_exists
explain select * from t1 left join t2 on exists (select /*+ convert_in_exists */ 0 from t3 where t1.a = t3.a);
QUERY PLAN(text)      
----------------------
Nested Loop(Left Join)  (cost=7.11..1807850.11 rows=5000 width=8)
  Join Filter: (hashed subplan)
  ->  Seq Scan on T1  (cost=0.00..18.00 rows=1000 width=4)
  ->  Materialize  (cost=7.11..7.21 rows=10 width=4)
        ->  Seq Scan on T2  (cost=0.00..7.10 rows=10 width=4)
  SubPlan
    ->  Seq Scan on T3  (cost=0.00..149.81 rows=12381 width=4)
总数目：7


--  不使用convert_in_exists
explain select * from t1 left join t2 on exists (select 0 from t3 where t1.a = t3.a);
QUERY PLAN(text)      
----------------------
Nested Loop(Left Join)  (cost=7.11..1807850.11 rows=5000 width=8)
  Join Filter: (subplan)
  ->  Seq Scan on T1  (cost=0.00..18.00 rows=1000 width=4)
  ->  Materialize  (cost=7.11..7.21 rows=10 width=4)
        ->  Seq Scan on T2  (cost=0.00..7.10 rows=10 width=4)
  SubPlan
    ->  Seq Scan on T3  (cost=0.00..180.76 rows=1 width=0)
          Filter: ($0 = A)
总数目：8

--  删除表
drop table t1 cascade;
drop table t2 cascade;
drop table t3 cascade;

--  还原连接条件下推参数
reset ENABLE_IN_EXISTS_MUTUAL_CONVERT;