Skip to content

Instantly share code, notes, and snippets.

@ringerc
Last active December 28, 2015 01:49
Show Gist options
  • Save ringerc/7423819 to your computer and use it in GitHub Desktop.
Save ringerc/7423819 to your computer and use it in GitHub Desktop.
Diff of parse, rewritten and plan trees from "DELETE FROM t;" against "DELETE FROM t USING t2 WHERE t.id = t2.id;"
--- /tmp/simple-delete 2013-11-12 09:26:55.077406915 +0800
+++ /tmp/join-delete 2013-11-12 09:49:06.172045074 +0800
@@ -1,4 +1,4 @@
-regress=> DELETE FROM t;
+regress=> DELETE FROM t USING t2 WHERE t.id = t2.id;
LOG: parse tree:
DETAIL: {QUERY
:commandType 4
@@ -28,9 +28,27 @@
:lateral false
:inh true
:inFromCl false
- :requiredPerms 8
+ :requiredPerms 10
:checkAsUser 0
- :selectedCols (b)
+ :selectedCols (b 9)
+ :modifiedCols (b)
+ }
+ {RTE
+ :alias <>
+ :eref
+ {ALIAS
+ :aliasname t2
+ :colnames ("id" "secret")
+ }
+ :rtekind 0
+ :relid 16402
+ :relkind r
+ :lateral false
+ :inh true
+ :inFromCl true
+ :requiredPerms 2
+ :checkAsUser 0
+ :selectedCols (b 9)
:modifiedCols (b)
}
)
@@ -40,8 +58,44 @@
{RANGETBLREF
:rtindex 1
}
+ {RANGETBLREF
+ :rtindex 2
+ }
)
- :quals <>
+ :quals
+ {OPEXPR
+ :opno 96
+ :opfuncid 65
+ :opresulttype 16
+ :opretset false
+ :opcollid 0
+ :inputcollid 0
+ :args (
+ {VAR
+ :varno 1
+ :varattno 1
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 1
+ :varoattno 1
+ :location 29
+ }
+ {VAR
+ :varno 2
+ :varattno 1
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 2
+ :varoattno 1
+ :location 36
+ }
+ )
+ :location 34
+ }
}
:targetList <>
:returningList <>
@@ -87,9 +141,27 @@
:lateral false
:inh true
:inFromCl false
- :requiredPerms 8
+ :requiredPerms 10
+ :checkAsUser 0
+ :selectedCols (b 9)
+ :modifiedCols (b)
+ }
+ {RTE
+ :alias <>
+ :eref
+ {ALIAS
+ :aliasname t2
+ :colnames ("id" "secret")
+ }
+ :rtekind 0
+ :relid 16402
+ :relkind r
+ :lateral false
+ :inh true
+ :inFromCl true
+ :requiredPerms 2
:checkAsUser 0
- :selectedCols (b)
+ :selectedCols (b 9)
:modifiedCols (b)
}
)
@@ -99,8 +171,44 @@
{RANGETBLREF
:rtindex 1
}
+ {RANGETBLREF
+ :rtindex 2
+ }
)
- :quals <>
+ :quals
+ {OPEXPR
+ :opno 96
+ :opfuncid 65
+ :opresulttype 16
+ :opretset false
+ :opcollid 0
+ :inputcollid 0
+ :args (
+ {VAR
+ :varno 1
+ :varattno 1
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 1
+ :varoattno 1
+ :location 29
+ }
+ {VAR
+ :varno 2
+ :varattno 1
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 2
+ :varoattno 1
+ :location 36
+ }
+ )
+ :location 34
+ }
}
:targetList (
{TARGETENTRY
@@ -148,10 +256,10 @@
:transientPlan false
:planTree
{MODIFYTABLE
- :startup_cost 0.00
- :total_cost 22.30
- :plan_rows 1230
- :plan_width 6
+ :startup_cost 1.23
+ :total_cost 28.76
+ :plan_rows 62
+ :plan_width 12
:targetlist <>
:qual <>
:lefttree <>
@@ -164,17 +272,17 @@
:resultRelations (i 1)
:resultRelIndex 0
:plans (
- {SEQSCAN
- :startup_cost 0.00
- :total_cost 22.30
- :plan_rows 1230
- :plan_width 6
+ {HASHJOIN
+ :startup_cost 1.23
+ :total_cost 28.76
+ :plan_rows 62
+ :plan_width 12
:targetlist (
{TARGETENTRY
:expr
{VAR
- :varno 1
- :varattno -1
+ :varno 65000
+ :varattno 1
:vartype 27
:vartypmod -1
:varcollid 0
@@ -190,19 +298,253 @@
:resorigcol 0
:resjunk true
}
+ {TARGETENTRY
+ :expr
+ {VAR
+ :varno 65001
+ :varattno 1
+ :vartype 27
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 2
+ :varoattno -1
+ :location -1
+ }
+ :resno 2
+ :resname ctid1
+ :ressortgroupref 0
+ :resorigtbl 0
+ :resorigcol 0
+ :resjunk true
+ }
)
:qual <>
- :lefttree <>
- :righttree <>
+ :lefttree
+ {SEQSCAN
+ :startup_cost 0.00
+ :total_cost 22.30
+ :plan_rows 1230
+ :plan_width 10
+ :targetlist (
+ {TARGETENTRY
+ :expr
+ {VAR
+ :varno 2
+ :varattno -1
+ :vartype 27
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 2
+ :varoattno -1
+ :location -1
+ }
+ :resno 1
+ :resname <>
+ :ressortgroupref 0
+ :resorigtbl 0
+ :resorigcol 0
+ :resjunk false
+ }
+ {TARGETENTRY
+ :expr
+ {VAR
+ :varno 2
+ :varattno 1
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 2
+ :varoattno 1
+ :location 36
+ }
+ :resno 2
+ :resname <>
+ :ressortgroupref 0
+ :resorigtbl 0
+ :resorigcol 0
+ :resjunk false
+ }
+ )
+ :qual <>
+ :lefttree <>
+ :righttree <>
+ :initPlan <>
+ :extParam (b 0)
+ :allParam (b 0)
+ :scanrelid 2
+ }
+ :righttree
+ {HASH
+ :startup_cost 1.10
+ :total_cost 1.10
+ :plan_rows 10
+ :plan_width 10
+ :targetlist (
+ {TARGETENTRY
+ :expr
+ {VAR
+ :varno 65001
+ :varattno 1
+ :vartype 27
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 1
+ :varoattno -1
+ :location -1
+ }
+ :resno 1
+ :resname <>
+ :ressortgroupref 0
+ :resorigtbl 0
+ :resorigcol 0
+ :resjunk false
+ }
+ {TARGETENTRY
+ :expr
+ {VAR
+ :varno 65001
+ :varattno 2
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 1
+ :varoattno 1
+ :location -1
+ }
+ :resno 2
+ :resname <>
+ :ressortgroupref 0
+ :resorigtbl 0
+ :resorigcol 0
+ :resjunk false
+ }
+ )
+ :qual <>
+ :lefttree
+ {SEQSCAN
+ :startup_cost 0.00
+ :total_cost 1.10
+ :plan_rows 10
+ :plan_width 10
+ :targetlist (
+ {TARGETENTRY
+ :expr
+ {VAR
+ :varno 1
+ :varattno -1
+ :vartype 27
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 1
+ :varoattno -1
+ :location -1
+ }
+ :resno 1
+ :resname <>
+ :ressortgroupref 0
+ :resorigtbl 0
+ :resorigcol 0
+ :resjunk false
+ }
+ {TARGETENTRY
+ :expr
+ {VAR
+ :varno 1
+ :varattno 1
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 1
+ :varoattno 1
+ :location 29
+ }
+ :resno 2
+ :resname <>
+ :ressortgroupref 0
+ :resorigtbl 0
+ :resorigcol 0
+ :resjunk false
+ }
+ )
+ :qual <>
+ :lefttree <>
+ :righttree <>
+ :initPlan <>
+ :extParam (b 0)
+ :allParam (b 0)
+ :scanrelid 1
+ }
+ :righttree <>
+ :initPlan <>
+ :extParam (b 0)
+ :allParam (b 0)
+ :skewTable 16402
+ :skewColumn 1
+ :skewInherit false
+ :skewColType 23
+ :skewColTypmod -1
+ }
:initPlan <>
:extParam (b 0)
:allParam (b 0)
- :scanrelid 1
+ :jointype 0
+ :joinqual <>
+ :hashclauses (
+ {OPEXPR
+ :opno 96
+ :opfuncid 65
+ :opresulttype 16
+ :opretset false
+ :opcollid 0
+ :inputcollid 0
+ :args (
+ {VAR
+ :varno 65001
+ :varattno 2
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 2
+ :varoattno 1
+ :location 36
+ }
+ {VAR
+ :varno 65000
+ :varattno 2
+ :vartype 23
+ :vartypmod -1
+ :varcollid 0
+ :varlevelsup 0
+ :varnoold 1
+ :varoattno 1
+ :location 29
+ }
+ )
+ :location -1
+ }
+ )
}
)
:returningLists <>
:fdwPrivLists (<>)
- :rowMarks <>
+ :rowMarks (
+ {PLANROWMARK
+ :rti 2
+ :prti 2
+ :rowmarkId 1
+ :markType 4
+ :noWait false
+ :isParent false
+ }
+ )
:epqParam 0
}
:rtable (
@@ -219,9 +561,27 @@
:lateral false
:inh false
:inFromCl false
- :requiredPerms 8
+ :requiredPerms 10
:checkAsUser 0
- :selectedCols (b)
+ :selectedCols (b 9)
+ :modifiedCols (b)
+ }
+ {RTE
+ :alias <>
+ :eref
+ {ALIAS
+ :aliasname t2
+ :colnames ("id" "secret")
+ }
+ :rtekind 0
+ :relid 16402
+ :relkind r
+ :lateral false
+ :inh false
+ :inFromCl true
+ :requiredPerms 2
+ :checkAsUser 0
+ :selectedCols (b 9)
:modifiedCols (b)
}
)
@@ -229,10 +589,20 @@
:utilityStmt <>
:subplans <>
:rewindPlanIDs (b)
- :rowMarks <>
- :relationOids (o 16387)
+ :rowMarks (
+ {PLANROWMARK
+ :rti 2
+ :prti 2
+ :rowmarkId 1
+ :markType 4
+ :noWait false
+ :isParent false
+ }
+ )
+ :relationOids (o 16387 16402)
:invalItems <>
:nParamExec 1
}
DELETE 10
+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment