Log Analysis of PlannedStmt structure in PostgreSQL

This article mainly introduces the PostgreSQL PlannedStmt structure of the log analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

APPEND- > appendplans

APPEND- > appendplans is a linked list structure with two elements. The type of each element is T_NESTLOOP (embedded loop). The information is usually similar to other node types. The point is that lefttree and righttree are not empty, and a jointype of 0 means INNER_JOIN.

LIMIT- > SORT- > APPEND- > appendplans- > head

Lefttree

Enter the left tree of the first element

: lefttree {Node of type SEQSCAN / T_SEQSCAN Sequential scan: startup_cost 0.00: total_cost 12.00: plan_rows 1 / / number of rows involved: plan_width 256 / / average line width: parallel_aware false: parallel_safe true: plan_node_id 5 / / Plan id: targetlist (...) / / omit: qual ({OPEXPR: opno 98 / / PG_OPERATOR OID of the operator) Texteq string equality: opfuncid 67 / / PG_PROC OID of underlying function,texteq string equality: opresulttype 16 / / PG_TYPE OID of result value Bool value: opretset false: opcollid 0 / / pg_collation: inputcollid 100 / / input collation (default): args (/ / parameter, linked list type {RELABELTYPE / / the first parameter is RelabelType type: arg / / pointer to Expr The actual type is VAR {VAR / / No.: varno 4 / / RTE: varattno 2 / / attribute number: vartype 1043 / / Type, pg_type OID in the fourth position in rtable Varchar: vartypmod 14: varcollid 100: varlevelsup 0: varnoold 4 / / original varno: varoattno 2 / / original varattno: location 110//token location (in SQL Sentence)}: resulttype 25: resulttypmod-1: resultcollid 100: relabelformat 2: location-1} {the second parameter CONST / / is of type Const: consttype 25 / / pg_type OID: consttypmod-1 / /: constcollid 100 / /: constlen-1: constbyval false / / pass the value? If false, the first 4 bytes in constvalue are the description of value, in this case, 32 (that is, 2 to the fourth power), starting with the fifth byte String with length 4: constisnull false: location 205 / / token location: constvalue 8 [32 00 0 49 48 48 49] / that is, the string "1001"}): location-1 }): lefttree / / left tree is empty: righttree / / right tree is empty: initPlan / / No initialization Plan: extParam (b): allParam (b): scanrelid 4 / / scan No. No. 4 RTE}

Rigthtree

Enter the right tree of the first element

: righttree {HASHJOIN / / NestLoop right tree node type is HashJoin (t_grxx join t_jfxx): startup_cost 16.15: total_cost 36.12: plan_rows 7 / / number of rows involved: plan_width 180 / / average row size: parallel_aware False: parallel_safe true: plan_node_id 6 / / Plan Node id: targetlist (...) / / projection column Omit: qual / / expression: lefttree / / left tree, temporarily collapse {.}: righttree / / right tree Temporarily collapse {...}: initPlan / / initialize Plan: extParam (b): allParam (b): jointype 0 / / INNER_JOIN: inner_unique false / / non-unique inner join: joinqual : hashclauses (/ / hash information Type is 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 (/ / Parameter list {RELABELTYPE// first element RelabelType: arg {VAR / / VAR type: varno 65001 / / TODO: varattno 1 / / column 1: vartype 1043 / / string Varchar: vartypmod 14: varcollid 100: varlevelsup 0: varnoold 7 / / the original varno,7 number RTE That is, the token position in the t_jfxx: varoattno 1amp / original attribute no: location 171//SQL statement}: resulttype 25: resulttypmod-1: resultcollid 100 : relabelformat 2: location-1} {RELABELTYPE / / first element RelabelType: arg {VAR / / VAR Type: varno 65000 : varattno 1: vartype 1043: vartypmod 14: varcollid 1043: varlevelsup 0: varnoold 5 / / 5 RTE That is, t_grxx: varoattno 2 / / 2 attribute: location 157}: resulttype 25: resulttypmod-1: resultcollid 100: relabelformat 2 : location-1}): location-1})}: initPlan / / No initialization Plan: extParam (b) : allParam (b): jointype 0 / / INNER_JOIN: inner_unique false: joinqual: nestParams

Let's examine the left and right trees of HashJoin. First, look at the left trees.

... head (Plan)-> righttree (HashJoin)-> lefttree

Lefttree {SEQSCAN / / Sequential scan: startup_cost 0.00: total_cost 17.20: plan_rows 720: plan_width 84: parallel_aware false: parallel_safe True: plan_node_id 7 / / Plan id: targetlist (...): qual: lefttree: righttree: initPlan: extParam (b) : allParam (b): scanrelid 7 allParam / RTE numbered 7 is t_jfxx}

Look at the HashJoin right tree again.

... head (Plan)-> righttree (HashJoin)-> righttree

: righttree {HASH / / Hash operation (create Hash table): startup_cost 16.12: total_cost 16.12: plan_rows 2 / / involves 2 lines: plan_width 134: parallel_aware false : parallel_safe true: plan_node_id 8: targetlist (...): qual: lefttree / / the left tree is also a Plan {SEQSCAN / / left tree for sequential scanning: 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 type: opno 98: opfuncid 67: opresulttype 16: opretset false : opcollid 0: inputcollid 100: args ({RELABELTYPE: arg {VAR: varno 5 / / 5 RTE That is, t_grxx: varattno 1 / / the first column Namely 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 / / non-parametric pass: constisnull false: location 205: constvalue 8 [32 00 0 49 48 48 49] / / string "1001"} ): location-1}): the left tree of the lefttree / / subleft tree is empty: the right tree of the righttree / / subleft tree is empty: initPlan : extParam (b): allParam (b): scanrelid 5max / scanned RTE Number 5 is t_grxx}: righttree / / the right tree is empty: initPlan: extParam (b): allParam (b): skewTable 16397 / / HashJoin table oid: skewColumn 1 / / column Serial number: skewInherit false: rows_total 0}

LIMIT- > SORT- > APPEND- > appendplans- > head- > next

The second NestLoop in the subquery can refer to LIMIT- > SORT- > APPEND- > appendplans- > head, and the condition is changed to dwbh= "1002". The other elements are the same as the head elements in the linked list.

1. Plan tree structure: analyze the plan tree structure through log output

2. Important data structures: RTE, Plan, etc.

IV. Appendix

How do I open the trace log? Postgresql.conf profile setting parameters:

Log_destination = 'csvlog'log_directory =' pg_log' # is at the same level as the postgresql.conf file log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'log_rotation_age = 2dlog_rotation_size = 100MB#debug_print_parse = on # print parse tree debug_print_rewritten = on # print parse rewrite tree debug_print_plan = on # print plan tree debug_pretty_print = on # display in pretty Thank you for reading this article carefully. I hope the article "Log Analysis of PlannedStmt structure in PostgreSQL" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!