PostgreSQL中remove_useless_joins的实现逻辑是怎样的

这篇文章主要介绍“PostgreSQL中remove_useless_joins的实现逻辑是怎样的”，在日常操作中，相信很多人在PostgreSQL中remove_useless_joins的实现逻辑是怎样的问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中remove_useless_joins的实现逻辑是怎样的”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

query_planner代码片段:

     //...
 
     
     joinlist = remove_useless_joins(root, joinlist);//清除无用的外连接
 
     
     reduce_unique_semijoins(root);//消除半连接
 
     
     add_placeholders_to_base_rels(root);//在"base rels"中添加PH
 
      //...

一、数据结构

PlaceHolderVar
上一小节已介绍过PHInfo

 
 
 typedef struct PlaceHolderVar
 {
     Expr        xpr;
     Expr       *phexpr;         
     Relids      phrels;         
     Index       phid;           
     Index       phlevelsup;     
 } PlaceHolderVar;

SpecialJoinInfo

 
 
 typedef struct SpecialJoinInfo
 {
     NodeTag     type;
     Relids      min_lefthand;   
     Relids      min_righthand;  
     Relids      syn_lefthand;   
     Relids      syn_righthand;  
     JoinType    jointype;       
     bool        lhs_strict;     
     bool        delay_upper_joins;  
     
     bool        semi_can_btree; 
     bool        semi_can_hash;  
     List       *semi_operators; 
     List       *semi_rhs_exprs; 
 } SpecialJoinInfo;

二、源码解读

remove_useless_joins
清除无用的连接,比如以下的SQL语句:

select t1.dwbh 
from t_grxx t1 left join t_dwxx t2 on t1.dwbh = t2.dwbh;

左连接,而且t_dwxx.dwbh唯一,这样的连接是不需要的连接,直接查询t_grxx即可.
从执行计划来看,PG只对t_grxx进行扫描:

testdb=# explain verbose select t1.dwbh from t_grxx t1 left join t_dwxx t2 on t1.dwbh = t2.dwbh;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Seq Scan on public.t_grxx t1  (cost=0.00..14.00 rows=400 width=38)
   Output: t1.dwbh
(2 rows)

源代码如下:

 
 List *
 remove_useless_joins(PlannerInfo *root, List *joinlist)
 {
     ListCell   *lc;
 
     
 restart:
     foreach(lc, root->join_info_list)//遍历连接信息链表
     {
         SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(lc);
         int         innerrelid;
         int         nremoved;
 
         
         if (!join_is_removable(root, sjinfo))//判断是否可以清除连接
             continue;
 
         
         innerrelid = bms_singleton_member(sjinfo->min_righthand);
 
         remove_rel_from_query(root, innerrelid,
                               bms_union(sjinfo->min_lefthand,
                                         sjinfo->min_righthand));//从查询中删除相应的Rel
 
         
         nremoved = 0;
         joinlist = remove_rel_from_joinlist(joinlist, innerrelid, &nremoved);
         if (nremoved != 1)
             elog(ERROR, "failed to find relation %d in joinlist", innerrelid);
 
         
         //更新连接链表信息 
         root->join_info_list = list_delete_ptr(root->join_info_list, sjinfo);
 
         
         goto restart;
     }
 
     return joinlist;
 }

reduce_unique_semijoins
把可以简化的半连接转化为内连接.
比如以下的SQL语句:

select t1.*
from t_grxx t1 
where dwbh IN (select t2.dwbh from t_dwxx t2);

由于子查询"select t2.dwbh from t_dwxx t2"的dwbh是PK,子查询提升后,t_grxx的dwbh只对应t_dwxx唯一的一条记录,因此可以把半连接转换为内连接,执行计划如下:

