适用范围

MogDB中SELECT与FROM之间的标量子查询优化

问题概述

参数rewrite_rule默认为magicset，将参数rewrite_rule设置为intargetlist 优化器可以自动对标量子查询进行改写 注意：并不是所有的标量子查询都能进行自动改写

示例一：标量子查询中有聚合函数，可以自动改写

orcl=> show rewrite_rule;
 rewrite_rule 
--------------
 magicset
(1 row)

orcl=> explain analyze select d.deptno, (select count(*) from emp e where d.deptno = e.deptno) cnt from dept d;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on dept d  (cost=0.00..9805.68 rows=619 width=12) (actual time=0.029..0.045 rows=4 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=15.81..15.82 rows=1 width=8) (actual time=0.032..0.032 rows=4 loops=4)
           ->  Seq Scan on emp e  (cost=0.00..15.80 rows=2 width=0) (actual time=0.019..0.026 rows=19 loops=4)
                 Filter: (d.deptno = deptno)
                 Rows Removed by Filter: 73
 Total runtime: 0.146 ms
(7 rows)

orcl=> set rewrite_rule=intargetlist;
SET