摘要：SQL語句解析完成後被解析成Query結構，在進行優化時是以Query為單位進行的，Query的優化分為基於規則的邏輯優化（查詢重寫）和基於代價的物理優化（計劃生成），主入口函數為subquery_planner。subquery_planner函數接收Query(查詢樹)，返回一個Plan( ...
摘要：SQL語句解析完成後被解析成Query結構，在進行優化時是以Query為單位進行的，Query的優化分為基於規則的邏輯優化（查詢重寫）和基於代價的物理優化（計劃生成），主入口函數為subquery_planner。subquery_planner函數接收Query(查詢樹)，返回一個Plan(計劃樹)。
本文分享自華為雲社區《openGauss內核分析（六） 執行計劃生成》，作者：Gauss松鼠會。
SQL語句解析完成後被解析成Query結構，在進行優化時是以Query為單位進行的，Query的優化分為基於規則的邏輯優化（查詢重寫）和基於代價的物理優化（計劃生成），主入口函數為subquery_planner。subquery_planner函數接收Query(查詢樹)，返回一個Plan(計劃樹)。
Plan* subquery_planner(PlannerGlobal* glob, Query* parse, PlannerInfo* parent_root, bool hasRecursion, double tuple_fraction, PlannerInfo** subroot, int options, ItstDisKey* diskeys, List* subqueryRestrictInfo) { PlannerInfo* root = NULL; Plan* plan = NULL; //返回結果 … preprocess_const_params(root, (Node*)parse->jointree); // 常數替換等式 … if (parse->hasSubLinks) { pull_up_sublinks(root); //提升子鏈接 DEBUG_QRW("After sublink pullup"); } /* Reduce orderby clause in subquery for join */ reduce_orderby(parse, false); //減少orderby DEBUG_QRW("After order by reduce"); if (u_sess->attr.attr_sql.enable_constraint_optimization) { removeNotNullTest(root); //刪除NotNullTest DEBUG_QRW("After soft constraint removal"); } … if ((LAZY_AGG & u_sess->attr.attr_sql.rewrite_rule) && permit_from_rewrite_hint(root, LAZY_AGG)) { lazyagg_main(parse); // lazyagg重寫 DEBUG_QRW("After lazyagg"); } … parse->jointree = (FromExpr*)pull_up_subqueries(root, (Node*)parse->jointree); //提升子查詢 … if (parse->setOperations) { flatten_simple_union_all(root); //UNIONALL優化 DEBUG_QRW("After simple union all flatten"); } … expand_inherited_tables(root); //展開繼承表 … parse->targetList = (List*)preprocess_expression(root, (Node*)parse->targetList, EXPRKIND_TARGET); //預處理表達式 … parse->havingQual = (Node *) newHaving; //處理HAVING子句 … reduce_outer_joins(root); //外連接消除 … reduce_inequality_fulljoins(root); //全連接重寫 … plan = grouping_planner(root, tuple_fraction); //主要的計划過程 … return plan; }
subquery_planner函數由函數standard_planner調用，standard_planner函數由exec_simple_query->pg_plan_queries->pg_plan_query->planner函數調用。standard_planner將Query(查詢樹)生成規劃好的語句，可用於執行器實際執行。
PlannedStmt* standard_planner(Query* parse, int cursorOptions, ParamListInfo boundParams) { PlannedStmt* result = NULL; //返回結果 PlannerGlobal* glob = NULL; double tuple_fraction; PlannerInfo* root = NULL; Plan* top_plan = NULL; … glob = makeNode(PlannerGlobal); /* primary planning entry point (may recurse for subqueries) */ top_plan = subquery_planner(glob, parse, NULL, false, tuple_fraction, &root); //主規划過程入口 … /* build the PlannedStmt result */ result = makeNode(PlannedStmt); //構造PlannedStmt result->commandType = parse->commandType; result->queryId = parse->queryId; result->uniqueSQLId = parse->uniqueSQLId; result->hasReturning = (parse->returningList != NIL); result->hasModifyingCTE = parse->hasModifyingCTE; result->canSetTag = parse->canSetTag; result->transientPlan = glob->transientPlan; result->dependsOnRole = glob->dependsOnRole; result->planTree = top_plan; //執行計劃 result->rtable = glob->finalrtable; result->resultRelations = glob->resultRelations; … return result; }
仍然以前文的join列子來說明
SELECT * FROM t1 inner JOIN t2 ON t1.c1 = t2.c1;複製
在planner函數打斷點，用gdb查看standard_planner返回的PlannedStmt
(gdb) bt #0 planner (parse=0x7fd93a410288, cursorOptions=0, boundParams=0x0) at planner.cpp:389 #1 0x0000000001936fbd in pg_plan_query (querytree=0x7fd93a410288, cursorOptions=0, boundParams=0x0, underExplain=false) at postgres.cpp:1197 #2 0x0000000001937381 in pg_plan_queries (querytrees=0x7fd939b81090, cursorOptions=0, boundParams=0x0) at postgres.cpp:1315 #3 0x000000000193a6b8 in exec_simple_query (query_string=0x7fd966ad2060 "SELECT * FROM t1 inner JOIN t2 ON t1.c1 = t2.c1;", messageType=QUERY_MESSAGE, msg=0x7fd931056210) at postgres.cpp:2560 #4 0x0000000001947104 in PostgresMain (argc=1, argv=0x7fd93a2cf1c0, dbname=0x7fd93a2ce1f8 "postgres", username=0x7fd93a2ce1b0 "test") at postgres.cpp:8403 #5 0x0000000001890740 in BackendRun (port=0x7fd931056720) at postmaster.cpp:8053 #6 0x00000000018a00b1 in GaussDbThreadMain<(knl_thread_role)1> (arg=0x7fd97c55c5f0) at postmaster.cpp:12181 #7 0x000000000189c0de in InternalThreadFunc (args=0x7fd97c55c5f0) at postmaster.cpp:12755 #8 0x00000000024bf7d8 in ThreadStarterFunc (arg=0x7fd97c55c5e0) at gs_thread.cpp:382 #9 0x00007fd9a60cfdd5 in start_thread () from /lib64/libpthread.so.0 #10 0x00007fd9a5df8ead in clone () from /lib64/libc.so.6 (gdb) p *result $14 = {type = T_PlannedStmt, commandType = CMD_SELECT, queryId = 0, hasReturning = false, hasModifyingCTE = false, canSetTag = true, transientPlan = false, dependsOnRole = false, planTree = 0x7fd93a409d58, rtable = 0x7fd939b81660, …} (gdb) p *result->planTree->lefttree $46 = {type = T_SeqScan, plan_node_id = 2, parent_node_id = 1, exec_type = EXEC_ON_DATANODES, startup_cost = 0, total_cost = 1.03, plan_rows = 3, multiple = 1, plan_width = 8,…}
將Query規劃後得到PlannedStmt
可以看到，Plannedstmt 與explain執行計劃是一致的
