github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/spool (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE t(x INT PRIMARY KEY) 5 6 statement ok 7 CREATE TABLE t2(x INT PRIMARY KEY) 8 9 # Check that if a mutation uses further processing, a spool is added. 10 query TTT 11 EXPLAIN WITH a AS (INSERT INTO t SELECT * FROM t2 RETURNING x) 12 SELECT * FROM a LIMIT 1 13 ---- 14 · distributed false 15 · vectorized false 16 root · · 17 ├── limit · · 18 │ │ count 1 19 │ └── scan buffer node · · 20 │ label buffer 1 (a) 21 └── subquery · · 22 │ id @S1 23 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x 24 │ exec mode all rows 25 └── buffer node · · 26 │ label buffer 1 (a) 27 └── spool · · 28 └── run · · 29 └── insert · · 30 │ into t(x) 31 │ strategy inserter 32 └── scan · · 33 · table t2@primary 34 · spans FULL SCAN 35 36 query TTT 37 EXPLAIN WITH a AS (DELETE FROM t RETURNING x) 38 SELECT * FROM a LIMIT 1 39 ---- 40 · distributed false 41 · vectorized false 42 root · · 43 ├── limit · · 44 │ │ count 1 45 │ └── scan buffer node · · 46 │ label buffer 1 (a) 47 └── subquery · · 48 │ id @S1 49 │ original sql DELETE FROM t RETURNING x 50 │ exec mode all rows 51 └── buffer node · · 52 │ label buffer 1 (a) 53 └── spool · · 54 └── run · · 55 └── delete · · 56 │ from t 57 │ strategy deleter 58 └── scan · · 59 · table t@primary 60 · spans FULL SCAN 61 62 63 query TTT 64 EXPLAIN WITH a AS (UPDATE t SET x = x + 1 RETURNING x) 65 SELECT * FROM a LIMIT 1 66 ---- 67 · distributed false 68 · vectorized false 69 root · · 70 ├── limit · · 71 │ │ count 1 72 │ └── scan buffer node · · 73 │ label buffer 1 (a) 74 └── subquery · · 75 │ id @S1 76 │ original sql UPDATE t SET x = x + 1 RETURNING x 77 │ exec mode all rows 78 └── buffer node · · 79 │ label buffer 1 (a) 80 └── spool · · 81 └── run · · 82 └── update · · 83 │ table t 84 │ set x 85 │ strategy updater 86 └── render · · 87 └── scan · · 88 · table t@primary 89 · spans FULL SCAN 90 · locking strength for update 91 92 query TTT 93 EXPLAIN WITH a AS (UPSERT INTO t VALUES (2), (3) RETURNING x) 94 SELECT * FROM a LIMIT 1 95 ---- 96 · distributed false 97 · vectorized false 98 root · · 99 ├── limit · · 100 │ │ count 1 101 │ └── scan buffer node · · 102 │ label buffer 1 (a) 103 └── subquery · · 104 │ id @S1 105 │ original sql UPSERT INTO t VALUES (2), (3) RETURNING x 106 │ exec mode all rows 107 └── buffer node · · 108 │ label buffer 1 (a) 109 └── spool · · 110 └── run · · 111 └── upsert · · 112 │ into t(x) 113 │ strategy opt upserter 114 └── values · · 115 · size 1 column, 2 rows 116 117 # Ditto all mutations, with the statement source syntax. 118 query TTT 119 EXPLAIN SELECT * FROM [INSERT INTO t SELECT * FROM t2 RETURNING x] LIMIT 1 120 ---- 121 · distributed false 122 · vectorized false 123 root · · 124 ├── limit · · 125 │ │ count 1 126 │ └── scan buffer node · · 127 │ label buffer 1 128 └── subquery · · 129 │ id @S1 130 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x 131 │ exec mode all rows 132 └── buffer node · · 133 │ label buffer 1 134 └── spool · · 135 └── run · · 136 └── insert · · 137 │ into t(x) 138 │ strategy inserter 139 └── scan · · 140 · table t2@primary 141 · spans FULL SCAN 142 143 query TTT 144 EXPLAIN SELECT * FROM [DELETE FROM t RETURNING x] LIMIT 1 145 ---- 146 · distributed false 147 · vectorized false 148 root · · 149 ├── limit · · 150 │ │ count 1 151 │ └── scan buffer node · · 152 │ label buffer 1 153 └── subquery · · 154 │ id @S1 155 │ original sql DELETE FROM t RETURNING x 156 │ exec mode all rows 157 └── buffer node · · 158 │ label buffer 1 159 └── spool · · 160 └── run · · 161 └── delete · · 162 │ from t 163 │ strategy deleter 164 └── scan · · 165 · table t@primary 166 · spans FULL SCAN 167 168 query TTT 169 EXPLAIN SELECT * FROM [UPDATE t SET x = x + 1 RETURNING x] LIMIT 1 170 ---- 171 · distributed false 172 · vectorized false 173 root · · 174 ├── limit · · 175 │ │ count 1 176 │ └── scan buffer node · · 177 │ label buffer 1 178 └── subquery · · 179 │ id @S1 180 │ original sql UPDATE t SET x = x + 1 RETURNING x 181 │ exec mode all rows 182 └── buffer node · · 183 │ label buffer 1 184 └── spool · · 185 └── run · · 186 └── update · · 187 │ table t 188 │ set x 189 │ strategy updater 190 └── render · · 191 └── scan · · 192 · table t@primary 193 · spans FULL SCAN 194 · locking strength for update 195 196 query TTT 197 EXPLAIN SELECT * FROM [UPSERT INTO t VALUES (2), (3) RETURNING x] LIMIT 1 198 ---- 199 · distributed false 200 · vectorized false 201 root · · 202 ├── limit · · 203 │ │ count 1 204 │ └── scan buffer node · · 205 │ label buffer 1 206 └── subquery · · 207 │ id @S1 208 │ original sql UPSERT INTO t VALUES (2), (3) RETURNING x 209 │ exec mode all rows 210 └── buffer node · · 211 │ label buffer 1 212 └── spool · · 213 └── run · · 214 └── upsert · · 215 │ into t(x) 216 │ strategy opt upserter 217 └── values · · 218 · size 1 column, 2 rows 219 220 # Check that a spool is also inserted for other processings than LIMIT. 221 query TTT 222 EXPLAIN SELECT count(*) FROM [INSERT INTO t SELECT * FROM t2 RETURNING x] 223 ---- 224 · distributed false 225 · vectorized false 226 root · · 227 ├── group · · 228 │ │ aggregate 0 count_rows() 229 │ │ scalar · 230 │ └── render · · 231 │ └── scan buffer node · · 232 │ label buffer 1 233 └── subquery · · 234 │ id @S1 235 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x 236 │ exec mode all rows 237 └── buffer node · · 238 │ label buffer 1 239 └── spool · · 240 └── run · · 241 └── insert · · 242 │ into t(x) 243 │ strategy inserter 244 └── scan · · 245 · table t2@primary 246 · spans FULL SCAN 247 248 query TTT 249 EXPLAIN SELECT * FROM [INSERT INTO t SELECT * FROM t2 RETURNING x], t 250 ---- 251 · distributed false 252 · vectorized false 253 root · · 254 ├── cross-join · · 255 │ │ type cross 256 │ ├── scan buffer node · · 257 │ │ label buffer 1 258 │ └── scan · · 259 │ table t@primary 260 │ spans FULL SCAN 261 └── subquery · · 262 │ id @S1 263 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x 264 │ exec mode all rows 265 └── buffer node · · 266 │ label buffer 1 267 └── spool · · 268 └── run · · 269 └── insert · · 270 │ into t(x) 271 │ strategy inserter 272 └── scan · · 273 · table t2@primary 274 · spans FULL SCAN 275 276 # Check that if a spool is already added at some level, then it is not added 277 # again at levels below. 278 # TODO(andyk): This optimization is not part of CBO yet. 279 query TTT 280 EXPLAIN WITH a AS (INSERT INTO t SELECT * FROM t2 RETURNING x), 281 b AS (INSERT INTO t SELECT x+1 FROM a RETURNING x) 282 SELECT * FROM b LIMIT 1 283 ---- 284 · distributed false 285 · vectorized false 286 root · · 287 ├── limit · · 288 │ │ count 1 289 │ └── scan buffer node · · 290 │ label buffer 2 (b) 291 ├── subquery · · 292 │ │ id @S1 293 │ │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x 294 │ │ exec mode all rows 295 │ └── buffer node · · 296 │ │ label buffer 1 (a) 297 │ └── spool · · 298 │ └── run · · 299 │ └── insert · · 300 │ │ into t(x) 301 │ │ strategy inserter 302 │ └── scan · · 303 │ table t2@primary 304 │ spans FULL SCAN 305 └── subquery · · 306 │ id @S2 307 │ original sql INSERT INTO t SELECT x + 1 FROM a RETURNING x 308 │ exec mode all rows 309 └── buffer node · · 310 │ label buffer 2 (b) 311 └── spool · · 312 └── run · · 313 └── insert · · 314 │ into t(x) 315 │ strategy inserter 316 └── render · · 317 └── scan buffer node · · 318 · label buffer 1 (a) 319 320 # Check that no spool is inserted if a top-level render is elided. 321 query TTT 322 EXPLAIN SELECT * FROM [INSERT INTO t SELECT * FROM t2 RETURNING x] 323 ---- 324 · distributed false 325 · vectorized false 326 root · · 327 ├── scan buffer node · · 328 │ label buffer 1 329 └── subquery · · 330 │ id @S1 331 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x 332 │ exec mode all rows 333 └── buffer node · · 334 │ label buffer 1 335 └── spool · · 336 └── run · · 337 └── insert · · 338 │ into t(x) 339 │ strategy inserter 340 └── scan · · 341 · table t2@primary 342 · spans FULL SCAN 343 344 # Check that no spool is used for a top-level INSERT, but 345 # sub-INSERTs still get a spool. 346 query TTT 347 EXPLAIN INSERT INTO t SELECT x+1 FROM [INSERT INTO t SELECT * FROM t2 RETURNING x] 348 ---- 349 · distributed false 350 · vectorized false 351 root · · 352 ├── count · · 353 │ └── insert · · 354 │ │ into t(x) 355 │ │ strategy inserter 356 │ └── render · · 357 │ └── scan buffer node · · 358 │ label buffer 1 359 └── subquery · · 360 │ id @S1 361 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x 362 │ exec mode all rows 363 └── buffer node · · 364 │ label buffer 1 365 └── spool · · 366 └── run · · 367 └── insert · · 368 │ into t(x) 369 │ strategy inserter 370 └── scan · · 371 · table t2@primary 372 · spans FULL SCAN 373 374 # Check that simple computations using RETURNING get their spool pulled up. 375 query TTT 376 EXPLAIN SELECT * FROM [INSERT INTO t SELECT * FROM t2 RETURNING x+10] WHERE @1 < 3 LIMIT 10 377 ---- 378 · distributed false 379 · vectorized false 380 root · · 381 ├── limit · · 382 │ │ count 10 383 │ └── filter · · 384 │ │ filter "?column?" < 3 385 │ └── scan buffer node · · 386 │ label buffer 1 387 └── subquery · · 388 │ id @S1 389 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x + 10 390 │ exec mode all rows 391 └── buffer node · · 392 │ label buffer 1 393 └── spool · · 394 └── render · · 395 └── run · · 396 └── insert · · 397 │ into t(x) 398 │ strategy inserter 399 └── scan · · 400 · table t2@primary 401 · spans FULL SCAN 402 403 # Check that a pulled up spool gets elided at the top level. 404 query TTT 405 EXPLAIN SELECT * FROM [INSERT INTO t SELECT * FROM t2 RETURNING x+10] WHERE @1 < 3 406 ---- 407 · distributed false 408 · vectorized false 409 root · · 410 ├── filter · · 411 │ │ filter "?column?" < 3 412 │ └── scan buffer node · · 413 │ label buffer 1 414 └── subquery · · 415 │ id @S1 416 │ original sql INSERT INTO t SELECT * FROM t2 RETURNING x + 10 417 │ exec mode all rows 418 └── buffer node · · 419 │ label buffer 1 420 └── spool · · 421 └── render · · 422 └── run · · 423 └── insert · · 424 │ into t(x) 425 │ strategy inserter 426 └── scan · · 427 · table t2@primary 428 · spans FULL SCAN