Visual Representation after each phase for Query Life Cycle
CREATE TABLE FOO (empName text, empId int) DISTRIBUTED BY (empName);
CREATE TABLE BAR (empName text, empLocation text) DISTRIBUTED RANDOMLY;
SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo';
The following trees represent the state of the above sql query during it’s life cycle
(1) Parse Tree
- Incoming SQL query is checked for syntax and is converted to the data structure represented by QUERY node
- Refer to typedef struct Query in parsenodes.h to understand the meaning of different nodes
- What do we know for the query
EXPLAIN SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo'
; 2 Tables are being joined: BAR and FOO;Join Condition is: FOO.empName=BAR.empName;Filter Condition is: FOO.empName=‘Rambo’;Projection: All the Columns of FOO and BAR are requested (or a.k.a projected); SELECT *, where * represents all the columns. - If you look at the below parse tree, it’s the same SQL query but represented by QUERY node.
3 RTE are referred in rtable list, RTE stands for Range Table Entries, and it represents the relations【You could see the name :aliasname foo and :aliasname bar. The other members in RTE node are basically the metadata for the table. Note: The RTE entries could be the relation which are used in the query, or intermediate relations created by the parser】
Join Tree :jointree contains the FROM part of the query (i.e FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName=‘Rambo’ ) where【Join Condition: Found under the node :jointree -> FROMEXPR -> JOINEXPR (larg or left argument is the relation referred by RTE 1, i.e FOO;rarg or right argument is the relation referred by RTE 2, i.e BAR;rtindex is the index into the rtable list which contains RTE;quals containing OPEXPR is the join condition which refers to FOO.empName=BAR.empName { OPEXPR contains args which has 2 VARS;:varno 1 identifies which RTE this column / variable is coming from, in this case RTE at index 1, i.e FOO;:varno 2 identifies which RTE this column / variable is coming from, in this case RTE at index 2, i.e BAR;varattno 1 indicates the first column for FOO, check :colnames (“empname” “empid”) in RTE for Foo;varattno 1 indicates the first column for BAR, check :colnames :colnames (“empname” “emplocation”) in RTE for BAR;So basically FOO INNER JOIN BAR on FOO.empName=BAR.empName });Filter Condition under :jointree: quals under FROMEXPR contains OPEXPR which is the filter condition where(OPEXPR contains args which has { 1 VAR corresponding to varno 1, varattno 1 which is FOO.empName;1 CONST (Constant) corresponding to ‘Rambo’;So, basically FOO.empName=‘Rambo’})】
Projection / Target List contains the columns projected in the query SELECT * 【Refer to the :targetList containing references to varno 3. Target List refers to columns output by this node. It has 4 entries for the columns, varattno 1 to 4. varno 3 refers to the RTE at index 3 i.e :aliasname unnamed_join in rtable list. aliasname unnamed_join contains the columns :colnames (“empname” “empid” “empname” “emplocation”). Parser created this entry to represent the join between FOO and BAR.】
So, in this case, Join Tree, Projection / Target List node explained above are the primary objects which contains references to other supporting members like rtable
The SQL is equivalent to the Parse Tree, just converted into a QUERY Node. In order to view the parse tree, do the following:
set debug_print_parse=on;
set client_min_messages=log;
SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo';
调试小技巧:
p nodeToString(parsetree_list))
-
p pretty_format_node_dump(nodeToString(parsetree_list))
If the content printed is lengthy, it may be trimmed, you set the following parameter for lldbsetting set target.max-string-summary-length 10000
Put it in ~/.lldbinit so that you don’t have to set it every time.
parse tree:
DETAIL: {QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasSubLinks false
:hasDynamicFunctions false
:hasFuncsWithExecRestrictions false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname foo
:colnames ("empname" "empid")
}
:rtekind 0
:relid 124170
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname bar
:colnames ("empname" "emplocation")
}
:rtekind 0
:relid 124176
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname unnamed_join
:colnames ("empname" "empid" "empname" "emplocation")
}
:rtekind 2
:jointype 0
:joinaliasvars (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 2
:location -1
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location -1
}
{VAR
:varno 2
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 2
:location -1
}
)
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{JOINEXPR
:jointype 0
:isNatural false
:larg
{RANGETBLREF
:rtindex 1
}
:rarg
{RANGETBLREF
:rtindex 2
}
:usingClause <>
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 36
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location 48
}
)
:location 47
}
:alias <>
:rtindex 3
}
)
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 66
}
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 78
:constvalue 9 [ 0 0 0 9 82 97 109 98 111 ]
}
)
:location 77
}
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 1
:location 7
}
:resno 1
:resname empname
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 3
:varoattno 2
:location 7
}
:resno 2
:resname empid
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 3
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 3
:location 7
}
:resno 3
:resname empname
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 4
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 4
:location 7
}
:resno 4
:resname emplocation
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 2
:resjunk false
}
)
:withCheckOptions <>
:returningList <>
:groupClause <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:scatterClause <>
:isTableValueSelect false
:limitOffset <>
:limitCount <>
:rowMarks <>
:setOperations <>
:constraintDeps <>
:parentStmtType false
}
Rewritten Parse Tree
There is no difference in Parse Tree and Rewritten Parse Tree in this particular case. In order to view the rewritten parse tree, do the following:
set debug_print_rewritten=on;
set client_min_messages=log;
SELECT * FROM FOO INNER JOIN BAR on FOO.empName=BAR.empName where FOO.empName='Rambo';
rewritten parse tree:
DETAIL: (
{QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasSubLinks false
:hasDynamicFunctions false
:hasFuncsWithExecRestrictions false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname foo
:colnames ("empname" "empid")
}
:rtekind 0
:relid 124170
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname bar
:colnames ("empname" "emplocation")
}
:rtekind 0
:relid 124176
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 10 11)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname unnamed_join
:colnames ("empname" "empid" "empname" "emplocation")
}
:rtekind 2
:jointype 0
:joinaliasvars (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 2
:location -1
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location -1
}
{VAR
:varno 2
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 2
:location -1
}
)
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b)
:modifiedCols (b)
:forceDistRandom false
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{JOINEXPR
:jointype 0
:isNatural false
:larg
{RANGETBLREF
:rtindex 1
}
:rarg
{RANGETBLREF
:rtindex 2
}
:usingClause <>
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 36
}
{VAR
:varno 2
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 2
:varoattno 1
:location 48
}
)
:location 47
}
:alias <>
:rtindex 3
}
)
:quals
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 66
}
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 78
:constvalue 9 [ 0 0 0 9 82 97 109 98 111 ]
}
)
:location 77
}
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 1
:location 7
}
:resno 1
:resname empname
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 2
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 3
:varoattno 2
:location 7
}
:resno 2
:resname empid
:ressortgroupref 0
:resorigtbl 124170
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 3
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 3
:location 7
}
:resno 3
:resname empname
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 4
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 4
:location 7
}
:resno 4
:resname emplocation
:ressortgroupref 0
:resorigtbl 124176
:resorigcol 2
:resjunk false
}
)
:withCheckOptions <>
:returningList <>
:groupClause <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:scatterClause <>
:isTableValueSelect false
:limitOffset <>
:limitCount <>
:rowMarks <>
:setOperations <>
:constraintDeps <>
:parentStmtType false
}
)