PostgreSQL中create_plan的實(shí)現(xiàn)邏輯是什么

本篇內(nèi)容主要講解“PostgreSQL中create_plan的實(shí)現(xiàn)邏輯是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“PostgreSQL中create_plan的實(shí)現(xiàn)邏輯是什么”吧!

一、數(shù)據(jù)結(jié)構(gòu)

Plan
所有計(jì)劃節(jié)點(diǎn)通過將Plan結(jié)構(gòu)作為第一個(gè)字段從Plan結(jié)構(gòu)“派生”。這確保了在將節(jié)點(diǎn)轉(zhuǎn)換為計(jì)劃節(jié)點(diǎn)時(shí)，一切都能正常工作。(在執(zhí)行器中以通用方式傳遞時(shí)，節(jié)點(diǎn)指針經(jīng)常被轉(zhuǎn)換為Plan *)

/* ----------------
 *      Plan node
 *
 * All plan nodes "derive" from the Plan structure by having the
 * Plan structure as the first field.  This ensures that everything works
 * when nodes are cast to Plan's.  (node pointers are frequently cast to Plan*
 * when passed around generically in the executor)
 * 所有計(jì)劃節(jié)點(diǎn)通過將Plan結(jié)構(gòu)作為第一個(gè)字段從Plan結(jié)構(gòu)“派生”。
 * 這確保了在將節(jié)點(diǎn)轉(zhuǎn)換為計(jì)劃節(jié)點(diǎn)時(shí)，一切都能正常工作。
 * (在執(zhí)行器中以通用方式傳遞時(shí)，節(jié)點(diǎn)指針經(jīng)常被轉(zhuǎn)換為Plan *)
 *
 * We never actually instantiate any Plan nodes; this is just the common
 * abstract superclass for all Plan-type nodes.
 * 從未實(shí)例化任何Plan節(jié)點(diǎn);這只是所有Plan-type節(jié)點(diǎn)的通用抽象超類。
 * ----------------
 */
typedef struct Plan
{
    NodeTag     type;//節(jié)點(diǎn)類型

    /*
     * 成本估算信息;estimated execution costs for plan (see costsize.c for more info)
     */
    Cost        startup_cost;   /* 啟動(dòng)成本;cost expended before fetching any tuples */
    Cost        total_cost;     /* 總成本;total cost (assuming all tuples fetched) */

    /*
     * 優(yōu)化器估算信息;planner's estimate of result size of this plan step
     */
    double      plan_rows;      /* 行數(shù);number of rows plan is expected to emit */
    int         plan_width;     /* 平均行大小(Byte為單位);average row width in bytes */

    /*
     * 并行執(zhí)行相關(guān)的信息;information needed for parallel query
     */
    bool        parallel_aware; /* 是否參與并行執(zhí)行邏輯?engage parallel-aware logic? */
    bool        parallel_safe;  /* 是否并行安全;OK to use as part of parallel plan? */

    /*
     * Plan類型節(jié)點(diǎn)通用的信息.Common structural data for all Plan types.
     */
    int         plan_node_id;   /* unique across entire final plan tree */
    List       *targetlist;     /* target list to be computed at this node */
    List       *qual;           /* implicitly-ANDed qual conditions */
    struct Plan *lefttree;      /* input plan tree(s) */
    struct Plan *righttree;
    List       *initPlan;       /* Init Plan nodes (un-correlated expr
                                 * subselects) */

    /*
     * Information for management of parameter-change-driven rescanning
     * parameter-change-driven重掃描的管理信息.
     * 
     * extParam includes the paramIDs of all external PARAM_EXEC params
     * affecting this plan node or its children.  setParam params from the
     * node's initPlans are not included, but their extParams are.
     *
     * allParam includes all the extParam paramIDs, plus the IDs of local
     * params that affect the node (i.e., the setParams of its initplans).
     * These are _all_ the PARAM_EXEC params that affect this node.
     */
    Bitmapset  *extParam;
    Bitmapset  *allParam;
} Plan;

