grouping_planner主要做了3個工作：

對集合進行處理

對非SPJ函數進行優化

對SQL查詢語句進行物理優化

grouping_planner實現代碼如下：

static void grouping_planner(PlannerInfo *root, bool inheritance_update,double tuple_fraction) {/* 如果存在limit，offset，元組片段因子要改小 */if (parse->limitCount || parse->limitOffset){tuple_fraction = preprocess_limit(root, tuple_fraction,&offset_est, &count_est);}/* Make tuple_fraction accessible to lower-level routines */root->tuple_fraction = tuple_fraction;//判斷是否存在集合操作，如何存在，則處理集合運算。if (parse->setOperations){//會把集合語句按照集合操作符(差，并，交)分割SQL語句，//然后調用為每一個獨立的部分調用subquery_planner，//所以Postgresql幾乎不支持集合優化//current_rel = plan_set_operations(root);//順便求出路徑排序root->sort_pathkeys = make_pathkeys_for_sortclauses(root,parse->sortClause,tlist);}else//非集合操作{/* ORDER BY和GROUP BY同時存在，先GROUP BY，在ORDER BY */if (parse->groupingSets){groupclause = preprocess_groupclause(root,linitial(current_sets));}/* 對目標列進行處理*/tlist = preprocess_targetlist(root, tlist);//提前執行帶有max/min的聚合函數子句if (parse->hasAggs)preprocess_minmax_aggregates(root, tlist);}/*最優路徑*/current_rel = query_planner(root, tlist,standard_qp_callback, &qp_extra);//為max/min生成執行計劃if (parse->hasAggs)preprocess_minmax_aggregates(root, tlist);} }

query_planner生成最優查詢路徑

產生兩個最優查詢路徑，主要是cheatest_path(未排序)和sorted_path(排序)

RelOptInfo * query_planner(PlannerInfo *root, List *tlist,query_pathkeys_callback qp_callback, void *qp_extra) {/** If the query has an empty join tree, then it's something easy like* "SELECT 2+2;" or "INSERT ... VALUES()". Fall through quickly.*/if (parse->jointree->fromlist == NIL){/** We still are required to call qp_callback, in case it's something* like "SELECT 2+2 ORDER BY 1".標準化其他排序鍵，例如ORDER BY，GROUP BY*/root->canon_pathkeys = NIL;(*qp_callback) (root, qp_extra);return final_rel;}//初始化ROOT成員/*找出所有基本表，放入simple_rte_array */setup_simple_rel_arrays(root);/*找出所有基本表，放入生成基本關系*/add_base_rels_to_query(root, (Node *) parse->jointree);//分解where和join中的約束條件，構建連接樹joinlist = deconstruct_jointree(root);/*檢查外連接子句，把外連接的約束條件分發到對應關系上* ，看源碼好像沒有推到join關系上，而是推到join關系的子關系上*/reconsider_outer_join_clauses(root);/*處理隱含約束條件*/generate_base_implied_equalities(root);/*去除無用連接*/joinlist = remove_useless_joins(root, joinlist);/*完成多表鏈接，采用動態規劃和遺傳算法 */final_rel = make_one_rel(root, joinlist);return final_rel; }

deconstruct_jointree構造連接樹函數

deconstruct_jointree用于分解樹上的連接結構，分解方式為：把where和join中每個子句加入一個list中，然后把約束條件分配到每個關系上。一是把限制條件分配到基本關系上，二是把連接條件分配到連接關系上。這些本質上是邏輯優化階段的“謂詞下推操作”。但是由于此時還沒有構造join關系，所以不能推到join關系上

static List * deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,Relids *qualscope, Relids *inner_join_rels,List **postponed_qual_list) {if (IsA(jtnode, RangeTblRef)){//構造只有一個節點的關系joinlist = list_make1(jtnode);}else if (IsA(jtnode, FromExpr)){//遞歸構造每一個From子句，然后把結果下推/** Now process the top-level quals.*/foreach(l, (List *) f->quals){ //還構建了RestrictInfodistribute_qual_to_rels(root, qual,false, below_outer_join, JOIN_INNER,*qualscope, NULL, NULL, NULL,postponed_qual_list);}}else if (IsA(jtnode, JoinExpr)){//遞歸構造join兩邊switch (j->jointype){case JOIN_INNER:case JOIN_ANTI:case JOIN_FULL:default:}/*處理join下推*/foreach(l, my_quals){Node *qual = (Node *) lfirst(l);distribute_qual_to_rels(root, qual,false, below_outer_join, j->jointype,*qualscope,ojscope, nonnullable_rels, NULL,postponed_qual_list);}}return joinlist; }

reconsider_outer_join_clauses

分發外連接子句的約束條件

generate_base_implied_equalites

找出隱含條件，進一步謂詞下推

make_one_rel 構造多表連接路徑并選擇最優路徑的函數

RelOptInfo * make_one_rel(PlannerInfo *root, List *joinlist) {/* Mark base rels as to whether we care about fast-start plans */set_base_rel_consider_startup(root);//為每個基本關系估計大小set_base_rel_sizes(root);//為每個基本關系生成RelOptInfo結構，并且生成訪問路徑放在path，這是單表/子查詢的最佳掃描方式.set_base_rel_pathlists(root);/*返回一個最終的連接所有表的RelOptInfo */rel = make_rel_from_joinlist(root, joinlist);/** The result should join all and only the query's base rels.*/Assert(bms_equal(rel->relids, root->all_baserels));return rel; }

make_rel_from_joinlist

joinlist是從where和join on子句找出能做連接操作的對象

static RelOptInfo * make_rel_from_joinlist(PlannerInfo *root, List *joinlist) {/** Construct a list of rels corresponding to the child joinlist nodes.* This may contain both base rels and rels constructed according to* sub-joinlists.*/initial_rels = NIL;foreach(jl, joinlist){if (IsA(jlnode, RangeTblRef))//范圍表直接找出要連接的關系{int varno = ((RangeTblRef *) jlnode)->rtindex;thisrel = find_base_rel(root, varno);}else if (IsA(jlnode, List))//遍歷子查詢{/* Recurse to handle subproblem */thisrel = make_rel_from_joinlist(root, (List *) jlnode);}initial_rels = lappend(initial_rels, thisrel);}if (levels_needed == 1){}else{root->initial_rels = initial_rels;if (join_search_hook)return (*join_search_hook) (root, levels_needed, initial_rels);//用戶自定義else if (enable_geqo && levels_needed >= geqo_threshold)return geqo(root, levels_needed, initial_rels);//遺傳算法elsereturn standard_join_search(root, levels_needed, initial_rels);//動態規劃} }

動態規劃算法

例如：有一條SQL語句
SELECT * FROM A,B,C,D where A.a=B.a and ...

每層的關系如下：

第四層：ABCD

第三層：ABC,ACD,BCD

第二層：AB,AC,AD,BC,BD...

第一層：A,B,C,D

RelOptInfo * standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) {int lev;RelOptInfo *rel;/* root->join_rel_level[j]存放的是第j層的連接路徑，* 如果有n個關系，最大鏈接層數就是n。*/root->join_rel_level = (List **) palloc0((levels_needed + 1) * sizeof(List *));root->join_rel_level[1] = initial_rels;//初始層數for (lev = 2; lev <= levels_needed; lev++){ListCell *lc;/*使用動態規劃求第lev層的所有關系，采用左深樹和緊密熟的方式。N=N-1 +1;N=N-k + k */join_search_one_level(root, lev);/** Run generate_gather_paths() for each just-processed joinrel. We* could not do this earlier because both regular and partial paths* can get added to a particular joinrel at multiple times within* join_search_one_level. After that, we're done creating paths for* the joinrel, so run set_cheapest().*/foreach(lc, root->join_rel_level[lev]){rel = (RelOptInfo *) lfirst(lc);//為lev層每個關系求最優路徑set_cheapest(rel);}}rel = (RelOptInfo *) linitial(root->join_rel_level[levels_needed]);root->join_rel_level = NULL;return rel; }

轉載于:https://www.cnblogs.com/biterror/p/7161671.html

總結

以上是生活随笔為你收集整理的PostgreSQL查询优化器之grouping_planner的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。