文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

PostgreSQL 源码解读(79)- 查询语句#64(create_plan函数#3-Se...

2024-04-02 19:55

关注

本节介绍了创建计划create_plan函数中扫描计划的实现过程,主要的逻辑在函数create_scan_plan中实现。

一、数据结构

Plan
所有计划节点通过将Plan结构作为第一个字段从Plan结构“派生”。这确保了在将节点转换为计划节点时,一切都能正常工作。(在执行器中以通用方式传递时,节点指针经常被转换为Plan *)


typedef struct Plan
{
    NodeTag     type;//节点类型

    
    Cost        startup_cost;   
    Cost        total_cost;     

    
    double      plan_rows;      
    int         plan_width;     

    
    bool        parallel_aware; 
    bool        parallel_safe;  

    
    int         plan_node_id;   
    List       *targetlist;     
    List       *qual;           
    struct Plan *lefttree;      
    struct Plan *righttree;
    List       *initPlan;       

    
    Bitmapset  *extParam;
    Bitmapset  *allParam;
} Plan;

二、源码解读

create_scan_plan函数创建Scan Plan.扫描可以分为顺序扫描(全表扫描)/索引扫描/索引快速扫描/TID扫描等多种扫描方式,这里主要介绍常见的顺序扫描和索引扫描,相应的实现函数是create_seqscan_plan和create_indexscan_plan.



//--------------------------------------------------- create_scan_plan

static Plan *
create_scan_plan(PlannerInfo *root, Path *best_path, int flags)
{
    RelOptInfo *rel = best_path->parent;
    List       *scan_clauses;
    List       *gating_clauses;
    List       *tlist;
    Plan       *plan;

    
    switch (best_path->pathtype)
    {
        case T_IndexScan:
        case T_IndexOnlyScan://索引扫描,使用索引约束条件
            scan_clauses = castNode(IndexPath, best_path)->indexinfo->indrestrictinfo;
            break;
        default:
            scan_clauses = rel->baserestrictinfo;//默认使用关系中的约束条件
            break;
    }

    
    if (best_path->param_info)
        scan_clauses = list_concat(list_copy(scan_clauses),
                                   best_path->param_info->ppi_clauses);

    
    gating_clauses = get_gating_quals(root, scan_clauses);
    if (gating_clauses)
        flags = 0;

    
    if (flags == CP_IGNORE_TLIST)
    {
        tlist = NULL;//使用CP_IGNORE_TLIST标志,则设置tlist为NULL
    }
    else if (use_physical_tlist(root, best_path, flags))
    {
        if (best_path->pathtype == T_IndexOnlyScan)//索引快速扫描
        {
            
            //对于所有快速扫描,tlist中的列应在索引中
            tlist = copyObject(((IndexPath *) best_path)->indexinfo->indextlist);

            
            if (flags & CP_LABEL_TLIST)
                apply_pathtarget_labeling_to_tlist(tlist, best_path->pathtarget);
        }
        else//非索引快速扫描
        {
            tlist = build_physical_tlist(root, rel);//构建物理的tlist
            if (tlist == NIL)
            {
                
                //build_physical_tlist无法构建,则使用常规方法构建
                tlist = build_path_tlist(root, best_path);
            }
            else
            {
                
                if (flags & CP_LABEL_TLIST)
                    apply_pathtarget_labeling_to_tlist(tlist, best_path->pathtarget);
            }
        }
    }
    else
    {
        tlist = build_path_tlist(root, best_path);//使用常规方法构建
    }

    switch (best_path->pathtype)//根据路径类型进行相应的处理
    {
        case T_SeqScan://顺序扫描
            plan = (Plan *) create_seqscan_plan(root,
                                                best_path,
                                                tlist,
                                                scan_clauses);
            break;

        case T_SampleScan:
            plan = (Plan *) create_samplescan_plan(root,
                                                   best_path,
                                                   tlist,
                                                   scan_clauses);
            break;

        case T_IndexScan:
            plan = (Plan *) create_indexscan_plan(root,
                                                  (IndexPath *) best_path,
                                                  tlist,
                                                  scan_clauses,
                                                  false);
            break;

        case T_IndexOnlyScan:
            plan = (Plan *) create_indexscan_plan(root,
                                                  (IndexPath *) best_path,
                                                  tlist,
                                                  scan_clauses,
                                                  true);
            break;

        case T_BitmapHeapScan:
            plan = (Plan *) create_bitmap_scan_plan(root,
                                                    (BitmapHeapPath *) best_path,
                                                    tlist,
                                                    scan_clauses);
            break;

        case T_TidScan:
            plan = (Plan *) create_tidscan_plan(root,
                                                (TidPath *) best_path,
                                                tlist,
                                                scan_clauses);
            break;

        case T_SubqueryScan:
            plan = (Plan *) create_subqueryscan_plan(root,
                                                     (SubqueryScanPath *) best_path,
                                                     tlist,
                                                     scan_clauses);
            break;

        case T_FunctionScan:
            plan = (Plan *) create_functionscan_plan(root,
                                                     best_path,
                                                     tlist,
                                                     scan_clauses);
            break;

        case T_TableFuncScan:
            plan = (Plan *) create_tablefuncscan_plan(root,
                                                      best_path,
                                                      tlist,
                                                      scan_clauses);
            break;

        case T_ValuesScan:
            plan = (Plan *) create_valuesscan_plan(root,
                                                   best_path,
                                                   tlist,
                                                   scan_clauses);
            break;

        case T_CteScan:
            plan = (Plan *) create_ctescan_plan(root,
                                                best_path,
                                                tlist,
                                                scan_clauses);
            break;

        case T_NamedTuplestoreScan:
            plan = (Plan *) create_namedtuplestorescan_plan(root,
                                                            best_path,
                                                            tlist,
                                                            scan_clauses);
            break;

        case T_WorkTableScan:
            plan = (Plan *) create_worktablescan_plan(root,
                                                      best_path,
                                                      tlist,
                                                      scan_clauses);
            break;

        case T_ForeignScan:
            plan = (Plan *) create_foreignscan_plan(root,
                                                    (ForeignPath *) best_path,
                                                    tlist,
                                                    scan_clauses);
            break;

        case T_CustomScan:
            plan = (Plan *) create_customscan_plan(root,
                                                   (CustomPath *) best_path,
                                                   tlist,
                                                   scan_clauses);
            break;

        default:
            elog(ERROR, "unrecognized node type: %d",
                 (int) best_path->pathtype);
            plan = NULL;        
            break;
    }

    
    if (gating_clauses)
        plan = create_gating_plan(root, best_path, plan, gating_clauses);

    return plan;
}