二、源碼解讀

create_plan調(diào)用create_plan_recurse函數(shù)，遞歸遍歷訪問路徑，相應(yīng)的創(chuàng)建計(jì)劃（Plan）節(jié)點(diǎn)。

/*
 * create_plan
 *    Creates the access plan for a query by recursively processing the
 *    desired tree of pathnodes, starting at the node 'best_path'.  For
 *    every pathnode found, we create a corresponding plan node containing
 *    appropriate id, target list, and qualification information.
 *    從節(jié)點(diǎn)'best_path'開始,遞歸處理路徑節(jié)點(diǎn)樹，為查詢語句創(chuàng)建執(zhí)行計(jì)劃。
 *    對(duì)于找到的每個(gè)訪問路徑節(jié)點(diǎn)，創(chuàng)建一個(gè)相應(yīng)的計(jì)劃節(jié)點(diǎn)，其中包含合適的id、投影列和限定信息。
 *
 *    The tlists and quals in the plan tree are still in planner format,
 *    ie, Vars still correspond to the parser's numbering.  This will be
 *    fixed later by setrefs.c.
 *    計(jì)劃中的投影列和約束條件仍然以優(yōu)化器的格式存儲(chǔ).
 *    比如Vars對(duì)應(yīng)著解析樹的編號(hào)等,這些處理都在setrefs.c中完成
 *
 *    best_path is the best access path
 *    best_path是最優(yōu)的訪問路徑
 *
 *    Returns a Plan tree.
 *    返回Plan樹.
 */
Plan *
create_plan(PlannerInfo *root, Path *best_path)
{
    Plan       *plan;

    /* plan_params should not be in use in current query level */
    //plan_params在當(dāng)前查詢層次上不應(yīng)使用(值為NULL)
    Assert(root->plan_params == NIL);

    /* Initialize this module's private workspace in PlannerInfo */
    //初始化該模塊中優(yōu)化器信息的私有工作空間
    root->curOuterRels = NULL;
    root->curOuterParams = NIL;

    /* Recursively process the path tree, demanding the correct tlist result */
    //遞歸處理計(jì)劃樹(tlist參數(shù)設(shè)置為CP_EXACT_TLIST)
    plan = create_plan_recurse(root, best_path, CP_EXACT_TLIST);

    /*
     * Make sure the topmost plan node's targetlist exposes the original
     * column names and other decorative info.  Targetlists generated within
     * the planner don't bother with that stuff, but we must have it on the
     * top-level tlist seen at execution time.  However, ModifyTable plan
     * nodes don't have a tlist matching the querytree targetlist.
     * 確保最頂層計(jì)劃節(jié)點(diǎn)的投影列targetlist可以獲知原始列名和其他調(diào)整過的信息。
     * 在計(jì)劃器中生成的投影列targetlist不需要處理這些信息，但是必須在執(zhí)行時(shí)看到最高層的投影列tlist。
     * 注意:ModifyTable計(jì)劃節(jié)點(diǎn)沒有一個(gè)匹配查詢樹targetlist的tlist。
     */
    if (!IsA(plan, ModifyTable))
        apply_tlist_labeling(plan->targetlist, root->processed_tlist);//非ModifyTable

    /*
     * Attach any initPlans created in this query level to the topmost plan
     * node.  (In principle the initplans could go in any plan node at or
     * above where they're referenced, but there seems no reason to put them
     * any lower than the topmost node for the query level.  Also, see
     * comments for SS_finalize_plan before you try to change this.)
     * 將此查詢級(jí)別中創(chuàng)建的任何initplan附加到最高層的計(jì)劃節(jié)點(diǎn)中。
     * (原則上，initplans可以在引用它們的任何計(jì)劃節(jié)點(diǎn)或以上的節(jié)點(diǎn)中訪問，
     * 但似乎沒有理由將它們放在查詢級(jí)別的最高層節(jié)點(diǎn)以下。
     * 另外，如需嘗試更改SS_finalize_plan,請(qǐng)參閱注釋。)
     */
    SS_attach_initplans(root, plan);

    /* Check we successfully assigned all NestLoopParams to plan nodes */
    //檢查已經(jīng)為計(jì)劃節(jié)點(diǎn)參數(shù)NestLoopParams賦值
    if (root->curOuterParams != NIL)
        elog(ERROR, "failed to assign all NestLoopParams to plan nodes");

    /*
     * Reset plan_params to ensure param IDs used for nestloop params are not
     * re-used later
     * 重置plan_params參數(shù),以確保用于nestloop參數(shù)的參數(shù)IDs不會(huì)在后續(xù)被重復(fù)使用
     */
    root->plan_params = NIL;

    return plan;
}

