PostgreSQL如何实现上拉子链接

这篇文章主要介绍了postgresql如何实现上拉子链接，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

按官方文档的介绍,子链接Sublink代表的是出现在表达式(可能会出现组合运算符)中的子查询,子查询的类型包括:
EXISTS_SUBLINK
语法:EXISTS(SELECT ...)

select * 
from t_dwxx a
where exists (select b.dwbh from t_grxx b where a.dwbh = b.dwbh);

ALL_SUBLINK
语法:(lefthand) op ALL (SELECT ...)

select * 
from t_dwxx a
where dwbh > all (select b.dwbh from t_grxx b);

ANY_SUBLINK
语法:(lefthand) op ANY (SELECT ...)

select * 
from t_dwxx a
where dwbh = any (select b.dwbh from t_grxx b);

ROWCOMPARE_SUBLINK
语法:(lefthand) op (SELECT ...)

select * 
from t_dwxx a
where dwbh > (select max(b.dwbh) from t_grxx b);

EXPR_SUBLINK
语法:(SELECT with single targetlist item ...)

select *,(select max(dwbh) from t_grxx) 
from t_dwxx a;

MULTIEXPR_SUBLINK
语法:(SELECT with multiple targetlist items ...)

ARRAY_SUBLINK
语法:ARRAY(SELECT with single targetlist item ...)

CTE_SUBLINK
语法:
WITH query (never actually part of an expression)

官方说明:

typedef enum SubLinkType
{
  EXISTS_SUBLINK,
  ALL_SUBLINK,
  ANY_SUBLINK,
  ROWCOMPARE_SUBLINK,
  EXPR_SUBLINK,
  MULTIEXPR_SUBLINK,
  ARRAY_SUBLINK,
  CTE_SUBLINK         
} SubLinkType;


typedef struct SubLink
{
  Expr    xpr;
  SubLinkType subLinkType;  
  int     subLinkId;    
  node     *testexpr;   
  List     *operName;   
  Node     *subselect;    
  int     location;   
} SubLink;

二、源码解读

