github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/lookup_join (about) 1 # LogicTest: 5node 2 3 statement ok 4 CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY (a, c)) 5 6 statement ok 7 CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY (f, e)) 8 9 # Set up the statistics as if the first table is much smaller than the second. 10 # This will make lookup join into the second table be the best plan. 11 statement ok 12 ALTER TABLE abc INJECT STATISTICS '[ 13 { 14 "columns": ["a"], 15 "created_at": "2018-01-01 1:00:00.00000+00:00", 16 "row_count": 100, 17 "distinct_count": 100 18 } 19 ]' 20 21 statement ok 22 ALTER TABLE def INJECT STATISTICS '[ 23 { 24 "columns": ["f"], 25 "created_at": "2018-01-01 1:00:00.00000+00:00", 26 "row_count": 10000, 27 "distinct_count": 10000 28 } 29 ]' 30 31 query TTTTT colnames 32 EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b 33 ---- 34 tree field description columns ordering 35 · distributed true · · 36 · vectorized true · · 37 lookup-join · · (a, b, c, d, e, f) · 38 │ table def@primary · · 39 │ type inner · · 40 │ equality (b) = (f) · · 41 └── scan · · (a, b, c) · 42 · table abc@primary · · 43 · spans FULL SCAN · · 44 45 query TTTTT colnames 46 EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b AND e = c 47 ---- 48 tree field description columns ordering 49 · distributed true · · 50 · vectorized true · · 51 lookup-join · · (a, b, c, d, e, f) · 52 │ table def@primary · · 53 │ type inner · · 54 │ equality (b, c) = (f, e) · · 55 │ equality cols are key · · · 56 │ parallel · · · 57 └── scan · · (a, b, c) · 58 · table abc@primary · · 59 · spans FULL SCAN · · 60 61 query TTTTT colnames 62 EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b WHERE a > 1 AND e > 1 63 ---- 64 tree field description columns ordering 65 · distributed true · · 66 · vectorized true · · 67 lookup-join · · (a, b, c, d, e, f) · 68 │ table def@primary · · 69 │ type inner · · 70 │ equality (b) = (f) · · 71 │ pred @5 > 1 · · 72 └── scan · · (a, b, c) · 73 · table abc@primary · · 74 · spans /2- · · 75 76 query TTTTT colnames 77 EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = a WHERE f > 1 78 ---- 79 tree field description columns ordering 80 · distributed true · · 81 · vectorized true · · 82 lookup-join · · (a, b, c, d, e, f) · 83 │ table def@primary · · 84 │ type inner · · 85 │ equality (a) = (f) · · 86 │ pred @6 > 1 · · 87 └── scan · · (a, b, c) · 88 · table abc@primary · · 89 · spans /2- · · 90 91 query TTTTT colnames 92 EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b WHERE a >= e 93 ---- 94 tree field description columns ordering 95 · distributed true · · 96 · vectorized true · · 97 lookup-join · · (a, b, c, d, e, f) · 98 │ table def@primary · · 99 │ type inner · · 100 │ equality (b) = (f) · · 101 │ pred @1 >= @5 · · 102 └── scan · · (a, b, c) · 103 · table abc@primary · · 104 · spans FULL SCAN · · 105 106 query TTTTT colnames 107 EXPLAIN (VERBOSE) SELECT * FROM abc JOIN def ON f = b AND a >= e 108 ---- 109 tree field description columns ordering 110 · distributed true · · 111 · vectorized true · · 112 lookup-join · · (a, b, c, d, e, f) · 113 │ table def@primary · · 114 │ type inner · · 115 │ equality (b) = (f) · · 116 │ pred @1 >= @5 · · 117 └── scan · · (a, b, c) · 118 · table abc@primary · · 119 · spans FULL SCAN · · 120 121 # Verify a distsql plan. 122 statement ok 123 CREATE TABLE data (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b, c, d)) 124 125 # Split into ten parts. 126 statement ok 127 ALTER TABLE data SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i) 128 129 statement ok 130 ALTER TABLE data INJECT STATISTICS '[ 131 { 132 "columns": ["a"], 133 "created_at": "2018-01-01 1:00:00.00000+00:00", 134 "row_count": 100000, 135 "distinct_count": 100000 136 } 137 ]' 138 139 # Relocate the ten parts to the five nodes. 140 statement ok 141 ALTER TABLE data EXPERIMENTAL_RELOCATE 142 SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i) 143 144 # Verify data placement. 145 query TTTI colnames,rowsort 146 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE data] 147 ---- 148 start_key end_key replicas lease_holder 149 NULL /1 {1} 1 150 /1 /2 {2} 2 151 /2 /3 {3} 3 152 /3 /4 {4} 4 153 /4 /5 {5} 5 154 /5 /6 {1} 1 155 /6 /7 {2} 2 156 /7 /8 {3} 3 157 /8 /9 {4} 4 158 /9 NULL {5} 5 159 160 query TTTTT 161 EXPLAIN (VERBOSE) SELECT * FROM (SELECT * FROM data WHERE c = 1) AS l NATURAL JOIN data AS r 162 ---- 163 · distributed true · · 164 · vectorized true · · 165 render · · (a, b, c, d) · 166 │ render 0 a · · 167 │ render 1 b · · 168 │ render 2 c · · 169 │ render 3 d · · 170 └── lookup-join · · (a, b, c, d, a, b, c, d) · 171 │ table data@primary · · 172 │ type inner · · 173 │ equality (a, b, c, d) = (a, b, c, d) · · 174 │ equality cols are key · · · 175 │ parallel · · · 176 └── scan · · (a, b, c, d) · 177 · table data@primary · · 178 · spans FULL SCAN · · 179 · filter c = 1 · · 180 181 query T 182 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM (SELECT * FROM data WHERE c = 1) AS l NATURAL JOIN data AS r] 183 ---- 184 https://cockroachdb.github.io/distsqlplan/decode.html#eJzElNFq2zAUhu_3FIdz1Q4ZR7bTpoKBw5Yyl8zp4owNii-8-NB5cy1PlmEj5N2H40KTkMqGhOxSyvn5v3zHaIXV7xwFRpPp5P0CapXD7Xz2CR4m3-6n4yCEiw9BtIg-Ty_heeRtO3Cxe0wTncDXj5P5BJbwDvgljCPIIRwvvszHU7ibBWE7M45AxciwkCmFyRNVKB6QI0MHGbrI0EOGQ4wZlkouqaqkakZWm0CQ_kExYJgVZa2b65jhUipCsUKd6ZxQ4CL5ntOckpSUPUCGKekkyzc1pcqeEvXXb0CQYVQmRSXAsjkkRQocpP5BChneZrkmJcB3m78ihAjCxQjjNUNZ65fmSiePhIKvWX-6O5kVz3BDI9xUyl91CT9lVoAsBPic-Q7zXeY3gma13r16Dc55Fe6FqS6kSklRugMUrw_gh9KSpc0He5OHu92dbt5_bbzP2mxu2c4JF9fBt7W4q_Mvzukvz-klz7Fs94TyOvi25F2fX57bX57bS55r2d4J5XXwbckbnV-e11-e10ueZ9nDE8rr4NuSd_N_39sDcHOqSllU1Os1HTTvMaWP1D7elazVku6VXG5q2uNsk9tcpFTp9lfeHoKi_akB3A5zY9jZCfP9sGNu7qh2jWnPHPaO4R4aw1fm5qtjmq-N4ZG5eXRM8415V4OOz8T8ke13x-s3_wIAAP__KNhJ9Q== 185 186 statement ok 187 CREATE TABLE books (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition)) 188 189 statement ok 190 CREATE TABLE books2 (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition)) 191 192 statement ok 193 ALTER TABLE books INJECT STATISTICS '[ 194 { 195 "columns": ["title"], 196 "created_at": "2018-01-01 1:00:00.00000+00:00", 197 "row_count": 100, 198 "distinct_count": 100 199 } 200 ]' 201 202 statement ok 203 ALTER TABLE books2 INJECT STATISTICS '[ 204 { 205 "columns": ["title"], 206 "created_at": "2018-01-01 1:00:00.00000+00:00", 207 "row_count": 10000, 208 "distinct_count": 1000 209 } 210 ]' 211 212 query TTTTT colnames 213 EXPLAIN (VERBOSE) SELECT DISTINCT b1.title FROM books as b1 JOIN books2 as b2 ON b1.title = b2.title WHERE b1.shelf <> b2.shelf 214 ---- 215 tree field description columns ordering 216 · distributed true · · 217 · vectorized true · · 218 distinct · · (title) · 219 │ distinct on title · · 220 │ order key title · · 221 └── render · · (title) +title 222 │ render 0 title · · 223 └── lookup-join · · (title, shelf, title, shelf) +title 224 │ table books2@primary · · 225 │ type inner · · 226 │ equality (title) = (title) · · 227 │ pred @2 != @4 · · 228 └── scan · · (title, shelf) +title 229 · table books@primary · · 230 · spans FULL SCAN · · 231 232 statement ok 233 CREATE TABLE authors (name STRING, book STRING) 234 235 statement ok 236 ALTER TABLE authors INJECT STATISTICS '[ 237 { 238 "columns": ["name"], 239 "created_at": "2018-01-01 1:00:00.00000+00:00", 240 "row_count": 100, 241 "distinct_count": 100 242 } 243 ]' 244 245 query TTTTT colnames 246 EXPLAIN (VERBOSE) SELECT DISTINCT authors.name FROM books AS b1, books2 AS b2, authors WHERE b1.title = b2.title AND authors.book = b1.title AND b1.shelf <> b2.shelf 247 ---- 248 tree field description columns ordering 249 · distributed true · · 250 · vectorized true · · 251 distinct · · (name) · 252 │ distinct on name · · 253 └── render · · (name) · 254 │ render 0 name · · 255 └── lookup-join · · (name, book, title, shelf, title, shelf) · 256 │ table books2@primary · · 257 │ type inner · · 258 │ equality (title) = (title) · · 259 │ pred @4 != @6 · · 260 └── hash-join · · (name, book, title, shelf) · 261 │ type inner · · 262 │ equality (book) = (title) · · 263 ├── scan · · (name, book) · 264 │ table authors@primary · · 265 │ spans FULL SCAN · · 266 └── scan · · (title, shelf) · 267 · table books@primary · · 268 · spans FULL SCAN · · 269 270 # Verify data placement. 271 query TTTI colnames 272 SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE books] 273 ---- 274 start_key end_key replicas lease_holder 275 NULL NULL {5} 5 276 277 query T 278 SELECT url FROM [EXPLAIN (DISTSQL) SELECT DISTINCT authors.name FROM books AS b1, books2 AS b2, authors WHERE b1.title = b2.title AND authors.book = b1.title AND b1.shelf <> b2.shelf] 279 ---- 280 https://cockroachdb.github.io/distsqlplan/decode.html#eJyck19v2jAQwN_3KW73lEhuIX_opEiVggpTmVjoAGmTKh4ScoWswc5sR9qE-O6TEyoa1KRjbznf_c4_X-w9ql85BrgYT8d3SyhlDp_ns6_wOP7xMB1OIrBGk8Vy8W1qw7HExJPobglxqbdCqmse76iGEiGeFQwXkDisDtwqctlLMXy_H8_HYFmJc60znRPcQuLWnzYMoxFYL20Nb7LHQvuYTpxrtaX8CT5WZPVtr5AhFylF8Y4UBo_oIMMBrhgWUqxJKSHN8r4qmqS_MegzzHhRarO8YrgWkjDYY7UVBhiJK1H0BsgwJR1neVV2YChKfYKUjjeEwc2BvWrsdDdexklOc4pTkr1-oz0WMtvF8k94HAAyXBQxVwFcIcNZqQMIHRa62Obh_K-H87ZH9QNbLLxWC7fV4rR5yYVMSVJ6Pt_3S944yn2stl9Exkn23OZJcnrSVujatzLbbLUVOnZjkiz0WOi3nsS7ZJ5G4DhOr2OcRnAqxHNZwE-RcRA8gNAAswhC39zp8NPJsVXNv0RtlCmd8bXu-U2xjv6DRv933sqcVCG4on96LH3zByndUH0jlCjlmh6kWFfb1OGs4qqFlJSuszd1MOF1ygi-hp1O2GvAzjnsXgC757DXCfvd2n4nPOiGB51w_wxeHT78DQAA__8FW-lK 281 282 query TTTTT colnames 283 EXPLAIN (VERBOSE) SELECT a.name FROM authors AS a JOIN books2 AS b2 ON a.book = b2.title ORDER BY a.name 284 ---- 285 tree field description columns ordering 286 · distributed true · · 287 · vectorized true · · 288 render · · (name) +name 289 │ render 0 name · · 290 └── lookup-join · · (name, book, title) +name 291 │ table books2@primary · · 292 │ type inner · · 293 │ equality (book) = (title) · · 294 └── sort · · (name, book) +name 295 │ order +name · · 296 └── scan · · (name, book) · 297 · table authors@primary · · 298 · spans FULL SCAN · · 299 300 # Cross joins should not be planned as lookup joins. 301 query TTTTT colnames 302 EXPLAIN (VERBOSE) SELECT * FROM books CROSS JOIN books2 303 ---- 304 tree field description columns ordering 305 · distributed true · · 306 · vectorized true · · 307 render · · (title, edition, shelf, title, edition, shelf) · 308 │ render 0 title · · 309 │ render 1 edition · · 310 │ render 2 shelf · · 311 │ render 3 title · · 312 │ render 4 edition · · 313 │ render 5 shelf · · 314 └── cross-join · · (title, edition, shelf, title, edition, shelf) · 315 │ type cross · · 316 ├── scan · · (title, edition, shelf) · 317 │ table books2@primary · · 318 │ spans FULL SCAN · · 319 └── scan · · (title, edition, shelf) · 320 · table books@primary · · 321 · spans FULL SCAN · · 322 323 query T 324 SELECT url FROM [EXPLAIN (DISTSQL) SELECT * FROM authors INNER JOIN books2 ON books2.edition = 1 WHERE books2.title = authors.book] 325 ---- 326 https://cockroachdb.github.io/distsqlplan/decode.html#eJyUkdFr1EAQxt_9K4Z5Upm2l5wVWShENGLKmdTcgULJw_Z2qGvTnbi7AeW4_12S3GEr3qlvmW--35cvmQ2Gby0qXOaL_M0Ket_Cu7r6ANf556vF66KEp2-L5Wr5cfEMdpbnk0H38Yv4AEVZ5jVcVkUJNyJ3IYVq_3TKxkYrDi4ggU_v8zrfL6KNLcPFPuR0kBskdGK41PccUF1jgoTn2BB2XtYcgvhB3oymwnxHNSO0ruvjIDeEa_GMaoNjOCos5US6sxQJDUdt29G2JZQ-_oJC1LeMar6lB8HJ8eCVvmm5Zm3Yn80exWPn7b32P7LdhyHhstMuKDhBwpqdYa8gUUoV5eoVQZYQZCkeapX8T6tLsW5XKvlzqennI-FC5K7v4KtYB-IUZHPKBqbqo4IspWF-Qdk5ZS8PdksfdfvLKWoOnbjA_3SL2bYhZHPL07mD9H7NV17W42umsRq5UTAc4rSdT0PhptVQ8CGcHIXT43B6FJ79BjfbJz8DAAD__0gqE20= 327 328 #################################### 329 # LOOKUP JOIN ON SECONDARY INDEX # 330 #################################### 331 332 statement ok 333 CREATE TABLE small (a INT PRIMARY KEY, b INT, c INT, d INT) 334 335 statement ok 336 CREATE TABLE large (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX bc (b) STORING (c)) 337 338 statement ok 339 ALTER TABLE small SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i) 340 341 statement ok 342 ALTER TABLE small EXPERIMENTAL_RELOCATE 343 SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i) 344 345 statement ok 346 INSERT INTO small SELECT x, 2*x, 3*x, 4*x FROM 347 generate_series(1, 10) AS a(x) 348 349 statement ok 350 ALTER TABLE small INJECT STATISTICS '[ 351 { 352 "columns": ["a"], 353 "created_at": "2018-01-01 1:00:00.00000+00:00", 354 "row_count": 100, 355 "distinct_count": 100 356 } 357 ]' 358 359 statement ok 360 ALTER TABLE large INJECT STATISTICS '[ 361 { 362 "columns": ["a"], 363 "created_at": "2018-01-01 1:00:00.00000+00:00", 364 "row_count": 10000, 365 "distinct_count": 10000 366 } 367 ]' 368 369 # Lookup join on covering secondary index 370 query TTTTT 371 EXPLAIN (VERBOSE) SELECT small.a, large.c FROM small JOIN large ON small.a = large.b 372 ---- 373 · distributed true · · 374 · vectorized true · · 375 render · · (a, c) · 376 │ render 0 a · · 377 │ render 1 c · · 378 └── lookup-join · · (a, b, c) · 379 │ table large@bc · · 380 │ type inner · · 381 │ equality (a) = (b) · · 382 └── scan · · (a) · 383 · table small@primary · · 384 · spans FULL SCAN · · 385 386 # Lookup join on non-covering secondary index 387 query TTTTT 388 EXPLAIN (VERBOSE) SELECT small.a, large.d FROM small JOIN large ON small.a = large.b 389 ---- 390 · distributed true · · 391 · vectorized true · · 392 render · · (a, d) · 393 │ render 0 a · · 394 │ render 1 d · · 395 └── lookup-join · · (a, a, b, d) · 396 │ table large@primary · · 397 │ type inner · · 398 │ equality (a, b) = (a, b) · · 399 │ equality cols are key · · · 400 │ parallel · · · 401 └── lookup-join · · (a, a, b) · 402 │ table large@bc · · 403 │ type inner · · 404 │ equality (a) = (b) · · 405 └── scan · · (a) · 406 · table small@primary · · 407 · spans FULL SCAN · · 408 409 ############################ 410 # LEFT OUTER LOOKUP JOIN # 411 ############################ 412 413 # Left join against primary index 414 query TTTTT 415 EXPLAIN (VERBOSE) SELECT small.b, large.a FROM small LEFT JOIN large ON small.b = large.a 416 ---- 417 · distributed true · · 418 · vectorized true · · 419 lookup-join · · (b, a) · 420 │ table large@primary · · 421 │ type left outer · · 422 │ equality (b) = (a) · · 423 └── scan · · (b) · 424 · table small@primary · · 425 · spans FULL SCAN · · 426 427 # Left join should preserve input order. 428 query TTTTT 429 EXPLAIN (VERBOSE) SELECT t1.a, t2.b FROM small t1 LEFT JOIN large t2 ON t1.a = t2.a AND t2.b % 6 = 0 ORDER BY t1.a 430 ---- 431 · distributed true · · 432 · vectorized true · · 433 render · · (a, b) +a 434 │ render 0 a · · 435 │ render 1 b · · 436 └── lookup-join · · (a, a, b) +a 437 │ table large@primary · · 438 │ type left outer · · 439 │ equality (a) = (a) · · 440 │ pred (@3 % 6) = 0 · · 441 └── scan · · (a) +a 442 · table small@primary · · 443 · spans FULL SCAN · · 444 445 query T 446 SELECT url FROM [EXPLAIN (DISTSQL) SELECT t1.a, t2.b FROM small t1 LEFT JOIN large t2 ON t1.a = t2.a AND t2.b % 6 = 0 ORDER BY t1.a] 447 ---- 448 https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlV9r2zAUxd_3KS4XBglT6sh20lRQSLem4JLZnePBRvGDGovOm2t5sgwrJd99WC5rUlbb4Je8RX9Ozrm_K3yfsPydIcPNar36FEGlMrgKg89wu_p2s77wfBhdepto82U9hucrmp5wAto-uWtulg88y-BiA5rCenUVwXXg-ZBxdS_Mrg2BD6NaBee1jI_hwr-E0cj8xXuYj-EcpmMIwstVCB-_G4MYCeYyET5_ECWyW6RI0EaCDhJ0keAMY4KFkltRllLVV56MwEv-IJsSTPOi0vV2THArlUD2hDrVmUCGEb_LRCh4IpQ1RYKJ0DzNjE2h0geuHpemKCS4KXheMphYFHieAAWpfwiFBINKM1hSjHcEZaVfzErN7wUyuiP9A13LNH_OMzvMEz0WgjVYg6_RKjRwkfzLaTAjwbWUv6oCfso0B5mbZARr7kunRswY8_xoYUg__36pgSydN8uw3yzjJb1UiVAiOQy-pB8w3v2nVl9OZGHRQ-5v2TsH9rR_W2mvtlp0YtmDGtsRaa-x82NurN2frN2PrD2xnEFkOyLtkT09ZrJOf7JOP7LOxHIHke2ItEd2ccxk3f5k3X5k3Yk1G0S2I9Ie2bNjJtsxrUJRFjIvRa8v-LSeASK5F83MKGWltuJGya2xaZaB0ZmNRJS6OaXNwsubozrgvpi2iu0DMX0tttudO6ydVrXbLnaH5J61iuftzvMhzqet4kW782KI81l7r6Ydz6T9kb32jnfv_gYAAP__giJ4OQ== 449 450 # Left join against covering secondary index 451 query TTTTT 452 EXPLAIN (VERBOSE) SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b 453 ---- 454 · distributed true · · 455 · vectorized true · · 456 render · · (c, c) · 457 │ render 0 c · · 458 │ render 1 c · · 459 └── lookup-join · · (c, b, c) · 460 │ table large@bc · · 461 │ type left outer · · 462 │ equality (c) = (b) · · 463 └── scan · · (c) · 464 · table small@primary · · 465 · spans FULL SCAN · · 466 467 # Left join against non-covering secondary index 468 query TTTTT 469 EXPLAIN (VERBOSE) SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b 470 ---- 471 · distributed true · · 472 · vectorized true · · 473 render · · (c, d) · 474 │ render 0 c · · 475 │ render 1 d · · 476 └── lookup-join · · (c, a, b, d) · 477 │ table large@primary · · 478 │ type left outer · · 479 │ equality (a, b) = (a, b) · · 480 │ equality cols are key · · · 481 │ parallel · · · 482 └── lookup-join · · (c, a, b) · 483 │ table large@bc · · 484 │ type left outer · · 485 │ equality (c) = (b) · · 486 └── scan · · (c) · 487 · table small@primary · · 488 · spans FULL SCAN · · 489 490 # Left join with ON filter on covering index 491 query TTTTT 492 EXPLAIN (VERBOSE) SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b AND large.c < 20 493 ---- 494 · distributed true · · 495 · vectorized true · · 496 render · · (c, c) · 497 │ render 0 c · · 498 │ render 1 c · · 499 └── lookup-join · · (c, b, c) · 500 │ table large@bc · · 501 │ type left outer · · 502 │ equality (c) = (b) · · 503 │ pred @3 < 20 · · 504 └── scan · · (c) · 505 · table small@primary · · 506 · spans FULL SCAN · · 507 508 # Left join with ON filter on non-covering index 509 # TODO(radu): this doesn't use lookup join yet, the current rules don't cover 510 # left join with ON condition on columns that are not covered by the index. 511 query TTTTT 512 EXPLAIN (VERBOSE) SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b AND large.d < 30 513 ---- 514 · distributed true · · 515 · vectorized true · · 516 render · · (c, d) · 517 │ render 0 c · · 518 │ render 1 d · · 519 └── hash-join · · (b, d, c) · 520 │ type right outer · · 521 │ equality (b) = (c) · · 522 ├── scan · · (b, d) · 523 │ table large@primary · · 524 │ spans FULL SCAN · · 525 │ filter d < 30 · · 526 └── scan · · (c) · 527 · table small@primary · · 528 · spans FULL SCAN · · 529 530 ########################################################### 531 # LOOKUP JOINS ON IMPLICIT INDEX KEY COLUMNS # 532 # https://github.com/cockroachdb/cockroach/issues/31777 # 533 ########################################################### 534 statement ok 535 CREATE TABLE t (a INT, b INT, c INT, d INT, e INT) 536 537 statement ok 538 CREATE TABLE u (a INT, b INT, c INT, d INT, e INT, PRIMARY KEY (a DESC, b, c)) 539 540 # Test index with all primary key columns implicit. 541 statement ok 542 CREATE INDEX idx ON u (d) 543 544 query TTTTT 545 EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5 546 ---- 547 · distributed true · · 548 · vectorized true · · 549 render · · (a) · 550 │ render 0 a · · 551 └── lookup-join · · (a, d, e, a, d) · 552 │ table u@idx · · 553 │ type inner · · 554 │ equality (d, a) = (d, a) · · 555 └── scan · · (a, d, e) · 556 · table t@primary · · 557 · spans FULL SCAN · · 558 · filter e = 5 · · 559 560 # Test unique version of same index. (Lookup join should not use column a.) 561 statement ok 562 DROP INDEX u@idx 563 564 statement ok 565 CREATE UNIQUE INDEX idx ON u (d) 566 567 query TTTTT 568 EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5 569 ---- 570 · distributed true · · 571 · vectorized true · · 572 render · · (a) · 573 │ render 0 a · · 574 └── lookup-join · · (a, d, e, a, d) · 575 │ table u@idx · · 576 │ type inner · · 577 │ equality (d) = (d) · · 578 │ equality cols are key · · · 579 │ parallel · · · 580 │ pred @1 = @4 · · 581 └── scan · · (a, d, e) · 582 · table t@primary · · 583 · spans FULL SCAN · · 584 · filter e = 5 · · 585 586 # Test index with first primary key column explicit and the rest implicit. 587 statement ok 588 DROP INDEX u@idx CASCADE 589 590 statement ok 591 CREATE INDEX idx ON u (d, a) 592 593 query TTTTT 594 EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.b = u.b WHERE t.e = 5 595 ---- 596 · distributed true · · 597 · vectorized true · · 598 render · · (a) · 599 │ render 0 a · · 600 └── lookup-join · · (a, b, d, e, a, b, d) · 601 │ table u@idx · · 602 │ type inner · · 603 │ equality (d, a, b) = (d, a, b) · · 604 └── scan · · (a, b, d, e) · 605 · table t@primary · · 606 · spans FULL SCAN · · 607 · filter e = 5 · · 608 609 # Test index with middle primary key column explicit and the rest implicit. 610 statement ok 611 DROP INDEX u@idx 612 613 statement ok 614 CREATE INDEX idx ON u (d, b) 615 616 query TTTTT 617 EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.b = u.b WHERE t.e = 5 618 ---- 619 · distributed true · · 620 · vectorized true · · 621 render · · (a) · 622 │ render 0 a · · 623 └── lookup-join · · (a, b, d, e, a, b, d) · 624 │ table u@idx · · 625 │ type inner · · 626 │ equality (d, b, a) = (d, b, a) · · 627 └── scan · · (a, b, d, e) · 628 · table t@primary · · 629 · spans FULL SCAN · · 630 · filter e = 5 · · 631 632 # Test index with last primary key column explicit and the rest implicit. 633 statement ok 634 DROP INDEX u@idx 635 636 statement ok 637 CREATE INDEX idx ON u (d, c) 638 639 query TTTTT 640 EXPLAIN (VERBOSE) SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.d = u.d WHERE t.e = 5 641 ---- 642 · distributed true · · 643 · vectorized true · · 644 render · · (a) · 645 │ render 0 a · · 646 └── lookup-join · · (a, d, e, a, d) · 647 │ table u@idx · · 648 │ type inner · · 649 │ equality (d) = (d) · · 650 │ pred @1 = @4 · · 651 └── scan · · (a, d, e) · 652 · table t@primary · · 653 · spans FULL SCAN · · 654 · filter e = 5 · · 655 656 query TTTTT 657 EXPLAIN (VERBOSE) SELECT * FROM def JOIN abc ON a=f ORDER BY a 658 ---- 659 · distributed true · · 660 · vectorized true · · 661 render · · (d, e, f, a, b, c) · 662 │ render 0 d · · 663 │ render 1 e · · 664 │ render 2 f · · 665 │ render 3 a · · 666 │ render 4 b · · 667 │ render 5 c · · 668 └── lookup-join · · (a, b, c, d, e, f) +a 669 │ table def@primary · · 670 │ type inner · · 671 │ equality (a) = (f) · · 672 └── scan · · (a, b, c) +a 673 · table abc@primary · · 674 · spans FULL SCAN · · 675 676 # Test that we don't get a lookup join if we force a merge join. 677 query TTTTT 678 EXPLAIN (VERBOSE) SELECT * FROM def INNER MERGE JOIN abc ON a=f ORDER BY a 679 ---- 680 · distributed true · · 681 · vectorized true · · 682 merge-join · · (d, e, f, a, b, c) +f 683 │ type inner · · 684 │ equality (f) = (a) · · 685 │ mergeJoinOrder +"(f=a)" · · 686 ├── scan · · (d, e, f) +f 687 │ table def@primary · · 688 │ spans FULL SCAN · · 689 └── scan · · (a, b, c) +a 690 · table abc@primary · · 691 · spans FULL SCAN · · 692 693 # Test that we don't get a lookup join if we force a hash join. 694 query TTTTT 695 EXPLAIN (VERBOSE) SELECT * FROM def INNER HASH JOIN abc ON a=f ORDER BY a 696 ---- 697 · distributed true · · 698 · vectorized true · · 699 sort · · (d, e, f, a, b, c) +f 700 │ order +f · · 701 └── hash-join · · (d, e, f, a, b, c) · 702 │ type inner · · 703 │ equality (f) = (a) · · 704 ├── scan · · (d, e, f) · 705 │ table def@primary · · 706 │ spans FULL SCAN · · 707 └── scan · · (a, b, c) · 708 · table abc@primary · · 709 · spans FULL SCAN · · 710 711 # Test lookup semi and anti join. 712 query TTTTT 713 EXPLAIN (VERBOSE) SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f) 714 ---- 715 · distributed true · · 716 · vectorized true · · 717 lookup-join · · (a, b, c) · 718 │ table def@primary · · 719 │ type semi · · 720 │ equality (a) = (f) · · 721 └── scan · · (a, b, c) · 722 · table abc@primary · · 723 · spans FULL SCAN · · 724 725 query TTTTT 726 EXPLAIN (VERBOSE) SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f) 727 ---- 728 · distributed true · · 729 · vectorized true · · 730 lookup-join · · (a, b, c) · 731 │ table def@primary · · 732 │ type anti · · 733 │ equality (a) = (f) · · 734 └── scan · · (a, b, c) · 735 · table abc@primary · · 736 · spans FULL SCAN · · 737 738 query TTTTT 739 EXPLAIN (VERBOSE) SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f AND c=e) 740 ---- 741 · distributed true · · 742 · vectorized true · · 743 lookup-join · · (a, b, c) · 744 │ table def@primary · · 745 │ type semi · · 746 │ equality (a, c) = (f, e) · · 747 │ equality cols are key · · · 748 │ parallel · · · 749 └── scan · · (a, b, c) · 750 · table abc@primary · · 751 · spans FULL SCAN · · 752 753 query TTTTT 754 EXPLAIN (VERBOSE) SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f AND c=e) 755 ---- 756 · distributed true · · 757 · vectorized true · · 758 lookup-join · · (a, b, c) · 759 │ table def@primary · · 760 │ type anti · · 761 │ equality (a, c) = (f, e) · · 762 │ equality cols are key · · · 763 │ parallel · · · 764 └── scan · · (a, b, c) · 765 · table abc@primary · · 766 · spans FULL SCAN · · 767 768 query TTTTT 769 EXPLAIN (VERBOSE) SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f AND d+b>1) 770 ---- 771 · distributed true · · 772 · vectorized true · · 773 lookup-join · · (a, b, c) · 774 │ table def@primary · · 775 │ type semi · · 776 │ equality (a) = (f) · · 777 │ pred (@4 + @2) > 1 · · 778 └── scan · · (a, b, c) · 779 · table abc@primary · · 780 · spans FULL SCAN · · 781 782 query TTTTT 783 EXPLAIN (VERBOSE) SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f AND d+b>1) 784 ---- 785 · distributed true · · 786 · vectorized true · · 787 lookup-join · · (a, b, c) · 788 │ table def@primary · · 789 │ type anti · · 790 │ equality (a) = (f) · · 791 │ pred (@4 + @2) > 1 · · 792 └── scan · · (a, b, c) · 793 · table abc@primary · · 794 · spans FULL SCAN · · 795 796 query T 797 SELECT url FROM [ EXPLAIN (DISTSQL) 798 SELECT a,b from small WHERE EXISTS (SELECT a FROM data WHERE small.a=data.a) ORDER BY a 799 ] 800 ---- 801 https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlFFvmzwUhu-_X3F0rlp9psRA0hRpUraValRp0pFI61Rx4QarYyOY2SCtivLfJyBbk6o1lrjILjHn1fvwHOQNqp8Z-rgIpsHHJVQyg6tofgP3wd3t9H04g5PLcLFcfJ6ewm6EEXhoZ9SaZRl8-RREAQR39Ric_BlqJxJWst1AM3zG4F1zeMZOYR5dBhF8-AosRoK5SPiMrblC_x4pEnSQoIsEPSQ4xJhgIcWKKyVkPbJpAmHyC_0BwTQvqrI-jgmuhOTob7BMy4yjj0v2kPGIs4RLe4AEE16yNGtqCpmumXyaNGxIcFGwXPlg2RRYngAFUX7jEgnOq9KHCSUTB-MtQVGVz32qZI8cfbol5kzXIs13SMNDpOVTwX2YBldLWAQ3IVzPwxmSv6S1OyQ4FeJHVcB3keYg8hrNDNJ5E_KZTciES54cYk3o_xhvX_mSmbBEYdNDsW_Vuwf11Hxv1GhvNrVsp-_mOqj2Njc63uYcc3WOmTrHst2-6jqo9tSdH0-da67ONVPnWrbXV10H1Z668fHUeebqPDN1nmUP-6rroNpTd_FvXLWvQEZcFSJX3OgWHdT3ME8eeXtvK1HJFb-VYtXUtI_zJtccJFyV7VvaPoR5-6oG3A9Tbdg5CNOXYUff3FHtatOePuz14R5qwyN986hP87k2PNY3j_s0X-h3Nej4TfQ_2cvuePvf7wAAAP__PhVS0w== 802 803 query T 804 SELECT url FROM [ EXPLAIN (DISTSQL) 805 SELECT a,b from small WHERE a+b<20 AND EXISTS (SELECT a FROM data WHERE small.a=data.a AND small.b+data.c>15) ORDER BY a 806 ] 807 ---- 808 https://cockroachdb.github.io/distsqlplan/decode.html#eJzMleFrm0AYxr_vr3h5PxlyqZ6aNj0Y2K2WWVLTJYF1dPlw0aNzs547FVZK_vehpqspqzoKIR99vcfneX-PcI-Y_YqR4cKduh-XUKgYLuazK7h1b66nZ54P2rm3WC4-TwewPcIJrOsz2T2PY_jyyZ27oGkchrAewLfCMKwATGMAZ_45uDelHLQnca0Mec6fhNVXjji8r6ZHvNZp2_kahvU82H5aAB0PBjCbn7tz-PAV-AoJJjIUPr8XGbJbpEjQRIIWErSR4BhXBFMlA5FlUpVHHiuBF_5GZhCMkrTIy_GKYCCVQPaIeZTHAhku-ToWc8FDoXQDCYYi51Fc2aQquufqwalyIsFFypOMwUinwJMQKMj8u1BI8CKKc6EYaA6FIThmgxFjzPOXEyQ4K3IGDiWOiasNQVnkz6GynN8JZHRD-ge_lFGyzT3ezb18SAWDqXuxhIV75cHlzPOR_F2nhI0Ep1L-LFL4IaMEZFJGK0P6oDlmucS40cZ_LWG-usRzdqlCoUS4G9uhQ1xt_rGpL0cy1eluO6_ZWzv2tH_5tFf5Oh3p5l7q74jeqP_4cOs3-_M3-_E3R7q1F_4d0Rv8Tw6Xv9Wfv9WPvzXS7b3w74je4D85XP52f_52P_72SB_vhX9H9Ab_08Pl33GHzkWWyiQTvW4Wo7ybRHgn6rssk4UKxLWSQWVTP84qXTUIRZbXb2n94CX1qzJgU0xbxeaOmL4Um-3OHdZWq9puF9tvyT1uFR-3Ox-_xfmkVTxpd568xfm0vSuj4zdp_8leeq827_4EAAD__5SUslA= 809 810 # Regression test for #35950: Make sure that lookup joins use a batch limit. 811 812 statement ok 813 CREATE TABLE a (a INT, b INT, PRIMARY KEY (a, b)) 814 815 statement ok 816 CREATE TABLE b (a INT PRIMARY KEY) 817 818 # We insert over 10k rows, which is the currently configured batch limit. 819 820 statement ok 821 INSERT INTO a SELECT 1, g FROM generate_series(1,11000) g 822 823 statement ok 824 INSERT INTO b VALUES(1) 825 826 query TTT 827 EXPLAIN SELECT count(*) FROM (SELECT * FROM b NATURAL INNER LOOKUP JOIN a) 828 ---- 829 · distributed true 830 · vectorized true 831 group · · 832 │ aggregate 0 count_rows() 833 │ scalar · 834 └── render · · 835 └── lookup-join · · 836 │ table a@primary 837 │ type inner 838 │ equality (a) = (a) 839 └── scan · · 840 · table b@primary 841 · spans FULL SCAN 842 843 statement ok 844 SET tracing = on 845 846 query I 847 SELECT count(*) FROM (SELECT * FROM b NATURAL INNER LOOKUP JOIN a) 848 ---- 849 11000 850 851 statement ok 852 SET tracing = off 853 854 let $lookupTableID 855 SELECT 'a'::regclass::oid 856 857 # Now assert that we get more than 1 separate batch request into the lookup 858 # table, since the first one wouldn't have returned all of the results. 859 860 query T 861 SELECT message FROM [SHOW TRACE FOR SESSION] WHERE message LIKE 'Scan /Table/$lookupTableID%' 862 ---- 863 Scan /Table/63/1/{1-2} 864 Scan /Table/63/1/{1/10001/0-2} 865 866 867 # Regression test for #40562. 868 869 statement ok 870 CREATE TABLE public.region 871 ( 872 r_regionkey int PRIMARY KEY, 873 r_name char(25) NOT NULL, 874 r_comment varchar(152) 875 ) 876 877 statement ok 878 ALTER TABLE public.region INJECT STATISTICS '[ 879 { 880 "columns": ["r_regionkey"], 881 "created_at": "2018-01-01 1:00:00.00000+00:00", 882 "row_count": 5, 883 "distinct_count": 5 884 }, 885 { 886 "columns": ["r_name"], 887 "created_at": "2018-01-01 1:00:00.00000+00:00", 888 "row_count": 5, 889 "distinct_count": 5 890 }, 891 { 892 "columns": ["r_comment"], 893 "created_at": "2018-01-01 1:00:00.00000+00:00", 894 "row_count": 5, 895 "distinct_count": 5 896 } 897 ]' 898 899 statement ok 900 CREATE TABLE public.nation 901 ( 902 n_nationkey int PRIMARY KEY, 903 n_name char(25) NOT NULL, 904 n_regionkey int NOT NULL, 905 n_comment varchar(152), 906 INDEX n_rk (n_regionkey ASC), 907 CONSTRAINT nation_fkey_region FOREIGN KEY (n_regionkey) references public.region (r_regionkey) 908 ) 909 910 statement ok 911 ALTER TABLE public.nation INJECT STATISTICS '[ 912 { 913 "columns": ["n_nationkey"], 914 "created_at": "2018-01-01 1:00:00.00000+00:00", 915 "row_count": 25, 916 "distinct_count": 25 917 }, 918 { 919 "columns": ["n_name"], 920 "created_at": "2018-01-01 1:00:00.00000+00:00", 921 "row_count": 25, 922 "distinct_count": 25 923 }, 924 { 925 "columns": ["n_regionkey"], 926 "created_at": "2018-01-01 1:00:00.00000+00:00", 927 "row_count": 25, 928 "distinct_count": 5 929 }, 930 { 931 "columns": ["n_comment"], 932 "created_at": "2018-01-01 1:00:00.00000+00:00", 933 "row_count": 25, 934 "distinct_count": 25 935 } 936 ]' 937 938 statement ok 939 CREATE TABLE public.supplier 940 ( 941 s_suppkey int PRIMARY KEY, 942 s_name char(25) NOT NULL, 943 s_address varchar(40) NOT NULL, 944 s_nationkey int NOT NULL, 945 s_phone char(15) NOT NULL, 946 s_acctbal float NOT NULL, 947 s_comment varchar(101) NOT NULL, 948 INDEX s_nk (s_nationkey ASC), 949 CONSTRAINT supplier_fkey_nation FOREIGN KEY (s_nationkey) references public.nation (n_nationkey) 950 ) 951 952 statement ok 953 ALTER TABLE public.supplier INJECT STATISTICS '[ 954 { 955 "columns": ["s_suppkey"], 956 "created_at": "2018-01-01 1:00:00.00000+00:00", 957 "row_count": 10000, 958 "distinct_count": 10000 959 }, 960 { 961 "columns": ["s_name"], 962 "created_at": "2018-01-01 1:00:00.00000+00:00", 963 "row_count": 10000, 964 "distinct_count": 10000 965 }, 966 { 967 "columns": ["s_address"], 968 "created_at": "2018-01-01 1:00:00.00000+00:00", 969 "row_count": 10000, 970 "distinct_count": 10000 971 }, 972 { 973 "columns": ["s_nationkey"], 974 "created_at": "2018-01-01 1:00:00.00000+00:00", 975 "row_count": 10000, 976 "distinct_count": 25 977 }, 978 { 979 "columns": ["s_phone"], 980 "created_at": "2018-01-01 1:00:00.00000+00:00", 981 "row_count": 10000, 982 "distinct_count": 10000 983 }, 984 { 985 "columns": ["s_acctbal"], 986 "created_at": "2018-01-01 1:00:00.00000+00:00", 987 "row_count": 10000, 988 "distinct_count": 10000 989 }, 990 { 991 "columns": ["s_comment"], 992 "created_at": "2018-01-01 1:00:00.00000+00:00", 993 "row_count": 10000, 994 "distinct_count": 10000 995 } 996 ]' 997 998 statement ok 999 CREATE TABLE public.part 1000 ( 1001 p_partkey int PRIMARY KEY, 1002 p_name varchar(55) NOT NULL, 1003 p_mfgr char(25) NOT NULL, 1004 p_brand char(10) NOT NULL, 1005 p_type varchar(25) NOT NULL, 1006 p_size int NOT NULL, 1007 p_container char(10) NOT NULL, 1008 p_retailprice float NOT NULL, 1009 p_comment varchar(23) NOT NULL 1010 ) 1011 1012 statement ok 1013 ALTER TABLE public.part INJECT STATISTICS '[ 1014 { 1015 "columns": ["p_partkey"], 1016 "created_at": "2018-01-01 1:00:00.00000+00:00", 1017 "row_count": 200000, 1018 "distinct_count": 200000 1019 }, 1020 { 1021 "columns": ["p_name"], 1022 "created_at": "2018-01-01 1:00:00.00000+00:00", 1023 "row_count": 200000, 1024 "distinct_count": 200000 1025 }, 1026 { 1027 "columns": ["p_mfgr"], 1028 "created_at": "2018-01-01 1:00:00.00000+00:00", 1029 "row_count": 200000, 1030 "distinct_count": 5 1031 }, 1032 { 1033 "columns": ["p_brand"], 1034 "created_at": "2018-01-01 1:00:00.00000+00:00", 1035 "row_count": 200000, 1036 "distinct_count": 25 1037 }, 1038 { 1039 "columns": ["p_type"], 1040 "created_at": "2018-01-01 1:00:00.00000+00:00", 1041 "row_count": 200000, 1042 "distinct_count": 150 1043 }, 1044 { 1045 "columns": ["p_size"], 1046 "created_at": "2018-01-01 1:00:00.00000+00:00", 1047 "row_count": 200000, 1048 "distinct_count": 50 1049 }, 1050 { 1051 "columns": ["p_container"], 1052 "created_at": "2018-01-01 1:00:00.00000+00:00", 1053 "row_count": 200000, 1054 "distinct_count": 40 1055 }, 1056 { 1057 "columns": ["p_retailprice"], 1058 "created_at": "2018-01-01 1:00:00.00000+00:00", 1059 "row_count": 200000, 1060 "distinct_count": 20000 1061 }, 1062 { 1063 "columns": ["p_comment"], 1064 "created_at": "2018-01-01 1:00:00.00000+00:00", 1065 "row_count": 200000, 1066 "distinct_count": 130000 1067 } 1068 ]' 1069 1070 statement ok 1071 CREATE TABLE public.partsupp 1072 ( 1073 ps_partkey int NOT NULL, 1074 ps_suppkey int NOT NULL, 1075 ps_availqty int NOT NULL, 1076 ps_supplycost float NOT NULL, 1077 ps_comment varchar(199) NOT NULL, 1078 PRIMARY KEY (ps_partkey, ps_suppkey), 1079 INDEX ps_sk (ps_suppkey ASC), 1080 CONSTRAINT partsupp_fkey_part FOREIGN KEY (ps_partkey) references public.part (p_partkey), 1081 CONSTRAINT partsupp_fkey_supplier FOREIGN KEY (ps_suppkey) references public.supplier (s_suppkey) 1082 ) 1083 1084 statement ok 1085 ALTER TABLE public.partsupp INJECT STATISTICS '[ 1086 { 1087 "columns": ["ps_partkey"], 1088 "created_at": "2018-01-01 1:00:00.00000+00:00", 1089 "row_count": 800000, 1090 "distinct_count": 200000 1091 }, 1092 { 1093 "columns": ["ps_suppkey"], 1094 "created_at": "2018-01-01 1:00:00.00000+00:00", 1095 "row_count": 800000, 1096 "distinct_count": 10000 1097 }, 1098 { 1099 "columns": ["ps_availqty"], 1100 "created_at": "2018-01-01 1:00:00.00000+00:00", 1101 "row_count": 800000, 1102 "distinct_count": 10000 1103 }, 1104 { 1105 "columns": ["ps_supplycost"], 1106 "created_at": "2018-01-01 1:00:00.00000+00:00", 1107 "row_count": 800000, 1108 "distinct_count": 100000 1109 }, 1110 { 1111 "columns": ["ps_comment"], 1112 "created_at": "2018-01-01 1:00:00.00000+00:00", 1113 "row_count": 800000, 1114 "distinct_count": 800000 1115 } 1116 ]' 1117 1118 statement ok 1119 CREATE TABLE public.customer 1120 ( 1121 c_custkey int PRIMARY KEY, 1122 c_name varchar(25) NOT NULL, 1123 c_address varchar(40) NOT NULL, 1124 c_nationkey int NOT NULL NOT NULL, 1125 c_phone char(15) NOT NULL, 1126 c_acctbal float NOT NULL, 1127 c_mktsegment char(10) NOT NULL, 1128 c_comment varchar(117) NOT NULL, 1129 INDEX c_nk (c_nationkey ASC), 1130 CONSTRAINT customer_fkey_nation FOREIGN KEY (c_nationkey) references public.nation (n_nationkey) 1131 ) 1132 1133 statement ok 1134 ALTER TABLE public.customer INJECT STATISTICS '[ 1135 { 1136 "columns": ["c_custkey"], 1137 "created_at": "2018-01-01 1:00:00.00000+00:00", 1138 "row_count": 150000, 1139 "distinct_count": 150000 1140 }, 1141 { 1142 "columns": ["c_name"], 1143 "created_at": "2018-01-01 1:00:00.00000+00:00", 1144 "row_count": 150000, 1145 "distinct_count": 150000 1146 }, 1147 { 1148 "columns": ["c_address"], 1149 "created_at": "2018-01-01 1:00:00.00000+00:00", 1150 "row_count": 150000, 1151 "distinct_count": 150000 1152 }, 1153 { 1154 "columns": ["c_nationkey"], 1155 "created_at": "2018-01-01 1:00:00.00000+00:00", 1156 "row_count": 150000, 1157 "distinct_count": 25 1158 }, 1159 { 1160 "columns": ["c_phone"], 1161 "created_at": "2018-01-01 1:00:00.00000+00:00", 1162 "row_count": 150000, 1163 "distinct_count": 150000 1164 }, 1165 { 1166 "columns": ["c_acctbal"], 1167 "created_at": "2018-01-01 1:00:00.00000+00:00", 1168 "row_count": 150000, 1169 "distinct_count": 150000 1170 }, 1171 { 1172 "columns": ["c_mktsegment"], 1173 "created_at": "2018-01-01 1:00:00.00000+00:00", 1174 "row_count": 150000, 1175 "distinct_count": 5 1176 }, 1177 { 1178 "columns": ["c_comment"], 1179 "created_at": "2018-01-01 1:00:00.00000+00:00", 1180 "row_count": 150000, 1181 "distinct_count": 150000 1182 } 1183 ]' 1184 1185 statement ok 1186 CREATE TABLE public.orders 1187 ( 1188 o_orderkey int PRIMARY KEY, 1189 o_custkey int NOT NULL, 1190 o_orderstatus char(1) NOT NULL, 1191 o_totalprice float NOT NULL, 1192 o_orderdate date NOT NULL, 1193 o_orderpriority char(15) NOT NULL, 1194 o_clerk char(15) NOT NULL, 1195 o_shippriority int NOT NULL, 1196 o_comment varchar(79) NOT NULL, 1197 INDEX o_ck (o_custkey ASC), 1198 INDEX o_od (o_orderdate ASC), 1199 CONSTRAINT orders_fkey_customer FOREIGN KEY (o_custkey) references public.customer (c_custkey) 1200 ) 1201 1202 statement ok 1203 ALTER TABLE public.orders INJECT STATISTICS '[ 1204 { 1205 "columns": ["o_orderkey"], 1206 "created_at": "2018-01-01 1:00:00.00000+00:00", 1207 "row_count": 1500000, 1208 "distinct_count": 1500000 1209 }, 1210 { 1211 "columns": ["o_custkey"], 1212 "created_at": "2018-01-01 1:00:00.00000+00:00", 1213 "row_count": 1500000, 1214 "distinct_count": 100000 1215 }, 1216 { 1217 "columns": ["o_orderstatus"], 1218 "created_at": "2018-01-01 1:00:00.00000+00:00", 1219 "row_count": 1500000, 1220 "distinct_count": 3 1221 }, 1222 { 1223 "columns": ["o_totalprice"], 1224 "created_at": "2018-01-01 1:00:00.00000+00:00", 1225 "row_count": 1500000, 1226 "distinct_count": 1500000 1227 }, 1228 { 1229 "columns": ["o_orderdate"], 1230 "created_at": "2018-01-01 1:00:00.00000+00:00", 1231 "row_count": 1500000, 1232 "distinct_count": 2500 1233 }, 1234 { 1235 "columns": ["o_orderpriority"], 1236 "created_at": "2018-01-01 1:00:00.00000+00:00", 1237 "row_count": 1500000, 1238 "distinct_count": 5 1239 }, 1240 { 1241 "columns": ["o_clerk"], 1242 "created_at": "2018-01-01 1:00:00.00000+00:00", 1243 "row_count": 1500000, 1244 "distinct_count": 1000 1245 }, 1246 { 1247 "columns": ["o_shippriority"], 1248 "created_at": "2018-01-01 1:00:00.00000+00:00", 1249 "row_count": 1500000, 1250 "distinct_count": 1 1251 }, 1252 { 1253 "columns": ["o_comment"], 1254 "created_at": "2018-01-01 1:00:00.00000+00:00", 1255 "row_count": 1500000, 1256 "distinct_count": 1500000 1257 } 1258 ]' 1259 1260 statement ok 1261 CREATE TABLE public.lineitem 1262 ( 1263 l_orderkey int NOT NULL, 1264 l_partkey int NOT NULL, 1265 l_suppkey int NOT NULL, 1266 l_linenumber int NOT NULL, 1267 l_quantity float NOT NULL, 1268 l_extendedprice float NOT NULL, 1269 l_discount float NOT NULL, 1270 l_tax float NOT NULL, 1271 l_returnflag char(1) NOT NULL, 1272 l_linestatus char(1) NOT NULL, 1273 l_shipdate date NOT NULL, 1274 l_commitdate date NOT NULL, 1275 l_receiptdate date NOT NULL, 1276 l_shipinstruct char(25) NOT NULL, 1277 l_shipmode char(10) NOT NULL, 1278 l_comment varchar(44) NOT NULL, 1279 PRIMARY KEY (l_orderkey, l_linenumber), 1280 INDEX l_ok (l_orderkey ASC), 1281 INDEX l_pk (l_partkey ASC), 1282 INDEX l_sk (l_suppkey ASC), 1283 INDEX l_sd (l_shipdate ASC), 1284 INDEX l_cd (l_commitdate ASC), 1285 INDEX l_rd (l_receiptdate ASC), 1286 INDEX l_pk_sk (l_partkey ASC, l_suppkey ASC), 1287 INDEX l_sk_pk (l_suppkey ASC, l_partkey ASC), 1288 CONSTRAINT lineitem_fkey_orders FOREIGN KEY (l_orderkey) references public.orders (o_orderkey), 1289 CONSTRAINT lineitem_fkey_part FOREIGN KEY (l_partkey) references public.part (p_partkey), 1290 CONSTRAINT lineitem_fkey_supplier FOREIGN KEY (l_suppkey) references public.supplier (s_suppkey) 1291 ) 1292 1293 statement ok 1294 ALTER TABLE public.lineitem INJECT STATISTICS '[ 1295 { 1296 "columns": ["l_orderkey"], 1297 "created_at": "2018-01-01 1:00:00.00000+00:00", 1298 "row_count": 6001215, 1299 "distinct_count": 1500000 1300 }, 1301 { 1302 "columns": ["l_partkey"], 1303 "created_at": "2018-01-01 1:00:00.00000+00:00", 1304 "row_count": 6001215, 1305 "distinct_count": 200000 1306 }, 1307 { 1308 "columns": ["l_suppkey"], 1309 "created_at": "2018-01-01 1:00:00.00000+00:00", 1310 "row_count": 6001215, 1311 "distinct_count": 10000 1312 }, 1313 { 1314 "columns": ["l_linenumber"], 1315 "created_at": "2018-01-01 1:00:00.00000+00:00", 1316 "row_count": 6001215, 1317 "distinct_count": 7 1318 }, 1319 { 1320 "columns": ["l_quantity"], 1321 "created_at": "2018-01-01 1:00:00.00000+00:00", 1322 "row_count": 6001215, 1323 "distinct_count": 50 1324 }, 1325 { 1326 "columns": ["l_extendedprice"], 1327 "created_at": "2018-01-01 1:00:00.00000+00:00", 1328 "row_count": 6001215, 1329 "distinct_count": 1000000 1330 }, 1331 { 1332 "columns": ["l_discount"], 1333 "created_at": "2018-01-01 1:00:00.00000+00:00", 1334 "row_count": 6001215, 1335 "distinct_count": 11 1336 }, 1337 { 1338 "columns": ["l_tax"], 1339 "created_at": "2018-01-01 1:00:00.00000+00:00", 1340 "row_count": 6001215, 1341 "distinct_count": 9 1342 }, 1343 { 1344 "columns": ["l_returnflag"], 1345 "created_at": "2018-01-01 1:00:00.00000+00:00", 1346 "row_count": 6001215, 1347 "distinct_count": 3 1348 }, 1349 { 1350 "columns": ["l_linestatus"], 1351 "created_at": "2018-01-01 1:00:00.00000+00:00", 1352 "row_count": 6001215, 1353 "distinct_count": 2 1354 }, 1355 { 1356 "columns": ["l_shipdate"], 1357 "created_at": "2018-01-01 1:00:00.00000+00:00", 1358 "row_count": 6001215, 1359 "distinct_count": 2500 1360 }, 1361 { 1362 "columns": ["l_commitdate"], 1363 "created_at": "2018-01-01 1:00:00.00000+00:00", 1364 "row_count": 6001215, 1365 "distinct_count": 2500 1366 }, 1367 { 1368 "columns": ["l_receiptdate"], 1369 "created_at": "2018-01-01 1:00:00.00000+00:00", 1370 "row_count": 6001215, 1371 "distinct_count": 2500 1372 }, 1373 { 1374 "columns": ["l_shipinstruct"], 1375 "created_at": "2018-01-01 1:00:00.00000+00:00", 1376 "row_count": 6001215, 1377 "distinct_count": 4 1378 }, 1379 { 1380 "columns": ["l_shipmode"], 1381 "created_at": "2018-01-01 1:00:00.00000+00:00", 1382 "row_count": 6001215, 1383 "distinct_count": 7 1384 }, 1385 { 1386 "columns": ["l_comment"], 1387 "created_at": "2018-01-01 1:00:00.00000+00:00", 1388 "row_count": 6001215, 1389 "distinct_count": 4500000 1390 } 1391 ]' 1392 1393 query TTT 1394 EXPLAIN SELECT s_name, count(*) AS numwait 1395 FROM supplier, lineitem AS l1, orders, nation 1396 WHERE s_suppkey = l1.l_suppkey 1397 AND o_orderkey = l1.l_orderkey 1398 AND o_orderstatus = 'F' 1399 AND l1.l_receiptdate > l1.l_commitdate 1400 AND EXISTS( 1401 SELECT * 1402 FROM lineitem AS l2 1403 WHERE l2.l_orderkey = l1.l_orderkey 1404 AND l2.l_suppkey != l1.l_suppkey 1405 ) 1406 AND NOT EXISTS( 1407 SELECT * 1408 FROM lineitem AS l3 1409 WHERE l3.l_orderkey = l1.l_orderkey 1410 AND l3.l_receiptdate > l3.l_commitdate 1411 ) 1412 AND s_nationkey = n_nationkey 1413 AND n_name = 'SAUDI ARABIA' 1414 GROUP BY s_name 1415 ORDER BY numwait DESC, s_name 1416 LIMIT 100; 1417 ---- 1418 · distributed true 1419 · vectorized true 1420 limit · · 1421 │ count 100 1422 └── sort · · 1423 │ order -numwait,+s_name 1424 └── group · · 1425 │ aggregate 0 s_name 1426 │ aggregate 1 count_rows() 1427 │ group by s_name 1428 └── render · · 1429 └── lookup-join · · 1430 │ table orders@primary 1431 │ type inner 1432 │ equality (l_orderkey) = (o_orderkey) 1433 │ equality cols are key · 1434 │ parallel · 1435 │ pred @11 = 'F' 1436 └── lookup-join · · 1437 │ table nation@primary 1438 │ type inner 1439 │ equality (s_nationkey) = (n_nationkey) 1440 │ equality cols are key · 1441 │ parallel · 1442 │ pred @9 = 'SAUDI ARABIA' 1443 └── lookup-join · · 1444 │ table supplier@primary 1445 │ type inner 1446 │ equality (l_suppkey) = (s_suppkey) 1447 │ equality cols are key · 1448 │ parallel · 1449 └── lookup-join · · 1450 │ table lineitem@primary 1451 │ type semi 1452 │ equality (l_orderkey) = (l_orderkey) 1453 │ pred @6 != @2 1454 └── merge-join · · 1455 │ type anti 1456 │ equality (l_orderkey) = (l_orderkey) 1457 │ mergeJoinOrder +"(l_orderkey=l_orderkey)" 1458 ├── scan · · 1459 │ table lineitem@primary 1460 │ spans FULL SCAN 1461 │ filter l_receiptdate > l_commitdate 1462 └── scan · · 1463 · table lineitem@primary 1464 · spans FULL SCAN 1465 · filter l_receiptdate > l_commitdate