//------------------------------------------------------------------------ create_plan_recurse
/*
 * create_plan_recurse
 *    Recursive guts of create_plan().
 *    create_plan()函數(shù)中的遞歸實(shí)現(xiàn)過程.
 */
static Plan *
create_plan_recurse(PlannerInfo *root, Path *best_path, int flags)
{
    Plan       *plan;

    /* Guard against stack overflow due to overly complex plans */
    //確保堆棧不會(huì)溢出
    check_stack_depth();

    switch (best_path->pathtype)//根據(jù)路徑類型,執(zhí)行相應(yīng)的處理
    {
        case T_SeqScan://順序掃描
        case T_SampleScan://采樣掃描
        case T_IndexScan://索引掃描
        case T_IndexOnlyScan://索引快速掃描
        case T_BitmapHeapScan://位圖堆掃描
        case T_TidScan://TID掃描
        case T_SubqueryScan://子查詢掃描
        case T_FunctionScan://函數(shù)掃描
        case T_TableFuncScan://表函數(shù)掃描
        case T_ValuesScan://Values掃描
        case T_CteScan://CTE掃描
        case T_WorkTableScan://WorkTable掃描
        case T_NamedTuplestoreScan://NamedTuplestore掃描
        case T_ForeignScan://外表掃描
        case T_CustomScan://自定義掃描
            plan = create_scan_plan(root, best_path, flags);//掃描計(jì)劃
            break;
        case T_HashJoin://Hash連接
        case T_MergeJoin://合并連接
        case T_NestLoop://內(nèi)嵌循環(huán)連接
            plan = create_join_plan(root,
                                    (JoinPath *) best_path);//連接結(jié)合
            break;
        case T_Append://追加(集合)
            plan = create_append_plan(root,
                                      (AppendPath *) best_path);//追加(集合并)計(jì)劃
            break;
        case T_MergeAppend://合并
            plan = create_merge_append_plan(root,
                                            (MergeAppendPath *) best_path);
            break;
        case T_Result://投影操作
            if (IsA(best_path, ProjectionPath))
            {
                plan = create_projection_plan(root,
                                              (ProjectionPath *) best_path,
                                              flags);
            }
            else if (IsA(best_path, MinMaxAggPath))
            {
                plan = (Plan *) create_minmaxagg_plan(root,
                                                      (MinMaxAggPath *) best_path);
            }
            else
            {
                Assert(IsA(best_path, ResultPath));
                plan = (Plan *) create_result_plan(root,
                                                   (ResultPath *) best_path);
            }
            break;
        case T_ProjectSet://投影集合操作
            plan = (Plan *) create_project_set_plan(root,
                                                    (ProjectSetPath *) best_path);
            break;
        case T_Material://物化
            plan = (Plan *) create_material_plan(root,
                                                 (MaterialPath *) best_path,
                                                 flags);
            break;
        case T_Unique://唯一處理
            if (IsA(best_path, UpperUniquePath))
            {
                plan = (Plan *) create_upper_unique_plan(root,
                                                         (UpperUniquePath *) best_path,
                                                         flags);
            }
            else
            {
                Assert(IsA(best_path, UniquePath));
                plan = create_unique_plan(root,
                                          (UniquePath *) best_path,
                                          flags);
            }
            break;
        case T_Gather://匯總收集
            plan = (Plan *) create_gather_plan(root,
                                               (GatherPath *) best_path);
            break;
        case T_Sort://排序
            plan = (Plan *) create_sort_plan(root,
                                             (SortPath *) best_path,
                                             flags);
            break;
        case T_Group://分組
            plan = (Plan *) create_group_plan(root,
                                              (GroupPath *) best_path);
            break;
        case T_Agg://聚集計(jì)算
            if (IsA(best_path, GroupingSetsPath))
                plan = create_groupingsets_plan(root,
                                                (GroupingSetsPath *) best_path);
            else
            {
                Assert(IsA(best_path, AggPath));
                plan = (Plan *) create_agg_plan(root,
                                                (AggPath *) best_path);
            }
            break;
        case T_WindowAgg://窗口函數(shù)
            plan = (Plan *) create_windowagg_plan(root,
                                                  (WindowAggPath *) best_path);
            break;
        case T_SetOp://集合操作
            plan = (Plan *) create_setop_plan(root,
                                              (SetOpPath *) best_path,
                                              flags);
            break;
        case T_RecursiveUnion://遞歸UNION
            plan = (Plan *) create_recursiveunion_plan(root,
                                                       (RecursiveUnionPath *) best_path);
            break;
        case T_LockRows://鎖定(for update)
            plan = (Plan *) create_lockrows_plan(root,
                                                 (LockRowsPath *) best_path,
                                                 flags);
            break;
        case T_ModifyTable://更新
            plan = (Plan *) create_modifytable_plan(root,
                                                    (ModifyTablePath *) best_path);
            break;
        case T_Limit://限制操作
            plan = (Plan *) create_limit_plan(root,
                                              (LimitPath *) best_path,
                                              flags);
            break;
        case T_GatherMerge://收集合并
            plan = (Plan *) create_gather_merge_plan(root,
                                                     (GatherMergePath *) best_path);
            break;
        default://其他非法類型
            elog(ERROR, "unrecognized node type: %d",
                 (int) best_path->pathtype);
            plan = NULL;        /* keep compiler quiet */
            break;
    }

    return plan;
}


