建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
PostgreSQL的set_base_rel_sizes函數(shù)分析
這篇文章主要講解了“PostgreSQL的set_base_rel_sizes函數(shù)分析”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“PostgreSQL的set_base_rel_sizes函數(shù)分析”吧！
公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)建站是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來(lái)驚喜。創(chuàng)新互聯(lián)建站推出南樂免費(fèi)做網(wǎng)站回饋大家。
在set_base_rel_sizes函數(shù)調(diào)用過(guò)程中，如RTE為子查詢，則生成子查詢的訪問路徑，通過(guò)調(diào)用函數(shù)set_subquery_pathlist實(shí)現(xiàn)。
make_one_rel源代碼:
RelOptInfo *
make_one_rel(PlannerInfo *root, List *joinlist)
{
//...
/*
* Compute size estimates and consider_parallel flags for each base rel,
* then generate access paths.
*/
set_base_rel_sizes(root);//估算Relation的Size并且設(shè)置consider_parallel標(biāo)記
//...
}一、數(shù)據(jù)結(jié)構(gòu)
RelOptInfo
如前所述,RelOptInfo數(shù)據(jù)結(jié)構(gòu)貫穿整個(gè)優(yōu)化過(guò)程.
typedef struct RelOptInfo
{
NodeTag type;//節(jié)點(diǎn)標(biāo)識(shí)
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; /*結(jié)果元組的估算數(shù)量 estimated number of result tuples */
/* per-relation planner control flags */
bool consider_startup; /*是否考慮啟動(dòng)成本?是,需要保留啟動(dòng)成本低的路徑 keep cheap-startup-cost paths? */
bool consider_param_startup; /*是否考慮參數(shù)化?的路徑 ditto, for parameterized paths? */
bool consider_parallel; /*是否考慮并行處理路徑 consider parallel paths? */
/* default result targetlist for Paths scanning this relation */
struct PathTarget *reltarget; /*掃描該Relation時(shí)默認(rèn)的結(jié)果 list of Vars/Exprs, cost, width */
/* materialization information */
List *pathlist; /*訪問路徑鏈表 Path structures */
List *ppilist; /*路徑鏈表中使用參數(shù)化路徑進(jìn)行 ParamPathInfos used in pathlist */
List *partial_pathlist; /* partial Paths */
struct Path *cheapest_startup_path;//代價(jià)最低的啟動(dòng)路徑
struct Path *cheapest_total_path;//代價(jià)最低的整體路徑
struct Path *cheapest_unique_path;//代價(jià)最低的獲取唯一值的路徑
List *cheapest_parameterized_paths;//代價(jià)最低的參數(shù)化路徑鏈表
/* parameterization information needed for both base rels and join rels */
/* (see also lateral_vars and lateral_referencers) */
Relids direct_lateral_relids; /*使用lateral語(yǔ)法,需依賴的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時(shí)使用的數(shù)據(jù)結(jié)構(gòu)
Index relid; /* Relation ID */
Oid reltablespace; /* 表空間 containing tablespace */
RTEKind rtekind; /* 基表?子查詢?還是函數(shù)等等?RELATION, SUBQUERY, FUNCTION, etc */
AttrNumber min_attr; /* 最小的屬性編號(hào) smallest attrno of rel (often <0) */
AttrNumber max_attr; /* 最大的屬性編號(hào) largest attrno of rel */
Relids *attr_needed; /* 數(shù)組 array indexed [min_attr .. max_attr] */
int32 *attr_widths; /* 屬性寬度 array indexed [min_attr .. max_attr] */
List *lateral_vars; /* 關(guān)系依賴的Vars/PHVs LATERAL Vars and PHVs referenced by rel */
Relids lateral_referencers; /*依賴該關(guān)系的Relids rels that reference me laterally */
List *indexlist; /* 該關(guān)系的IndexOptInfo鏈表 list of IndexOptInfo */
List *statlist; /* 統(tǒng)計(jì)信息鏈表 list of StatisticExtInfo */
BlockNumber pages; /* 塊數(shù) size estimates derived from pg_class */
double tuples; /* 元組數(shù) */
double allvisfrac; /* ? */
PlannerInfo *subroot; /* 如為子查詢,存儲(chǔ)子查詢的root if subquery */
List *subplan_params; /* 如為子查詢,存儲(chǔ)子查詢的參數(shù) if subquery */
int rel_parallel_workers; /* 并行執(zhí)行,需要多少個(gè)workers? wanted number of parallel workers */
/* Information about foreign tables and foreign joins */
//FDW相關(guān)信息
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; /* 已知的,返回的數(shù)據(jù)不唯一的Relids鏈表 known not unique for these set(s) */
/* used by various scans and joins: */
List *baserestrictinfo; /* 如為基本關(guān)系,則存儲(chǔ)約束條件 RestrictInfo structures (if base rel) */
QualCost baserestrictcost; /* 解析約束表達(dá)式的成本? cost of evaluating the above */
Index baserestrict_min_security; /* 最低安全等級(jí) min security_level found in
* baserestrictinfo */
List *joininfo; /* 連接語(yǔ)句的約束條件信息 RestrictInfo structures for join clauses
* involving this rel */
bool has_eclass_joins; /* 是否存在等價(jià)類連接? True意味著joininfo并不完整,,T means joininfo is incomplete */
/* used by partitionwise joins: */
//是否嘗試partitionwise連接,這是PG 11的一個(gè)新特性.
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 */
//分區(qū)表使用
PartitionScheme part_scheme; /* 分區(qū)的schema Partitioning scheme. */
int nparts; /* 分區(qū)數(shù) number of partitions */
struct PartitionBoundInfoData *boundinfo; /* 分區(qū)邊界信息 Partition bounds */
List *partition_qual; /* 分區(qū)約束 partition constraint */
struct RelOptInfo **part_rels; /* 分區(qū)的RelOptInfo數(shù)組 Array of RelOptInfos of partitions,
* stored in the same order of bounds */
List **partexprs; /* 非空分區(qū)鍵表達(dá)式 Non-nullable partition key expressions. */
List **nullable_partexprs; /* 可為空的分區(qū)鍵表達(dá)式 Nullable partition key expressions. */
List *partitioned_child_rels; /* RT Indexes鏈表 List of RT indexes. */
} RelOptInfo;二、源碼解讀
set_rel_size
如前所述,set_rel_size函數(shù)估算關(guān)系的大小,如RTE為子查詢,則調(diào)用set_subquery_pathlist方法生成子查詢?cè)L問路徑,相關(guān)代碼如下:
/*
* set_rel_size
* Set size estimates for a base relation
*/
static void
set_rel_size(PlannerInfo *root, RelOptInfo *rel,
Index rti, RangeTblEntry *rte)
{
if (rel->reloptkind == RELOPT_BASEREL &&
relation_excluded_by_constraints(root, rel, rte))
{
//...
}
else
{
switch (rel->rtekind)
{
case //...
//...
case RTE_SUBQUERY://子查詢
/*
* Subqueries don't support making a choice between
* parameterized and unparameterized paths, so just go ahead
* and build their paths immediately.
*/
set_subquery_pathlist(root, rel, rti, rte);//生成子查詢?cè)L問路徑
break;
case ...
}
//...
}
//...
}set_subquery_pathlist
生成子查詢的掃描路徑,在生成過(guò)程中嘗試下推外層的限制條件(減少參與運(yùn)算的元組數(shù)量),并調(diào)用subquery_planner生成執(zhí)行計(jì)劃.
/*
* set_subquery_pathlist
* Generate SubqueryScan access paths for a subquery RTE
* 生成子查詢的掃描路徑
*
* We don't currently support generating parameterized paths for subqueries
* by pushing join clauses down into them; it seems too expensive to re-plan
* the subquery multiple times to consider different alternatives.
* (XXX that could stand to be reconsidered, now that we use Paths.)
* So the paths made here will be parameterized if the subquery contains
* LATERAL references, otherwise not. As long as that's true, there's no need
* for a separate set_subquery_size phase: just make the paths right away.
*/
static void
set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
Index rti, RangeTblEntry *rte)
{
Query *parse = root->parse;
Query *subquery = rte->subquery;
Relids required_outer;
pushdown_safety_info safetyInfo;
double tuple_fraction;
RelOptInfo *sub_final_rel;
ListCell *lc;
/*
* Must copy the Query so that planning doesn't mess up the RTE contents
* (really really need to fix the planner to not scribble on its input,
* someday ... but see remove_unused_subquery_outputs to start with).
*/
subquery = copyObject(subquery);//拷貝
/*
* If it's a LATERAL subquery, it might contain some Vars of the current
* query level, requiring it to be treated as parameterized, even though
* we don't support pushing down join quals into subqueries.
*/
required_outer = rel->lateral_relids;//外層的Relids
/*
* Zero out result area for subquery_is_pushdown_safe, so that it can set
* flags as needed while recursing. In particular, we need a workspace
* for keeping track of unsafe-to-reference columns. unsafeColumns[i]
* will be set true if we find that output column i of the subquery is
* unsafe to use in a pushed-down qual.
*/
memset(&safetyInfo, 0, sizeof(safetyInfo));
safetyInfo.unsafeColumns = (bool *)
palloc0((list_length(subquery->targetList) + 1) * sizeof(bool));
/*
* If the subquery has the "security_barrier" flag, it means the subquery
* originated from a view that must enforce row level security. Then we
* must not push down quals that contain leaky functions. (Ideally this
* would be checked inside subquery_is_pushdown_safe, but since we don't
* currently pass the RTE to that function, we must do it here.)
*/
safetyInfo.unsafeLeaky = rte->security_barrier;
/*
* If there are any restriction clauses that have been attached to the
* subquery relation, consider pushing them down to become WHERE or HAVING
* quals of the subquery itself. This transformation is useful because it
* may allow us to generate a better plan for the subquery than evaluating
* all the subquery output rows and then filtering them.
* 限制條件是否可以下推到子查詢中?如可以,優(yōu)化器有可能生成更好的執(zhí)行計(jì)劃
*
* There are several cases where we cannot push down clauses. Restrictions
* involving the subquery are checked by subquery_is_pushdown_safe().
* Restrictions on individual clauses are checked by
* qual_is_pushdown_safe(). Also, we don't want to push down
* pseudoconstant clauses; better to have the gating node above the
* subquery.
*
* Non-pushed-down clauses will get evaluated as qpquals of the
* SubqueryScan node.
*
* XXX Are there any cases where we want to make a policy decision not to
* push down a pushable qual, because it'd result in a worse plan?
*/
if (rel->baserestrictinfo != NIL &&
subquery_is_pushdown_safe(subquery, subquery, &safetyInfo))
{
//可以下推限制條件
/* OK to consider pushing down individual quals */
List *upperrestrictlist = NIL;
ListCell *l;
foreach(l, rel->baserestrictinfo)//遍歷子查詢上的限制條件
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
Node *clause = (Node *) rinfo->clause;
if (!rinfo->pseudoconstant &&
qual_is_pushdown_safe(subquery, rti, clause, &safetyInfo))
{
/* Push it down */
subquery_push_qual(subquery, rte, rti, clause);//下推限制條件
}
else
{
/* Keep it in the upper query */
upperrestrictlist = lappend(upperrestrictlist, rinfo);//保留在上層中
}
}
rel->baserestrictinfo = upperrestrictlist;
/* We don't bother recomputing baserestrict_min_security */
}
pfree(safetyInfo.unsafeColumns);
/*
* The upper query might not use all the subquery's output columns; if
* not, we can simplify.
*/
remove_unused_subquery_outputs(subquery, rel);
/*
* We can safely pass the outer tuple_fraction down to the subquery if the
* outer level has no joining, aggregation, or sorting to do. Otherwise
* we'd better tell the subquery to plan for full retrieval. (XXX This
* could probably be made more intelligent ...)
*/
if (parse->hasAggs ||
parse->groupClause ||
parse->groupingSets ||
parse->havingQual ||
parse->distinctClause ||
parse->sortClause ||
has_multiple_baserels(root))
tuple_fraction = 0.0; /* default case */
else
tuple_fraction = root->tuple_fraction;
/* plan_params should not be in use in current query level */
Assert(root->plan_params == NIL);
/* Generate a subroot and Paths for the subquery */
rel->subroot = subquery_planner(root->glob, subquery,
root,
false, tuple_fraction);//調(diào)用subquery_planner獲取子查詢的執(zhí)行計(jì)劃
/* Isolate the params needed by this specific subplan */
rel->subplan_params = root->plan_params;
root->plan_params = NIL;
/*
* It's possible that constraint exclusion proved the subquery empty. If
* so, it's desirable to produce an unadorned dummy path so that we will
* recognize appropriate optimizations at this query level.
*/
sub_final_rel = fetch_upper_rel(rel->subroot, UPPERREL_FINAL, NULL);//子查詢返回的最終關(guān)系
if (IS_DUMMY_REL(sub_final_rel))
{
set_dummy_rel_pathlist(rel);
return;
}
/*
* Mark rel with estimated output rows, width, etc. Note that we have to
* do this before generating outer-query paths, else cost_subqueryscan is
* not happy.
*/
set_subquery_size_estimates(root, rel);//設(shè)置子查詢的估算信息
/*
* For each Path that subquery_planner produced, make a SubqueryScanPath
* in the outer query.
*/
foreach(lc, sub_final_rel->pathlist)//遍歷最終關(guān)系的訪問路徑
{
Path *subpath = (Path *) lfirst(lc);
List *pathkeys;
/* Convert subpath's pathkeys to outer representation */
//轉(zhuǎn)換pathkeys為外層的表示法
pathkeys = convert_subquery_pathkeys(root,
rel,
subpath->pathkeys,
make_tlist_from_pathtarget(subpath->pathtarget));
/* Generate outer path using this subpath */
add_path(rel, (Path *)
create_subqueryscan_path(root, rel, subpath,
pathkeys, required_outer));//通過(guò)子查詢路徑生成外層訪問路徑
}
/* If outer rel allows parallelism, do same for partial paths. */
if (rel->consider_parallel && bms_is_empty(required_outer))//是否可以并行處理
{
/* If consider_parallel is false, there should be no partial paths. */
Assert(sub_final_rel->consider_parallel ||
sub_final_rel->partial_pathlist == NIL);
/* Same for partial paths. */
foreach(lc, sub_final_rel->partial_pathlist)
{
Path *subpath = (Path *) lfirst(lc);
List *pathkeys;
/* Convert subpath's pathkeys to outer representation */
pathkeys = convert_subquery_pathkeys(root,
rel,
subpath->pathkeys,
make_tlist_from_pathtarget(subpath->pathtarget));
/* Generate outer path using this subpath */
add_partial_path(rel, (Path *)
create_subqueryscan_path(root, rel, subpath,
pathkeys,
required_outer));
}
}
}
//-------------------------------------------------------- create_subqueryscan_path
/*
* create_subqueryscan_path
* Creates a path corresponding to a scan of a subquery,
* returning the pathnode.
*/
SubqueryScanPath *
create_subqueryscan_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
List *pathkeys, Relids required_outer)
{
SubqueryScanPath *pathnode = makeNode(SubqueryScanPath);
pathnode->path.pathtype = T_SubqueryScan;//路徑類型:子查詢掃描
pathnode->path.parent = rel;//父RelOptInfo
pathnode->path.pathtarget = rel->reltarget;//投影列
pathnode->path.param_info = get_baserel_parampathinfo(root, rel,
required_outer);//參數(shù)化信息
pathnode->path.parallel_aware = false;//并行相關(guān)參數(shù)
pathnode->path.parallel_safe = rel->consider_parallel &&
subpath->parallel_safe;
pathnode->path.parallel_workers = subpath->parallel_workers;
pathnode->path.pathkeys = pathkeys;//排序鍵
pathnode->subpath = subpath;//子訪問路徑
cost_subqueryscan(pathnode, root, rel, pathnode->path.param_info);//子查詢的成本
return pathnode;
}
//-------------------------------------------------------- cost_subqueryscan
/*
* cost_subqueryscan
* Determines and returns the cost of scanning a subquery RTE.
*
* 'baserel' is the relation to be scanned
* 'param_info' is the ParamPathInfo if this is a parameterized path, else NULL
*/
void
cost_subqueryscan(SubqueryScanPath *path, PlannerInfo *root,
RelOptInfo *baserel, ParamPathInfo *param_info)
{
Cost startup_cost;
Cost run_cost;
QualCost qpqual_cost;
Cost cpu_per_tuple;
/* Should only be applied to base relations that are subqueries */
Assert(baserel->relid > 0);
Assert(baserel->rtekind == RTE_SUBQUERY);
/* Mark the path with the correct row estimate */
if (param_info)
path->path.rows = param_info->ppi_rows;
else
path->path.rows = baserel->rows;
/*
* Cost of path is cost of evaluating the subplan, plus cost of evaluating
* any restriction clauses and tlist that will be attached to the
* SubqueryScan node, plus cpu_tuple_cost to account for selection and
* projection overhead.
*/
path->path.startup_cost = path->subpath->startup_cost;
path->path.total_cost = path->subpath->total_cost;
get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);
startup_cost = qpqual_cost.startup;
cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
run_cost = cpu_per_tuple * baserel->tuples;
/* tlist eval costs are paid per output row, not per tuple scanned */
startup_cost += path->path.pathtarget->cost.startup;
run_cost += path->path.pathtarget->cost.per_tuple * path->path.rows;
path->path.startup_cost += startup_cost;
path->path.total_cost += startup_cost + run_cost;
}三、跟蹤分析
測(cè)試腳本如下:
select t1.*,t2.dwbh,t2.counter from t_dwxx t1, (select dwbh,count(*) as counter from t_grxx group by dwbh) t2 where t1.dwbh = t2.dwbh and t1.dwbh = '1001';
啟動(dòng)gdb:
(gdb) c Continuing. Breakpoint 1, set_subquery_pathlist (root=0x2d749b0, rel=0x2d34dd0, rti=2, rte=0x2d341a0) at allpaths.c:2082 2082 Query *parse = root->parse;
進(jìn)入函數(shù)set_subquery_pathlist,輸入?yún)?shù)中的root->simple_rel_array[2],rtekind為RTE_SUBQUERY子查詢
(gdb) p *root->simple_rel_array[2]
$13 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2d6a428, rows = 0, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x2d6a440, 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 = 2, reltablespace = 0,
rtekind = RTE_SUBQUERY, min_attr = 0, max_attr = 2, attr_needed = 0x2d69b00, attr_widths = 0x2d69b50, lateral_vars = 0x0,
lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, allvisfrac = 0, subroot = 0x0,
subplan_params = 0x0, rel_parallel_workers = -1, serverid = 0, userid = 0, useridiscurrent = false, fdwroutine = 0x0,
fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x2d6b648, baserestrictcost = {
startup = 0, per_tuple = 0}, baserestrict_min_security = 0, joininfo = 0x0, has_eclass_joins = true,
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 *rte
$10 = {type = T_RangeTblEntry, rtekind = RTE_SUBQUERY, relid = 0, relkind = 0 '\000', tablesample = 0x0,
subquery = 0x2d342b0, security_barrier = false, jointype = JOIN_INNER, joinaliasvars = 0x0, functions = 0x0,
funcordinality = false, tablefunc = 0x0, values_lists = 0x0, ctename = 0x0, ctelevelsup = 0, self_reference = false,
coltypes = 0x0, coltypmods = 0x0, colcollations = 0x0, enrname = 0x0, enrtuples = 0, alias = 0x2c82728, eref = 0x2d35328,
lateral = false, inh = false, inFromCl = true, requiredPerms = 0, checkAsUser = 0, selectedCols = 0x0,
insertedCols = 0x0, updatedCols = 0x0, securityQuals = 0x0}下推限制條件:
... (gdb) n 2157 qual_is_pushdown_safe(subquery, rti, clause, &safetyInfo)) (gdb) 2156 if (!rinfo->pseudoconstant && (gdb) 2160 subquery_push_qual(subquery, rte, rti, clause);
tuple_fraction設(shè)置為0.0
... (gdb) 2193 tuple_fraction = 0.0; /* default case */
調(diào)用subquery_planner獲取執(zhí)行計(jì)劃:
(gdb) n 2201 rel->subroot = subquery_planner(root->glob, subquery,
獲取子查詢生成的最終關(guān)系,reloptkind為RELOPT_UPPER_REL
...
(gdb)
2214 sub_final_rel = fetch_upper_rel(rel->subroot, UPPERREL_FINAL, NULL);
(gdb)
2216 if (IS_DUMMY_REL(sub_final_rel))
(gdb) p *sub_final_rel
$16 = {type = T_RelOptInfo, reloptkind = RELOPT_UPPER_REL, relids = 0x0, rows = 0, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x2d7bd50, pathlist = 0x2d7be10, ppilist = 0x0,
partial_pathlist = 0x0, cheapest_startup_path = 0x2d7aaa8, cheapest_total_path = 0x2d7aaa8, cheapest_unique_path = 0x0,
cheapest_parameterized_paths = 0x2d7be60, direct_lateral_relids = 0x0, lateral_relids = 0x0, relid = 0,
reltablespace = 0, rtekind = RTE_RELATION, 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 *sub_final_rel->cheapest_total_path
$17 = {type = T_AggPath, pathtype = T_Agg, parent = 0x2d7b6d0, pathtarget = 0x2d7adc8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.29249999999999998,
total_cost = 20.143376803383145, pathkeys = 0x0}通過(guò)子查詢?cè)L問路徑生成外層的訪問路徑
(gdb) n 2227 set_subquery_size_estimates(root, rel); (gdb) 2233 foreach(lc, sub_final_rel->pathlist) (gdb) 2235 Path *subpath = (Path *) lfirst(lc); (gdb) 2239 pathkeys = convert_subquery_pathkeys(root, (gdb) 2246 create_subqueryscan_path(root, rel, subpath, (gdb) 2245 add_path(rel, (Path *) (gdb) 2233 foreach(lc, sub_final_rel->pathlist)
完成函數(shù)調(diào)用,結(jié)束處理
(gdb) set_rel_size (root=0x2d749b0, rel=0x2d34dd0, rti=2, rte=0x2d341a0) at allpaths.c:380 380 break;
執(zhí)行計(jì)劃如下:
testdb=# explain verbose select t1.*,t2.dwbh,t2.counter from t_dwxx t1,(select dwbh,count(*) as counter from t_grxx group by dwbh) t2 where t1.dwbh = t2.dwbh and t1.dwbh = '1001'; QUERY PLAN -------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.58..28.65 rows=10 width=32) Output: t1.dwmc, t1.dwbh, t1.dwdz, t_grxx.dwbh, (count(*)) -> Index Scan using t_dwxx_pkey on public.t_dwxx t1 (cost=0.29..8.30 rows=1 width=20) Output: t1.dwmc, t1.dwbh, t1.dwdz Index Cond: ((t1.dwbh)::text = '1001'::text) -> GroupAggregate (cost=0.29..20.14 rows=10 width=12) Output: t_grxx.dwbh, count(*) Group Key: t_grxx.dwbh -> Index Only Scan using idx_t_dwxx_grbh on public.t_grxx (cost=0.29..19.99 rows=10 width=4) Output: t_grxx.dwbh Index Cond: (t_grxx.dwbh = '1001'::text) (11 rows)
感謝各位的閱讀，以上就是“PostgreSQL的set_base_rel_sizes函數(shù)分析”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)PostgreSQL的set_base_rel_sizes函數(shù)分析這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
文章名稱：PostgreSQL的set_base_rel_sizes函數(shù)分析
網(wǎng)頁(yè)地址：http://www.ef60e0e.cn/article/ghsgho.html
