PostgreSQL 原始碼解讀（38）- 查詢語句#23（query_planner函式#1）
上一小節已介紹了grouping_planner函式,該函式透過呼叫query_planner函式為掃描/連線部分生成最優的未排序和預排序(presorted)路徑,本節主要介紹query_planner函式對簡單SQL如SELECT 2+2;的處理邏輯。
一、重要的資料結構
RelOptInfo
查詢語句經過查詢重寫/表示式簡化/外連線消除等處理後,查詢樹Query已完成規範化,查詢樹中的RangeTblEntry(RTE)資料結構已完成其歷史使命,由此可進入邏輯最佳化處理,在此階段使用RelOptInfo資料結構.
typedef enum RelOptKind
{
RELOPT_BASEREL,//基本關係(如基表/子查詢等)
RELOPT_JOINREL,//連線產生的關係,要注意的是透過連線等方式產生的結果亦可以視為關係
RELOPT_OTHER_MEMBER_REL,
RELOPT_OTHER_JOINREL,
RELOPT_UPPER_REL,//上層的關係
RELOPT_OTHER_UPPER_REL,
RELOPT_DEADREL
} RelOptKind;
/*
* Is the given relation a simple relation i.e a base or "other" member
* relation?
*/
#define IS_SIMPLE_REL(rel) \
((rel)->reloptkind == RELOPT_BASEREL || \
(rel)->reloptkind == RELOPT_OTHER_MEMBER_REL)
/* Is the given relation a join relation? */
#define IS_JOIN_REL(rel) \
((rel)->reloptkind == RELOPT_JOINREL || \
(rel)->reloptkind == RELOPT_OTHER_JOINREL)
/* Is the given relation an upper relation? */
#define IS_UPPER_REL(rel) \
((rel)->reloptkind == RELOPT_UPPER_REL || \
(rel)->reloptkind == RELOPT_OTHER_UPPER_REL)
/* Is the given relation an "other" relation? */
#define IS_OTHER_REL(rel) \
((rel)->reloptkind == RELOPT_OTHER_MEMBER_REL || \
(rel)->reloptkind == RELOPT_OTHER_JOINREL || \
(rel)->reloptkind == RELOPT_OTHER_UPPER_REL)
typedef struct RelOptInfo
{
NodeTag type;//節點標識
RelOptKind reloptkind;//RelOpt型別
/* all relations included in this RelOptInfo */
Relids relids; /*Relids(rtindex)集合 set of base relids (rangetable indexes) */
/* size estimates generated by planner */
double rows; /*結果元組的估算數量 estimated number of result tuples */
/* per-relation planner control flags */
bool consider_startup; /*是否考慮啟動成本?是,需要保留啟動成本低的路徑 keep cheap-startup-cost paths? */
bool consider_param_startup; /*是否考慮引數化?的路徑 ditto, for parameterized paths? */
bool consider_parallel; /*是否考慮並行處理路徑 consider parallel paths? */
/* default result targetlist for Paths scanning this relation */
struct PathTarget *reltarget; /*掃描該Relation時預設的結果 list of Vars/Exprs, cost, width */
/* materialization information */
List *pathlist; /*訪問路徑連結串列 Path structures */
List *ppilist; /*路徑連結串列中使用引數化路徑進行 ParamPathInfos used in pathlist */
List *partial_pathlist; /* partial Paths */
struct Path *cheapest_startup_path;//代價最低的啟動路徑
struct Path *cheapest_total_path;//代價最低的整體路徑
struct Path *cheapest_unique_path;//代價最低的獲取唯一值的路徑
List *cheapest_parameterized_paths;//代價最低的引數化?路徑連結串列
/* parameterization information needed for both base rels and join rels */
/* (see also lateral_vars and lateral_referencers) */
Relids direct_lateral_relids; /*使用lateral語法,需依賴的Relids rels directly laterally referenced */
Relids lateral_relids; /* minimum parameterization of rel */
/* information about a base rel (not set for join rels!) */
//reloptkind=RELOPT_BASEREL時使用的資料結構
Index relid; /* Relation ID */
Oid reltablespace; /* 表空間 containing tablespace */
RTEKind rtekind; /* 基表?子查詢?還是函式等等?RELATION, SUBQUERY, FUNCTION, etc */
AttrNumber min_attr; /* 最小的屬性編號 smallest attrno of rel (often <0) */
AttrNumber max_attr; /* 最大的屬性編號 largest attrno of rel */
Relids *attr_needed; /* 陣列 array indexed [min_attr .. max_attr] */
int32 *attr_widths; /* 屬性寬度 array indexed [min_attr .. max_attr] */
List *lateral_vars; /* 關係依賴的Vars/PHVs LATERAL Vars and PHVs referenced by rel */
Relids lateral_referencers; /*依賴該關係的Relids rels that reference me laterally */
List *indexlist; /* 該關係的IndexOptInfo連結串列 list of IndexOptInfo */
List *statlist; /* 統計資訊連結串列 list of StatisticExtInfo */
BlockNumber pages; /* 塊數 size estimates derived from pg_class */
double tuples; /* 元組數 */
double allvisfrac; /* ? */
PlannerInfo *subroot; /* 如為子查詢,儲存子查詢的root if subquery */
List *subplan_params; /* 如為子查詢,儲存子查詢的引數 if subquery */
int rel_parallel_workers; /* 並行執行,需要多少個workers? wanted number of parallel workers */
/* Information about foreign tables and foreign joins */
//FWD相關資訊
Oid serverid; /* identifies server for the table or join */
Oid userid; /* identifies user to check access as */
bool useridiscurrent; /* join is only valid for current user */
/* use "struct FdwRoutine" to avoid including fdwapi.h here */
struct FdwRoutine *fdwroutine;
void *fdw_private;
/* cache space for remembering if we have proven this relation unique */
//已知的,可保證唯一的Relids連結串列
List *unique_for_rels; /* known unique for these other relid
* set(s) */
List *non_unique_for_rels; /* 已知的,不唯一的Relids連結串列 known not unique for these set(s) */
/* used by various scans and joins: */
List *baserestrictinfo; /* 如為基本關係,儲存約束條件 RestrictInfo structures (if base rel) */
QualCost baserestrictcost; /* 解析約束表示式的成本? cost of evaluating the above */
Index baserestrict_min_security; /* 最低安全等級 min security_level found in
* baserestrictinfo */
List *joininfo; /* 連線語句的約束條件資訊 RestrictInfo structures for join clauses
* involving this rel */
bool has_eclass_joins; /* 是否存在等價類連線? T means joininfo is incomplete */
/* used by partitionwise joins: */
bool consider_partitionwise_join; /* 分割槽? consider partitionwise
* join paths? (if
* partitioned rel) */
Relids top_parent_relids; /* Relids of topmost parents (if "other"
* rel) */
/* used for partitioned relations */
//分割槽表使用
PartitionScheme part_scheme; /* 分割槽的schema Partitioning scheme. */
int nparts; /* 分割槽數 number of partitions */
struct PartitionBoundInfoData *boundinfo; /* 分割槽邊界資訊 Partition bounds */
List *partition_qual; /* 分割槽約束 partition constraint */
struct RelOptInfo **part_rels; /* 分割槽的RelOptInfo陣列 Array of RelOptInfos of partitions,
* stored in the same order of bounds */
List **partexprs; /* 非空分割槽鍵表示式 Non-nullable partition key expressions. */
List **nullable_partexprs; /* 可為空的分割槽鍵表示式 Nullable partition key expressions. */
List *partitioned_child_rels; /* RT Indexes連結串列 List of RT indexes. */
} RelOptInfo;
PathCostComparison
typedef enum
{
COSTS_EQUAL, /* 近似相等 path costs are fuzzily equal */
COSTS_BETTER1, /* 第一個路徑成本較低 first path is cheaper than second */
COSTS_BETTER2, /* 第二個相對較低 second path is cheaper than first */
COSTS_DIFFERENT /* 不管是哪個路徑,成本上都不佔優勢 neither path dominates the other on cost */
} PathCostComparison;
ResultPath
/*
* ResultPath represents use of a Result plan node to compute a variable-free
* targetlist with no underlying tables (a "SELECT expressions" query).
* The query could have a WHERE clause, too, represented by "quals".
*
* Note that quals is a list of bare clauses, not RestrictInfos.
*/
typedef struct ResultPath //表示無基礎表的結果計劃節點
{
Path path;//掃描路徑
List *quals;//where語句表示式,bare clauses, not RestrictInfos
} ResultPath;
二、原始碼解讀
/*
* query_planner
* Generate a path (that is, a simplified plan) for a basic query,
* which may involve joins but not any fancier features.
*
* 為一個基本的查詢(可能涉及連線)生成訪問路徑(也可以視為一個簡化的計劃).
*
* Since query_planner does not handle the toplevel processing (grouping,
* sorting, etc) it cannot select the best path by itself. Instead, it
* returns the RelOptInfo for the top level of joining, and the caller
* (grouping_planner) can choose among the surviving paths for the rel.
*
* query_planner不會處理頂層的處理過程(如最後的分組/排序等操作),因此,不能選擇最優的訪問路徑
* 該函式會返回RelOptInfo給最高層的連線,grouping_planner可以在剩下的路徑中進行選擇
*
* root describes the query to plan
* tlist is the target list the query should produce
* (this is NOT necessarily root->parse->targetList!)
* qp_callback is a function to compute query_pathkeys once it's safe to do so
* qp_extra is optional extra data to pass to qp_callback
*
* root是計劃資訊/tlist是投影列
* qp_callback是計算query_pathkeys的函式/qp_extra是傳遞給qp_callback的函式
*
* Note: the PlannerInfo node also includes a query_pathkeys field, which
* tells query_planner the sort order that is desired in the final output
* plan. This value is *not* available at call time, but is computed by
* qp_callback once we have completed merging the query's equivalence classes.
* (We cannot construct canonical pathkeys until that's done.)
*/
RelOptInfo *
query_planner(PlannerInfo *root, List *tlist,
query_pathkeys_callback qp_callback, void *qp_extra)
{
Query *parse = root->parse;//查詢樹
List *joinlist;
RelOptInfo *final_rel;//結果
Index rti;//RTE的index
double total_pages;//總pages數
/*
* 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)//簡單SQL,無FROM/WHERE語句
{
/* We need a dummy joinrel to describe the empty set of baserels */
final_rel = build_empty_join_rel(root);//建立返回結果
/*
* If query allows parallelism in general, check whether the quals are
* parallel-restricted. (We need not check final_rel->reltarget
* because it's empty at this point. Anything parallel-restricted in
* the query tlist will be dealt with later.)
*/
if (root->glob->parallelModeOK)//並行模式?
final_rel->consider_parallel =
is_parallel_safe(root, parse->jointree->quals);
/* The only path for it is a trivial Result path */
add_path(final_rel, (Path *)
create_result_path(root, final_rel,
final_rel->reltarget,
(List *) parse->jointree->quals));//新增訪問路徑
/* Select cheapest path (pretty easy in this case...) */
set_cheapest(final_rel);//選擇最優的訪問路徑
/*
* We still are required to call qp_callback, in case it's something
* like "SELECT 2+2 ORDER BY 1".
*/
root->canon_pathkeys = NIL;
(*qp_callback) (root, qp_extra);//回撥函式
return final_rel;//返回
}
//其他程式碼
...
}
add_path/set_cheapest
後續再行介紹
create_result_path
/*
* create_result_path
* Creates a path representing a Result-and-nothing-else plan.
*
* This is only used for degenerate cases, such as a query with an empty
* jointree.
*/
ResultPath *
create_result_path(PlannerInfo *root, RelOptInfo *rel,
PathTarget *target, List *resconstantqual)
{
ResultPath *pathnode = makeNode(ResultPath);//結果
pathnode->path.pathtype = T_Result;//掃描路徑型別
pathnode->path.parent = rel;//路徑的partentbuild_empty_join_rel
pathnode->path.pathtarget = target;//目標列
pathnode->path.param_info = NULL; /* there are no other rels... */
pathnode->path.parallel_aware = false;
pathnode->path.parallel_safe = rel->consider_parallel;
pathnode->path.parallel_workers = 0;//並行workers數目,設定為0
pathnode->path.pathkeys = NIL;//
pathnode->quals = resconstantqual;//表示式
/* Hardly worth defining a cost_result() function ... just do it */
pathnode->path.rows = 1;//行數為1
pathnode->path.startup_cost = target->cost.startup;
pathnode->path.total_cost = target->cost.startup +
cpu_tuple_cost + target->cost.per_tuple;
/*
* Add cost of qual, if any --- but we ignore its selectivity, since our
* rowcount estimate should be 1 no matter what the qual is.
*/
if (resconstantqual)
{
QualCost qual_cost;
cost_qual_eval(&qual_cost, resconstantqual, root);
/* resconstantqual is evaluated once at startup */
pathnode->path.startup_cost += qual_cost.startup + qual_cost.per_tuple;
pathnode->path.total_cost += qual_cost.startup + qual_cost.per_tuple;
}
return pathnode;
}
build_empty_join_rel
/*
* build_empty_join_rel
* Build a dummy join relation describing an empty set of base rels.
*
* This is used for queries with empty FROM clauses, such as "SELECT 2+2" or
* "INSERT INTO foo VALUES(...)". We don't try very hard to make the empty
* joinrel completely valid, since no real planning will be done with it ---
* we just need it to carry a simple Result path out of query_planner().
*/
RelOptInfo *
build_empty_join_rel(PlannerInfo *root)
{
RelOptInfo *joinrel;
/* The dummy join relation should be the only one ... */
Assert(root->join_rel_list == NIL);
joinrel = makeNode(RelOptInfo);
joinrel->reloptkind = RELOPT_JOINREL;
joinrel->relids = NULL; /* empty set */
joinrel->rows = 1; /* we produce one row for such cases */
joinrel->rtekind = RTE_JOIN;
joinrel->reltarget = create_empty_pathtarget();
root->join_rel_list = lappend(root->join_rel_list, joinrel);
return joinrel;
}
三、跟蹤分析
(gdb) b query_planner
Breakpoint 1 at 0x76942c: file planmain.c, line 57.
(gdb) c
Continuing.
Breakpoint 1, query_planner (root=0x275c878, tlist=0x277fd78, qp_callback=0x76e97d <standard_qp_callback>,
qp_extra=0x7ffdd435d490) at planmain.c:57
57 Query *parse = root->parse;
(gdb) n
67 if (parse->jointree->fromlist == NIL)
(gdb)
70 final_rel = build_empty_join_rel(root);
(gdb)
78 if (root->glob->parallelModeOK)
#建立的空RELOPT_JOINREL
(gdb) p *final_rel
$4 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x0, rows = 1, consider_startup = false,
consider_param_startup = false, consider_parallel = false, reltarget = 0x277fda8, pathlist = 0x0, ppilist = 0x0,
partial_pathlist = 0x0, cheapest_startup_path = 0x0, cheapest_total_path = 0x0, cheapest_unique_path = 0x0,
cheapest_parameterized_paths = 0x0, direct_lateral_relids = 0x0, lateral_relids = 0x0, relid = 0, reltablespace = 0,
rtekind = RTE_JOIN, min_attr = 0, max_attr = 0, attr_needed = 0x0, attr_widths = 0x0, lateral_vars = 0x0,
lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, allvisfrac = 0, subroot = 0x0,
subplan_params = 0x0, rel_parallel_workers = 0, serverid = 0, userid = 0, useridiscurrent = false, fdwroutine = 0x0,
fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x0, baserestrictcost = {
startup = 0, per_tuple = 0}, baserestrict_min_security = 0, joininfo = 0x0, has_eclass_joins = false,
top_parent_relids = 0x0, part_scheme = 0x0, nparts = 0, boundinfo = 0x0, partition_qual = 0x0, part_rels = 0x0,
partexprs = 0x0, nullable_partexprs = 0x0, partitioned_child_rels = 0x0}
...
(gdb) step
add_path (parent_rel=0x275cc88, new_path=0x275c498) at pathnode.c:424
424 bool accept_new = true; /* unless we find a superior old path */
#建立的path(ResultPath)
(gdb) p *new_path
$6 = {type = T_ResultPath, pathtype = T_Result, parent = 0x275cc88, pathtarget = 0x277fda8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 1, startup_cost = 0, total_cost = 0.01,
pathkeys = 0x0}
(gdb) finish
Run till exit from #0 add_path (parent_rel=0x275cc88, new_path=0x275c498) at pathnode.c:425
query_planner (root=0x275c878, tlist=0x277fd78, qp_callback=0x76e97d <standard_qp_callback>, qp_extra=0x7ffdd435d490)
at planmain.c:89
89 set_cheapest(final_rel);
...
98 return final_rel;
(gdb)
267 }
#返回值
(gdb) p *final_rel
$8 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x0, rows = 1, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x277fda8, pathlist = 0x277fe68, ppilist = 0x0,
partial_pathlist = 0x0, cheapest_startup_path = 0x275c498, cheapest_total_path = 0x275c498, cheapest_unique_path = 0x0,
cheapest_parameterized_paths = 0x277feb8, direct_lateral_relids = 0x0, lateral_relids = 0x0, relid = 0,
reltablespace = 0, rtekind = RTE_JOIN, min_attr = 0, max_attr = 0, attr_needed = 0x0, attr_widths = 0x0,
lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, allvisfrac = 0,
subroot = 0x0, subplan_params = 0x0, rel_parallel_workers = 0, serverid = 0, userid = 0, useridiscurrent = false,
fdwroutine = 0x0, fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x0,
baserestrictcost = {startup = 0, per_tuple = 0}, baserestrict_min_security = 0, joininfo = 0x0, has_eclass_joins = false,
top_parent_relids = 0x0, part_scheme = 0x0, nparts = 0, boundinfo = 0x0, partition_qual = 0x0, part_rels = 0x0,
partexprs = 0x0, nullable_partexprs = 0x0, partitioned_child_rels = 0x0}
(gdb) p *final_rel->cheapest_total_path
$9 = {type = T_ResultPath, pathtype = T_Result, parent = 0x275cc88, pathtarget = 0x277fda8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 1, startup_cost = 0, total_cost = 0.01,
pathkeys = 0x0}
(gdb)
#DONE!
四、參考資料
planmain.c
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374874/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（43）- 查詢語句#28（query_planner函式#5）SQL原始碼函式
- PostgreSQL 原始碼解讀（45）- 查詢語句#30（query_planner函式#6）SQL原始碼函式
- PostgreSQL 原始碼解讀（46）- 查詢語句#31（query_planner函式#7）SQL原始碼函式
- PostgreSQL 原始碼解讀（47）- 查詢語句#32（query_planner函式#8）SQL原始碼函式
- PostgreSQL 原始碼解讀（48）- 查詢語句#33（query_planner函式#9）SQL原始碼函式
- PostgreSQL 原始碼解讀（39）- 查詢語句#24（query_planner函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（40）- 查詢語句#25（query_planner函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（41）- 查詢語句#26（query_planner函式#4）SQL原始碼函式
- PostgreSQL 原始碼解讀（90）- 查詢語句#75（ExecHashJoin函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（88）- 查詢語句#73（SeqNext函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（83）- 查詢語句#68（PortalStart函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（53）- 查詢語句#38（make_one_rel函式#3-順...SQL原始碼函式
- PostgreSQL 原始碼解讀（82）- 查詢語句#67（PortalXXX系列函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（89）- 查詢語句#74（SeqNext函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（91）- 查詢語句#76（ExecHashJoin函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（93）- 查詢語句#77（ExecHashJoin函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（73）- 查詢語句#58（grouping_planner函式...SQL原始碼函式
- PostgreSQL 原始碼解讀（95）- 查詢語句#78（ExecHashJoin函式#4-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（97）- 查詢語句#79（ExecHashJoin函式#5-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（77）- 查詢語句#62（create_plan函式#1-主實...SQL原始碼函式
- PostgreSQL 原始碼解讀（23）- 查詢語句#8（PlannedStmt與QUERY P...SQL原始碼
- PostgreSQL 原始碼解讀（49）- 查詢語句#34（make_one_rel函式#1-概覽）SQL原始碼函式
- PostgreSQL 原始碼解讀（31）- 查詢語句#16（查詢優化-表示式預處理#1）SQL原始碼優化
- PostgreSQL 原始碼解讀（50）- 查詢語句#35（Optimizer Review#1）SQL原始碼View
- PostgreSQL 原始碼解讀（24）- 查詢語句#9（查詢重寫）SQL原始碼
- PostgreSQL 原始碼解讀（70）- 查詢語句#55（make_one_rel函式#20-...SQL原始碼函式
- PostgreSQL 原始碼解讀（71）- 查詢語句#56（make_one_rel函式#21-...SQL原始碼函式
- PostgreSQL 原始碼解讀（67）- 查詢語句#52（make_one_rel函式#17-...SQL原始碼函式
- PostgreSQL 原始碼解讀（68）- 查詢語句#53（make_one_rel函式#18-...SQL原始碼函式
- PostgreSQL 原始碼解讀（69）- 查詢語句#54（make_one_rel函式#19-...SQL原始碼函式
- PostgreSQL 原始碼解讀（66）- 查詢語句#51（make_one_rel函式#16-...SQL原始碼函式
- PostgreSQL 原始碼解讀（79）- 查詢語句#64（create_plan函式#3-Se...SQL原始碼函式
- PostgreSQL 原始碼解讀（80）- 查詢語句#65（create_plan函式#4-Jo...SQL原始碼函式
- PostgreSQL 原始碼解讀（78）- 查詢語句#63（create_plan函式#2-cr...SQL原始碼函式
- PostgreSQL 原始碼解讀（72）- 查詢語句#57（make_one_rel函式#22-...SQL原始碼函式
- PostgreSQL 原始碼解讀（65）- 查詢語句#50（make_one_rel函式#15-...SQL原始碼函式
- PostgreSQL 原始碼解讀（62）- 查詢語句#47（make_one_rel函式#12-...SQL原始碼函式
- PostgreSQL 原始碼解讀（63）- 查詢語句#48（make_one_rel函式#13-...SQL原始碼函式