force_convert_in_exists

用在子查询中，强制使用 [NOT] IN 与 [NOT] EXISTS 间改写后的计划。

举例:

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

--  创建表
create table t1(a int);
create table t2(a int);
create table t3(a int);
insert into t1 select random() from generate_series(1, 10);
insert into t2 select random() from generate_series(1, 10);
insert into t3 select random() from generate_series(1, 100);
analyze t1;
analyze t2;
analyze t3;


--  使用force_convert_in_exists
explain select * from t1 left join t2 on t1.a in (select /*+ force_convert_in_exists */ a from t3);
QUERY PLAN(text)      
----------------------
Nested Loop(Left Join)  (cost=7.11..841.21 rows=50 width=8)
  Join Filter: (subplan)
  ->  Seq Scan on T1  (cost=0.00..7.10 rows=10 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..8.25 rows=1 width=4)
          Filter: ($1 = A)
总数目：8


--  不使用force_convert_in_exists, 则会根据代价模型选择代价低的计划
explain select * from t1 left join t2 on t1.a in (select a from t3);
QUERY PLAN(text)      
----------------------
Nested Loop(Left Join)  (cost=7.11..16.71 rows=50 width=8)
  Join Filter: (hashed subplan)
  ->  Seq Scan on T1  (cost=0.00..7.10 rows=10 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..8.00 rows=100 width=4)
总数目：7

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