standard_planner函数

 PlannedStmt *
 planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
 {
     PlannedStmt *result;
 
     if (planner_hook)
         result = (*planner_hook) (parse, cursorOptions, boundParams); //钩子函数,可以实现定制化开发
     else
         result = standard_planner(parse, cursorOptions, boundParams);//PG的标准实现
     return result;
 }
 
 
 PlannedStmt *
 standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
 {
     PlannedStmt *result;//最终结果
     PlannerGlobal *glob;//全局优化信息
     double      tuple_fraction;//优化过程中元组的采样率
     PlannerInfo *root;//执行计划的根节点
     RelOptInfo *final_rel;//优化后的Relation信息
     Path       *best_path;//最优路径
     Plan       *top_plan;//顶层计划
     ListCell   *lp,//临时变量
                *lr;
 
     
     //初始化全局优化信息 
     glob = makeNode(PlannerGlobal);
 
     glob->boundParams = boundParams;
     glob->subplans = NIL;
     glob->subroots = NIL;
     glob->rewindPlanIDs = NULL;
     glob->finalrtable = NIL;
     glob->finalrowmarks = NIL;
     glob->resultRelations = NIL;
     glob->nonleafResultRelations = NIL;
     glob->rootResultRelations = NIL;
     glob->relationOids = NIL;
     glob->invalItems = NIL;
     glob->paramExecTypes = NIL;
     glob->lastPHId = 0;
     glob->lastRowMarkId = 0;
     glob->lastPlanNodeId = 0;
     glob->transientPlan = false;
     glob->dependsOnRole = false;
 
     
     //判断是否可以使用并行模式
     //并行是很大的一个话题,有待以后再谈 
     if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
         IsUnderPostmaster &&
         parse->commandType == CMD_SELECT &&
         !parse->hasModifyinGCTE &&
         max_parallel_workers_per_gather > 0 &&
         !IsParallelWorker() &&
         !IsolationIsSerializable())
     {
         
         glob->maxParallelHazard = max_parallel_hazard(parse);
         glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
     }
     else
     {
         
         glob->maxParallelHazard = PROPARALLEL_UNSAFE;
         glob->parallelModeOK = false;
     }
 
     
     glob->parallelModeNeeded = glob->parallelModeOK &&
         (force_parallel_mode != FORCE_PARALLEL_OFF);
 
     
     if (cursorOptions & CURSOR_OPT_FAST_PLAN)//fast-start plan?
     {
         
         tuple_fraction = cursor_tuple_fraction;
 
         
         if (tuple_fraction >= 1.0)
             tuple_fraction = 0.0;
         else if (tuple_fraction <= 0.0)
             tuple_fraction = 1e-10;
     }
     else
     {
         
         tuple_fraction = 0.0;
     }
     //以上:set up for recursive handling of subqueries,为子查询配置处理器(递归方式)
     
     root = subquery_planner(glob, parse, NULL,
                             false, tuple_fraction);//进入子查询优化器
 
     
     final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL);//获得最上层的优化后的Relation
     best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);//获得最佳路径
 
     top_plan = create_plan(root, best_path);//创建计划
 
     
     if (cursorOptions & CURSOR_OPT_SCROLL)
     {
         if (!ExecSupportsBackwardScan(top_plan))
             top_plan = materialize_finished_plan(top_plan);//如果是Scroll游标,在前台执行,那么物化之(数据不能长期占用内存,但又要满足Scroll的要求,需要物化至磁盘中)
     }
 
     
     if (force_parallel_mode != FORCE_PARALLEL_OFF && top_plan->parallel_safe)//并行执行,添加Gather节点
     {
         Gather     *gather = makeNode(Gather);
 
         
         gather->plan.initPlan = top_plan->initPlan;
         top_plan->initPlan = NIL;
 
         gather->plan.targetlist = top_plan->targetlist;
         gather->plan.qual = NIL;
         gather->plan.lefttree = top_plan;
         gather->plan.righttree = NULL;
         gather->num_workers = 1;
         gather->single_copy = true;
         gather->invisible = (force_parallel_mode == FORCE_PARALLEL_REGRESS);
 
         
         gather->rescan_param = -1;
 
         
         gather->plan.startup_cost = top_plan->startup_cost +
             parallel_setup_cost;
         gather->plan.total_cost = top_plan->total_cost +
             parallel_setup_cost + parallel_tuple_cost * top_plan->plan_rows;
         gather->plan.plan_rows = top_plan->plan_rows;
         gather->plan.plan_width = top_plan->plan_width;
         gather->plan.parallel_aware = false;
         gather->plan.parallel_safe = false;
 
         
         root->glob->parallelModeNeeded = true;
 
         top_plan = &gather->plan;
     }
 
     
     if (glob->paramExecTypes != NIL)
     {
         Assert(list_length(glob->subplans) == list_length(glob->subroots));
         forboth(lp, glob->subplans, lr, glob->subroots)
         {
             Plan       *subplan = (Plan *) lfirst(lp);
             PlannerInfo *subroot = lfirst_node(PlannerInfo, lr);
 
             SS_finalize_plan(subroot, subplan);
         }
         SS_finalize_plan(root, top_plan);
     }
 
     
     Assert(glob->finalrtable == NIL);
     Assert(glob->finalrowmarks == NIL);
     Assert(glob->resultRelations == NIL);
     Assert(glob->nonleafResultRelations == NIL);
     Assert(glob->rootResultRelations == NIL);
     top_plan = set_plan_references(root, top_plan);
     
     Assert(list_length(glob->subplans) == list_length(glob->subroots));
     forboth(lp, glob->subplans, lr, glob->subroots)
     {
         Plan       *subplan = (Plan *) lfirst(lp);
         PlannerInfo *subroot = lfirst_node(PlannerInfo, lr);
 
         lfirst(lp) = set_plan_references(subroot, subplan);
     }
 
     
     result = makeNode(PlannedStmt);
 
     result->commandType = parse->commandType;
     result->queryId = parse->queryId;
     result->hasReturning = (parse->returningList != NIL);
     result->hasModifyingCTE = parse->hasModifyingCTE;
     result->canSetTag = parse->canSetTag;
     result->transientPlan = glob->transientPlan;
     result->dependsOnRole = glob->dependsOnRole;
     result->parallelModeNeeded = glob->parallelModeNeeded;
     result->planTree = top_plan;
     result->rtable = glob->finalrtable;
     result->resultRelations = glob->resultRelations;
     result->nonleafResultRelations = glob->nonleafResultRelations;
     result->rootResultRelations = glob->rootResultRelations;
     result->subplans = glob->subplans;
     result->rewindPlanIDs = glob->rewindPlanIDs;
     result->rowMarks = glob->finalrowmarks;
     result->relationOids = glob->relationOids;
     result->invalItems = glob->invalItems;
     result->paramExecTypes = glob->paramExecTypes;
     
     result->utilityStmt = parse->utilityStmt;
     result->stmt_location = parse->stmt_location;
     result->stmt_len = parse->stmt_len;
 
     result->jitFlags = PGJIT_NONE;
     if (jit_enabled && jit_above_cost >= 0 &&
         top_plan->total_cost > jit_above_cost)
     {
         result->jitFlags |= PGJIT_PERFORM;
 
         
         if (jit_optimize_above_cost >= 0 &&
             top_plan->total_cost > jit_optimize_above_cost)
             result->jitFlags |= PGJIT_OPT3;
         if (jit_inline_above_cost >= 0 &&
             top_plan->total_cost > jit_inline_above_cost)
             result->jitFlags |= PGJIT_INLINE;
 
         
         if (jit_expressions)
             result->jitFlags |= PGJIT_EXPR;
         if (jit_tuple_deforming)
             result->jitFlags |= PGJIT_DEFORM;
     }
 
     return result;
 }

