Skip to content

Instantly share code, notes, and snippets.

@ringerc

ringerc/gist:7429182

Created November 12, 2013 11:04
Show Gist options
  • Save ringerc/7429182 to your computer and use it in GitHub Desktop.
Save ringerc/7429182 to your computer and use it in GitHub Desktop.
Download ZIP
Parse, rewrite and plan trees of a simple SELECT from a view, showing subquery expansion
Raw
gistfile1.txt
regress=> SELECT * FROM t_even;
LOG: parse tree:
DETAIL: {QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname t_even
:colnames ("id" "secret")
}
:rtekind 0
:relid 16393
:relkind v
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10)
:modifiedCols (b)
}
)
:jointree
{FROMEXPR
:fromlist (
{RANGETBLREF
:rtindex 1
}
)
:quals <>
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 7
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 7
}
:resno 2
:resname secret
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 2
:resjunk false
}
)
:returningList <>
:groupClause <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:rowMarks <>
:setOperations <>
:constraintDeps <>
}
LOG: rewritten parse tree:
DETAIL: (
{QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname t_even
:colnames ("id" "secret")
}
:rtekind 1
:subquery
{QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:cteList <>
:rtable (
{RTE
:alias
{ALIAS
:aliasname old
:colnames <>
}
:eref
{ALIAS
:aliasname old
:colnames ("id" "secret")
}
:rtekind 0
:relid 16393
:relkind v
:lateral false
:inh false
:inFromCl false
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10)
:modifiedCols (b)
}
{RTE
:alias
{ALIAS
:aliasname new
:colnames <>
}
:eref
{ALIAS
:aliasname new
:colnames ("id" "secret")
}
:rtekind 0
:relid 16393
:relkind v
:lateral false
:inh false
:inFromCl false
:requiredPerms 0
:checkAsUser 16384
:selectedCols (b)
:modifiedCols (b)
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname t
:colnames ("id" "secret")
}
:rtekind 0
:relid 16387
:relkind r
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 16384
:selectedCols (b 9 10)
:modifiedCols (b)
}
)
:jointree
{FROMEXPR
:fromlist (
{RANGETBLREF
:rtindex 3
}
)
:quals
{OPEXPR
:opno 96
:opfuncid 0
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 0
:args (
{OPEXPR
:opno 530
:opfuncid 0
:opresulttype 23
:opretset false
:opcollid 0
:inputcollid 0
:args (
{VAR
:varno 3
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 3
:varoattno 1
:location -1
}
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location -1
:constvalue 4 [ 2 0 0 0 0 0 0 0 ]
}
)
:location -1
}
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location -1
:constvalue 4 [ 0 0 0 0 0 0 0 0 ]
}
)
:location -1
}
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 3
:varoattno 1
:location -1
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 16387
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 3
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 3
:varoattno 2
:location -1
}
:resno 2
:resname secret
:ressortgroupref 0
:resorigtbl 16387
:resorigcol 2
:resjunk false
}
)
:returningList <>
:groupClause <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:rowMarks <>
:setOperations <>
:constraintDeps <>
}
:security_barrier false
:lateral false
:inh false
:inFromCl true
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:modifiedCols (b)
}
)
:jointree
{FROMEXPR
:fromlist (
{RANGETBLREF
:rtindex 1
}
)
:quals <>
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 7
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 7
}
:resno 2
:resname secret
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 2
:resjunk false
}
)
:returningList <>
:groupClause <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:rowMarks <>
:setOperations <>
:constraintDeps <>
}
)
LOG: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:planTree
{SEQSCAN
:startup_cost 0.00
:total_cost 1.15
:plan_rows 1
:plan_width 12
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 4
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 4
:varoattno 1
:location -1
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 4
:varattno 2
:vartype 25
:vartypmod -1
:varcollid 100
:varlevelsup 0
:varnoold 4
:varoattno 2
:location -1
}
:resno 2
:resname secret
:ressortgroupref 0
:resorigtbl 16393
:resorigcol 2
:resjunk false
}
)
:qual (
{OPEXPR
:opno 96
:opfuncid 65
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 0
:args (
{OPEXPR
:opno 530
:opfuncid 156
:opresulttype 23
:opretset false
:opcollid 0
:inputcollid 0
:args (
{VAR
:varno 4
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 4
:varoattno 1
:location -1
}
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location -1
:constvalue 4 [ 2 0 0 0 0 0 0 0 ]
}
)
:location -1
}
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location -1
:constvalue 4 [ 0 0 0 0 0 0 0 0 ]
}
)
:location -1
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 4
}
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname t_even
:colnames ("id" "secret")
}
:rtekind 1
:subquery <>
:security_barrier false
:lateral false
:inh false
:inFromCl true
:requiredPerms 0
:checkAsUser 0
:selectedCols (b)
:modifiedCols (b)
}
{RTE
:alias
{ALIAS
:aliasname old
:colnames <>
}
:eref
{ALIAS
:aliasname old
:colnames ("id" "secret")
}
:rtekind 0
:relid 16393
:relkind v
:lateral false
:inh false
:inFromCl false
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10)
:modifiedCols (b)
}
{RTE
:alias
{ALIAS
:aliasname new
:colnames <>
}
:eref
{ALIAS
:aliasname new
:colnames ("id" "secret")
}
:rtekind 0
:relid 16393
:relkind v
:lateral false
:inh false
:inFromCl false
:requiredPerms 0
:checkAsUser 16384
:selectedCols (b)
:modifiedCols (b)
}
{RTE
:alias <>
:eref
{ALIAS
:aliasname t
:colnames ("id" "secret")
}
:rtekind 0
:relid 16387
:relkind r
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 16384
:selectedCols (b 9 10)
:modifiedCols (b)
}
)
:resultRelations <>
:utilityStmt <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 16393 16393 16387)
:invalItems <>
:nParamExec 0
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment