原作者：罗炳森

• 适用范围
• 问题概述
• 问题原因
• 解决方案

适用范围

DBA,数据库开发工程师

问题概述

MogDB中in相关子查询性能差

问题原因

MogDB中in相关子查询只能走filter，不能走HASH JOIN

解决方案

改写SQL为exists

原始写法：

select count(*)
  from a
 where object_name in
       (select object_name from b where a.object_id > b.object_id);

执行计划：

orcl=> explain select count(*)
orcl->   from a
orcl->  where object_name in
orcl->        (select object_name from b where a.object_id > b.object_id);
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Aggregate  (cost=1305949638539.40..1305949638539.41 rows=1 width=8)
   ->  Seq Scan on a  (cost=0.00..1305949624626.56 rows=5565137 width=0)
         Filter: (SubPlan 1)
         SubPlan 1
           ->  Seq Scan on b  (cost=0.00..225391.35 rows=3709929 width=24)
                 Filter: (a.object_id > object_id)


改写后的写法：

select 
 count(*)
  from a
 where exists (select null
          from b
         where a.object_name = b.object_name
           and a.object_id > b.object_id);

执行计划：

orcl=> explain select 
orcl->  count(*)
orcl->   from a
orcl->  where exists (select null
orcl(>           from b
orcl(>          where a.object_name = b.object_name
orcl(>            and a.object_id > b.object_id);
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Aggregate  (cost=1525889.30..1525889.31 rows=1 width=8)
   ->  Hash Semi Join  (cost=499724.23..1516614.07 rows=3710091 width=0)
         Hash Cond: ((a.object_name)::text = (b.object_name)::text)
         Join Filter: (a.object_id > b.object_id)
         ->  Seq Scan on a  (cost=0.00..197572.74 rows=11130274 width=30)
         ->  Hash  (cost=197566.88..197566.88 rows=11129788 width=30)
               ->  Seq Scan on b  (cost=0.00..197566.88 rows=11129788 width=30)