第 16 課 PostgreSQL查詢過程原始碼分析

1. 查詢基本流程

1.PLSQL語句,string
2.生成語法樹,parseTree
3.生成查詢樹,queryTree
4.生成優化查詢樹列表,queryTreeList
5.生成計劃語句樹,planStmtTree
6.生成計劃執行狀態樹,planStateTree

2. 各個結構切換原始碼分析

2.1 把使用者輸入的string的SQL語句轉換成原始語法樹(parseTree)。

例如：insert into test values(1, 'xxxx'); 是使用者發過來的，由postgres服務程式通過網路獲取到，如果除錯的話可以從函式exec_simple_query()開始。SQL語句到語法樹的轉換是函式raw_parser()完成的，返回一個語法樹list。

List *
raw_parser(const char *str)
{
    core_yyscan_t yyscanner;
    base_yy_extra_type yyextra;
    int         yyresult;

    /* 初始化flex掃描器 */
    yyscanner = scanner_init(str, &yyextra.core_yy_extra,
                             ScanKeywords, NumScanKeywords);

    /* base_yylex() only needs this much initialization */
    yyextra.have_lookahead = false;

    /* 初始化bison解析器 */
    parser_init(&yyextra);

    /* 執行語法分析 */
    yyresult = base_yyparse(yyscanner);

    /* 解析完成後呼叫，在scanner_init()之後需要進行清理 */
    scanner_finish(yyscanner);

    if (yyresult)               /* error */
        return NIL;
        
    /* 返回原始語法樹列表， list裡儲存的型別是struct RawStmt */
    return yyextra.parsetree;
}

返回的list其實體是struct RawStmt結構體，在檔案src\include\nodes\parsenodes.h中定義。在該檔案中所有*Stmt結構體都是以NodeTag為基類，用來標識其節點型別。

typedef struct RawStmt
{
    NodeTag     type;
    Node       *stmt;           /* raw parse tree */
    int         stmt_location;  /* start location, or -1 if unknown */
    int         stmt_len;       /* length in bytes; 0 means "rest of string" */
} RawStmt;

例如我們舉例的insert語句的結構體如下，他的type型別是T_InsertStmt，該屬性用來在後面把語法樹轉化成查詢樹時使用，整個轉換過程通過Node型別來指導執行邏輯。

typedef struct InsertStmt
{
    NodeTag     type;
    RangeVar   *relation;       /* relation to insert into */
    List       *cols;           /* optional: names of the target columns */
    Node       *selectStmt;     /* the source SELECT/VALUES, or NULL */
    OnConflictClause *onConflictClause; /* ON CONFLICT clause */
    List       *returningList;  /* list of expressions to return */
    WithClause *withClause;     /* WITH clause */
    OverridingKind override;    /* OVERRIDING clause */
} InsertStmt;

2.2 原始語法樹轉換為查詢樹,queryTree

整個過程可以分成兩個步驟，語法樹到查詢樹和查詢重寫。