//------------------------------------- create_seqscan_plan

static SeqScan *
create_seqscan_plan(PlannerInfo *root, Path *best_path,
                    List *tlist, List *scan_clauses)
{
    SeqScan    *scan_plan;
    Index       scan_relid = best_path->parent->relid;

    
    //基本关系
    Assert(scan_relid > 0);
    Assert(best_path->parent->rtekind == RTE_RELATION);

    
    //约束条件排序为最佳的执行顺序
    scan_clauses = order_qual_clauses(root, scan_clauses);

    
    //规约限制条件信息链表到裸表达式,同时忽略pseudoconstants
    scan_clauses = extract_actual_clauses(scan_clauses, false);

    
    //参数化访问,则使用内嵌循环参数替换外表变量
    if (best_path->param_info)
    {
        scan_clauses = (List *)
            replace_nestloop_params(root, (Node *) scan_clauses);//约束条件
    }

    scan_plan = make_seqscan(tlist,
                             scan_clauses,
                             scan_relid);//构建扫描计划

    copy_generic_path_info(&scan_plan->plan, best_path);

    return scan_plan;//返回
}

//------------------------------------- copy_generic_path_info

 
 static void
 copy_generic_path_info(Plan *dest, Path *src)
 {
     dest->startup_cost = src->startup_cost;
     dest->total_cost = src->total_cost;
     dest->plan_rows = src->rows;
     dest->plan_width = src->pathtarget->width;
     dest->parallel_aware = src->parallel_aware;
     dest->parallel_safe = src->parallel_safe;
 }
 

//------------------------------------- make_seqscan

static SeqScan *
make_seqscan(List *qptlist,
             List *qpqual,
             Index scanrelid)
{
    SeqScan    *node = makeNode(SeqScan);
    Plan       *plan = &node->plan;

    plan->targetlist = qptlist;
    plan->qual = qpqual;
    plan->lefttree = NULL;
    plan->righttree = NULL;
    node->scanrelid = scanrelid;

    return node;//创建节点
}

//------------------------------------- create_indexscan_plan


