github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/testdata/explain_tree (about) 1 exec 2 CREATE TABLE t.orders (oid INT PRIMARY KEY, cid INT, value DECIMAL, date DATE) 3 ---- 4 5 # In the string version, the constants are not anonymized. 6 plan-string 7 SELECT oid FROM t.orders WHERE oid = 123 8 ---- 9 scan (oid int) 10 table orders@primary 11 spans /123-/123/# 12 13 plan-tree 14 SELECT oid FROM t.orders WHERE oid = 123 15 ---- 16 name: scan 17 attrs: 18 - key: table 19 value: orders@primary 20 - key: spans 21 value: 1 span 22 children: [] 23 24 plan-string 25 SELECT cid, date, value FROM t.orders 26 ---- 27 render (cid int, date date, value decimal) 28 │ render 0 (@1)[int] 29 │ render 1 (@3)[date] 30 │ render 2 (@2)[decimal] 31 └── scan (cid int, value decimal, date date) 32 table orders@primary 33 spans FULL SCAN 34 35 plan-tree 36 SELECT cid, date, value FROM t.orders 37 ---- 38 name: render 39 attrs: 40 - key: render 41 value: cid 42 - key: render 43 value: date 44 - key: render 45 value: value 46 children: 47 - name: scan 48 attrs: 49 - key: table 50 value: orders@primary 51 - key: spans 52 value: FULL SCAN 53 children: [] 54 55 plan-string 56 SELECT cid, sum(value) FROM t.orders WHERE date > '2015-01-01' GROUP BY cid ORDER BY 1 - sum(value) 57 ---- 58 render (cid int, sum decimal) 59 │ render 0 (@2)[int] 60 │ render 1 (@3)[decimal] 61 └── sort (column6 decimal, cid int, sum decimal) +column6 62 │ order +column6 63 └── render (column6 decimal, cid int, sum decimal) 64 │ render 0 ((1)[decimal] - (@2)[decimal])[decimal] 65 │ render 1 (@1)[int] 66 │ render 2 (@2)[decimal] 67 └── group (cid int, sum decimal) 68 │ aggregate 0 cid 69 │ aggregate 1 sum(value) 70 │ group by cid 71 └── render (cid int, value decimal) 72 │ render 0 (@1)[int] 73 │ render 1 (@2)[decimal] 74 └── scan (cid int, value decimal, date date) 75 table orders@primary 76 spans FULL SCAN 77 filter ((@3)[date] > ('2015-01-01')[date])[bool] 78 79 plan-tree 80 SELECT cid, sum(value) FROM t.orders WHERE date > '2015-01-01' GROUP BY cid ORDER BY 1 - sum(value) 81 ---- 82 name: render 83 attrs: 84 - key: render 85 value: cid 86 - key: render 87 value: sum 88 children: 89 - name: sort 90 attrs: 91 - key: order 92 value: +column6 93 children: 94 - name: render 95 attrs: 96 - key: render 97 value: _ - sum 98 - key: render 99 value: cid 100 - key: render 101 value: sum 102 children: 103 - name: group 104 attrs: 105 - key: aggregate 0 106 value: cid 107 - key: aggregate 1 108 value: sum(value) 109 - key: group by 110 value: cid 111 children: 112 - name: render 113 attrs: 114 - key: render 115 value: cid 116 - key: render 117 value: value 118 children: 119 - name: scan 120 attrs: 121 - key: table 122 value: orders@primary 123 - key: spans 124 value: FULL SCAN 125 - key: filter 126 value: date > _ 127 children: [] 128 129 plan-string 130 SELECT value FROM (SELECT cid, date, value FROM t.orders) 131 ---- 132 scan (value decimal) 133 table orders@primary 134 spans FULL SCAN 135 136 plan-tree 137 SELECT value FROM (SELECT cid, date, value FROM t.orders) 138 ---- 139 name: scan 140 attrs: 141 - key: table 142 value: orders@primary 143 - key: spans 144 value: FULL SCAN 145 children: [] 146 147 plan-string 148 SELECT cid, date, value FROM t.orders WHERE date IN (SELECT date FROM t.orders) 149 ---- 150 render (cid int, date date, value decimal) 151 │ render 0 (@1)[int] 152 │ render 1 (@3)[date] 153 │ render 2 (@2)[decimal] 154 └── hash-join (cid int, value decimal, date date, date date) 155 │ type inner 156 │ equality (date) = (date) 157 │ right cols are key 158 ├── scan (cid int, value decimal, date date) 159 │ table orders@primary 160 │ spans FULL SCAN 161 └── distinct (date date) 162 │ distinct on date 163 └── scan (date date) 164 table orders@primary 165 spans FULL SCAN 166 167 plan-tree 168 SELECT cid, date, value FROM t.orders WHERE date IN (SELECT date FROM t.orders) 169 ---- 170 name: render 171 attrs: 172 - key: render 173 value: cid 174 - key: render 175 value: date 176 - key: render 177 value: value 178 children: 179 - name: hash-join 180 attrs: 181 - key: type 182 value: inner 183 - key: equality 184 value: (date) = (date) 185 - key: right cols are key 186 value: "" 187 children: 188 - name: scan 189 attrs: 190 - key: table 191 value: orders@primary 192 - key: spans 193 value: FULL SCAN 194 children: [] 195 - name: distinct 196 attrs: 197 - key: distinct on 198 value: date 199 children: 200 - name: scan 201 attrs: 202 - key: table 203 value: orders@primary 204 - key: spans 205 value: FULL SCAN 206 children: [] 207 208 exec 209 CREATE TABLE t.movies ( 210 id SERIAL PRIMARY KEY, 211 title TEXT, 212 released INT 213 ) 214 ---- 215 216 exec 217 CREATE TABLE t.actors ( 218 id SERIAL PRIMARY KEY, 219 name TEXT 220 ) 221 ---- 222 223 # Subquery. 224 plan-string 225 SELECT id AS movie_id, title, (SELECT name FROM t.actors WHERE name = 'Foo') FROM t.movies 226 ---- 227 root (movie_id int, title string, name string) 228 ├── render (movie_id int, title string, name string) 229 │ │ render 0 (@1)[int] 230 │ │ render 1 (@2)[string] 231 │ │ render 2 (@S1)[string] 232 │ └── scan (id int, title string) 233 │ table movies@primary 234 │ spans FULL SCAN 235 └── subquery 236 │ id @S1 237 │ original sql (SELECT name FROM t.actors WHERE name = 'Foo') 238 │ exec mode one row 239 └── max1row (name string) 240 └── scan (name string) 241 table actors@primary 242 spans FULL SCAN 243 filter ((@1)[string] = ('Foo')[string])[bool] 244 245 plan-tree 246 SELECT id AS movie_id, title, (SELECT name FROM t.actors WHERE name = 'Foo') FROM t.movies 247 ---- 248 name: root 249 attrs: [] 250 children: 251 - name: render 252 attrs: 253 - key: render 254 value: id 255 - key: render 256 value: title 257 - key: render 258 value: (SELECT name FROM t.actors WHERE name = _) 259 children: 260 - name: scan 261 attrs: 262 - key: table 263 value: movies@primary 264 - key: spans 265 value: FULL SCAN 266 children: [] 267 - name: subquery 268 attrs: 269 - key: id 270 value: '@S1' 271 - key: original sql 272 value: (SELECT name FROM t.actors WHERE name = _) 273 - key: exec mode 274 value: one row 275 children: 276 - name: max1row 277 attrs: [] 278 children: 279 - name: scan 280 attrs: 281 - key: table 282 value: actors@primary 283 - key: spans 284 value: FULL SCAN 285 - key: filter 286 value: name = _ 287 children: []