subquery_planner

 

 PlannerInfo *
 subquery_planner(PlannerGlobal *glob, Query *parse,
                  PlannerInfo *parent_root,
                  bool hasRecursion, double tuple_fraction)
 {
     PlannerInfo *root;//返回值
     List       *newWithCheckOptions;//
     List       *newHaving;//Having子句
     bool        hasOuterJoins;//是否存在Outer Join?
     RelOptInfo *final_rel;//
     ListCell   *l;//临时变量
 
     
     root = makeNode(PlannerInfo);//构造返回值
     root->parse = parse;
     root->glob = glob;
     root->query_level = parent_root ? parent_root->query_level + 1 : 1;
     root->parent_root = parent_root;
     root->plan_params = NIL;
     root->outer_params = NULL;
     root->planner_cxt = CurrentMemoryContext;
     root->init_plans = NIL;
     root->cte_plan_ids = NIL;
     root->multiexpr_params = NIL;
     root->eq_classes = NIL;
     root->append_rel_list = NIL;
     root->rowMarks = NIL;
     memset(root->upper_rels, 0, sizeof(root->upper_rels));
     memset(root->upper_targets, 0, sizeof(root->upper_targets));
     root->processed_tlist = NIL;
     root->grouping_map = NULL;
     root->minmax_aggs = NIL;
     root->qual_security_level = 0;
     root->inhTargetKind = INHKIND_NONE;
     root->hasRecursion = hasRecursion;
     if (hasRecursion)
         root->wt_param_id = SS_assign_special_param(root);
     else
         root->wt_param_id = -1;
     root->non_recursive_path = NULL;
     root->partColsUpdated = false;
 
     
     if (parse->cteList)
         SS_process_ctes(root);//处理With 语句
 
     
     if (parse->hasSubLinks)
         pull_up_sublinks(root); //上拉子链接
 
     //其他内容...

     return root;
 }

pull_up_sublinks

下一小节介绍

三、基础信息

数据结构/宏定义
1、cursorOptions

 
 #define CURSOR_OPT_BINARY       0x0001  
 #define CURSOR_OPT_SCROLL       0x0002  
 #define CURSOR_OPT_NO_SCROLL    0x0004  
 #define CURSOR_OPT_INSENSITIVE  0x0008  
 #define CURSOR_OPT_HOLD         0x0010  
 
 #define CURSOR_OPT_FAST_PLAN    0x0020  
 #define CURSOR_OPT_GENERIC_PLAN 0x0040  
 #define CURSOR_OPT_CUSTOM_PLAN  0x0080  

2、Relids

 
 typedef Bitmapset *Relids;
 
 #define BITS_PER_BITMAPWord 32
 typedef uint32 bitmapword;      
 typedef int32 signedbitmapword; 
 
 typedef struct Bitmapset
 {
     int         nwords;         
     bitmapword  words[FLEXIBLE_ARRAY_MEMBER];   
 } Bitmapset;

四、跟踪分析

测试脚本:

