优化器的优化规则 rule based optimizer

1、exists
exists(select sum(i) from X) --> True
not exists(select sum(i) from X) --> False

2、bool =
true = (a > b) ==> a > b
false = (a > b) ==> a <= b
(a > b) = true ==> a > b
(a > b) = false ==> a <= b

3、bool 转化
not or => and
not and => or
and (and) => and
or (or) => or
not exists => exists
not not exists => exists

4、等价类 filter 下推
{cr1,cr2,cr3} ==> cr1=cr2 and cr1=cr3 and cr2=cr3
a=1 and {a,b} ==> pred b=1

5、inner join 的 qual 可以下推至 join condition
a join b on a.i=b.i where a.i=6 ==> a.i=6 与 b.i=6

6、执行 unnest 并去重

7、找出在所有or中涉及的列和 and，把它们组成这个列的限制条件
x.a=1 or (x.a=2 and x.b=3) or (x.a=4 and x.c = 0) ==> x.a =1 or 2 or 4

8、无用列去除
找到 project 、sequence project（window）、groupbyagg，
为 project 和 groupby 去除无用的列。
比如 select a,c from x group by a,b,c 可以将 group by b 去掉

9、left join 转 n array join
将 on 条件的值合在一起，组成 array

10、发现一个子查询最多返回 0 行，可以直接转为 const 空数据表

11、 简化子查询中输出为空的情况
SELECT (SELECT foo.b from bar) + 1 FROM foo
当 bar 为 空时，生成 dummy 的 输出（因为实际输出是空，而非foo.b的值，也非不输出）

12、比较条件重排
1 = a => a = 1

13、const fold
1 + 2 ==> 3

14、union 的一个 child 为空时，可以移除一个 child

15、bool 比较转 array 比较
x = 1 OR x = 2 => x IN (1,2)

16、转化 qual 条件中的子查询
select i from s where i in (select s.i +1 from s2) ==> select i from s where s.i=s.i+1 and exists (select * from s2)

17、group by case when 时可将string 转为 int
select count(1) from tbl group by case when a > 1 then ‘1111’ when a > ‘0’ then ‘2222’ else ‘3333’ end;
==> select count(1) from tbl group by case when a > 1 then 0 when a > ‘0’ then 0 else 2 end;

18、left join 转 inner join
select * from s join (s2 left join s3 on s2.j=1) k on k.i=s.i;
由于s与k 是 inner join且 k中的s2 is null 时 s2.j=1 是 not false （或null）所以 s2 与 s3 可从left join转 inner join

19、范围转 bool
any null = false
all null = true

20、去除重复条件
f in (select f from t) and f in (select f from t, t1 where t.f=t1.f1)
可以只保留右一个

21、min max distinct 可以 将 distinct 去掉
select min(distinct a) from x;

22、去除子查询中 group by 的外部表（当除掉导致project list 为空，则将groupbylist生成project节点）
select a from t where c in (select t.b from s group by t.b)
select a from t where c in (select s.j from s group by t.b, s.j)
select a from t where c in (select count(s.j) from s group by s.i, t.b)
t.b可以移除

23、window 函数中 order by 指向外部表时，可以移除
select rank() over(x.a,x.b partition by x.a order by y.c), y.c from x, y;

24、移除 any all
a = ANY (select sum(i) from X) ==> a = (select sum(i) from X)
a <> ALL (select 1) ==> a <> (select 1)

25、常量表解开
select (select * from (values (1))b);与select (select i from (values (1,2))b(i,j));转为 select 1 ;