//------------------------------------------------------------------------ apply_tlist_labeling

 /*
  * apply_tlist_labeling
  *      Apply the TargetEntry labeling attributes of src_tlist to dest_tlist
  *      將src_tlist的TargetEntry標(biāo)記屬性應(yīng)用到dest_tlist
  *
  * This is useful for reattaching column names etc to a plan's final output
  * targetlist.
  */
 void
 apply_tlist_labeling(List *dest_tlist, List *src_tlist)
 {
     ListCell   *ld,
                *ls;
 
     Assert(list_length(dest_tlist) == list_length(src_tlist));
     forboth(ld, dest_tlist, ls, src_tlist)
     {
         TargetEntry *dest_tle = (TargetEntry *) lfirst(ld);
         TargetEntry *src_tle = (TargetEntry *) lfirst(ls);
 
         Assert(dest_tle->resno == src_tle->resno);
         dest_tle->resname = src_tle->resname;
         dest_tle->ressortgroupref = src_tle->ressortgroupref;
         dest_tle->resorigtbl = src_tle->resorigtbl;
         dest_tle->resorigcol = src_tle->resorigcol;
         dest_tle->resjunk = src_tle->resjunk;
     }
 }
 

//------------------------------------------------------------------------ apply_tlist_labeling
 /*
  * SS_attach_initplans - attach initplans to topmost plan node
  * 將initplans附加到最頂層的計(jì)劃節(jié)點(diǎn) 
  *
  * Attach any initplans created in the current query level to the specified
  * plan node, which should normally be the topmost node for the query level.
  * (In principle the initPlans could go in any node at or above where they're
  * referenced; but there seems no reason to put them any lower than the
  * topmost node, so we don't bother to track exactly where they came from.)
  * We do not touch the plan node's cost; the initplans should have been
  * accounted for in path costing.
  */
 void
 SS_attach_initplans(PlannerInfo *root, Plan *plan)
 {
     plan->initPlan = root->init_plans;
 }