testdb=# explain select * 
from t_dwxx a
where dwbh > all (select b.dwbh from t_grxx b);
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Seq Scan on t_dwxx a  (cost=0.00..1498.00 rows=80 width=474)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Materialize  (cost=0.00..17.35 rows=490 width=38)
           ->  Seq Scan on t_grxx b  (cost=0.00..14.90 rows=490 width=38)
(5 rows)

启动gdb跟踪:

(gdb) b pull_up_sublinks
Breakpoint 1 at 0x77cbc6: file prepjointree.c, line 157.
(gdb) c
Continuing.

Breakpoint 1, pull_up_sublinks (root=0x126fd48) at prepjointree.c:157
157                          (Node *) root->parse->jointree,
(gdb) 
#查看输入参数
(gdb) p *root
$1 = {type = T_PlannerInfo, parse = 0x11b43D8, glob = 0x11b4e00, query_level = 1, parent_root = 0x0, plan_params = 0x0, 
  outer_params = 0x0, simple_rel_array = 0x0, simple_rel_array_size = 0, simple_rte_array = 0x0, all_baserels = 0x0, 
  nullable_baserels = 0x0, join_rel_list = 0x0, join_rel_hash = 0x0, join_rel_level = 0x0, join_cur_level = 0, 
  init_plans = 0x0, cte_plan_ids = 0x0, multiexpr_params = 0x0, eq_classes = 0x0, canon_pathkeys = 0x0, 
  left_join_clauses = 0x0, right_join_clauses = 0x0, full_join_clauses = 0x0, join_info_list = 0x0, append_rel_list = 0x0, 
  rowMarks = 0x0, placeholder_list = 0x0, fkey_list = 0x0, query_pathkeys = 0x0, group_pathkeys = 0x0, 
  window_pathkeys = 0x0, distinct_pathkeys = 0x0, sort_pathkeys = 0x0, part_schemes = 0x0, initial_rels = 0x0, 
  upper_rels = {0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, upper_targets = {0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, 
  processed_tlist = 0x0, grouping_map = 0x0, minmax_aggs = 0x0, planner_cxt = 0x11b2e90, total_table_pages = 0, 
  tuple_fraction = 0, limit_tuples = 0, qual_security_level = 0, inhTargetKind = INHKIND_NONE, hasJoinRTEs = false, 
  hasLateralRTEs = false, hasDeletedRTEs = false, hasHavingQual = false, hasPseudoConstantQuals = false, 
  hasRecursion = false, wt_param_id = -1, non_recursive_path = 0x0, curOuterRels = 0x0, curOuterParams = 0x0, 
  join_search_private = 0x0, partColsUpdated = false}
#jointree的类型为FromExpr  
(gdb) p *root->parse->jointree
$3 = {type = T_FromExpr, fromlist = 0x11b4870, quals = 0x11b40e8}
(gdb) n
156   jtnode = pull_up_sublinks_jointree_recurse(root,
#进入pull_up_sublinks_jointree_recurse函数
(gdb) step
pull_up_sublinks_jointree_recurse (root=0x126fd48, jtnode=0x1282ea0, relids=0x7ffe830ab9b0) at prepjointree.c:180
180   if (jtnode == NULL)
#查看参数
#1.root与pull_up_sublinks函数的输入一致
#2.jtnode类型为FromExpr
#3.relids
(gdb) p *jtnode
$7 = {type = T_FromExpr}
(gdb) p *(FromExpr *)jtnode
$8 = {type = T_FromExpr, fromlist = 0x11b4870, quals = 0x11b40e8}
#FromExpr->fromlist中的的元素类型为RangeTblRef
(gdb) p *((FromExpr *)jtnode)->fromlist
$9 = {type = T_List, length = 1, head = 0x11b4850, tail = 0x11b4850}
(gdb) p *(Node *)((FromExpr *)jtnode)->fromlist->head->data.ptr_value
$10 = {type = T_RangeTblRef}
...
#进入FromExpr分支
191   else if (IsA(jtnode, FromExpr))
...
201     foreach(l, f->fromlist)
(gdb) 
207                              lfirst(l),
(gdb) p *l
$11 = {data = {ptr_value = 0x11b4838, int_value = 18565176, oid_value = 18565176}, next = 0x0}
(gdb) p *(RangeTblRef *)l->data.ptr_value
$13 = {type = T_RangeTblRef, rtindex = 1}
#递归调用pull_up_sublinks_jointree_recurse
(gdb) n
206       newchild = pull_up_sublinks_jointree_recurse(root,
(gdb) step
pull_up_sublinks_jointree_recurse (root=0x126fd48, jtnode=0x11b4838, relids=0x7ffe830ab940) at prepjointree.c:180
180   if (jtnode == NULL)
#输入参数
#1.root与pull_up_sublinks函数的输入一致
#2.jtnode类型为RangeTblRef
#3.relids
#进入RangeTblRef分支
(gdb) n
184   else if (IsA(jtnode, RangeTblRef))
(gdb) n
186     int     varno = ((RangeTblRef *) jtnode)->rtindex;
(gdb) 
188     *relids = bms_make_singleton(varno);
(gdb) 
312   return jtnode;
(gdb) p *relids
$16 = (Relids) 0x12704c8
...
(gdb) p *newchild
$19 = {type = T_RangeTblRef}
(gdb) n
210       frelids = bms_join(frelids, childrelids);
(gdb) p *frelids
$25 = {nwords = 1, words = 0x12704cc}
(gdb) p *frelids->words
$26 = 2
...
#进入pull_up_sublinks_qual_recurse
(gdb) n
217     newf->quals = pull_up_sublinks_qual_recurse(root, f->quals,
(gdb) step
pull_up_sublinks_qual_recurse (root=0x126fd48, node=0x11b40e8, jtlink1=0x7ffe830ab948, available_rels1=0x12704c8, 
    jtlink2=0x0, available_rels2=0x0) at prepjointree.c:335
335   if (node == NULL)
#输入参数
#1.root与上述无异
#2.node
(gdb) p *node
$35 = {type = T_SubLink}
(gdb) p *(SubLink *)node
$36 = {xpr = {type = T_SubLink}, subLinkType = ALL_SUBLINK, subLinkId = 0, testexpr = 0x1282e00, operName = 0x11b3cf8, 
  subselect = 0x1282578, location = 35}
#3.jtlink1,指针数组
(gdb) p *(RangeTblRef *)((FromExpr *)jtlink1[0])->fromlist->head->data->ptr_value
$45 = {type = T_RangeTblRef, rtindex = 1}
#4.available_rels1,可用的Relids
(gdb) p *available_rels1
$47 = {nwords = 1, words = 0x12704cc}
(gdb) p *available_rels1->words
$48 = 2
#5/6,NULL值
(gdb) n
337   if (IsA(node, SubLink))
(gdb) 
339     SubLink    *sublink = (SubLink *) node;
(gdb) 
344     if (sublink->subLinkType == ANY_SUBLINK)
(gdb) 
398     else if (sublink->subLinkType == EXISTS_SUBLINK)
(gdb) p sublink->subLinkType
$49 = ALL_SUBLINK
#非ANY/EXISTS链接,退出
(gdb) n
453     return node;
(gdb) 
#回到pull_up_sublinks_jointree_recurse
(gdb) 
pull_up_sublinks_jointree_recurse (root=0x126fd48, jtnode=0x1282ea0, relids=0x7ffe830ab9b0) at prepjointree.c:230
230     *relids = frelids;
(gdb) 
231     jtnode = jtlink;
(gdb) 
312   return jtnode;
#返回的jtnode为RangeTblRef
(gdb) p *(RangeTblRef *)((FromExpr *)jtnode)->fromlist->head->data.ptr_value
$55 = {type = T_RangeTblRef, rtindex = 1}
(gdb) n
313 }
(gdb) 
pull_up_sublinks (root=0x126fd48) at prepjointree.c:164
164   if (IsA(jtnode, FromExpr))
(gdb) 
165     root->parse->jointree = (FromExpr *) jtnode;
(gdb) p *root->parse->jointree
$56 = {type = T_FromExpr, fromlist = 0x11b4870, quals = 0x11b40e8}
(gdb) p *root->parse->jointree->fromlist
$57 = {type = T_List, length = 1, head = 0x11b4850, tail = 0x11b4850}
(gdb) n
168 }
(gdb) 
subquery_planner (glob=0x11b4e00, parse=0x11b43d8, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at planner.c:656
656   inline_set_returning_functions(root);
(gdb) c
Continuing.
#ANY类型并没有做上拉操作

感谢你能够认真阅读完这篇文章，希望小编分享的“Postgresql如何实现上拉子链接”这篇文章对大家有帮助，同时也希望大家多多支持编程网，关注编程网数据库频道，更多相关知识等着你来学习!