云数据库 GAUSSDB-案例：改写SQL消除in-clause:优化说明

时间：2024-11-02 18:48:18

云数据库 GAUSSDB

优化说明

测试发现由于两表结果集过大，导致nestloop耗时过长，超过一小时未返回结果，因此性能优化的关键是消除nestloop，让join走更高效的hashjoin。从语义等价的角度消除any-clause，SQL改写如下：

  
   
     
     
       select
ls_pid_cusr1,COALESCE(max(round(ym/365)),0)
from
(
         (
                   SELECT 
                            ls_pid_cusr1,(current_date-bthdate) as ym
                   FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2
                   WHERE t1.ls_pid_cusr1 = t2.id and t1.ls_pid_cusr1 != t2.id15
         )
         union all
         (
                   SELECT 
                            ls_pid_cusr1,(current_date-bthdate) as ym
                   FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2
                   WHERE t1.ls_pid_cusr1 = id15
         )
)
GROUP BY ls_pid_cusr1;

      

    

  
 