static Scan *
create_indexscan_plan(PlannerInfo *root,
                      IndexPath *best_path,
                      List *tlist,
                      List *scan_clauses,
                      bool indexonly)
{
    Scan       *scan_plan;
    List       *indexquals = best_path->indexquals;
    List       *indexorderbys = best_path->indexorderbys;
    Index       baserelid = best_path->path.parent->relid;
    Oid         indexoid = best_path->indexinfo->indexoid;
    List       *qpqual;
    List       *stripped_indexquals;
    List       *fixed_indexquals;
    List       *fixed_indexorderbys;
    List       *indexorderbyops = NIL;
    ListCell   *l;

    
    //基本关系
    Assert(baserelid > 0);
    Assert(best_path->path.parent->rtekind == RTE_RELATION);

    
    stripped_indexquals = get_actual_clauses(indexquals);

    
    fixed_indexquals = fix_indexqual_references(root, best_path);

    
    fixed_indexorderbys = fix_indexorderby_references(root, best_path);

    
    qpqual = NIL;
    foreach(l, scan_clauses)//遍历scan_clauses链表
    {
        RestrictInfo *rinfo = lfirst_node(RestrictInfo, l);

        if (rinfo->pseudoconstant)
            continue;           
        if (list_member_ptr(indexquals, rinfo))
            continue;           
        if (is_redundant_derived_clause(rinfo, indexquals))
            continue;           
        if (!contain_mutable_functions((Node *) rinfo->clause) &&
            predicate_implied_by(list_make1(rinfo->clause), indexquals, false))
            continue;           
        qpqual = lappend(qpqual, rinfo);
    }

    
    //条件排序
    qpqual = order_qual_clauses(root, qpqual);

    
    //规约
    qpqual = extract_actual_clauses(qpqual, false);

    
    if (best_path->path.param_info)//存在参数信息,使用内嵌循环参数替换
    {
        stripped_indexquals = (List *)
            replace_nestloop_params(root, (Node *) stripped_indexquals);
        qpqual = (List *)
            replace_nestloop_params(root, (Node *) qpqual);
        indexorderbys = (List *)
            replace_nestloop_params(root, (Node *) indexorderbys);
    }

    
    if (indexorderbys)
    {
        ListCell   *pathkeyCell,
                   *exprCell;

        
        Assert(list_length(best_path->path.pathkeys) == list_length(indexorderbys));
        forboth(pathkeyCell, best_path->path.pathkeys, exprCell, indexorderbys)
        {
            PathKey    *pathkey = (PathKey *) lfirst(pathkeyCell);
            Node       *expr = (Node *) lfirst(exprCell);
            Oid         exprtype = exprType(expr);
            Oid         sortop;

            
            sortop = get_opfamily_member(pathkey->pk_opfamily,
                                         exprtype,
                                         exprtype,
                                         pathkey->pk_strategy);
            if (!OidIsValid(sortop))
                elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
                     pathkey->pk_strategy, exprtype, exprtype, pathkey->pk_opfamily);
            indexorderbyops = lappend_oid(indexorderbyops, sortop);
        }
    }

    
    //OK,下面可以构建计划节点了
    if (indexonly)
        scan_plan = (Scan *) make_indexonlyscan(tlist,
                                                qpqual,
                                                baserelid,
                                                indexoid,
                                                fixed_indexquals,
                                                fixed_indexorderbys,
                                                best_path->indexinfo->indextlist,
                                                best_path->indexscandir);
    else
        scan_plan = (Scan *) make_indexscan(tlist,
                                            qpqual,
                                            baserelid,
                                            indexoid,
                                            fixed_indexquals,
                                            stripped_indexquals,
                                            fixed_indexorderbys,
                                            indexorderbys,
                                            indexorderbyops,
                                            best_path->indexscandir);

    copy_generic_path_info(&scan_plan->plan, &best_path->path);

    return scan_plan;
}


//------------------------------------- make_indexscan/make_indexonlyscan
static IndexScan *
make_indexscan(List *qptlist,
               List *qpqual,
               Index scanrelid,
               Oid indexid,
               List *indexqual,
               List *indexqualorig,
               List *indexorderby,
               List *indexorderbyorig,
               List *indexorderbyops,
               ScanDirection indexscandir)
{
    IndexScan  *node = makeNode(IndexScan);
    Plan       *plan = &node->scan.plan;

    plan->targetlist = qptlist;
    plan->qual = qpqual;
    plan->lefttree = NULL;
    plan->righttree = NULL;
    node->scan.scanrelid = scanrelid;
    node->indexid = indexid;
    node->indexqual = indexqual;
    node->indexqualorig = indexqualorig;
    node->indexorderby = indexorderby;
    node->indexorderbyorig = indexorderbyorig;
    node->indexorderbyops = indexorderbyops;
    node->indexorderdir = indexscandir;

    return node;
}