完成轉換後生成查詢樹結構Query，如下。語法樹轉查詢樹的入口函式是transformTopLevelStmt()，該函式根據語法樹，實施深度優先遍歷所有節點。最後建立一個Query物件，整個轉換過程就是填充Query結構中的各個成員變數，例如成員變數targetList，如果的我們語句是：”INSERT INTO p4(num, name) VALUES(nums[1], names[1])”，其中nums、names都是陣列，那麼targetList儲存的就是如何訪問這兩個陣列物件的方式。
List cteList; / WITH list (of CommonTableExpr's) */
上面的是關於with as語句的相關資訊，如果我們要增加一個新的語法特性，就需要在這裡增加一個特性的相關屬性。

typedef struct Query
{
    NodeTag     type;

    CmdType     commandType;    /* select|insert|update|delete|utility */

    QuerySource querySource;    /* where did I come from? */
        
        List       *targetList;     /* target list (of TargetEntry) */
        List       *cteList;        /* WITH list (of CommonTableExpr's) */
       後面省略
} Query;

查詢重寫過程

入口是pg_rewrite_query(query)，引數就是上面返回的Query指標。具體實施是在QueryRewrite()->RewriteQuery()函式，返回一個QueryList。

    if (query->commandType == CMD_UTILITY)
    {
        /* don't rewrite utilities, just dump 'em into result list */
        querytree_list = list_make1(query);
    }
    else
    {
        /* rewrite regular queries */
        querytree_list = QueryRewrite(query);
    }

2.3 查詢樹轉計劃語句樹,planStmtTree
迴圈查詢樹List，呼叫pg_plan_query()把查詢樹轉換成PlannedStmt(資料結構往下看)。

/*
 * 為已經重寫的查詢樹列表生成計劃樹。
 * 正常可優化的語句在結果列表中生成PlannedStmt。實用語句僅由它們的語句節點表示。
 * 所有的語句解析完成，return stmt_list。
 */
List *
pg_plan_queries(List *querytrees, int cursorOptions, ParamListInfo boundParams)
{
    List       *stmt_list = NIL;
    ListCell   *query_list;

    foreach(query_list, querytrees)
    {
        Query      *query = lfirst_node(Query, query_list);
        PlannedStmt *stmt;

        if (query->commandType == CMD_UTILITY)
        {
            /* Utility commands require no planning. */
            stmt = makeNode(PlannedStmt);
            stmt->commandType = CMD_UTILITY;
            stmt->canSetTag = query->canSetTag;
            stmt->utilityStmt = query->utilityStmt;
            stmt->stmt_location = query->stmt_location;
            stmt->stmt_len = query->stmt_len;
        }
        else
        {
            stmt = pg_plan_query(query, cursorOptions, boundParams);
        }

        stmt_list = lappend(stmt_list, stmt);
    }

    return stmt_list;
}

typedef struct PlannedStmt

typedef struct PlannedStmt
{
    NodeTag     type;

    CmdType     commandType;    /* select|insert|update|delete|utility */

    uint64      queryId;        /* query identifier (copied from Query) */

    bool        hasReturning;   /* is it insert|update|delete RETURNING? */

    bool        hasModifyingCTE;    /* has insert|update|delete in WITH? */

    bool        canSetTag;      /* do I set the command result tag? */

    bool        transientPlan;  /* redo plan when TransactionXmin changes? */

    bool        dependsOnRole;  /* is plan specific to current role? */

    bool        parallelModeNeeded; /* parallel mode required to execute? */

    int         jitFlags;       /* which forms of JIT should be performed */

    struct Plan *planTree;      /* tree of Plan nodes */

    List       *rtable;         /* list of RangeTblEntry nodes */

    /* rtable indexes of target relations for INSERT/UPDATE/DELETE */
    List       *resultRelations;    /* integer list of RT indexes, or NIL */

    /*
     * rtable indexes of partitioned table roots that are UPDATE/DELETE
     * targets; needed for trigger firing.
     */
    List       *rootResultRelations;

    List       *subplans;       /* Plan trees for SubPlan expressions; note
                                 * that some could be NULL */

    Bitmapset  *rewindPlanIDs;  /* indices of subplans that require REWIND */

    List       *rowMarks;       /* a list of PlanRowMark's */

    List       *relationOids;   /* OIDs of relations the plan depends on */

    List       *invalItems;     /* other dependencies, as PlanInvalItems */

    List       *paramExecTypes; /* type OIDs for PARAM_EXEC Params */

    Node       *utilityStmt;    /* non-null if this is utility stmt */

    /* statement location in source string (copied from Query) */
    int         stmt_location;  /* start location, or -1 if unknown */
    int         stmt_len;       /* length in bytes; 0 means "rest of string" */
} PlannedStmt;

2.4 計劃語句樹轉計劃執行狀態樹,planStateTree
轉換後的結果放到queryDesc->estate->es_subplanstates中。在執行器執行過程中，便是依據es_subplanstates連結串列和樹形結構進行執行。

static void
InitPlan(QueryDesc *queryDesc, int eflags)
{
    CmdType     operation = queryDesc->operation;
    PlannedStmt *plannedstmt = queryDesc->plannedstmt;
    Plan       *plan = plannedstmt->planTree;
    List       *rangeTable = plannedstmt->rtable;
    EState     *estate = queryDesc->estate;
    PlanState  *planstate;
    TupleDesc   tupType;
    ListCell   *l;
    int         i;

    // 把所有子查詢生成PlanState，放到estate->es_subplanstates列表中
    foreach(l, plannedstmt->subplans)
    {
        Plan       *subplan = (Plan *) lfirst(l);
        PlanState  *subplanstate;
        int         sp_eflags;

        /*
         * A subplan will never need to do BACKWARD scan nor MARK/RESTORE. If
         * it is a parameterless subplan (not initplan), we suggest that it be
         * prepared to handle REWIND efficiently; otherwise there is no need.
         */
        sp_eflags = eflags
            & (EXEC_FLAG_EXPLAIN_ONLY | EXEC_FLAG_WITH_NO_DATA);
        if (bms_is_member(i, plannedstmt->rewindPlanIDs))
            sp_eflags |= EXEC_FLAG_REWIND;

        subplanstate = ExecInitNode(subplan, estate, sp_eflags);

        estate->es_subplanstates = lappend(estate->es_subplanstates,
                                           subplanstate);

        i++;
    }