三、跟蹤分析

測(cè)試腳本如下

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-# order by dw.dwbh;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Sort  (cost=20070.93..20320.93 rows=100000 width=47)
   Sort Key: dw.dwbh
   ->  Hash Join  (cost=3754.00..8689.61 rows=100000 width=47)
         Hash Cond: ((gr.dwbh)::text = (dw.dwbh)::text)
         ->  Hash Join  (cost=3465.00..8138.00 rows=100000 width=31)
               Hash Cond: ((jf.grbh)::text = (gr.grbh)::text)
               ->  Seq Scan on t_jfxx jf  (cost=0.00..1637.00 rows=100000 width=20)
               ->  Hash  (cost=1726.00..1726.00 rows=100000 width=16)
                     ->  Seq Scan on t_grxx gr  (cost=0.00..1726.00 rows=100000 width=16)
         ->  Hash  (cost=164.00..164.00 rows=10000 width=20)
               ->  Seq Scan on t_dwxx dw  (cost=0.00..164.00 rows=10000 width=20)
(11 rows)

啟動(dòng)gdb,設(shè)置斷點(diǎn),進(jìn)入

(gdb) info break
Num     Type           Disp Enb Address            What
2       breakpoint     keep y   0x00000000007b76c1 in create_plan at createplan.c:313
(gdb) c
Continuing.

Breakpoint 2, create_plan (root=0x26c1258, best_path=0x2722d00) at createplan.c:313
313     Assert(root->plan_params == NIL);

進(jìn)入create_plan_recurse函數(shù)

313     Assert(root->plan_params == NIL);
(gdb) n
316     root->curOuterRels = NULL;
(gdb) 
317     root->curOuterParams = NIL;
(gdb) 
320     plan = create_plan_recurse(root, best_path, CP_EXACT_TLIST);
(gdb) step
create_plan_recurse (root=0x26c1258, best_path=0x2722d00, flags=1) at createplan.c:364
364     check_stack_depth();

根據(jù)訪問路徑類型(T_ProjectionPath)選擇處理分支

(gdb) p *best_path
$1 = {type = T_ProjectionPath, pathtype = T_Result, parent = 0x2722998, pathtarget = 0x27226f8, param_info = 0x0, 
  parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 100000, startup_cost = 20070.931487218411, 
  total_cost = 20320.931487218411, pathkeys = 0x26cfe98}

調(diào)用create_projection_plan函數(shù)

(gdb) n
400             if (IsA(best_path, ProjectionPath))
(gdb) 
402                 plan = create_projection_plan(root,

創(chuàng)建相應(yīng)的Plan(T_Sort,存在左右子樹),下一節(jié)將詳細(xì)解釋create_projection_plan函數(shù)

(gdb) 
504     return plan;
(gdb) p *plan
$1 = {type = T_Sort, startup_cost = 20070.931487218411, total_cost = 20320.931487218411, plan_rows = 100000, 
  plan_width = 47, parallel_aware = false, parallel_safe = true, plan_node_id = 0, targetlist = 0x2724548, qual = 0x0, 
  lefttree = 0x27243d0, righttree = 0x0, initPlan = 0x0, extParam = 0x0, allParam = 0x0}

執(zhí)行返回

(gdb) 
create_plan (root=0x270f9c8, best_path=0x2722d00) at createplan.c:329
329     if (!IsA(plan, ModifyTable))
(gdb) 
330         apply_tlist_labeling(plan->targetlist, root->processed_tlist);
(gdb) 
339     SS_attach_initplans(root, plan);
(gdb) 
342     if (root->curOuterParams != NIL)
(gdb) 
349     root->plan_params = NIL;
(gdb) 
351     return plan;

到此，相信大家對(duì)“PostgreSQL中create_plan的實(shí)現(xiàn)邏輯是什么”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！