static IndexOnlyScan *
make_indexonlyscan(List *qptlist,
                   List *qpqual,
                   Index scanrelid,
                   Oid indexid,
                   List *indexqual,
                   List *indexorderby,
                   List *indextlist,
                   ScanDirection indexscandir)
{
    IndexOnlyScan *node = makeNode(IndexOnlyScan);
    Plan       *plan = &node->scan.plan;

    plan->targetlist = qptlist;
    plan->qual = qpqual;
    plan->lefttree = NULL;
    plan->righttree = NULL;
    node->scan.scanrelid = scanrelid;
    node->indexid = indexid;
    node->indexqual = indexqual;
    node->indexorderby = indexorderby;
    node->indextlist = indextlist;
    node->indexorderdir = indexscandir;

    return node;
}

三、跟踪分析

测试脚本如下

testdb=# explain select dw.*,grjf.grbh,grjf.xm,grjf.ny,grjf.je 
testdb-# from t_dwxx dw,lateral (select gr.grbh,gr.xm,jf.ny,jf.je 
testdb(#                         from t_grxx gr inner join t_jfxx jf 
testdb(#                                        on gr.dwbh = dw.dwbh 
testdb(#                                           and gr.grbh = jf.grbh) grjf
testdb-# where dw.dwbh in ('1001','1002')
testdb-# order by dw.dwbh;
                                          QUERY PLAN                                               
-------------------------------------------------------------------------------------------------
 Sort  (cost=2010.12..2010.17 rows=20 width=47)
   Sort Key: dw.dwbh
   ->  Nested Loop  (cost=14.24..2009.69 rows=20 width=47)
         ->  Hash Join  (cost=13.95..2002.56 rows=20 width=32)
               Hash Cond: ((gr.dwbh)::text = (dw.dwbh)::text)
               ->  Seq Scan on t_grxx gr  (cost=0.00..1726.00 rows=100000 width=16)
               ->  Hash  (cost=13.92..13.92 rows=2 width=20)
                     ->  Index Scan using t_dwxx_pkey on t_dwxx dw  (cost=0.29..13.92 rows=2 width=20)
                           Index Cond: ((dwbh)::text = ANY ('{1001,1002}'::text[]))
         ->  Index Scan using idx_t_jfxx_grbh on t_jfxx jf  (cost=0.29..0.35 rows=1 width=20)
               Index Cond: ((grbh)::text = (gr.grbh)::text)
(11 rows)

启动gdb,设置断点,进入函数create_scan_plan

(gdb) b create_scan_plan
Breakpoint 1 at 0x7b7b78: file createplan.c, line 514.
(gdb) c
Continuing.

Breakpoint 1, create_scan_plan (root=0x2d36d00, best_path=0x2d57ad0, flags=0) at createplan.c:514
514     RelOptInfo *rel = best_path->parent;

pathtype为T_SeqScan

(gdb) p best_path->pathtype
$1 = T_SeqScan

进入相应的分支,约束条件直接使用Relation的限制条件

(gdb) n
532     switch (best_path->pathtype)
(gdb) 
539             scan_clauses = rel->baserestrictinfo;
(gdb) 
540             break;

非索引快速扫描,构建目标投影列

(gdb) n
559     if (gating_clauses)
(gdb) 
572     if (flags == CP_IGNORE_TLIST)
(gdb) 
576     else if (use_physical_tlist(root, best_path, flags))
(gdb) 
578         if (best_path->pathtype == T_IndexOnlyScan)
(gdb) 
592             tlist = build_physical_tlist(root, rel);
(gdb) 
593             if (tlist == NIL)
(gdb) 
601                 if (flags & CP_LABEL_TLIST)
(gdb) 
(gdb) p *tlist
$4 = {type = T_List, length = 5, head = 0x2d89440, tail = 0x2d897d8}
(gdb) p *(Node *)tlist->head->data.ptr_value
$5 = {type = T_TargetEntry}
(gdb) p *(TargetEntry *)tlist->head->data.ptr_value
$6 = {xpr = {type = T_TargetEntry}, expr = 0x2d89390, resno = 1, resname = 0x0, ressortgroupref = 0, resorigtbl = 0, 
  resorigcol = 0, resjunk = false}

根据pathtype进入相应的处理逻辑,调用函数create_seqscan_plan

(gdb) n
614             plan = (Plan *) create_seqscan_plan(root,
(gdb) step
create_seqscan_plan (root=0x2d36d00, best_path=0x2d57ad0, tlist=0x2d89468, scan_clauses=0x0) at createplan.c:2444
2444        Index       scan_relid = best_path->parent->relid;

构建扫描条件,为NULL

2451        scan_clauses = order_qual_clauses(root, scan_clauses);
(gdb) 
2454        scan_clauses = extract_actual_clauses(scan_clauses, false);
(gdb) 
2457        if (best_path->param_info)
(gdb) 
(gdb) p *scan_clauses
Cannot access memory at address 0x0

生成SeqScan Plan节点,并把启动成本/总成本等相关信息拷贝到该Plan中

(gdb) n
2463        scan_plan = make_seqscan(tlist,
(gdb) 
2467        copy_generic_path_info(&scan_plan->plan, best_path);

完成创建,返回Plan

(gdb) n
2469        return scan_plan;
(gdb) p *scan_plan
$1 = {plan = {type = T_SeqScan, startup_cost = 0, total_cost = 1726, plan_rows = 100000, plan_width = 16, 
    parallel_aware = false, parallel_safe = true, plan_node_id = 0, targetlist = 0x2db4e38, qual = 0x0, lefttree = 0x0, 
    righttree = 0x0, initPlan = 0x0, extParam = 0x0, allParam = 0x0}, scanrelid = 3}

下面跟踪分析create_indexscan_plan函数
设置断点,进入函数

(gdb) b create_indexscan_plan
Breakpoint 2 at 0x7badad: file createplan.c, line 2536.
(gdb) c
Continuing.

Breakpoint 1, create_scan_plan (root=0x2d50a00, best_path=0x2da9e98, flags=2) at createplan.c:514
514     RelOptInfo *rel = best_path->parent;
(gdb) c
Continuing.

Breakpoint 2, create_indexscan_plan (root=0x2d50a00, best_path=0x2da9e98, tlist=0x2db5250, scan_clauses=0x2da8998, 
    indexonly=false) at createplan.c:2536
2536        List       *indexquals = best_path->indexquals;
(gdb) 

赋值并获取相关信息

2536        List       *indexquals = best_path->indexquals;
(gdb) n
2537        List       *indexorderbys = best_path->indexorderbys;
(gdb) 
2538        Index       baserelid = best_path->path.parent->relid;
(gdb) 
2539        Oid         indexoid = best_path->indexinfo->indexoid;
(gdb) 
2544        List       *indexorderbyops = NIL;
(gdb) 
2548        Assert(baserelid > 0);
(gdb) 
2549        Assert(best_path->path.parent->rtekind == RTE_RELATION);
(gdb) 
2555        stripped_indexquals = get_actual_clauses(indexquals);
(gdb) n
2561        fixed_indexquals = fix_indexqual_references(root, best_path);
(gdb) 
2566        fixed_indexorderbys = fix_indexorderby_references(root, best_path);
(gdb) 
2596        qpqual = NIL;
(gdb) 
(gdb) p baserelid
$3 = 1
(gdb) p indexoid
$4 = 16738
#t_dwxx_pkey
testdb=# select relname from pg_class where oid=16738;
   relname   
-------------
 t_dwxx_pkey
(1 row)

遍历扫描条件

2597        foreach(l, scan_clauses)
(gdb) p *scan_clauses
$5 = {type = T_List, length = 1, head = 0x2da8970, tail = 0x2da8970}

重复的约束条件,不需要处理

2603            if (list_member_ptr(indexquals, rinfo))
(gdb) 
2604                continue;           

对条件进行排序&规约

2614        qpqual = order_qual_clauses(root, qpqual);
(gdb) n
2617        qpqual = extract_actual_clauses(qpqual, false);
(gdb) n
2628        if (best_path->path.param_info)
(gdb) p *qpqual
Cannot access memory at address 0x0

创建IndexScan节点

(gdb) n
2642        if (indexorderbys)
(gdb) 
2673        if (indexonly)
(gdb) 
2683            scan_plan = (Scan *) make_indexscan(tlist,
(gdb) 
2694        copy_generic_path_info(&scan_plan->plan, &best_path->path);
(gdb) 
2696        return scan_plan;
(gdb) 
2697    }
(gdb) p *scan_plan
$6 = {plan = {type = T_IndexScan, startup_cost = 0.28500000000000003, total_cost = 13.924222117799655, plan_rows = 2, 
    plan_width = 20, parallel_aware = false, parallel_safe = true, plan_node_id = 0, targetlist = 0x2db5250, qual = 0x0, 
    lefttree = 0x0, righttree = 0x0, initPlan = 0x0, extParam = 0x0, allParam = 0x0}, scanrelid = 1}
(gdb) 

回到create_scan_plan

(gdb) n
create_scan_plan (root=0x2d50a00, best_path=0x2da9e98, flags=2) at createplan.c:633
633             break;
(gdb) 
732     if (gating_clauses)
(gdb) 
735     return plan;
(gdb) 
736 }

调用完成.

四、参考资料

createplan.c
PG Document:Query Planning

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 资料下载
  • 历年真题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