PostgreSQL 原始碼解讀（22）- 查詢語句#7（PlannedStmt結構詳解-日誌分析）
本文透過分析日誌輸出簡單介紹了PG根據查詢樹生成的執行計劃的詳細結構。
一、PlannedStmt結構
生成執行計劃在函式pg_plan_queries中實現,返回的是連結串列querytree_list,連結串列中的元素是PlannedStmt.
回顧PlannedStmt結構:
/* ----------------
* PlannedStmt node
*
* The output of the planner is a Plan tree headed by a PlannedStmt node.
* PlannedStmt holds the "one time" information needed by the executor.
*
* For simplicity in APIs, we also wrap utility statements in PlannedStmt
* nodes; in such cases, commandType == CMD_UTILITY, the statement itself
* is in the utilityStmt field, and the rest of the struct is mostly dummy.
* (We do use canSetTag, stmt_location, stmt_len, and possibly queryId.)
* ----------------
*/
typedef struct PlannedStmt
{
NodeTag type;//這是節點的識別符號Tag
//命令型別
CmdType commandType; /* select|insert|update|delete|utility */
//查詢ID
uint64 queryId; /* query identifier (copied from Query) */
//是否insert|update|delete命令的RETURNING?(有待進一步研究)
bool hasReturning; /* is it insert|update|delete RETURNING? */
//CTE= Common Table Expressions(With語句)
//WITH語句中是否存在insert|update|delete關鍵字?
bool hasModifyingCTE; /* has insert|update|delete in WITH? */
//TODO
bool canSetTag; /* do I set the command result tag? */
//TODO
bool transientPlan; /* redo plan when TransactionXmin changes? */
//TODO
bool dependsOnRole; /* is plan specific to current role? */
//並行模式?
bool parallelModeNeeded; /* parallel mode required to execute? */
//使用哪種形式的JIT
int jitFlags; /* which forms of JIT should be performed */
//Plan節點樹,這是SQL語句的關鍵資訊
struct Plan *planTree; /* tree of Plan nodes */
//SQL所依賴的RTE(包括子查詢等)
List *rtable; /* list of RangeTblEntry nodes */
//INSERT/UPDATE/DELETE命令所影響的關係在rtable中的位置(index)
/* rtable indexes of target relations for INSERT/UPDATE/DELETE */
List *resultRelations; /* integer list of RT indexes, or NIL */
//TODO
/*
* rtable indexes of non-leaf target relations for UPDATE/DELETE on all
* the partitioned tables mentioned in the query.
*/
List *nonleafResultRelations;
//UPDATE/DELETE命令相關
/*
* rtable indexes of root target relations for UPDATE/DELETE; this list
* maintains a subset of the RT indexes in nonleafResultRelations,
* indicating the roots of the respective partition hierarchies.
*/
List *rootResultRelations;
//子查詢計劃連結串列
List *subplans; /* Plan trees for SubPlan expressions; note
* that some could be NULL */
//TODO
Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */
//TODO
List *rowMarks; /* a list of PlanRowMark's */
//Plan相關的關係OIDs(Relation OIDs),透過pg_class可以查詢
List *relationOids; /* OIDs of relations the plan depends on */
//TODO
List *invalItems; /* other dependencies, as PlanInvalItems */
//TODO
List *paramExecTypes; /* type OIDs for PARAM_EXEC Params */
//工具類語句(如CREATE TABLE等)節點
Node *utilityStmt; /* non-null if this is utility stmt */
//SQL語句的起始位置?
/* statement location in source string (copied from Query) */
int stmt_location; /* start location, or -1 if unknown */
//SQL語句的長度
int stmt_len; /* length in bytes; 0 means "rest of string" */
} PlannedStmt;
/* macro for fetching the Plan associated with a SubPlan node */
#define exec_subplan_get_plan(plannedstmt, subplan) \
((Plan *) list_nth((plannedstmt)->subplans, (subplan)->plan_id - 1))
二、日誌分析
測試前重置了樣例資料庫,因此相關資訊如資料表Oid與上一節略有不同,敬請注意
SQL語句:
select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
from t_dwxx,t_grxx,t_jfxx
where t_dwxx.dwbh = t_grxx.dwbh
and t_grxx.grbh = t_jfxx.grbh
and t_dwxx.dwbh IN ('1001','1002')
order by t_grxx.grbh
limit 8;
select * from (
select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
where t_dwxx.dwbh IN ('1001')
union all
select t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je
from t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh
inner join t_jfxx on t_grxx.grbh = t_jfxx.grbh
where t_dwxx.dwbh IN ('1002')
) as ret
order by ret.grbh
limit 4;
該SQL語句的計劃樹如下圖所示:
計劃樹
查詢樹中的詳細資料結構可以結合相應的日誌進行分析:
1.PlannedStmt
總覽
如上圖所示（planTree、rtable已摺疊），commandType值為1,對應的是SELECT,queryID為0,SQL語句長度為455,relationOids的值為(o 16391 16394 16397 16391 16394 16397)，分別對應t_dwxx/t_grxx/t_jfxx三張表.
testdb=# select relname from pg_class where oid in (16391,16394,16397); relname --------- t_dwxx t_grxx t_jfxx (3 rows)
下面詳細看看rtable和planTree中的結構.
2.rtable
rtable連結串列中的元素為RangeTblEntry,下面先回顧RTE的資料結構:
RangeTblEntry
*/
typedef enum RTEKind
{
RTE_RELATION, /* ordinary relation reference */ //這是常規的Relation,即資料表
RTE_SUBQUERY, /* subquery in FROM */ //出現在From語句中的子查詢
RTE_JOIN, /* join */ //連線
RTE_FUNCTION, /* function in FROM */ //FROM中的函式
RTE_TABLEFUNC, /* TableFunc(.., column list) */ //函式
RTE_VALUES, /* VALUES (<exprlist>), (<exprlist>), ... */ //
RTE_CTE, /* common table expr (WITH list element) */ //WITH語句
RTE_NAMEDTUPLESTORE /* tuplestore, e.g. for AFTER triggers */ //
} RTEKind;//RTE型別,本例中涉及三種型別,RTE_RELATION/RTE_SUBQUERY/RTE_JOIN
typedef struct RangeTblEntry
{
//RTEKind=*時使用的結構
NodeTag type;//節點標識
RTEKind rtekind; /* see above */ //RTE型別
/*
* XXX the fields applicable to only some rte kinds should be merged into
* a union. I didn't do this yet because the diffs would impact a lot of
* code that is being actively worked on. FIXME someday.
*/
/*
* Fields valid for a plain relation RTE (else zero):
*
* As a special case, RTE_NAMEDTUPLESTORE can also set relid to indicate
* that the tuple format of the tuplestore is the same as the referenced
* relation. This allows plans referencing AFTER trigger transition
* tables to be invalidated if the underlying table is altered.
*/
Oid relid; /* OID of the relation */ //關係的Oid
char relkind; /* relation kind (see pg_class.relkind) */ //pg_class中的relkind,在這裡是'r'
struct TableSampleClause *tablesample; /* sampling info, or NULL */ //取樣語句
/*
* Fields valid for a subquery RTE (else NULL):
*/
//RTEKind=RTE_SUBQUERY時使用的結構
Query *subquery; /* the sub-query */ //子查詢,如RTEKind=RTE_SUBQUERY時
bool security_barrier; /* is from security_barrier view? */
/*
* Fields valid for a join RTE (else NULL/zero):
*
* joinaliasvars is a list of (usually) Vars corresponding to the columns
* of the join result. An alias Var referencing column K of the join
* result can be replaced by the K'th element of joinaliasvars --- but to
* simplify the task of reverse-listing aliases correctly, we do not do
* that until planning time. In detail: an element of joinaliasvars can
* be a Var of one of the join's input relations, or such a Var with an
* implicit coercion to the join's output column type, or a COALESCE
* expression containing the two input column Vars (possibly coerced).
* Within a Query loaded from a stored rule, it is also possible for
* joinaliasvars items to be null pointers, which are placeholders for
* (necessarily unreferenced) columns dropped since the rule was made.
* Also, once planning begins, joinaliasvars items can be almost anything,
* as a result of subquery-flattening substitutions.
*/
//RTEKind=RTE_JOIN時使用的結構
JoinType jointype; /* type of join */ //連線型別
List *joinaliasvars; /* list of alias-var expansions */ //
/*
* Fields valid for a function RTE (else NIL/zero):
*
* When funcordinality is true, the eref->colnames list includes an alias
* for the ordinality column. The ordinality column is otherwise
* implicit, and must be accounted for "by hand" in places such as
* expandRTE().
*/
//RTEKind=RTE_FUNCTION時使用的結構
List *functions; /* list of RangeTblFunction nodes */
bool funcordinality; /* is this called WITH ORDINALITY? */
//
/*
* Fields valid for a TableFunc RTE (else NULL):
*/
//RTEKind=RTE_TABLEFUNC時使用的結構
TableFunc *tablefunc;
/*
* Fields valid for a values RTE (else NIL):
*/
//RTEKind=RTE_VALUES時使用的結構
List *values_lists; /* list of expression lists */
/*
* Fields valid for a CTE RTE (else NULL/zero):
*/
//RTEKind=RTE_CTE時使用的結構
char *ctename; /* name of the WITH list item */
Index ctelevelsup; /* number of query levels up */
bool self_reference; /* is this a recursive self-reference? */
/*
* Fields valid for table functions, values, CTE and ENR RTEs (else NIL):
*
* We need these for CTE RTEs so that the types of self-referential
* columns are well-defined. For VALUES RTEs, storing these explicitly
* saves having to re-determine the info by scanning the values_lists. For
* ENRs, we store the types explicitly here (we could get the information
* from the catalogs if 'relid' was supplied, but we'd still need these
* for TupleDesc-based ENRs, so we might as well always store the type
* info here).
*
* For ENRs only, we have to consider the possibility of dropped columns.
* A dropped column is included in these lists, but it will have zeroes in
* all three lists (as well as an empty-string entry in eref). Testing
* for zero coltype is the standard way to detect a dropped column.
*/
//RTEKind=RTE_FUNCTION/RTE_VALUES/RTE_CTE時使用的結構
List *coltypes; /* OID list of column type OIDs */
List *coltypmods; /* integer list of column typmods */
List *colcollations; /* OID list of column collation OIDs */
/*
* Fields valid for ENR RTEs (else NULL/zero):
*/
char *enrname; /* name of ephemeral named relation */
double enrtuples; /* estimated or actual from caller */
/*
* Fields valid in all RTEs:
*/
//RTEKind=*時使用的結構
Alias *alias; /* user-written alias clause, if any */
Alias *eref; /* expanded reference names */
bool lateral; /* subquery, function, or values is LATERAL? */
bool inh; /* inheritance requested? */
bool inFromCl; /* present in FROM clause? */
//許可權控制
AclMode requiredPerms; /* bitmask of required access permissions */
Oid checkAsUser; /* if valid, check access as this role */
Bitmapset *selectedCols; /* columns needing SELECT permission */
Bitmapset *insertedCols; /* columns needing INSERT permission */
Bitmapset *updatedCols; /* columns needing UPDATE permission */
List *securityQuals; /* security barrier quals to apply, if any */
} RangeTblEntry;
rtable儲存的是SQL語句執行時所依賴的RangeTblEntry(簡稱RTE),就本例而言,有13個RTE.
第1個RTE
:rtable (
{RTE ---------->第1個RTE
:alias
{ALIAS
:aliasname ret //使用者自定義的別名:"ret"
:colnames <>
}
:eref
{ALIAS
:aliasname ret //使用者自定義的別名:"ret"
:colnames (""dwmc"" ""grbh"" ""xm"" ""ny"" ""je"") //資料列
}
:rtekind 1 //RTE_SUBQUERY,子查詢(注意:列舉從0開始)
:subquery <>
:security_barrier false
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
第2個RTE
{RTE ---------->第2個RTE
:alias
{ALIAS
:aliasname *SELECT*\ 1 //第一個子查詢
:colnames <>
}
:eref
{ALIAS
:aliasname *SELECT*\ 1
:colnames (""dwmc"" ""grbh"" ""xm"" ""ny"" ""je"")
}
:rtekind 1 //RTE_SUBQUERY
:subquery <>
:security_barrier false
:lateral false
:inh false
:inFromCl false
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
集合UNION操作對應的第一個子查詢
第3個RTE
類第2個RTE,不同的地方是aliasname為 *SELECT*\ 2 集合UNION操作對應的第二個子查詢
第4個RTE
{RTE ---------->第4個RTE
:alias <>
:eref
{ALIAS
:aliasname t_dwxx //單位資訊表
:colnames (""dwmc"" ""dwbh"" ""dwdz"") //資料列有dwmc/dwbh/dwdz
}
:rtekind 0 //RTE_RELATION,關係/資料表
:relid 16391 //這是t_dwxx的Oid
:relkind r //pg_class中的relkind
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10) //
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
第5個RTE
t_grxx表,參照第4個RTE
第6個RTE
{RTE ---------->第6個RTE
:alias <>
:eref
{ALIAS
:aliasname unnamed_join //未命名的join(連線)
:colnames (""dwmc"" ""dwbh"" ""dwdz"" ""dwbh"" ""grbh"" ""xm"" ""nl"") //單位&個人資訊表的資料列
}
:rtekind 2 //RTE_JOIN,連線
:jointype 0 //JOIN_INNER,內連線
:joinaliasvars <>
:lateral false
:inh false
:inFromCl true //是否在From語句中,true=是
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
第7個RTE
t_jfxx表,參照第4個RTE
第8個RTE
{RTE ---------->第8個RTE
:alias <>
:eref
{ALIAS
:aliasname unnamed_join //未命名的join
:colnames (""dwmc"" ""dwbh"" ""dwdz"" ""dwbh"" ""grbh"" ""xm"" ""nl"" ""grbh"" ""ny"" ""
je"")//三個表的列
}
:rtekind 2 //RTE_JOIN
:jointype 0 //JOIN_INNER
:joinaliasvars <>
:lateral false
:inh false
:inFromCl true //在From子句中
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
第9-13個RTE
與第4-8個RTE一樣 3個RTE_RELATION,2個RTE_JOIN
3.planTree
介紹完了RTE,下面要解析的是planTree,指向型別為Plan的指標.
首先回顧Plan結構體:
/* ----------------
* Plan node
*
* All plan nodes "derive" from the Plan structure by having the
* Plan structure as the first field. This ensures that everything works
* when nodes are cast to Plan's. (node pointers are frequently cast to Plan*
* when passed around generically in the executor)
*
* We never actually instantiate any Plan nodes; this is just the common
* abstract superclass for all Plan-type nodes.
* ----------------
*/
typedef struct Plan
{
NodeTag type;//節點標識
/*
* 計劃的估算成本,estimated execution costs for plan (see costsize.c for more info)
*/
Cost startup_cost; /* 啟動成本,cost expended before fetching any tuples */
Cost total_cost; /*總成本, total cost (assuming all tuples fetched) */
/*
* planner's estimate of result size of this plan step
*/
//plan_rows * plan_width可以大體算出該plan涉及的資料量大小
double plan_rows; /* 該計劃涉及的行數,number of rows plan is expected to emit */
int plan_width; /* 該計劃涉及的行的平均寬度(大小)average row width in bytes */
/*
* 並行查詢所需要的資訊,information needed for parallel query
*/
bool parallel_aware; /* engage parallel-aware logic? */
bool parallel_safe; /* OK to use as part of parallel plan? */
/*
* Plan型別的常規資訊,Common structural data for all Plan types.
*/
int plan_node_id; /* 計劃節點id,unique across entire final plan tree */
List *targetlist; /* 投影列,target list to be computed at this node */
List *qual; /* 條件表示式,implicitly-ANDed qual conditions */
struct Plan *lefttree; /* 作為該計劃輸入的Plan(執行完某個Plan後才到這個Plan,比如先執行Sort才到Limit),input plan tree(s) */
struct Plan *righttree;//右邊樹
List *initPlan; /* 用於初始化的Plan,Init Plan nodes (un-correlated expr
* subselects) */
/*
* //引數變化驅動(比如繫結變數?)的再次查詢資訊,Information for management of parameter-change-driven rescanning
*
* extParam includes the paramIDs of all external PARAM_EXEC params
* affecting this plan node or its children. setParam params from the
* node's initPlans are not included, but their extParams are.
*
* allParam includes all the extParam paramIDs, plus the IDs of local
* params that affect the node (i.e., the setParams of its initplans).
* These are _all_ the PARAM_EXEC params that affect this node.
*/
Bitmapset *extParam;
Bitmapset *allParam;
} Plan;
planTree概覽
planTree概覽
planTree指標指向的Plan是T_LIMIT的Node,其中startup_cost為96.80,total_cost為96.81,該計劃涉及的行數為4行(plan_rows),平均行寬度(大小)為360Bytes(plan_width),計劃節點id為0(plan_node_id),返回的列有5個(投影列),lefttree不為空,說明進入該Plan前還存在其他的Plan,右樹/初始化Plan為空,limitCount為4(對應SQL語句中的limit 4).limitCount的詳細解析如下:
//limitCount
:limitCount
{CONST //CONST型別
:consttype 20 //Oid=20,pg_type中oid=20的條目,即typename=int8,即bigint(64Bytes)
:consttypmod -1
:constcollid 0 //pg_collation中Oid=0的條碼
:constlen 8 //8Bytes
:constbyval true //是否透過值傳遞,是,則constvalue即為該Const的值,否則constvalue為指向實際值的指標
:constisnull false //是否為null?
:location -1
:constvalue 8 [ 4 0 0 0 0 0 0 0 ]//常量值為0x4
}
LIMIT->lefttree
剛才提到planTree中的左樹不為空,該值同樣為指向Plan的指標:
SORT
lefttree指向的是型別為T_SORT的Node.與Limit型別,該步驟涉及的行數為14(plan_rows),執行節點id為1,targetlist與Limit類似為投影列,lefttree不為空,說明進入該Plan前還存在其他的Plan,右樹/初始化Plan為空.除了Plan常規的列外還有SORT特有的資訊,包括排序的列數numCols值為1,排序列在targetlist中的位置值為2,排序的操作型別sortOperators(pg_operator,Oid=664,text_lt),排序依據的collation(pg_collation,Oid=100,預設的規則)
LIMIT->SORT->lefttree
繼續進入SORT的左樹:
APPEND
SORT的左樹為型別T_APPEND的Node,T_APPEND節點用於UNION等集合操作.該步驟涉及的行數為14(plan_rows),執行節點id為2,targetlist與Limit類似為投影列,lefttree和righttree為空,但appendplans(List型別)不為空,APPEND的結果由子Plan的結果"級聯"而成(Generate the concatenation of the results of sub-plans).
LIMIT->SORT->APPEND->appendplans
進入APPEND的子計劃appendplans:
APPEND->appendplans
APPEND->appendplans是連結串列結構,有2個元素,每個元素的型別為T_NESTLOOP(內嵌迴圈),通常的資訊與其他節點型別類似,重點是lefttree和righttree均不為空,jointype為0表示INNER_JOIN
LIMIT->SORT->APPEND->appendplans->head
lefttree
進入第一個元素的左樹
:lefttree
{SEQSCAN /T_SEQSCAN型別的Node,順序掃描
:startup_cost 0.00
:total_cost 12.00
:plan_rows 1 //涉及的行數
:plan_width 256 //平均行寬
:parallel_aware false
:parallel_safe true
:plan_node_id 5 //Plan id
:targetlist (...) //省略
:qual (
{OPEXPR
:opno 98 //PG_OPERATOR OID of the operator,texteq字串相等
:opfuncid 67 //PG_PROC OID of underlying function,texteq字串相等
:opresulttype 16 //PG_TYPE OID of result value,bool值
:opretset false
:opcollid 0 //pg_collation
:inputcollid 100 //輸入的collation(default)
:args (//引數,連結串列型別
{RELABELTYPE //第1個引數為RelabelType型別
:arg //指向Expr的指標,實際型別為VAR
{VAR //第
:varno 4 //在rtable中處於第4個位置的RTE
:varattno 2 //屬性編號
:vartype 1043 //型別,pg_type OID,varchar
:vartypmod 14
:varcollid 100
:varlevelsup 0
:varnoold 4 //原始的varno
:varoattno 2 //原始的varattno
:location 110//token位置(在SQL語句中)
}
:resulttype 25
:resulttypmod -1
:resultcollid 100
:relabelformat 2
:location -1
}
{CONST //第2個引數為Const型別
:consttype 25 //pg_type OID
:consttypmod -1 //
:constcollid 100 //
:constlen -1
:constbyval false //傳值?如為false,則constvalue中的前4個位元組為value的說明,在這個案例中,為32(即2的4次方),從第5個位元組開始,長度為4的字串
:constisnull false
:location 205 //token所在位置
:constvalue 8 [ 32 0 0 0 49 48 48 49 ]//即字串"1001"
}
)
:location -1
}
)
:lefttree <> //左樹為空
:righttree <> //右樹為空
:initPlan <> //無初始化Plan
:extParam (b)
:allParam (b)
:scanrelid 4 //掃描第4號RTE
}
rigthtree
進入第一個元素的右樹
:righttree
{HASHJOIN //NestLoop右樹節點型別是HashJoin(t_grxx join t_jfxx)
:startup_cost 16.15
:total_cost 36.12
:plan_rows 7 //涉及的行數
:plan_width 180 //平均行大小
:parallel_aware false
:parallel_safe true
:plan_node_id 6 //計劃節點id
:targetlist (...) //投影列,省略
:qual <> //表示式
:lefttree //左樹,暫時摺疊
{...}
:righttree //右樹,暫時摺疊
{...}
:initPlan <> //初始化Plan
:extParam (b)
:allParam (b)
:jointype 0 //INNER_JOIN
:inner_unique false //非唯一inner join
:joinqual <>
:hashclauses (//hash資訊,型別為OpExpr
{OPEXPR
:opno 98 //pg_operator Oid,"=",texteq
:opfuncid 67 //pg_proc Oid,texteq
:opresulttype 16
:opretset false
:opcollid 0 //default collation
:inputcollid 100
:args (//引數連結串列
{RELABELTYPE//第1個元素 RelabelType
:arg
{VAR //VAR型別
:varno 65001 //TODO
:varattno 1 //第1列
:vartype 1043 //字串,varchar
:vartypmod 14
:varcollid 100
:varlevelsup 0
:varnoold 7 //原varno,7號RTE,即t_jfxx
:varoattno 1 //原屬性no
:location 171//SQL語句中的token位置
}
:resulttype 25
:resulttypmod -1
:resultcollid 100
:relabelformat 2
:location -1
}
{RELABELTYPE //第1個元素 RelabelType
:arg
{VAR //VAR型別
:varno 65000
:varattno 1
:vartype 1043
:vartypmod 14
:varcollid 100
:varlevelsup 0
:varnoold 5 //5號RTE,即t_grxx
:varoattno 2 //2號屬性
:location 157
}
:resulttype 25
:resulttypmod -1
:resultcollid 100
:relabelformat 2
:location -1
}
)
:location -1
}
)
}
:initPlan <> //無初始化Plan
:extParam (b)
:allParam (b)
:jointype 0 //INNER_JOIN
:inner_unique false
:joinqual <>
:nestParams <>
下面考察HashJoin的左樹和右樹,首先看左樹
...head(Plan)->righttree(HashJoin)->lefttree
:lefttree
{SEQSCAN //順序掃描
:startup_cost 0.00
:total_cost 17.20
:plan_rows 720
:plan_width 84
:parallel_aware false
:parallel_safe true
:plan_node_id 7 //計劃id
:targetlist (...)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 7//編號為7的RTE即t_jfxx
}
再看HashJoin右樹
...head(Plan)->righttree(HashJoin)->righttree
:righttree
{HASH //Hash操作(建立Hash表)
:startup_cost 16.12
:total_cost 16.12
:plan_rows 2 //涉及2行
:plan_width 134
:parallel_aware false
:parallel_safe true
:plan_node_id 8
:targetlist (...)
:qual <>
:lefttree //左樹也是一個Plan
{SEQSCAN //左樹為順序掃描
:startup_cost 0.00
:total_cost 16.12
:plan_rows 2
:plan_width 134
:parallel_aware false
:parallel_safe true
:plan_node_id 9
:targetlist (...)
:qual (
{OPEXPR //OpExpr型別
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{RELABELTYPE
:arg
{VAR
:varno 5 //5號RTE,即t_grxx
:varattno 1 //第1個列,即dwbh
:vartype 1043
:vartypmod 14
:varcollid 100
:varlevelsup 0
:varnoold 5
:varoattno 1
:location 124
}
:resulttype 25
:resulttypmod -1
:resultcollid 100
:relabelformat 2
:location -1
}
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false //非引數傳遞
:constisnull false
:location 205
:constvalue 8 [ 32 0 0 0 49 48 48 49 ]//字串"1001"
}
)
:location -1
}
)
:lefttree <> //子左樹的左樹為空
:righttree <> //子左樹的右樹為空
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 5//掃描的RTE,5號即t_grxx
}
:righttree <> //右樹為空
:initPlan <>
:extParam (b)
:allParam (b)
:skewTable 16397 //HashJoin的表Oid
:skewColumn 1 //列序號
:skewInherit false
:rows_total 0
}
LIMIT->SORT->APPEND->appendplans->head->next
子查詢中的第2個NestLoop 參照LIMIT->SORT->APPEND->appendplans->head即可, 條件變為dwbh="1002",其他與連結串列中的head元素無異,不再累述
三、小結
1、計劃樹結構：透過日誌輸出分析計劃樹結構；
2、重要的資料結構：RTE、Plan等。
四、附錄
如何開啟跟蹤日誌？postgresql.conf配置檔案設定引數:
log_destination = 'csvlog' log_directory = 'pg_log' #與postgresql.conf檔案在同一級目錄 log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 2d log_rotation_size = 100MB # debug_print_parse = on #列印parse樹 debug_print_rewritten = on #列印parse rewrite樹 debug_print_plan = on #列印plan樹 debug_pretty_print = on #以pretty方式顯示
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374894/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（21）- 查詢語句#6（PlannedStmt詳解-跟蹤分析）SQL原始碼
- PostgreSQL 原始碼解讀（23）- 查詢語句#8（PlannedStmt與QUERY P...SQL原始碼
- PostgreSQL 原始碼解讀（14）- Insert語句（如何構造PlannedStmt）SQL原始碼
- PostgreSQL 原始碼解讀（19）- 查詢語句#4（ParseTree詳解）SQL原始碼
- PostgreSQL 原始碼解讀（20）- 查詢語句#5（查詢樹Query詳解）SQL原始碼
- PostgreSQL 原始碼解讀（24）- 查詢語句#9（查詢重寫）SQL原始碼
- PostgreSQL 原始碼解讀（37）- 查詢語句#22（查詢優化-grouping_plan...SQL原始碼優化
- PostgreSQL 原始碼解讀（18）- 查詢語句#3（SQL Parse）SQL原始碼
- PostgreSQL 原始碼解讀（46）- 查詢語句#31（query_planner函式#7）SQL原始碼函式
- PostgreSQL 原始碼解讀（83）- 查詢語句#68（PortalStart函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（42）- 查詢語句#27（等價類）SQL原始碼
- PostgreSQL 原始碼解讀（74）- 查詢語句#59（Review - subquery_...SQL原始碼View
- PostgreSQL 原始碼解讀（75）- 查詢語句#60（Review - standard_...SQL原始碼View
- PostgreSQL 原始碼解讀（25）- 查詢語句#10（查詢優化概覽）SQL原始碼優化
- PostgreSQL 原始碼解讀（17）- 查詢語句#2（查詢優化基礎）SQL原始碼優化
- PostgreSQL 原始碼解讀（44）- 查詢語句#29（等價類相關資料結構）SQL原始碼資料結構
- PostgreSQL 原始碼解讀（26）- 查詢語句#11（查詢優化-上拉子連結#1）SQL原始碼優化
- PostgreSQL 原始碼解讀（27）- 查詢語句#12（查詢優化-上拉子連結#2）SQL原始碼優化
- PostgreSQL 原始碼解讀（28）- 查詢語句#13（查詢優化-上拉子連結#3）SQL原始碼優化
- PostgreSQL 原始碼解讀（29）- 查詢語句#14（查詢優化-上拉子查詢）SQL原始碼優化
- PostgreSQL 原始碼解讀（90）- 查詢語句#75（ExecHashJoin函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（91）- 查詢語句#76（ExecHashJoin函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（93）- 查詢語句#77（ExecHashJoin函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（81）- 查詢語句#66（Review - exec_simp...SQL原始碼View
- PostgreSQL 原始碼解讀（84）- 查詢語句#69（PortalStart->InitP...SQL原始碼
- PostgreSQL 原始碼解讀（85）- 查詢語句#70（PortalRun->InitPla...SQL原始碼
- PostgreSQL 原始碼解讀（86）- 查詢語句#71（PortalRun->PortalR...SQL原始碼
- PostgreSQL 原始碼解讀（50）- 查詢語句#35（Optimizer Review#1）SQL原始碼View
- PostgreSQL 原始碼解讀（51）- 查詢語句#36（Optimizer Review#2）SQL原始碼View
- PostgreSQL 原始碼解讀（72）- 查詢語句#57（make_one_rel函式#22-...SQL原始碼函式
- PostgreSQL 原始碼解讀（36）- 查詢語句#21（查詢優化-消除外連線）SQL原始碼優化
- PostgreSQL 原始碼解讀（87）- 查詢語句#72（PortalRunSelect->E...SQL原始碼
- PostgreSQL 原始碼解讀（57）- 查詢語句#42（make_one_rel函式#7-索...SQL原始碼函式
- PostgreSQL 原始碼解讀（16）- 查詢語句#1（基礎：關係代數）SQL原始碼
- PostgreSQL 原始碼解讀（95）- 查詢語句#78（ExecHashJoin函式#4-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（97）- 查詢語句#79（ExecHashJoin函式#5-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（88）- 查詢語句#73（SeqNext函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（89）- 查詢語句#74（SeqNext函式#2）SQL原始碼函式