testdb=# explain verbose select t1.*
from t_grxx t1 
where dwbh IN (select t2.dwbh from t_dwxx t2);
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Hash Join  (cost=1.07..20.10 rows=6 width=176)
   Output: t1.dwbh, t1.grbh, t1.xm, t1.xb, t1.nl
   Inner Unique: true
   Hash Cond: ((t1.dwbh)::text = (t2.dwbh)::text)
   ->  Seq Scan on public.t_grxx t1  (cost=0.00..14.00 rows=400 width=176)
         Output: t1.dwbh, t1.grbh, t1.xm, t1.xb, t1.nl
   ->  Hash  (cost=1.03..1.03 rows=3 width=38)
         Output: t2.dwbh
         ->  Seq Scan on public.t_dwxx t2  (cost=0.00..1.03 rows=3 width=38)
               Output: t2.dwbh
(10 rows)

跟踪分析:

(gdb) n
199   reduce_unique_semijoins(root);
(gdb) step
reduce_unique_semijoins (root=0x1702968) at analyzejoins.c:520
520   for (lc = list_head(root->join_info_list); lc != NULL; lc = next)
(gdb) n
522     SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(lc);
(gdb)

查看SpecialJoinInfo内存结构:

528     next = lnext(lc);
(gdb) p *sjinfo
$1 = {type = T_SpecialJoinInfo, min_lefthand = 0x1749818, min_righthand = 0x1749830, syn_lefthand = 0x1749570, 
  syn_righthand = 0x17495d0, jointype = JOIN_SEMI, lhs_strict = true, delay_upper_joins = false, semi_can_btree = true, 
  semi_can_hash = true, semi_operators = 0x17496c8, semi_rhs_exprs = 0x17497b8}

内表(innerrel,即t_dwxx)如支持唯一性,则可以考虑把半连接转换为内连接

550     if (!rel_supports_distinctness(root, innerrel))
...
575     root->join_info_list = list_delete_ptr(root->join_info_list, sjinfo);
...

源代码如下:

 
 void
 reduce_unique_semijoins(PlannerInfo *root)
 {
     ListCell   *lc;
     ListCell   *next;
 
     
     for (lc = list_head(root->join_info_list); lc != NULL; lc = next)
     {
         SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(lc);//特殊连接信息,先前通过deconstruct函数生成
         int         innerrelid;
         RelOptInfo *innerrel;
         Relids      joinrelids;
         List       *restrictlist;
 
         next = lnext(lc);
 
         
         if (sjinfo->jointype != JOIN_SEMI ||
             sjinfo->delay_upper_joins)
             continue;
 
         if (!bms_get_singleton_member(sjinfo->min_righthand, &innerrelid))
             continue;
 
         innerrel = find_base_rel(root, innerrelid);
 
         
         if (!rel_supports_distinctness(root, innerrel))
             continue;
 
         
         joinrelids = bms_union(sjinfo->min_lefthand, sjinfo->min_righthand);
 
         
         restrictlist =
             list_concat(generate_join_implied_equalities(root,
                                                          joinrelids,
                                                          sjinfo->min_lefthand,
                                                          innerrel),
                         innerrel->joininfo);
 
         
         if (!innerrel_is_unique(root,
                                 joinrelids, sjinfo->min_lefthand, innerrel,
                                 JOIN_SEMI, restrictlist, true))
             continue;
 
         
         root->join_info_list = list_delete_ptr(root->join_info_list, sjinfo);//删除特殊连接信息
     }
 }

add_placeholders_to_base_rels
把PHV分发到base rels中,代码较为简单

 
 void
 add_placeholders_to_base_rels(PlannerInfo *root)
 {
     ListCell   *lc;
 
     foreach(lc, root->placeholder_list)//遍历PH链表
     {
         PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(lc);
         Relids      eval_at = phinfo->ph_eval_at;
         int         varno;
 
         if (bms_get_singleton_member(eval_at, &varno) &&
             bms_nonempty_difference(phinfo->ph_needed, eval_at))//添加到需要的RelOptInfo中
         {
             RelOptInfo *rel = find_base_rel(root, varno);
 
             rel->reltarget->exprs = lappend(rel->reltarget->exprs,
                                             copyObject(phinfo->ph_var));
             
         }
     }
 }

到此，关于“PostgreSQL中remove_useless_joins的实现逻辑是怎样的”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！