github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/rules/join (about) 1 exec-ddl 2 CREATE TABLE abc 3 ( 4 a INT, 5 b INT, 6 c INT, 7 INDEX ab (a,b) STORING (c), 8 INDEX bc (b,c) STORING (a) 9 ) 10 ---- 11 12 exec-ddl 13 CREATE TABLE stu 14 ( 15 s INT, 16 t INT, 17 u INT, 18 PRIMARY KEY (s,t,u), 19 INDEX uts (u,t,s) 20 ) 21 ---- 22 23 exec-ddl 24 CREATE TABLE xyz 25 ( 26 x INT, 27 y INT, 28 z INT, 29 INDEX xy (x,y) STORING (z), 30 INDEX yz (y,z) STORING (x) 31 ) 32 ---- 33 34 exec-ddl 35 CREATE TABLE pqr 36 ( 37 p INT PRIMARY KEY, 38 q INT, 39 r INT, 40 s STRING, 41 t STRING, 42 INDEX q (q), 43 INDEX r (r), 44 INDEX s (s) STORING (r), 45 INDEX rs (r,s), 46 INDEX ts (t,s) 47 ) 48 ---- 49 50 exec-ddl 51 CREATE TABLE zz ( 52 a INT8 PRIMARY KEY, 53 b INT8 NULL, 54 c INT8 NULL, 55 INDEX idx_b (b ASC), 56 CONSTRAINT idx_c UNIQUE (c) 57 ) 58 ---- 59 60 exec-ddl 61 CREATE TABLE zz_redundant ( 62 a INT8 PRIMARY KEY, 63 b INT8 NULL, 64 c INT8 NULL, 65 INDEX idx_u (b ASC, c ASC), 66 INDEX idx_v (b ASC, c ASC) 67 ) 68 ---- 69 70 # -------------------------------------------------- 71 # CommuteJoin 72 # -------------------------------------------------- 73 74 # Verify that the reversed join expressions get added to the memo, and there 75 # are no duplicates. 76 memo 77 SELECT * FROM abc JOIN xyz ON a=z 78 ---- 79 memo (optimized, ~10KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 80 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) (merge-join G2 G3 G5 inner-join,+1,+7) (lookup-join G3 G5 abc@ab,keyCols=[7],outCols=(1-3,5-7)) 81 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 82 │ ├── best: (inner-join G2 G3 G4) 83 │ └── cost: 2268.06 84 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 85 │ ├── [ordering: +1] 86 │ │ ├── best: (scan abc@ab,cols=(1-3)) 87 │ │ └── cost: 1070.02 88 │ └── [] 89 │ ├── best: (scan abc,cols=(1-3)) 90 │ └── cost: 1070.02 91 ├── G3: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 92 │ ├── [ordering: +7] 93 │ │ ├── best: (sort G3) 94 │ │ └── cost: 1289.35 95 │ └── [] 96 │ ├── best: (scan xyz,cols=(5-7)) 97 │ └── cost: 1070.02 98 ├── G4: (filters G6) 99 ├── G5: (filters) 100 ├── G6: (eq G7 G8) 101 ├── G7: (variable a) 102 └── G8: (variable z) 103 104 memo 105 SELECT * FROM abc FULL OUTER JOIN xyz ON a=z 106 ---- 107 memo (optimized, ~9KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 108 ├── G1: (full-join G2 G3 G4) (full-join G3 G2 G4) (merge-join G2 G3 G5 full-join,+1,+7) 109 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 110 │ ├── best: (full-join G2 G3 G4) 111 │ └── cost: 2270.05 112 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 113 │ ├── [ordering: +1] 114 │ │ ├── best: (scan abc@ab,cols=(1-3)) 115 │ │ └── cost: 1070.02 116 │ └── [] 117 │ ├── best: (scan abc,cols=(1-3)) 118 │ └── cost: 1070.02 119 ├── G3: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 120 │ ├── [ordering: +7] 121 │ │ ├── best: (sort G3) 122 │ │ └── cost: 1289.35 123 │ └── [] 124 │ ├── best: (scan xyz,cols=(5-7)) 125 │ └── cost: 1070.02 126 ├── G4: (filters G6) 127 ├── G5: (filters) 128 ├── G6: (eq G7 G8) 129 ├── G7: (variable a) 130 └── G8: (variable z) 131 132 # Verify that we swap to get the smaller side on the right. 133 opt 134 SELECT * FROM abc INNER JOIN xyz ON a=c WHERE b=1 135 ---- 136 inner-join (cross) 137 ├── columns: a:1!null b:2!null c:3!null x:5 y:6 z:7 138 ├── fd: ()-->(2), (1)==(3), (3)==(1) 139 ├── scan xyz 140 │ └── columns: x:5 y:6 z:7 141 ├── select 142 │ ├── columns: a:1!null b:2!null c:3!null 143 │ ├── fd: ()-->(2), (1)==(3), (3)==(1) 144 │ ├── scan abc@bc 145 │ │ ├── columns: a:1 b:2!null c:3!null 146 │ │ ├── constraint: /2/3/4: (/1/NULL - /1] 147 │ │ └── fd: ()-->(2) 148 │ └── filters 149 │ └── a:1 = c:3 [outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 150 └── filters (true) 151 152 # Verify that the hash join hint prevents swapping the sides. 153 opt 154 SELECT * FROM abc INNER HASH JOIN xyz ON a=c WHERE b=1 155 ---- 156 inner-join (cross) 157 ├── columns: a:1!null b:2!null c:3!null x:5 y:6 z:7 158 ├── flags: force hash join (store right side) 159 ├── fd: ()-->(2), (1)==(3), (3)==(1) 160 ├── select 161 │ ├── columns: a:1!null b:2!null c:3!null 162 │ ├── fd: ()-->(2), (1)==(3), (3)==(1) 163 │ ├── scan abc@bc 164 │ │ ├── columns: a:1 b:2!null c:3!null 165 │ │ ├── constraint: /2/3/4: (/1/NULL - /1] 166 │ │ └── fd: ()-->(2) 167 │ └── filters 168 │ └── a:1 = c:3 [outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 169 ├── scan xyz 170 │ └── columns: x:5 y:6 z:7 171 └── filters (true) 172 173 opt 174 SELECT * FROM (SELECT * FROM abc WHERE b=1) FULL OUTER JOIN xyz ON a=z 175 ---- 176 full-join (hash) 177 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 178 ├── scan xyz 179 │ └── columns: x:5 y:6 z:7 180 ├── scan abc@bc 181 │ ├── columns: a:1 b:2!null c:3 182 │ ├── constraint: /2/3/4: [/1 - /1] 183 │ └── fd: ()-->(2) 184 └── filters 185 └── a:1 = z:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 186 187 # Verify that commuting works correctly when there is a lookup join hint 188 # (specifically that it returns the original expression and flags when applied 189 # twice; if it didn't, we'd see more inner-join expressions). 190 memo 191 SELECT * FROM abc INNER LOOKUP JOIN xyz ON a=x 192 ---- 193 memo (optimized, ~10KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 194 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) (lookup-join G2 G5 xyz@xy,keyCols=[1],outCols=(1-3,5-7)) 195 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 196 │ ├── best: (lookup-join G2 G5 xyz@xy,keyCols=[1],outCols=(1-3,5-7)) 197 │ └── cost: 41358.10 198 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 199 │ └── [] 200 │ ├── best: (scan abc,cols=(1-3)) 201 │ └── cost: 1070.02 202 ├── G3: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 203 │ └── [] 204 │ ├── best: (scan xyz,cols=(5-7)) 205 │ └── cost: 1070.02 206 ├── G4: (filters G6) 207 ├── G5: (filters) 208 ├── G6: (eq G7 G8) 209 ├── G7: (variable a) 210 └── G8: (variable x) 211 212 # -------------------------------------------------- 213 # CommuteLeftJoin 214 # -------------------------------------------------- 215 216 memo 217 SELECT * FROM abc LEFT OUTER JOIN xyz ON a=z 218 ---- 219 memo (optimized, ~9KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 220 ├── G1: (left-join G2 G3 G4) (right-join G3 G2 G4) (merge-join G2 G3 G5 left-join,+1,+7) 221 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 222 │ ├── best: (left-join G2 G3 G4) 223 │ └── cost: 2270.05 224 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 225 │ ├── [ordering: +1] 226 │ │ ├── best: (scan abc@ab,cols=(1-3)) 227 │ │ └── cost: 1070.02 228 │ └── [] 229 │ ├── best: (scan abc,cols=(1-3)) 230 │ └── cost: 1070.02 231 ├── G3: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 232 │ ├── [ordering: +7] 233 │ │ ├── best: (sort G3) 234 │ │ └── cost: 1289.35 235 │ └── [] 236 │ ├── best: (scan xyz,cols=(5-7)) 237 │ └── cost: 1070.02 238 ├── G4: (filters G6) 239 ├── G5: (filters) 240 ├── G6: (eq G7 G8) 241 ├── G7: (variable a) 242 └── G8: (variable z) 243 244 opt 245 SELECT * FROM abc LEFT OUTER JOIN xyz ON a=z WHERE b=1 246 ---- 247 right-join (hash) 248 ├── columns: a:1 b:2!null c:3 x:5 y:6 z:7 249 ├── fd: ()-->(2) 250 ├── scan xyz 251 │ └── columns: x:5 y:6 z:7 252 ├── scan abc@bc 253 │ ├── columns: a:1 b:2!null c:3 254 │ ├── constraint: /2/3/4: [/1 - /1] 255 │ └── fd: ()-->(2) 256 └── filters 257 └── a:1 = z:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 258 259 # -------------------------------------------------- 260 # CommuteRightJoin 261 # -------------------------------------------------- 262 263 memo 264 SELECT * FROM abc RIGHT OUTER JOIN xyz ON a=z 265 ---- 266 memo (optimized, ~10KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 267 ├── G1: (left-join G2 G3 G4) (right-join G3 G2 G4) (lookup-join G2 G5 abc@ab,keyCols=[7],outCols=(1-3,5-7)) (merge-join G3 G2 G5 right-join,+1,+7) 268 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 269 │ ├── best: (left-join G2 G3 G4) 270 │ └── cost: 2270.05 271 ├── G2: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 272 │ ├── [ordering: +7] 273 │ │ ├── best: (sort G2) 274 │ │ └── cost: 1289.35 275 │ └── [] 276 │ ├── best: (scan xyz,cols=(5-7)) 277 │ └── cost: 1070.02 278 ├── G3: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 279 │ ├── [ordering: +1] 280 │ │ ├── best: (scan abc@ab,cols=(1-3)) 281 │ │ └── cost: 1070.02 282 │ └── [] 283 │ ├── best: (scan abc,cols=(1-3)) 284 │ └── cost: 1070.02 285 ├── G4: (filters G6) 286 ├── G5: (filters) 287 ├── G6: (eq G7 G8) 288 ├── G7: (variable a) 289 └── G8: (variable z) 290 291 opt 292 SELECT * FROM (SELECT * FROM abc WHERE b=1) RIGHT OUTER JOIN xyz ON a=z 293 ---- 294 left-join (hash) 295 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 296 ├── scan xyz 297 │ └── columns: x:5 y:6 z:7 298 ├── scan abc@bc 299 │ ├── columns: a:1 b:2!null c:3 300 │ ├── constraint: /2/3/4: [/1 - /1] 301 │ └── fd: ()-->(2) 302 └── filters 303 └── a:1 = z:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 304 305 # -------------------------------------------------- 306 # GenerateMergeJoins 307 # -------------------------------------------------- 308 309 opt 310 SELECT * FROM abc JOIN xyz ON a=x 311 ---- 312 inner-join (merge) 313 ├── columns: a:1!null b:2 c:3 x:5!null y:6 z:7 314 ├── left ordering: +1 315 ├── right ordering: +5 316 ├── fd: (1)==(5), (5)==(1) 317 ├── scan abc@ab 318 │ ├── columns: a:1 b:2 c:3 319 │ └── ordering: +1 320 ├── scan xyz@xy 321 │ ├── columns: x:5 y:6 z:7 322 │ └── ordering: +5 323 └── filters (true) 324 325 memo 326 SELECT * FROM abc JOIN xyz ON a=x 327 ---- 328 memo (optimized, ~12KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 329 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) (merge-join G2 G3 G5 inner-join,+1,+5) (lookup-join G2 G5 xyz@xy,keyCols=[1],outCols=(1-3,5-7)) (merge-join G3 G2 G5 inner-join,+5,+1) (lookup-join G3 G5 abc@ab,keyCols=[5],outCols=(1-3,5-7)) 330 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 331 │ ├── best: (merge-join G2="[ordering: +1]" G3="[ordering: +5]" G5 inner-join,+1,+5) 332 │ └── cost: 2258.06 333 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 334 │ ├── [ordering: +1] 335 │ │ ├── best: (scan abc@ab,cols=(1-3)) 336 │ │ └── cost: 1070.02 337 │ └── [] 338 │ ├── best: (scan abc,cols=(1-3)) 339 │ └── cost: 1070.02 340 ├── G3: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 341 │ ├── [ordering: +5] 342 │ │ ├── best: (scan xyz@xy,cols=(5-7)) 343 │ │ └── cost: 1070.02 344 │ └── [] 345 │ ├── best: (scan xyz,cols=(5-7)) 346 │ └── cost: 1070.02 347 ├── G4: (filters G6) 348 ├── G5: (filters) 349 ├── G6: (eq G7 G8) 350 ├── G7: (variable a) 351 └── G8: (variable x) 352 353 # Verify that we don't generate merge joins if there's a hint that says otherwise. 354 memo 355 SELECT * FROM abc INNER HASH JOIN xyz ON a=x 356 ---- 357 memo (optimized, ~9KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 358 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) 359 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 360 │ ├── best: (inner-join G2 G3 G4) 361 │ └── cost: 2268.06 362 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 363 │ └── [] 364 │ ├── best: (scan abc,cols=(1-3)) 365 │ └── cost: 1070.02 366 ├── G3: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 367 │ └── [] 368 │ ├── best: (scan xyz,cols=(5-7)) 369 │ └── cost: 1070.02 370 ├── G4: (filters G5) 371 ├── G5: (eq G6 G7) 372 ├── G6: (variable a) 373 └── G7: (variable x) 374 375 opt 376 SELECT * FROM abc JOIN xyz ON x=a 377 ---- 378 inner-join (merge) 379 ├── columns: a:1!null b:2 c:3 x:5!null y:6 z:7 380 ├── left ordering: +1 381 ├── right ordering: +5 382 ├── fd: (1)==(5), (5)==(1) 383 ├── scan abc@ab 384 │ ├── columns: a:1 b:2 c:3 385 │ └── ordering: +1 386 ├── scan xyz@xy 387 │ ├── columns: x:5 y:6 z:7 388 │ └── ordering: +5 389 └── filters (true) 390 391 opt 392 SELECT * FROM abc JOIN xyz ON a=x AND a=x AND x=a 393 ---- 394 inner-join (merge) 395 ├── columns: a:1!null b:2 c:3 x:5!null y:6 z:7 396 ├── left ordering: +1 397 ├── right ordering: +5 398 ├── fd: (1)==(5), (5)==(1) 399 ├── scan abc@ab 400 │ ├── columns: a:1 b:2 c:3 401 │ └── ordering: +1 402 ├── scan xyz@xy 403 │ ├── columns: x:5 y:6 z:7 404 │ └── ordering: +5 405 └── filters (true) 406 407 # Use constraints to force the choice of an index which doesn't help, and 408 # verify that we don't prefer a merge-join that has to sort both of its inputs. 409 opt 410 SELECT * FROM abc JOIN xyz ON a=x AND b=y WHERE b=1 AND y=1 411 ---- 412 inner-join (hash) 413 ├── columns: a:1!null b:2!null c:3 x:5!null y:6!null z:7 414 ├── fd: ()-->(2,6), (1)==(5), (5)==(1), (2)==(6), (6)==(2) 415 ├── scan abc@bc 416 │ ├── columns: a:1 b:2!null c:3 417 │ ├── constraint: /2/3/4: [/1 - /1] 418 │ └── fd: ()-->(2) 419 ├── scan xyz@yz 420 │ ├── columns: x:5 y:6!null z:7 421 │ ├── constraint: /6/7/8: [/1 - /1] 422 │ └── fd: ()-->(6) 423 └── filters 424 ├── a:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 425 └── b:2 = y:6 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 426 427 # Verify case where we generate multiple merge-joins. 428 memo 429 SELECT * FROM stu AS l JOIN stu AS r ON (l.s, l.t, l.u) = (r.s, r.t, r.u) 430 ---- 431 memo (optimized, ~11KB, required=[presentation: s:1,t:2,u:3,s:4,t:5,u:6]) 432 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) (merge-join G2 G3 G5 inner-join,+1,+2,+3,+4,+5,+6) (merge-join G2 G3 G5 inner-join,+3,+2,+1,+6,+5,+4) (lookup-join G2 G5 stu,keyCols=[1 2 3],outCols=(1-6)) (lookup-join G2 G5 stu@uts,keyCols=[3 2 1],outCols=(1-6)) (merge-join G3 G2 G5 inner-join,+4,+5,+6,+1,+2,+3) (merge-join G3 G2 G5 inner-join,+6,+5,+4,+3,+2,+1) (lookup-join G3 G5 stu,keyCols=[4 5 6],outCols=(1-6)) (lookup-join G3 G5 stu@uts,keyCols=[6 5 4],outCols=(1-6)) 433 │ └── [presentation: s:1,t:2,u:3,s:4,t:5,u:6] 434 │ ├── best: (merge-join G2="[ordering: +1,+2,+3]" G3="[ordering: +4,+5,+6]" G5 inner-join,+1,+2,+3,+4,+5,+6) 435 │ └── cost: 2140.06 436 ├── G2: (scan l) (scan l@uts) 437 │ ├── [ordering: +1,+2,+3] 438 │ │ ├── best: (scan l) 439 │ │ └── cost: 1060.02 440 │ ├── [ordering: +3,+2,+1] 441 │ │ ├── best: (scan l@uts) 442 │ │ └── cost: 1060.02 443 │ └── [] 444 │ ├── best: (scan l) 445 │ └── cost: 1060.02 446 ├── G3: (scan r) (scan r@uts) 447 │ ├── [ordering: +4,+5,+6] 448 │ │ ├── best: (scan r) 449 │ │ └── cost: 1060.02 450 │ ├── [ordering: +6,+5,+4] 451 │ │ ├── best: (scan r@uts) 452 │ │ └── cost: 1060.02 453 │ └── [] 454 │ ├── best: (scan r) 455 │ └── cost: 1060.02 456 ├── G4: (filters G6 G7 G8) 457 ├── G5: (filters) 458 ├── G6: (eq G9 G10) 459 ├── G7: (eq G11 G12) 460 ├── G8: (eq G13 G14) 461 ├── G9: (variable l.s) 462 ├── G10: (variable r.s) 463 ├── G11: (variable l.t) 464 ├── G12: (variable r.t) 465 ├── G13: (variable l.u) 466 └── G14: (variable r.u) 467 468 exploretrace rule=GenerateMergeJoins 469 SELECT * FROM stu AS l JOIN stu AS r ON (l.s, l.t, l.u) = (r.s, r.t, r.u) 470 ---- 471 ---- 472 ================================================================================ 473 GenerateMergeJoins 474 ================================================================================ 475 Source expression: 476 inner-join (hash) 477 ├── columns: s:1!null t:2!null u:3!null s:4!null t:5!null u:6!null 478 ├── key: (4-6) 479 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2), (3)==(6), (6)==(3) 480 ├── scan l 481 │ ├── columns: l.s:1!null l.t:2!null l.u:3!null 482 │ └── key: (1-3) 483 ├── scan r 484 │ ├── columns: r.s:4!null r.t:5!null r.u:6!null 485 │ └── key: (4-6) 486 └── filters 487 ├── l.s:1 = r.s:4 [outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 488 ├── l.t:2 = r.t:5 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ]), fd=(2)==(5), (5)==(2)] 489 └── l.u:3 = r.u:6 [outer=(3,6), constraints=(/3: (/NULL - ]; /6: (/NULL - ]), fd=(3)==(6), (6)==(3)] 490 491 New expression 1 of 2: 492 inner-join (merge) 493 ├── columns: s:1!null t:2!null u:3!null s:4!null t:5!null u:6!null 494 ├── left ordering: +1,+2,+3 495 ├── right ordering: +4,+5,+6 496 ├── key: (4-6) 497 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2), (3)==(6), (6)==(3) 498 ├── scan l 499 │ ├── columns: l.s:1!null l.t:2!null l.u:3!null 500 │ ├── key: (1-3) 501 │ └── ordering: +1,+2,+3 502 ├── scan r 503 │ ├── columns: r.s:4!null r.t:5!null r.u:6!null 504 │ ├── key: (4-6) 505 │ └── ordering: +4,+5,+6 506 └── filters (true) 507 508 New expression 2 of 2: 509 inner-join (merge) 510 ├── columns: s:1!null t:2!null u:3!null s:4!null t:5!null u:6!null 511 ├── left ordering: +3,+2,+1 512 ├── right ordering: +6,+5,+4 513 ├── key: (4-6) 514 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2), (3)==(6), (6)==(3) 515 ├── scan l@uts 516 │ ├── columns: l.s:1!null l.t:2!null l.u:3!null 517 │ ├── key: (1-3) 518 │ └── ordering: +3,+2,+1 519 ├── scan r@uts 520 │ ├── columns: r.s:4!null r.t:5!null r.u:6!null 521 │ ├── key: (4-6) 522 │ └── ordering: +6,+5,+4 523 └── filters (true) 524 525 ================================================================================ 526 GenerateMergeJoins 527 ================================================================================ 528 Source expression: 529 inner-join (hash) 530 ├── columns: s:1!null t:2!null u:3!null s:4!null t:5!null u:6!null 531 ├── key: (4-6) 532 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2), (3)==(6), (6)==(3) 533 ├── scan r 534 │ ├── columns: r.s:4!null r.t:5!null r.u:6!null 535 │ └── key: (4-6) 536 ├── scan l 537 │ ├── columns: l.s:1!null l.t:2!null l.u:3!null 538 │ └── key: (1-3) 539 └── filters 540 ├── l.s:1 = r.s:4 [outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 541 ├── l.t:2 = r.t:5 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ]), fd=(2)==(5), (5)==(2)] 542 └── l.u:3 = r.u:6 [outer=(3,6), constraints=(/3: (/NULL - ]; /6: (/NULL - ]), fd=(3)==(6), (6)==(3)] 543 544 New expression 1 of 2: 545 inner-join (merge) 546 ├── columns: s:1!null t:2!null u:3!null s:4!null t:5!null u:6!null 547 ├── left ordering: +4,+5,+6 548 ├── right ordering: +1,+2,+3 549 ├── key: (4-6) 550 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2), (3)==(6), (6)==(3) 551 ├── scan r 552 │ ├── columns: r.s:4!null r.t:5!null r.u:6!null 553 │ ├── key: (4-6) 554 │ └── ordering: +4,+5,+6 555 ├── scan l 556 │ ├── columns: l.s:1!null l.t:2!null l.u:3!null 557 │ ├── key: (1-3) 558 │ └── ordering: +1,+2,+3 559 └── filters (true) 560 561 New expression 2 of 2: 562 inner-join (merge) 563 ├── columns: s:1!null t:2!null u:3!null s:4!null t:5!null u:6!null 564 ├── left ordering: +6,+5,+4 565 ├── right ordering: +3,+2,+1 566 ├── key: (4-6) 567 ├── fd: (1)==(4), (4)==(1), (2)==(5), (5)==(2), (3)==(6), (6)==(3) 568 ├── scan r@uts 569 │ ├── columns: r.s:4!null r.t:5!null r.u:6!null 570 │ ├── key: (4-6) 571 │ └── ordering: +6,+5,+4 572 ├── scan l@uts 573 │ ├── columns: l.s:1!null l.t:2!null l.u:3!null 574 │ ├── key: (1-3) 575 │ └── ordering: +3,+2,+1 576 └── filters (true) 577 ---- 578 ---- 579 580 # Add statistics to make table stu large (so that sorting abc is relatively cheap). 581 exec-ddl 582 ALTER TABLE stu INJECT STATISTICS '[ 583 { 584 "columns": ["s"], 585 "created_at": "2018-05-01 1:00:00.00000+00:00", 586 "row_count": 1000000, 587 "distinct_count": 1000000 588 } 589 ]' 590 ---- 591 592 # The ordering is coming from the left side. 593 opt 594 SELECT * FROM stu LEFT OUTER JOIN abc ON (c,b,a) = (s,t,u) 595 ---- 596 left-join (merge) 597 ├── columns: s:1!null t:2!null u:3!null a:4 b:5 c:6 598 ├── left ordering: +3,+2,+1 599 ├── right ordering: +4,+5,+6 600 ├── scan stu@uts 601 │ ├── columns: s:1!null t:2!null u:3!null 602 │ ├── key: (1-3) 603 │ └── ordering: +3,+2,+1 604 ├── sort (segmented) 605 │ ├── columns: a:4 b:5 c:6 606 │ ├── ordering: +4,+5,+6 607 │ └── scan abc@ab 608 │ ├── columns: a:4 b:5 c:6 609 │ └── ordering: +4,+5 610 └── filters (true) 611 612 # The ordering is coming from the right side. 613 opt 614 SELECT * FROM abc RIGHT OUTER JOIN stu ON (c,b,a) = (s,t,u) 615 ---- 616 left-join (merge) 617 ├── columns: a:1 b:2 c:3 s:5!null t:6!null u:7!null 618 ├── left ordering: +7,+6,+5 619 ├── right ordering: +1,+2,+3 620 ├── scan stu@uts 621 │ ├── columns: s:5!null t:6!null u:7!null 622 │ ├── key: (5-7) 623 │ └── ordering: +7,+6,+5 624 ├── sort (segmented) 625 │ ├── columns: a:1 b:2 c:3 626 │ ├── ordering: +1,+2,+3 627 │ └── scan abc@ab 628 │ ├── columns: a:1 b:2 c:3 629 │ └── ordering: +1,+2 630 └── filters (true) 631 632 # In these cases, we shouldn't pick up equivalencies. 633 memo 634 SELECT * FROM abc JOIN xyz ON a=b 635 ---- 636 memo (optimized, ~13KB, required=[presentation: a:1,b:2,c:3,x:5,y:6,z:7]) 637 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) 638 │ └── [presentation: a:1,b:2,c:3,x:5,y:6,z:7] 639 │ ├── best: (inner-join G3 G2 G4) 640 │ └── cost: 2249.93 641 ├── G2: (select G5 G6) (select G7 G6) (select G8 G6) 642 │ └── [] 643 │ ├── best: (select G7 G6) 644 │ └── cost: 1069.22 645 ├── G3: (scan xyz,cols=(5-7)) (scan xyz@xy,cols=(5-7)) (scan xyz@yz,cols=(5-7)) 646 │ └── [] 647 │ ├── best: (scan xyz,cols=(5-7)) 648 │ └── cost: 1070.02 649 ├── G4: (filters) 650 ├── G5: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 651 │ └── [] 652 │ ├── best: (scan abc,cols=(1-3)) 653 │ └── cost: 1070.02 654 ├── G6: (filters G9) 655 ├── G7: (scan abc@ab,cols=(1-3),constrained) 656 │ └── [] 657 │ ├── best: (scan abc@ab,cols=(1-3),constrained) 658 │ └── cost: 1059.31 659 ├── G8: (scan abc@bc,cols=(1-3),constrained) 660 │ └── [] 661 │ ├── best: (scan abc@bc,cols=(1-3),constrained) 662 │ └── cost: 1059.31 663 ├── G9: (eq G10 G11) 664 ├── G10: (variable a) 665 └── G11: (variable b) 666 667 exec-ddl 668 CREATE TABLE kfloat (k FLOAT PRIMARY KEY) 669 ---- 670 671 memo 672 SELECT * FROM abc JOIN kfloat ON a=k 673 ---- 674 memo (optimized, ~8KB, required=[presentation: a:1,b:2,c:3,k:5]) 675 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) 676 │ └── [presentation: a:1,b:2,c:3,k:5] 677 │ ├── best: (inner-join G2 G3 G4) 678 │ └── cost: 12120.06 679 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 680 │ └── [] 681 │ ├── best: (scan abc,cols=(1-3)) 682 │ └── cost: 1070.02 683 ├── G3: (scan kfloat) 684 │ └── [] 685 │ ├── best: (scan kfloat) 686 │ └── cost: 1020.02 687 ├── G4: (filters G5) 688 ├── G5: (eq G6 G7) 689 ├── G6: (variable a) 690 └── G7: (variable k) 691 692 # We should only pick up one equivalency. 693 opt 694 SELECT * FROM abc JOIN xyz ON a=x AND a=y 695 ---- 696 inner-join (lookup abc@ab) 697 ├── columns: a:1!null b:2 c:3 x:5!null y:6!null z:7 698 ├── key columns: [5] = [1] 699 ├── fd: (5)==(1,6), (6)==(1,5), (1)==(5,6) 700 ├── select 701 │ ├── columns: x:5!null y:6!null z:7 702 │ ├── fd: (5)==(6), (6)==(5) 703 │ ├── scan xyz@xy 704 │ │ ├── columns: x:5!null y:6 z:7 705 │ │ └── constraint: /5/6/8: (/NULL - ] 706 │ └── filters 707 │ └── x:5 = y:6 [outer=(5,6), constraints=(/5: (/NULL - ]; /6: (/NULL - ]), fd=(5)==(6), (6)==(5)] 708 └── filters (true) 709 710 # Verify multiple merge-joins can be chained. 711 opt 712 SELECT * FROM abc JOIN xyz ON a=x AND b=y RIGHT OUTER JOIN stu ON a=s 713 ---- 714 left-join (merge) 715 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 s:9!null t:10!null u:11!null 716 ├── left ordering: +9 717 ├── right ordering: +1 718 ├── fd: (1)==(5), (5)==(1), (2)==(6), (6)==(2) 719 ├── scan stu 720 │ ├── columns: s:9!null t:10!null u:11!null 721 │ ├── key: (9-11) 722 │ └── ordering: +9 723 ├── inner-join (merge) 724 │ ├── columns: a:1!null b:2!null c:3 x:5!null y:6!null z:7 725 │ ├── left ordering: +1,+2 726 │ ├── right ordering: +5,+6 727 │ ├── fd: (1)==(5), (5)==(1), (2)==(6), (6)==(2) 728 │ ├── ordering: +(1|5) [actual: +1] 729 │ ├── scan abc@ab 730 │ │ ├── columns: a:1 b:2 c:3 731 │ │ └── ordering: +1,+2 732 │ ├── scan xyz@xy 733 │ │ ├── columns: x:5 y:6 z:7 734 │ │ └── ordering: +5,+6 735 │ └── filters (true) 736 └── filters (true) 737 738 opt 739 SELECT * FROM abc JOIN xyz ON a=x AND b=y RIGHT OUTER JOIN stu ON a=u AND y=t 740 ---- 741 left-join (merge) 742 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 s:9!null t:10!null u:11!null 743 ├── left ordering: +11,+10 744 ├── right ordering: +1,+6 745 ├── fd: (1)==(5), (5)==(1), (2)==(6), (6)==(2) 746 ├── scan stu@uts 747 │ ├── columns: s:9!null t:10!null u:11!null 748 │ ├── key: (9-11) 749 │ └── ordering: +11,+10 750 ├── inner-join (merge) 751 │ ├── columns: a:1!null b:2!null c:3 x:5!null y:6!null z:7 752 │ ├── left ordering: +1,+2 753 │ ├── right ordering: +5,+6 754 │ ├── fd: (1)==(5), (5)==(1), (2)==(6), (6)==(2) 755 │ ├── ordering: +(1|5),+(2|6) [actual: +1,+2] 756 │ ├── scan abc@ab 757 │ │ ├── columns: a:1 b:2 c:3 758 │ │ └── ordering: +1,+2 759 │ ├── scan xyz@xy 760 │ │ ├── columns: x:5 y:6 z:7 761 │ │ └── ordering: +5,+6 762 │ └── filters (true) 763 └── filters (true) 764 765 # -------------------------------------------------- 766 # GenerateLookupJoins 767 # -------------------------------------------------- 768 769 exec-ddl 770 CREATE TABLE abcd (a INT, b INT, c INT, INDEX (a,b)) 771 ---- 772 773 exec-ddl 774 CREATE TABLE abcde (a INT, b INT, c INT, d INT, e INT, INDEX (a,b,c)) 775 ---- 776 777 exec-ddl 778 CREATE TABLE small (m INT, n INT) 779 ---- 780 781 exec-ddl 782 ALTER TABLE small INJECT STATISTICS '[ 783 { 784 "columns": ["m"], 785 "created_at": "2018-01-01 1:00:00.00000+00:00", 786 "row_count": 10, 787 "distinct_count": 10 788 } 789 ]' 790 ---- 791 792 # Covering case. 793 opt 794 SELECT a,b,n,m FROM small JOIN abcd ON a=m 795 ---- 796 inner-join (lookup abcd@secondary) 797 ├── columns: a:4!null b:5 n:2 m:1!null 798 ├── key columns: [1] = [4] 799 ├── fd: (1)==(4), (4)==(1) 800 ├── scan small 801 │ └── columns: m:1 n:2 802 └── filters (true) 803 804 # Covering case, left-join. 805 opt 806 SELECT a,b,n,m FROM small LEFT JOIN abcd ON a=m 807 ---- 808 left-join (lookup abcd@secondary) 809 ├── columns: a:4 b:5 n:2 m:1 810 ├── key columns: [1] = [4] 811 ├── scan small 812 │ └── columns: m:1 n:2 813 └── filters (true) 814 815 # Non-covering case. 816 opt 817 SELECT * FROM small JOIN abcd ON a=m 818 ---- 819 inner-join (lookup abcd) 820 ├── columns: m:1!null n:2 a:4!null b:5 c:6 821 ├── key columns: [7] = [7] 822 ├── lookup columns are key 823 ├── fd: (1)==(4), (4)==(1) 824 ├── inner-join (lookup abcd@secondary) 825 │ ├── columns: m:1!null n:2 a:4!null b:5 abcd.rowid:7!null 826 │ ├── key columns: [1] = [4] 827 │ ├── fd: (7)-->(4,5), (1)==(4), (4)==(1) 828 │ ├── scan small 829 │ │ └── columns: m:1 n:2 830 │ └── filters (true) 831 └── filters (true) 832 833 # Non-covering case, left join. 834 opt 835 SELECT * FROM small LEFT JOIN abcd ON a=m 836 ---- 837 left-join (lookup abcd) 838 ├── columns: m:1 n:2 a:4 b:5 c:6 839 ├── key columns: [7] = [7] 840 ├── lookup columns are key 841 ├── left-join (lookup abcd@secondary) 842 │ ├── columns: m:1 n:2 a:4 b:5 abcd.rowid:7 843 │ ├── key columns: [1] = [4] 844 │ ├── fd: (7)-->(4,5) 845 │ ├── scan small 846 │ │ └── columns: m:1 n:2 847 │ └── filters (true) 848 └── filters (true) 849 850 # Non-covering case, extra filter bound by index. 851 opt 852 SELECT * FROM small JOIN abcd ON a=m AND b>n 853 ---- 854 inner-join (lookup abcd) 855 ├── columns: m:1!null n:2!null a:4!null b:5!null c:6 856 ├── key columns: [7] = [7] 857 ├── lookup columns are key 858 ├── fd: (1)==(4), (4)==(1) 859 ├── inner-join (lookup abcd@secondary) 860 │ ├── columns: m:1!null n:2!null a:4!null b:5!null abcd.rowid:7!null 861 │ ├── key columns: [1] = [4] 862 │ ├── fd: (7)-->(4,5), (1)==(4), (4)==(1) 863 │ ├── scan small 864 │ │ └── columns: m:1 n:2 865 │ └── filters 866 │ └── b:5 > n:2 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ])] 867 └── filters (true) 868 869 # Non-covering case, extra filter bound by index, left join. 870 opt 871 SELECT * FROM small LEFT JOIN abcd ON a=m AND b>n 872 ---- 873 left-join (lookup abcd) 874 ├── columns: m:1 n:2 a:4 b:5 c:6 875 ├── key columns: [7] = [7] 876 ├── lookup columns are key 877 ├── left-join (lookup abcd@secondary) 878 │ ├── columns: m:1 n:2 a:4 b:5 abcd.rowid:7 879 │ ├── key columns: [1] = [4] 880 │ ├── fd: (7)-->(4,5) 881 │ ├── scan small 882 │ │ └── columns: m:1 n:2 883 │ └── filters 884 │ └── b:5 > n:2 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ])] 885 └── filters (true) 886 887 # Non-covering case, extra filter not bound by index. 888 opt 889 SELECT * FROM small JOIN abcd ON a=m AND c>n 890 ---- 891 inner-join (lookup abcd) 892 ├── columns: m:1!null n:2!null a:4!null b:5 c:6!null 893 ├── key columns: [7] = [7] 894 ├── lookup columns are key 895 ├── fd: (1)==(4), (4)==(1) 896 ├── inner-join (lookup abcd@secondary) 897 │ ├── columns: m:1!null n:2 a:4!null b:5 abcd.rowid:7!null 898 │ ├── key columns: [1] = [4] 899 │ ├── fd: (7)-->(4,5), (1)==(4), (4)==(1) 900 │ ├── scan small 901 │ │ └── columns: m:1 n:2 902 │ └── filters (true) 903 └── filters 904 └── c:6 > n:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ])] 905 906 # Non-covering case, extra filter not bound by index, left join. 907 # In this case, we can't yet convert to a lookup join (see 908 # the GenerateLookupJoins custom func). 909 opt 910 SELECT * FROM small LEFT JOIN abcd ON a=m AND c>n 911 ---- 912 right-join (hash) 913 ├── columns: m:1 n:2 a:4 b:5 c:6 914 ├── scan abcd 915 │ └── columns: a:4 b:5 c:6 916 ├── scan small 917 │ └── columns: m:1 n:2 918 └── filters 919 ├── a:4 = m:1 [outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 920 └── c:6 > n:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ])] 921 922 923 # Verify rule application when we can do a lookup join on both sides. 924 exploretrace rule=GenerateLookupJoins 925 SELECT * FROM abc JOIN xyz ON a=x AND a=y 926 ---- 927 ---- 928 ================================================================================ 929 GenerateLookupJoins 930 ================================================================================ 931 Source expression: 932 inner-join (hash) 933 ├── columns: a:1!null b:2 c:3 x:5!null y:6!null z:7 934 ├── fd: (5)==(1,6), (6)==(1,5), (1)==(5,6) 935 ├── select 936 │ ├── columns: x:5!null y:6!null z:7 937 │ ├── fd: (5)==(6), (6)==(5) 938 │ ├── scan xyz@xy 939 │ │ ├── columns: x:5!null y:6 z:7 940 │ │ └── constraint: /5/6/8: (/NULL - ] 941 │ └── filters 942 │ └── x:5 = y:6 [outer=(5,6), constraints=(/5: (/NULL - ]; /6: (/NULL - ]), fd=(5)==(6), (6)==(5)] 943 ├── scan abc 944 │ └── columns: a:1 b:2 c:3 945 └── filters 946 └── a:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 947 948 New expression 1 of 1: 949 inner-join (lookup abc@ab) 950 ├── columns: a:1!null b:2 c:3 x:5!null y:6!null z:7 951 ├── key columns: [5] = [1] 952 ├── fd: (5)==(1,6), (6)==(1,5), (1)==(5,6) 953 ├── select 954 │ ├── columns: x:5!null y:6!null z:7 955 │ ├── fd: (5)==(6), (6)==(5) 956 │ ├── scan xyz@xy 957 │ │ ├── columns: x:5!null y:6 z:7 958 │ │ └── constraint: /5/6/8: (/NULL - ] 959 │ └── filters 960 │ └── x:5 = y:6 [outer=(5,6), constraints=(/5: (/NULL - ]; /6: (/NULL - ]), fd=(5)==(6), (6)==(5)] 961 └── filters (true) 962 ---- 963 ---- 964 965 # Verify rule application when we can do a lookup join on the left side. 966 exploretrace rule=GenerateLookupJoins 967 SELECT * FROM abc JOIN xyz ON a=z 968 ---- 969 ---- 970 ================================================================================ 971 GenerateLookupJoins 972 ================================================================================ 973 Source expression: 974 inner-join (hash) 975 ├── columns: a:1!null b:2 c:3 x:5 y:6 z:7!null 976 ├── fd: (1)==(7), (7)==(1) 977 ├── scan abc 978 │ └── columns: a:1 b:2 c:3 979 ├── scan xyz 980 │ └── columns: x:5 y:6 z:7 981 └── filters 982 └── a:1 = z:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 983 984 No new expressions. 985 986 ================================================================================ 987 GenerateLookupJoins 988 ================================================================================ 989 Source expression: 990 inner-join (hash) 991 ├── columns: a:1!null b:2 c:3 x:5 y:6 z:7!null 992 ├── fd: (1)==(7), (7)==(1) 993 ├── scan xyz 994 │ └── columns: x:5 y:6 z:7 995 ├── scan abc 996 │ └── columns: a:1 b:2 c:3 997 └── filters 998 └── a:1 = z:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 999 1000 New expression 1 of 1: 1001 inner-join (lookup abc@ab) 1002 ├── columns: a:1!null b:2 c:3 x:5 y:6 z:7!null 1003 ├── key columns: [7] = [1] 1004 ├── fd: (1)==(7), (7)==(1) 1005 ├── scan xyz 1006 │ └── columns: x:5 y:6 z:7 1007 └── filters (true) 1008 ---- 1009 ---- 1010 1011 exploretrace rule=GenerateLookupJoins 1012 SELECT * FROM abc RIGHT JOIN xyz ON a=z 1013 ---- 1014 ---- 1015 ================================================================================ 1016 GenerateLookupJoins 1017 ================================================================================ 1018 Source expression: 1019 left-join (hash) 1020 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 1021 ├── scan xyz 1022 │ └── columns: x:5 y:6 z:7 1023 ├── scan abc 1024 │ └── columns: a:1 b:2 c:3 1025 └── filters 1026 └── a:1 = z:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 1027 1028 New expression 1 of 1: 1029 left-join (lookup abc@ab) 1030 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 1031 ├── key columns: [7] = [1] 1032 ├── scan xyz 1033 │ └── columns: x:5 y:6 z:7 1034 └── filters (true) 1035 ---- 1036 ---- 1037 1038 # Verify rule application when we can do a lookup join on the right side. 1039 exploretrace rule=GenerateLookupJoins 1040 SELECT * FROM abc JOIN xyz ON c=x 1041 ---- 1042 ---- 1043 ================================================================================ 1044 GenerateLookupJoins 1045 ================================================================================ 1046 Source expression: 1047 inner-join (hash) 1048 ├── columns: a:1 b:2 c:3!null x:5!null y:6 z:7 1049 ├── fd: (3)==(5), (5)==(3) 1050 ├── scan abc 1051 │ └── columns: a:1 b:2 c:3 1052 ├── scan xyz 1053 │ └── columns: x:5 y:6 z:7 1054 └── filters 1055 └── c:3 = x:5 [outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1056 1057 New expression 1 of 1: 1058 inner-join (lookup xyz@xy) 1059 ├── columns: a:1 b:2 c:3!null x:5!null y:6 z:7 1060 ├── key columns: [3] = [5] 1061 ├── fd: (3)==(5), (5)==(3) 1062 ├── scan abc 1063 │ └── columns: a:1 b:2 c:3 1064 └── filters (true) 1065 1066 ================================================================================ 1067 GenerateLookupJoins 1068 ================================================================================ 1069 Source expression: 1070 inner-join (hash) 1071 ├── columns: a:1 b:2 c:3!null x:5!null y:6 z:7 1072 ├── fd: (3)==(5), (5)==(3) 1073 ├── scan xyz 1074 │ └── columns: x:5 y:6 z:7 1075 ├── scan abc 1076 │ └── columns: a:1 b:2 c:3 1077 └── filters 1078 └── c:3 = x:5 [outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1079 1080 No new expressions. 1081 ---- 1082 ---- 1083 1084 exploretrace rule=GenerateLookupJoins 1085 SELECT * FROM abc LEFT JOIN xyz ON c=x 1086 ---- 1087 ---- 1088 ================================================================================ 1089 GenerateLookupJoins 1090 ================================================================================ 1091 Source expression: 1092 left-join (hash) 1093 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 1094 ├── scan abc 1095 │ └── columns: a:1 b:2 c:3 1096 ├── scan xyz 1097 │ └── columns: x:5 y:6 z:7 1098 └── filters 1099 └── c:3 = x:5 [outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1100 1101 New expression 1 of 1: 1102 left-join (lookup xyz@xy) 1103 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 1104 ├── key columns: [3] = [5] 1105 ├── scan abc 1106 │ └── columns: a:1 b:2 c:3 1107 └── filters (true) 1108 ---- 1109 ---- 1110 1111 # Verify we don't generate a lookup join. 1112 exploretrace rule=GenerateLookupJoins 1113 SELECT * FROM abc RIGHT JOIN xyz ON c=x 1114 ---- 1115 ---- 1116 ================================================================================ 1117 GenerateLookupJoins 1118 ================================================================================ 1119 Source expression: 1120 left-join (hash) 1121 ├── columns: a:1 b:2 c:3 x:5 y:6 z:7 1122 ├── scan xyz 1123 │ └── columns: x:5 y:6 z:7 1124 ├── scan abc 1125 │ └── columns: a:1 b:2 c:3 1126 └── filters 1127 └── c:3 = x:5 [outer=(3,5), constraints=(/3: (/NULL - ]; /5: (/NULL - ]), fd=(3)==(5), (5)==(3)] 1128 1129 No new expressions. 1130 ---- 1131 ---- 1132 1133 # Verify we don't generate lookup joins if there is a hint that says otherwise. 1134 memo 1135 SELECT a,b,n,m FROM small INNER HASH JOIN abcd ON a=m 1136 ---- 1137 memo (optimized, ~8KB, required=[presentation: a:4,b:5,n:2,m:1]) 1138 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) 1139 │ └── [presentation: a:4,b:5,n:2,m:1] 1140 │ ├── best: (inner-join G2 G3 G4) 1141 │ └── cost: 1079.16 1142 ├── G2: (scan small,cols=(1,2)) 1143 │ └── [] 1144 │ ├── best: (scan small,cols=(1,2)) 1145 │ └── cost: 10.52 1146 ├── G3: (scan abcd,cols=(4,5)) (scan abcd@secondary,cols=(4,5)) 1147 │ └── [] 1148 │ ├── best: (scan abcd@secondary,cols=(4,5)) 1149 │ └── cost: 1050.02 1150 ├── G4: (filters G5) 1151 ├── G5: (eq G6 G7) 1152 ├── G6: (variable a) 1153 └── G7: (variable m) 1154 1155 # -------------------------------------------------- 1156 # GenerateLookupJoinsWithFilter 1157 # -------------------------------------------------- 1158 # 1159 # The rule and cases are similar to GenerateLookupJoins, except that we have a 1160 # filter that was pushed down to the lookup side (which needs to be pulled back 1161 # into the ON condition). 1162 1163 # Covering case. 1164 opt 1165 SELECT a,b,n,m FROM small JOIN abcd ON a=m AND b>1 1166 ---- 1167 inner-join (lookup abcd@secondary) 1168 ├── columns: a:4!null b:5!null n:2 m:1!null 1169 ├── key columns: [1] = [4] 1170 ├── fd: (1)==(4), (4)==(1) 1171 ├── scan small 1172 │ └── columns: m:1 n:2 1173 └── filters 1174 └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1175 1176 # Covering case, left-join. 1177 opt 1178 SELECT a,b,n,m FROM small LEFT JOIN abcd ON a=m AND b>1 1179 ---- 1180 left-join (lookup abcd@secondary) 1181 ├── columns: a:4 b:5 n:2 m:1 1182 ├── key columns: [1] = [4] 1183 ├── scan small 1184 │ └── columns: m:1 n:2 1185 └── filters 1186 └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1187 1188 # Non-covering case. 1189 opt 1190 SELECT * FROM small JOIN abcd ON a=m AND b>1 1191 ---- 1192 inner-join (lookup abcd) 1193 ├── columns: m:1!null n:2 a:4!null b:5!null c:6 1194 ├── key columns: [7] = [7] 1195 ├── lookup columns are key 1196 ├── fd: (1)==(4), (4)==(1) 1197 ├── inner-join (lookup abcd@secondary) 1198 │ ├── columns: m:1!null n:2 a:4!null b:5!null abcd.rowid:7!null 1199 │ ├── key columns: [1] = [4] 1200 │ ├── fd: (7)-->(4,5), (1)==(4), (4)==(1) 1201 │ ├── scan small 1202 │ │ └── columns: m:1 n:2 1203 │ └── filters 1204 │ └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1205 └── filters (true) 1206 1207 # Non-covering case, left join. 1208 opt 1209 SELECT * FROM small LEFT JOIN abcd ON a=m AND b>1 1210 ---- 1211 left-join (lookup abcd) 1212 ├── columns: m:1 n:2 a:4 b:5 c:6 1213 ├── key columns: [7] = [7] 1214 ├── lookup columns are key 1215 ├── left-join (lookup abcd@secondary) 1216 │ ├── columns: m:1 n:2 a:4 b:5 abcd.rowid:7 1217 │ ├── key columns: [1] = [4] 1218 │ ├── fd: (7)-->(4,5) 1219 │ ├── scan small 1220 │ │ └── columns: m:1 n:2 1221 │ └── filters 1222 │ └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1223 └── filters (true) 1224 1225 # Non-covering case, extra filter bound by index. 1226 opt 1227 SELECT * FROM small JOIN abcd ON a=m AND b>n AND b>1 1228 ---- 1229 inner-join (lookup abcd) 1230 ├── columns: m:1!null n:2!null a:4!null b:5!null c:6 1231 ├── key columns: [7] = [7] 1232 ├── lookup columns are key 1233 ├── fd: (1)==(4), (4)==(1) 1234 ├── inner-join (lookup abcd@secondary) 1235 │ ├── columns: m:1!null n:2!null a:4!null b:5!null abcd.rowid:7!null 1236 │ ├── key columns: [1] = [4] 1237 │ ├── fd: (7)-->(4,5), (1)==(4), (4)==(1) 1238 │ ├── scan small 1239 │ │ └── columns: m:1 n:2 1240 │ └── filters 1241 │ ├── b:5 > n:2 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ])] 1242 │ └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1243 └── filters (true) 1244 1245 # Non-covering case, extra filter bound by index, left join. 1246 opt 1247 SELECT * FROM small LEFT JOIN abcd ON a=m AND b>n AND b>1 1248 ---- 1249 left-join (lookup abcd) 1250 ├── columns: m:1 n:2 a:4 b:5 c:6 1251 ├── key columns: [7] = [7] 1252 ├── lookup columns are key 1253 ├── left-join (lookup abcd@secondary) 1254 │ ├── columns: m:1 n:2 a:4 b:5 abcd.rowid:7 1255 │ ├── key columns: [1] = [4] 1256 │ ├── fd: (7)-->(4,5) 1257 │ ├── scan small 1258 │ │ └── columns: m:1 n:2 1259 │ └── filters 1260 │ ├── b:5 > n:2 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ])] 1261 │ └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1262 └── filters (true) 1263 1264 # Non-covering case, extra filter not bound by index. 1265 opt 1266 SELECT * FROM small JOIN abcd ON a=m AND c>n AND b>1 1267 ---- 1268 inner-join (lookup abcd) 1269 ├── columns: m:1!null n:2!null a:4!null b:5!null c:6!null 1270 ├── key columns: [7] = [7] 1271 ├── lookup columns are key 1272 ├── fd: (1)==(4), (4)==(1) 1273 ├── inner-join (lookup abcd@secondary) 1274 │ ├── columns: m:1!null n:2 a:4!null b:5!null abcd.rowid:7!null 1275 │ ├── key columns: [1] = [4] 1276 │ ├── fd: (7)-->(4,5), (1)==(4), (4)==(1) 1277 │ ├── scan small 1278 │ │ └── columns: m:1 n:2 1279 │ └── filters 1280 │ └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1281 └── filters 1282 └── c:6 > n:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ])] 1283 1284 # Non-covering case, extra filter not bound by index, left join. 1285 # In this case, we can't yet convert to a lookup join (see 1286 # the GenerateLookupJoins custom func). 1287 opt 1288 SELECT * FROM small LEFT JOIN abcd ON a=m AND c>n AND b>1 1289 ---- 1290 right-join (hash) 1291 ├── columns: m:1 n:2 a:4 b:5 c:6 1292 ├── select 1293 │ ├── columns: a:4 b:5!null c:6 1294 │ ├── scan abcd 1295 │ │ └── columns: a:4 b:5 c:6 1296 │ └── filters 1297 │ └── b:5 > 1 [outer=(5), constraints=(/5: [/2 - ]; tight)] 1298 ├── scan small 1299 │ └── columns: m:1 n:2 1300 └── filters 1301 ├── a:4 = m:1 [outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 1302 └── c:6 > n:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ])] 1303 1304 # Constant columns are projected and used by lookup joiner. 1305 opt 1306 SELECT * FROM small INNER JOIN abcde ON a=m AND b=10 1307 ---- 1308 inner-join (lookup abcde) 1309 ├── columns: m:1!null n:2 a:4!null b:5!null c:6 d:7 e:8 1310 ├── key columns: [9] = [9] 1311 ├── lookup columns are key 1312 ├── fd: ()-->(5), (1)==(4), (4)==(1) 1313 ├── inner-join (lookup abcde@secondary) 1314 │ ├── columns: m:1!null n:2 a:4!null b:5!null c:6 abcde.rowid:9!null 1315 │ ├── key columns: [1 10] = [4 5] 1316 │ ├── fd: ()-->(5), (9)-->(4,6), (1)==(4), (4)==(1) 1317 │ ├── project 1318 │ │ ├── columns: "project_const_col_@5":10!null m:1 n:2 1319 │ │ ├── fd: ()-->(10) 1320 │ │ ├── scan small 1321 │ │ │ └── columns: m:1 n:2 1322 │ │ └── projections 1323 │ │ └── 10 [as="project_const_col_@5":10] 1324 │ └── filters (true) 1325 └── filters (true) 1326 1327 # Constant columns not projected if not prefix of an index. 1328 opt 1329 SELECT * FROM small INNER JOIN abcde ON a=m AND c=10 1330 ---- 1331 inner-join (lookup abcde) 1332 ├── columns: m:1!null n:2 a:4!null b:5 c:6!null d:7 e:8 1333 ├── key columns: [9] = [9] 1334 ├── lookup columns are key 1335 ├── fd: ()-->(6), (1)==(4), (4)==(1) 1336 ├── inner-join (lookup abcde@secondary) 1337 │ ├── columns: m:1!null n:2 a:4!null b:5 c:6!null abcde.rowid:9!null 1338 │ ├── key columns: [1] = [4] 1339 │ ├── fd: ()-->(6), (9)-->(4,5), (1)==(4), (4)==(1) 1340 │ ├── scan small 1341 │ │ └── columns: m:1 n:2 1342 │ └── filters 1343 │ └── c:6 = 10 [outer=(6), constraints=(/6: [/10 - /10]; tight), fd=()-->(6)] 1344 └── filters (true) 1345 1346 # Multiple constant columns projected and used by lookup joiner. 1347 opt 1348 SELECT * FROM small INNER JOIN abcde ON a=m AND b=10 AND c=10 1349 ---- 1350 inner-join (lookup abcde) 1351 ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null d:7 e:8 1352 ├── key columns: [9] = [9] 1353 ├── lookup columns are key 1354 ├── fd: ()-->(5,6), (1)==(4), (4)==(1) 1355 ├── inner-join (lookup abcde@secondary) 1356 │ ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null abcde.rowid:9!null 1357 │ ├── key columns: [1 10 11] = [4 5 6] 1358 │ ├── fd: ()-->(5,6), (9)-->(4), (1)==(4), (4)==(1) 1359 │ ├── project 1360 │ │ ├── columns: "project_const_col_@5":10!null "project_const_col_@6":11!null m:1 n:2 1361 │ │ ├── fd: ()-->(10,11) 1362 │ │ ├── scan small 1363 │ │ │ └── columns: m:1 n:2 1364 │ │ └── projections 1365 │ │ ├── 10 [as="project_const_col_@5":10] 1366 │ │ └── 10 [as="project_const_col_@6":11] 1367 │ └── filters (true) 1368 └── filters (true) 1369 1370 # Filters are reduced properly as constant filters are extracted. 1371 opt 1372 SELECT * FROM small INNER JOIN abcde ON a=m AND b=10 AND c=10 AND d=10 1373 ---- 1374 inner-join (lookup abcde) 1375 ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null d:7!null e:8 1376 ├── key columns: [9] = [9] 1377 ├── lookup columns are key 1378 ├── fd: ()-->(5-7), (1)==(4), (4)==(1) 1379 ├── inner-join (lookup abcde@secondary) 1380 │ ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null abcde.rowid:9!null 1381 │ ├── key columns: [1 10 11] = [4 5 6] 1382 │ ├── fd: ()-->(5,6), (9)-->(4), (1)==(4), (4)==(1) 1383 │ ├── project 1384 │ │ ├── columns: "project_const_col_@5":10!null "project_const_col_@6":11!null m:1 n:2 1385 │ │ ├── fd: ()-->(10,11) 1386 │ │ ├── scan small 1387 │ │ │ └── columns: m:1 n:2 1388 │ │ └── projections 1389 │ │ ├── 10 [as="project_const_col_@5":10] 1390 │ │ └── 10 [as="project_const_col_@6":11] 1391 │ └── filters (true) 1392 └── filters 1393 └── d:7 = 10 [outer=(7), constraints=(/7: [/10 - /10]; tight), fd=()-->(7)] 1394 1395 # Non equality filters don't trigger constant projection. 1396 opt 1397 SELECT * FROM small INNER JOIN abcde ON a=m AND b<10 1398 ---- 1399 inner-join (lookup abcde) 1400 ├── columns: m:1!null n:2 a:4!null b:5!null c:6 d:7 e:8 1401 ├── key columns: [9] = [9] 1402 ├── lookup columns are key 1403 ├── fd: (1)==(4), (4)==(1) 1404 ├── inner-join (lookup abcde@secondary) 1405 │ ├── columns: m:1!null n:2 a:4!null b:5!null c:6 abcde.rowid:9!null 1406 │ ├── key columns: [1] = [4] 1407 │ ├── fd: (9)-->(4-6), (1)==(4), (4)==(1) 1408 │ ├── scan small 1409 │ │ └── columns: m:1 n:2 1410 │ └── filters 1411 │ └── b:5 < 10 [outer=(5), constraints=(/5: (/NULL - /9]; tight)] 1412 └── filters (true) 1413 1414 # Lookup Joiner uses the constant equality columns at the same time as the explicit 1415 # column equalities. 1416 opt 1417 SELECT a, b, c FROM small INNER LOOKUP JOIN abcde ON m=b AND a=10 AND c=10 1418 ---- 1419 project 1420 ├── columns: a:4!null b:5!null c:6!null 1421 ├── fd: ()-->(4,6) 1422 └── inner-join (lookup abcde@secondary) 1423 ├── columns: m:1!null a:4!null b:5!null c:6!null 1424 ├── flags: force lookup join (into right side) 1425 ├── key columns: [10 1 11] = [4 5 6] 1426 ├── fd: ()-->(4,6), (1)==(5), (5)==(1) 1427 ├── project 1428 │ ├── columns: "project_const_col_@4":10!null "project_const_col_@6":11!null m:1 1429 │ ├── fd: ()-->(10,11) 1430 │ ├── scan small 1431 │ │ └── columns: m:1 1432 │ └── projections 1433 │ ├── 10 [as="project_const_col_@4":10] 1434 │ └── 10 [as="project_const_col_@6":11] 1435 └── filters (true) 1436 1437 # Projection of constant columns work with non const expressions as well. 1438 exec-ddl 1439 CREATE TABLE bool_col (a INT, b INT, c bool, d bool, e bool, INDEX (a,b,c)) 1440 ---- 1441 1442 # Projection of constant columns work on boolean expressions. 1443 opt 1444 SELECT * FROM small INNER JOIN bool_col ON a=m AND b=10 AND c=true 1445 ---- 1446 inner-join (lookup bool_col) 1447 ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null d:7 e:8 1448 ├── key columns: [9] = [9] 1449 ├── lookup columns are key 1450 ├── fd: ()-->(5,6), (1)==(4), (4)==(1) 1451 ├── inner-join (lookup bool_col@secondary) 1452 │ ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null bool_col.rowid:9!null 1453 │ ├── key columns: [1 10 11] = [4 5 6] 1454 │ ├── fd: ()-->(5,6), (9)-->(4), (1)==(4), (4)==(1) 1455 │ ├── project 1456 │ │ ├── columns: "project_const_col_@5":10!null "project_const_col_@6":11!null m:1 n:2 1457 │ │ ├── fd: ()-->(10,11) 1458 │ │ ├── scan small 1459 │ │ │ └── columns: m:1 n:2 1460 │ │ └── projections 1461 │ │ ├── 10 [as="project_const_col_@5":10] 1462 │ │ └── true [as="project_const_col_@6":11] 1463 │ └── filters (true) 1464 └── filters (true) 1465 1466 opt 1467 SELECT * FROM small INNER JOIN bool_col ON a=m AND b=10 AND c 1468 ---- 1469 inner-join (lookup bool_col) 1470 ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null d:7 e:8 1471 ├── key columns: [9] = [9] 1472 ├── lookup columns are key 1473 ├── fd: ()-->(5,6), (1)==(4), (4)==(1) 1474 ├── inner-join (lookup bool_col@secondary) 1475 │ ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null bool_col.rowid:9!null 1476 │ ├── key columns: [1 10 11] = [4 5 6] 1477 │ ├── fd: ()-->(5,6), (9)-->(4), (1)==(4), (4)==(1) 1478 │ ├── project 1479 │ │ ├── columns: "project_const_col_@5":10!null "project_const_col_@6":11!null m:1 n:2 1480 │ │ ├── fd: ()-->(10,11) 1481 │ │ ├── scan small 1482 │ │ │ └── columns: m:1 n:2 1483 │ │ └── projections 1484 │ │ ├── 10 [as="project_const_col_@5":10] 1485 │ │ └── true [as="project_const_col_@6":11] 1486 │ └── filters (true) 1487 └── filters (true) 1488 1489 opt 1490 SELECT * FROM small INNER JOIN bool_col ON a=m AND b=10 AND NOT c 1491 ---- 1492 inner-join (lookup bool_col) 1493 ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null d:7 e:8 1494 ├── key columns: [9] = [9] 1495 ├── lookup columns are key 1496 ├── fd: ()-->(5,6), (1)==(4), (4)==(1) 1497 ├── inner-join (lookup bool_col@secondary) 1498 │ ├── columns: m:1!null n:2 a:4!null b:5!null c:6!null bool_col.rowid:9!null 1499 │ ├── key columns: [1 10 11] = [4 5 6] 1500 │ ├── fd: ()-->(5,6), (9)-->(4), (1)==(4), (4)==(1) 1501 │ ├── project 1502 │ │ ├── columns: "project_const_col_@5":10!null "project_const_col_@6":11!null m:1 n:2 1503 │ │ ├── fd: ()-->(10,11) 1504 │ │ ├── scan small 1505 │ │ │ └── columns: m:1 n:2 1506 │ │ └── projections 1507 │ │ ├── 10 [as="project_const_col_@5":10] 1508 │ │ └── false [as="project_const_col_@6":11] 1509 │ └── filters (true) 1510 └── filters (true) 1511 1512 exec-ddl 1513 CREATE TABLE t(pk INT PRIMARY KEY, col0 INT, col1 INT, col2 INT, col4 INT, UNIQUE INDEX (col2)) 1514 ---- 1515 1516 # Make sure we don't generate a lookup join with no key columns (#41676). 1517 opt 1518 SELECT pk FROM t WHERE col4 = 1 AND col0 = 1 AND col2 IN (SELECT col0 FROM t WHERE col0 = 1 AND col2 IS NULL); 1519 ---- 1520 project 1521 ├── columns: pk:1!null 1522 ├── cardinality: [0 - 1] 1523 ├── key: () 1524 ├── fd: ()-->(1) 1525 └── semi-join (cross) 1526 ├── columns: pk:1!null col0:2!null col2:4!null col4:5!null 1527 ├── cardinality: [0 - 1] 1528 ├── key: () 1529 ├── fd: ()-->(1,2,4,5) 1530 ├── select 1531 │ ├── columns: pk:1!null col0:2!null col2:4!null col4:5!null 1532 │ ├── cardinality: [0 - 1] 1533 │ ├── key: () 1534 │ ├── fd: ()-->(1,2,4,5) 1535 │ ├── index-join t 1536 │ │ ├── columns: pk:1!null col0:2 col2:4 col4:5 1537 │ │ ├── cardinality: [0 - 1] 1538 │ │ ├── key: () 1539 │ │ ├── fd: ()-->(1,2,4,5) 1540 │ │ └── scan t@secondary 1541 │ │ ├── columns: pk:1!null col2:4!null 1542 │ │ ├── constraint: /4: [/1 - /1] 1543 │ │ ├── cardinality: [0 - 1] 1544 │ │ ├── key: () 1545 │ │ └── fd: ()-->(1,4) 1546 │ └── filters 1547 │ ├── col4:5 = 1 [outer=(5), constraints=(/5: [/1 - /1]; tight), fd=()-->(5)] 1548 │ └── col0:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1549 ├── select 1550 │ ├── columns: col0:7!null col2:9 1551 │ ├── lax-key: (9) 1552 │ ├── fd: ()-->(7,9) 1553 │ ├── index-join t 1554 │ │ ├── columns: col0:7 col2:9 1555 │ │ ├── lax-key: (7,9) 1556 │ │ ├── fd: ()-->(9), (9)~~>(7) 1557 │ │ └── scan t@secondary 1558 │ │ ├── columns: pk:6!null col2:9 1559 │ │ ├── constraint: /9: [/NULL - /NULL] 1560 │ │ ├── key: (6) 1561 │ │ └── fd: ()-->(9), (9)~~>(6) 1562 │ └── filters 1563 │ └── col0:7 = 1 [outer=(7), constraints=(/7: [/1 - /1]; tight), fd=()-->(7)] 1564 └── filters (true) 1565 1566 # -------------------------------------------------- 1567 # GenerateGeospatialLookupJoins 1568 # -------------------------------------------------- 1569 1570 exec-ddl 1571 CREATE TABLE nyc_census_blocks ( 1572 gid serial PRIMARY KEY, 1573 blkid varchar(15), 1574 popn_total float8, 1575 popn_white float8, 1576 popn_black float8, 1577 popn_nativ float8, 1578 popn_asian float8, 1579 popn_other float8, 1580 boroname varchar(32), 1581 geom GEOMETRY(MULTIPOLYGON,4326), 1582 INVERTED INDEX nyc_census_blocks_geo_idx (geom) 1583 ) 1584 ---- 1585 1586 exec-ddl 1587 CREATE TABLE nyc_neighborhoods ( 1588 gid serial PRIMARY KEY, 1589 boroname varchar(43), 1590 name varchar(64), 1591 geom GEOMETRY(MULTIPOLYGON,4326), 1592 INVERTED INDEX nyc_neighborhoods_geo_idx (geom) 1593 ) 1594 ---- 1595 1596 exec-ddl 1597 ALTER TABLE nyc_census_blocks INJECT STATISTICS '[ 1598 { 1599 "columns": ["gid"], 1600 "created_at": "2018-01-01 1:00:00.00000+00:00", 1601 "row_count": 38794, 1602 "distinct_count": 38794 1603 }, 1604 { 1605 "columns": ["boroname"], 1606 "created_at": "2018-01-01 1:00:00.00000+00:00", 1607 "row_count": 38794, 1608 "distinct_count": 5 1609 } 1610 ]' 1611 ---- 1612 1613 exec-ddl 1614 ALTER TABLE nyc_neighborhoods INJECT STATISTICS '[ 1615 { 1616 "columns": ["gid"], 1617 "created_at": "2018-01-01 1:00:00.00000+00:00", 1618 "row_count": 129, 1619 "distinct_count": 129 1620 }, 1621 { 1622 "columns": ["boroname"], 1623 "created_at": "2018-01-01 1:00:00.00000+00:00", 1624 "row_count": 129, 1625 "distinct_count": 5 1626 }, 1627 { 1628 "columns": ["name"], 1629 "created_at": "2018-01-01 1:00:00.00000+00:00", 1630 "row_count": 129, 1631 "distinct_count": 129 1632 } 1633 ]' 1634 ---- 1635 1636 # This query calculates the population density of two different neighborhoods 1637 # in New York City. 1638 opt expect=GenerateGeoLookupJoins 1639 SELECT 1640 n.name, 1641 Sum(c.popn_total) / (ST_Area(n.geom) / 1000000.0) AS popn_per_sqkm 1642 FROM nyc_census_blocks AS c 1643 JOIN nyc_neighborhoods AS n 1644 ON ST_Intersects(c.geom, n.geom) AND c.boroname = n.boroname 1645 WHERE n.name = 'Upper West Side' 1646 OR n.name = 'Upper East Side' 1647 GROUP BY n.name, n.geom 1648 ---- 1649 project 1650 ├── columns: name:13!null popn_per_sqkm:16 1651 ├── immutable, side-effects 1652 ├── group-by 1653 │ ├── columns: name:13!null n.geom:14 sum:15 1654 │ ├── grouping columns: name:13!null n.geom:14 1655 │ ├── immutable 1656 │ ├── key: (13,14) 1657 │ ├── fd: (13,14)-->(15) 1658 │ ├── inner-join (lookup nyc_census_blocks) 1659 │ │ ├── columns: popn_total:3 c.boroname:9!null c.geom:10 n.boroname:12!null name:13!null n.geom:14 1660 │ │ ├── key columns: [1] = [1] 1661 │ │ ├── lookup columns are key 1662 │ │ ├── immutable 1663 │ │ ├── fd: (9)==(12), (12)==(9) 1664 │ │ ├── inner-join (geo-lookup nyc_census_blocks@nyc_census_blocks_geo_idx) 1665 │ │ │ ├── columns: c.gid:1!null n.boroname:12 name:13!null n.geom:14 1666 │ │ │ ├── geo-relationship: intersects 1667 │ │ │ ├── select 1668 │ │ │ │ ├── columns: n.boroname:12 name:13!null n.geom:14 1669 │ │ │ │ ├── scan n 1670 │ │ │ │ │ └── columns: n.boroname:12 name:13 n.geom:14 1671 │ │ │ │ └── filters 1672 │ │ │ │ └── (name:13 = 'Upper West Side') OR (name:13 = 'Upper East Side') [outer=(13), constraints=(/13: [/'Upper East Side' - /'Upper East Side'] [/'Upper West Side' - /'Upper West Side']; tight)] 1673 │ │ │ └── filters (true) 1674 │ │ └── filters 1675 │ │ ├── st_intersects(c.geom:10, n.geom:14) [outer=(10,14), immutable] 1676 │ │ └── c.boroname:9 = n.boroname:12 [outer=(9,12), constraints=(/9: (/NULL - ]; /12: (/NULL - ]), fd=(9)==(12), (12)==(9)] 1677 │ └── aggregations 1678 │ └── sum [as=sum:15, outer=(3)] 1679 │ └── popn_total:3 1680 └── projections 1681 └── sum:15 / (st_area(n.geom:14) / 1e+06) [as=popn_per_sqkm:16, outer=(14,15), immutable, side-effects] 1682 1683 memo expect=GenerateGeoLookupJoins 1684 SELECT 1685 n.name, 1686 Sum(c.popn_total) / (ST_Area(n.geom) / 1000000.0) AS popn_per_sqkm 1687 FROM nyc_census_blocks AS c 1688 JOIN nyc_neighborhoods AS n 1689 ON ST_Intersects(c.geom, n.geom) AND c.boroname = n.boroname 1690 WHERE n.name = 'Upper West Side' 1691 OR n.name = 'Upper East Side' 1692 GROUP BY n.name, n.geom 1693 ---- 1694 memo (optimized, ~23KB, required=[presentation: name:13,popn_per_sqkm:16]) 1695 ├── G1: (project G2 G3 name) 1696 │ └── [presentation: name:13,popn_per_sqkm:16] 1697 │ ├── best: (project G2 G3 name) 1698 │ └── cost: 6717.66 1699 ├── G2: (group-by G4 G5 cols=(13,14)) 1700 │ └── [] 1701 │ ├── best: (group-by G4 G5 cols=(13,14)) 1702 │ └── cost: 6717.61 1703 ├── G3: (projections G6) 1704 ├── G4: (inner-join G7 G8 G9) (inner-join G8 G7 G9) (lookup-join G10 G9 nyc_census_blocks,keyCols=[1],outCols=(3,9,10,12-14)) 1705 │ └── [] 1706 │ ├── best: (lookup-join G10 G9 nyc_census_blocks,keyCols=[1],outCols=(3,9,10,12-14)) 1707 │ └── cost: 6510.67 1708 ├── G5: (aggregations G11) 1709 ├── G6: (div G12 G13) 1710 ├── G7: (scan c,cols=(3,9,10)) 1711 │ └── [] 1712 │ ├── best: (scan c,cols=(3,9,10)) 1713 │ └── cost: 43837.24 1714 ├── G8: (select G14 G15) 1715 │ └── [] 1716 │ ├── best: (select G14 G15) 1717 │ └── cost: 139.35 1718 ├── G9: (filters G16 G17) 1719 ├── G10: (geo-lookup-join G8 G18 nyc_census_blocks@nyc_census_blocks_geo_idx) 1720 │ └── [] 1721 │ ├── best: (geo-lookup-join G8 G18 nyc_census_blocks@nyc_census_blocks_geo_idx) 1722 │ └── cost: 1754.40 1723 ├── G11: (sum G19) 1724 ├── G12: (variable sum) 1725 ├── G13: (div G20 G21) 1726 ├── G14: (scan n,cols=(12-14)) 1727 │ └── [] 1728 │ ├── best: (scan n,cols=(12-14)) 1729 │ └── cost: 138.05 1730 ├── G15: (filters G22) 1731 ├── G16: (function G23 st_intersects) 1732 ├── G17: (eq G24 G25) 1733 ├── G18: (filters) 1734 ├── G19: (variable popn_total) 1735 ├── G20: (function G26 st_area) 1736 ├── G21: (const 1e+06) 1737 ├── G22: (or G27 G28) 1738 ├── G23: (scalar-list G29 G30) 1739 ├── G24: (variable c.boroname) 1740 ├── G25: (variable n.boroname) 1741 ├── G26: (scalar-list G30) 1742 ├── G27: (eq G31 G32) 1743 ├── G28: (eq G31 G33) 1744 ├── G29: (variable c.geom) 1745 ├── G30: (variable n.geom) 1746 ├── G31: (variable name) 1747 ├── G32: (const 'Upper West Side') 1748 └── G33: (const 'Upper East Side') 1749 1750 # -------------------------------------------------- 1751 # GenerateZigZagJoins 1752 # -------------------------------------------------- 1753 1754 # Simple zigzag case - where all requested columns are in the indexes being 1755 # joined. 1756 opt 1757 SELECT q,r FROM pqr WHERE q = 1 AND r = 2 1758 ---- 1759 inner-join (zigzag pqr@q pqr@r) 1760 ├── columns: q:2!null r:3!null 1761 ├── eq columns: [1] = [1] 1762 ├── left fixed columns: [2] = [1] 1763 ├── right fixed columns: [3] = [2] 1764 ├── fd: ()-->(2,3) 1765 └── filters 1766 ├── q:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1767 └── r:3 = 2 [outer=(3), constraints=(/3: [/2 - /2]; tight), fd=()-->(3)] 1768 1769 opt 1770 SELECT q,r FROM pqr WHERE q = 1 AND r IS NULL 1771 ---- 1772 inner-join (zigzag pqr@q pqr@r) 1773 ├── columns: q:2!null r:3 1774 ├── eq columns: [1] = [1] 1775 ├── left fixed columns: [2] = [1] 1776 ├── right fixed columns: [3] = [NULL] 1777 ├── fd: ()-->(2,3) 1778 └── filters 1779 ├── q:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1780 └── r:3 IS NULL [outer=(3), constraints=(/3: [/NULL - /NULL]; tight), fd=()-->(3)] 1781 1782 memo 1783 SELECT q,r FROM pqr WHERE q = 1 AND r = 2 1784 ---- 1785 memo (optimized, ~13KB, required=[presentation: q:2,r:3]) 1786 ├── G1: (select G2 G3) (zigzag-join G3 pqr@q pqr@r) (select G4 G5) (select G6 G7) (select G8 G7) 1787 │ └── [presentation: q:2,r:3] 1788 │ ├── best: (zigzag-join G3 pqr@q pqr@r) 1789 │ └── cost: 1.92 1790 ├── G2: (scan pqr,cols=(2,3)) 1791 │ └── [] 1792 │ ├── best: (scan pqr,cols=(2,3)) 1793 │ └── cost: 1070.02 1794 ├── G3: (filters G9 G10) 1795 ├── G4: (index-join G11 pqr,cols=(2,3)) 1796 │ └── [] 1797 │ ├── best: (index-join G11 pqr,cols=(2,3)) 1798 │ └── cost: 51.22 1799 ├── G5: (filters G10) 1800 ├── G6: (index-join G12 pqr,cols=(2,3)) 1801 │ └── [] 1802 │ ├── best: (index-join G12 pqr,cols=(2,3)) 1803 │ └── cost: 51.22 1804 ├── G7: (filters G9) 1805 ├── G8: (index-join G13 pqr,cols=(2,3)) 1806 │ └── [] 1807 │ ├── best: (index-join G13 pqr,cols=(2,3)) 1808 │ └── cost: 51.32 1809 ├── G9: (eq G14 G15) 1810 ├── G10: (eq G16 G17) 1811 ├── G11: (scan pqr@q,cols=(1,2),constrained) 1812 │ └── [] 1813 │ ├── best: (scan pqr@q,cols=(1,2),constrained) 1814 │ └── cost: 10.41 1815 ├── G12: (scan pqr@r,cols=(1,3),constrained) 1816 │ └── [] 1817 │ ├── best: (scan pqr@r,cols=(1,3),constrained) 1818 │ └── cost: 10.41 1819 ├── G13: (scan pqr@rs,cols=(1,3),constrained) 1820 │ └── [] 1821 │ ├── best: (scan pqr@rs,cols=(1,3),constrained) 1822 │ └── cost: 10.51 1823 ├── G14: (variable q) 1824 ├── G15: (const 1) 1825 ├── G16: (variable r) 1826 └── G17: (const 2) 1827 1828 # Case where the fixed columns are extracted from a complicated expression. 1829 opt 1830 SELECT q,r FROM pqr WHERE q = 1 AND ((r < 1 AND r > 1) OR (r >= 2 AND r <= 2)) 1831 ---- 1832 inner-join (zigzag pqr@q pqr@r) 1833 ├── columns: q:2!null r:3!null 1834 ├── eq columns: [1] = [1] 1835 ├── left fixed columns: [2] = [1] 1836 ├── right fixed columns: [3] = [2] 1837 ├── fd: ()-->(2,3) 1838 └── filters 1839 ├── q:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1840 └── ((r:3 < 1) AND (r:3 > 1)) OR ((r:3 >= 2) AND (r:3 <= 2)) [outer=(3), constraints=(/3: [/2 - /2]; tight), fd=()-->(3)] 1841 1842 # Nested zigzag case - zigzag join needs to be wrapped in a lookup join to 1843 # satisfy required columns. 1844 opt 1845 SELECT q,r,s FROM pqr WHERE q = 1 AND r = 2 1846 ---- 1847 inner-join (lookup pqr) 1848 ├── columns: q:2!null r:3!null s:4 1849 ├── key columns: [1] = [1] 1850 ├── lookup columns are key 1851 ├── fd: ()-->(2,3) 1852 ├── inner-join (zigzag pqr@q pqr@r) 1853 │ ├── columns: p:1!null q:2!null r:3!null 1854 │ ├── eq columns: [1] = [1] 1855 │ ├── left fixed columns: [2] = [1] 1856 │ ├── right fixed columns: [3] = [2] 1857 │ ├── fd: ()-->(2,3) 1858 │ └── filters 1859 │ ├── q:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1860 │ └── r:3 = 2 [outer=(3), constraints=(/3: [/2 - /2]; tight), fd=()-->(3)] 1861 └── filters (true) 1862 1863 memo 1864 SELECT q,r,s FROM pqr WHERE q = 1 AND r = 2 1865 ---- 1866 memo (optimized, ~16KB, required=[presentation: q:2,r:3,s:4]) 1867 ├── G1: (select G2 G3) (lookup-join G4 G5 pqr,keyCols=[1],outCols=(2-4)) (select G6 G7) (select G8 G9) (select G10 G9) 1868 │ └── [presentation: q:2,r:3,s:4] 1869 │ ├── best: (lookup-join G4 G5 pqr,keyCols=[1],outCols=(2-4)) 1870 │ └── cost: 7.46 1871 ├── G2: (scan pqr,cols=(2-4)) 1872 │ └── [] 1873 │ ├── best: (scan pqr,cols=(2-4)) 1874 │ └── cost: 1080.02 1875 ├── G3: (filters G11 G12) 1876 ├── G4: (zigzag-join G3 pqr@q pqr@r) 1877 │ └── [] 1878 │ ├── best: (zigzag-join G3 pqr@q pqr@r) 1879 │ └── cost: 1.93 1880 ├── G5: (filters) 1881 ├── G6: (index-join G13 pqr,cols=(2-4)) 1882 │ └── [] 1883 │ ├── best: (index-join G13 pqr,cols=(2-4)) 1884 │ └── cost: 51.32 1885 ├── G7: (filters G12) 1886 ├── G8: (index-join G14 pqr,cols=(2-4)) 1887 │ └── [] 1888 │ ├── best: (index-join G14 pqr,cols=(2-4)) 1889 │ └── cost: 51.32 1890 ├── G9: (filters G11) 1891 ├── G10: (index-join G15 pqr,cols=(2-4)) 1892 │ └── [] 1893 │ ├── best: (index-join G15 pqr,cols=(2-4)) 1894 │ └── cost: 51.52 1895 ├── G11: (eq G16 G17) 1896 ├── G12: (eq G18 G19) 1897 ├── G13: (scan pqr@q,cols=(1,2),constrained) 1898 │ └── [] 1899 │ ├── best: (scan pqr@q,cols=(1,2),constrained) 1900 │ └── cost: 10.41 1901 ├── G14: (scan pqr@r,cols=(1,3),constrained) 1902 │ └── [] 1903 │ ├── best: (scan pqr@r,cols=(1,3),constrained) 1904 │ └── cost: 10.41 1905 ├── G15: (scan pqr@rs,cols=(1,3,4),constrained) 1906 │ └── [] 1907 │ ├── best: (scan pqr@rs,cols=(1,3,4),constrained) 1908 │ └── cost: 10.61 1909 ├── G16: (variable q) 1910 ├── G17: (const 1) 1911 ├── G18: (variable r) 1912 └── G19: (const 2) 1913 1914 # Zigzag with fixed columns of different types. 1915 opt 1916 SELECT q,s FROM pqr WHERE q = 1 AND s = 'foo' 1917 ---- 1918 inner-join (zigzag pqr@q pqr@s) 1919 ├── columns: q:2!null s:4!null 1920 ├── eq columns: [1] = [1] 1921 ├── left fixed columns: [2] = [1] 1922 ├── right fixed columns: [4] = ['foo'] 1923 ├── fd: ()-->(2,4) 1924 └── filters 1925 ├── q:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 1926 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 1927 1928 memo 1929 SELECT q,s FROM pqr WHERE q = 1 AND s = 'foo' 1930 ---- 1931 memo (optimized, ~11KB, required=[presentation: q:2,s:4]) 1932 ├── G1: (select G2 G3) (zigzag-join G3 pqr@q pqr@s) (select G4 G5) (select G6 G7) 1933 │ └── [presentation: q:2,s:4] 1934 │ ├── best: (zigzag-join G3 pqr@q pqr@s) 1935 │ └── cost: 1.93 1936 ├── G2: (scan pqr,cols=(2,4)) 1937 │ └── [] 1938 │ ├── best: (scan pqr,cols=(2,4)) 1939 │ └── cost: 1070.02 1940 ├── G3: (filters G8 G9) 1941 ├── G4: (index-join G10 pqr,cols=(2,4)) 1942 │ └── [] 1943 │ ├── best: (index-join G10 pqr,cols=(2,4)) 1944 │ └── cost: 51.22 1945 ├── G5: (filters G9) 1946 ├── G6: (index-join G11 pqr,cols=(2,4)) 1947 │ └── [] 1948 │ ├── best: (index-join G11 pqr,cols=(2,4)) 1949 │ └── cost: 51.32 1950 ├── G7: (filters G8) 1951 ├── G8: (eq G12 G13) 1952 ├── G9: (eq G14 G15) 1953 ├── G10: (scan pqr@q,cols=(1,2),constrained) 1954 │ └── [] 1955 │ ├── best: (scan pqr@q,cols=(1,2),constrained) 1956 │ └── cost: 10.41 1957 ├── G11: (scan pqr@s,cols=(1,4),constrained) 1958 │ └── [] 1959 │ ├── best: (scan pqr@s,cols=(1,4),constrained) 1960 │ └── cost: 10.51 1961 ├── G12: (variable q) 1962 ├── G13: (const 1) 1963 ├── G14: (variable s) 1964 └── G15: (const 'foo') 1965 1966 # Zigzag with implicit equality column in addition to primary key: 1967 # indexes on (r,s) and (t,s) should be chosen even though s is not being fixed 1968 # in the ON clause. 1969 opt 1970 SELECT r,t FROM pqr WHERE r = 1 AND t = 'foo' 1971 ---- 1972 inner-join (zigzag pqr@rs pqr@ts) 1973 ├── columns: r:3!null t:5!null 1974 ├── eq columns: [4 1] = [4 1] 1975 ├── left fixed columns: [3] = [1] 1976 ├── right fixed columns: [5] = ['foo'] 1977 ├── fd: ()-->(3,5) 1978 └── filters 1979 ├── r:3 = 1 [outer=(3), constraints=(/3: [/1 - /1]; tight), fd=()-->(3)] 1980 └── t:5 = 'foo' [outer=(5), constraints=(/5: [/'foo' - /'foo']; tight), fd=()-->(5)] 1981 1982 memo 1983 SELECT r,t FROM pqr WHERE r = 1 AND t = 'foo' 1984 ---- 1985 memo (optimized, ~13KB, required=[presentation: r:3,t:5]) 1986 ├── G1: (select G2 G3) (zigzag-join G3 pqr@rs pqr@ts) (select G4 G5) (select G6 G5) (select G7 G8) 1987 │ └── [presentation: r:3,t:5] 1988 │ ├── best: (zigzag-join G3 pqr@rs pqr@ts) 1989 │ └── cost: 1.94 1990 ├── G2: (scan pqr,cols=(3,5)) 1991 │ └── [] 1992 │ ├── best: (scan pqr,cols=(3,5)) 1993 │ └── cost: 1070.02 1994 ├── G3: (filters G9 G10) 1995 ├── G4: (index-join G11 pqr,cols=(3,5)) 1996 │ └── [] 1997 │ ├── best: (index-join G11 pqr,cols=(3,5)) 1998 │ └── cost: 51.22 1999 ├── G5: (filters G10) 2000 ├── G6: (index-join G12 pqr,cols=(3,5)) 2001 │ └── [] 2002 │ ├── best: (index-join G12 pqr,cols=(3,5)) 2003 │ └── cost: 51.32 2004 ├── G7: (index-join G13 pqr,cols=(3,5)) 2005 │ └── [] 2006 │ ├── best: (index-join G13 pqr,cols=(3,5)) 2007 │ └── cost: 51.32 2008 ├── G8: (filters G9) 2009 ├── G9: (eq G14 G15) 2010 ├── G10: (eq G16 G17) 2011 ├── G11: (scan pqr@r,cols=(1,3),constrained) 2012 │ └── [] 2013 │ ├── best: (scan pqr@r,cols=(1,3),constrained) 2014 │ └── cost: 10.41 2015 ├── G12: (scan pqr@rs,cols=(1,3),constrained) 2016 │ └── [] 2017 │ ├── best: (scan pqr@rs,cols=(1,3),constrained) 2018 │ └── cost: 10.51 2019 ├── G13: (scan pqr@ts,cols=(1,5),constrained) 2020 │ └── [] 2021 │ ├── best: (scan pqr@ts,cols=(1,5),constrained) 2022 │ └── cost: 10.51 2023 ├── G14: (variable r) 2024 ├── G15: (const 1) 2025 ├── G16: (variable t) 2026 └── G17: (const 'foo') 2027 2028 # Zigzag with choice between indexes for multiple equality predicates. 2029 opt 2030 SELECT p,q,r,s FROM pqr WHERE q = 1 AND r = 1 AND s = 'foo' 2031 ---- 2032 inner-join (zigzag pqr@q pqr@s) 2033 ├── columns: p:1!null q:2!null r:3!null s:4!null 2034 ├── eq columns: [1] = [1] 2035 ├── left fixed columns: [2] = [1] 2036 ├── right fixed columns: [4] = ['foo'] 2037 ├── key: (1) 2038 ├── fd: ()-->(2-4) 2039 └── filters 2040 ├── q:2 = 1 [outer=(2), constraints=(/2: [/1 - /1]; tight), fd=()-->(2)] 2041 ├── r:3 = 1 [outer=(3), constraints=(/3: [/1 - /1]; tight), fd=()-->(3)] 2042 └── s:4 = 'foo' [outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 2043 2044 # Don't generate a zigzag which has the PK as its equality columns against 2045 # nullable unique indexes where the primary key is not part of the indexed 2046 # columns. 2047 2048 # Regression test for #36051: prior to fixing this, we would try to use the PK 2049 # as the equality column here, but it's not actually part of the key so we 2050 # can't zigzag on it. 2051 opt 2052 SELECT * FROM zz WHERE b IS NULL AND c = 2 2053 ---- 2054 select 2055 ├── columns: a:1!null b:2 c:3!null 2056 ├── cardinality: [0 - 1] 2057 ├── key: () 2058 ├── fd: ()-->(1-3) 2059 ├── index-join zz 2060 │ ├── columns: a:1!null b:2 c:3 2061 │ ├── cardinality: [0 - 1] 2062 │ ├── key: () 2063 │ ├── fd: ()-->(1-3) 2064 │ └── scan zz@idx_c 2065 │ ├── columns: a:1!null c:3!null 2066 │ ├── constraint: /3: [/2 - /2] 2067 │ ├── cardinality: [0 - 1] 2068 │ ├── key: () 2069 │ └── fd: ()-->(1,3) 2070 └── filters 2071 └── b:2 IS NULL [outer=(2), constraints=(/2: [/NULL - /NULL]; tight), fd=()-->(2)] 2072 2073 memo 2074 SELECT p,q,r,s FROM pqr WHERE q = 1 AND r = 1 AND s = 'foo' 2075 ---- 2076 memo (optimized, ~32KB, required=[presentation: p:1,q:2,r:3,s:4]) 2077 ├── G1: (select G2 G3) (lookup-join G4 G5 pqr,keyCols=[1],outCols=(1-4)) (zigzag-join G3 pqr@q pqr@s) (zigzag-join G3 pqr@q pqr@rs) (lookup-join G6 G7 pqr,keyCols=[1],outCols=(1-4)) (select G8 G9) (select G10 G11) (select G12 G7) (select G13 G7) 2078 │ └── [presentation: p:1,q:2,r:3,s:4] 2079 │ ├── best: (zigzag-join G3 pqr@q pqr@s) 2080 │ └── cost: 1.94 2081 ├── G2: (scan pqr,cols=(1-4)) 2082 │ └── [] 2083 │ ├── best: (scan pqr,cols=(1-4)) 2084 │ └── cost: 1090.02 2085 ├── G3: (filters G14 G15 G16) 2086 ├── G4: (zigzag-join G17 pqr@q pqr@r) 2087 │ └── [] 2088 │ ├── best: (zigzag-join G17 pqr@q pqr@r) 2089 │ └── cost: 1.93 2090 ├── G5: (filters G16) 2091 ├── G6: (zigzag-join G9 pqr@r pqr@s) 2092 │ └── [] 2093 │ ├── best: (zigzag-join G9 pqr@r pqr@s) 2094 │ └── cost: 1.94 2095 ├── G7: (filters G14) 2096 ├── G8: (index-join G18 pqr,cols=(1-4)) 2097 │ └── [] 2098 │ ├── best: (index-join G18 pqr,cols=(1-4)) 2099 │ └── cost: 51.42 2100 ├── G9: (filters G15 G16) 2101 ├── G10: (index-join G19 pqr,cols=(1-4)) 2102 │ └── [] 2103 │ ├── best: (index-join G19 pqr,cols=(1-4)) 2104 │ └── cost: 51.42 2105 ├── G11: (filters G14 G16) 2106 ├── G12: (index-join G20 pqr,cols=(1-4)) 2107 │ └── [] 2108 │ ├── best: (index-join G20 pqr,cols=(1-4)) 2109 │ └── cost: 15.47 2110 ├── G13: (index-join G21 pqr,cols=(1-4)) 2111 │ └── [] 2112 │ ├── best: (index-join G21 pqr,cols=(1-4)) 2113 │ └── cost: 4.72 2114 ├── G14: (eq G22 G23) 2115 ├── G15: (eq G24 G23) 2116 ├── G16: (eq G25 G26) 2117 ├── G17: (filters G14 G15) 2118 ├── G18: (scan pqr@q,cols=(1,2),constrained) 2119 │ └── [] 2120 │ ├── best: (scan pqr@q,cols=(1,2),constrained) 2121 │ └── cost: 10.41 2122 ├── G19: (scan pqr@r,cols=(1,3),constrained) 2123 │ └── [] 2124 │ ├── best: (scan pqr@r,cols=(1,3),constrained) 2125 │ └── cost: 10.41 2126 ├── G20: (select G27 G28) 2127 │ └── [] 2128 │ ├── best: (select G27 G28) 2129 │ └── cost: 10.72 2130 ├── G21: (scan pqr@rs,cols=(1,3,4),constrained) 2131 │ └── [] 2132 │ ├── best: (scan pqr@rs,cols=(1,3,4),constrained) 2133 │ └── cost: 0.98 2134 ├── G22: (variable q) 2135 ├── G23: (const 1) 2136 ├── G24: (variable r) 2137 ├── G25: (variable s) 2138 ├── G26: (const 'foo') 2139 ├── G27: (scan pqr@s,cols=(1,3,4),constrained) 2140 │ └── [] 2141 │ ├── best: (scan pqr@s,cols=(1,3,4),constrained) 2142 │ └── cost: 10.61 2143 └── G28: (filters G15) 2144 2145 # Zigzag joins cannot be planned for indexes where equality columns do not 2146 # immediately follow fixed columns. Here, the only index on t is (t,s,p) and 2147 # s is not a fixed or equal column, so a zigzag join shouldn't be planned. 2148 opt 2149 SELECT q,t FROM pqr WHERE q = 1 AND t = 'foo' 2150 ---- 2151 select 2152 ├── columns: q:2!null t:5!null 2153 ├── fd: ()-->(2,5) 2154 ├── index-join pqr 2155 │ ├── columns: q:2 t:5 2156 │ ├── fd: ()-->(2) 2157 │ └── scan pqr@q 2158 │ ├── columns: p:1!null q:2!null 2159 │ ├── constraint: /2/1: [/1 - /1] 2160 │ ├── key: (1) 2161 │ └── fd: ()-->(2) 2162 └── filters 2163 └── t:5 = 'foo' [outer=(5), constraints=(/5: [/'foo' - /'foo']; tight), fd=()-->(5)] 2164 2165 memo 2166 SELECT q,t FROM pqr WHERE q = 1 AND t = 'foo' 2167 ---- 2168 memo (optimized, ~9KB, required=[presentation: q:2,t:5]) 2169 ├── G1: (select G2 G3) (select G4 G5) (select G6 G7) 2170 │ └── [presentation: q:2,t:5] 2171 │ ├── best: (select G4 G5) 2172 │ └── cost: 51.33 2173 ├── G2: (scan pqr,cols=(2,5)) 2174 │ └── [] 2175 │ ├── best: (scan pqr,cols=(2,5)) 2176 │ └── cost: 1070.02 2177 ├── G3: (filters G8 G9) 2178 ├── G4: (index-join G10 pqr,cols=(2,5)) 2179 │ └── [] 2180 │ ├── best: (index-join G10 pqr,cols=(2,5)) 2181 │ └── cost: 51.22 2182 ├── G5: (filters G9) 2183 ├── G6: (index-join G11 pqr,cols=(2,5)) 2184 │ └── [] 2185 │ ├── best: (index-join G11 pqr,cols=(2,5)) 2186 │ └── cost: 51.32 2187 ├── G7: (filters G8) 2188 ├── G8: (eq G12 G13) 2189 ├── G9: (eq G14 G15) 2190 ├── G10: (scan pqr@q,cols=(1,2),constrained) 2191 │ └── [] 2192 │ ├── best: (scan pqr@q,cols=(1,2),constrained) 2193 │ └── cost: 10.41 2194 ├── G11: (scan pqr@ts,cols=(1,5),constrained) 2195 │ └── [] 2196 │ ├── best: (scan pqr@ts,cols=(1,5),constrained) 2197 │ └── cost: 10.51 2198 ├── G12: (variable q) 2199 ├── G13: (const 1) 2200 ├── G14: (variable t) 2201 └── G15: (const 'foo') 2202 2203 # Don't zigzag on two identical indexes. 2204 memo 2205 SELECT c FROM zz_redundant WHERE b = 1 2206 ---- 2207 memo (optimized, ~6KB, required=[presentation: c:3]) 2208 ├── G1: (project G2 G3 c) 2209 │ └── [presentation: c:3] 2210 │ ├── best: (project G2 G3 c) 2211 │ └── cost: 10.62 2212 ├── G2: (select G4 G5) (scan zz_redundant@idx_u,cols=(2,3),constrained) (scan zz_redundant@idx_v,cols=(2,3),constrained) 2213 │ └── [] 2214 │ ├── best: (scan zz_redundant@idx_u,cols=(2,3),constrained) 2215 │ └── cost: 10.51 2216 ├── G3: (projections) 2217 ├── G4: (scan zz_redundant,cols=(2,3)) (scan zz_redundant@idx_u,cols=(2,3)) (scan zz_redundant@idx_v,cols=(2,3)) 2218 │ └── [] 2219 │ ├── best: (scan zz_redundant,cols=(2,3)) 2220 │ └── cost: 1050.02 2221 ├── G5: (filters G6) 2222 ├── G6: (eq G7 G8) 2223 ├── G7: (variable b) 2224 └── G8: (const 1) 2225 2226 # GenerateZigzagJoins is disabled in the presence of a row-level locking clause. 2227 opt 2228 SELECT q,r FROM pqr WHERE q = 1 AND r = 2 FOR UPDATE 2229 ---- 2230 select 2231 ├── columns: q:2!null r:3!null 2232 ├── volatile, side-effects 2233 ├── fd: ()-->(2,3) 2234 ├── index-join pqr 2235 │ ├── columns: q:2 r:3 2236 │ ├── volatile, side-effects 2237 │ ├── fd: ()-->(2) 2238 │ └── scan pqr@q 2239 │ ├── columns: p:1!null q:2!null 2240 │ ├── constraint: /2/1: [/1 - /1] 2241 │ ├── locking: for-update 2242 │ ├── volatile, side-effects 2243 │ ├── key: (1) 2244 │ └── fd: ()-->(2) 2245 └── filters 2246 └── r:3 = 2 [outer=(3), constraints=(/3: [/2 - /2]; tight), fd=()-->(3)] 2247 2248 # -------------------------------------------------- 2249 # GenerateInvertedIndexZigzagJoins 2250 # -------------------------------------------------- 2251 2252 exec-ddl 2253 CREATE TABLE t5 ( 2254 a INT PRIMARY KEY, 2255 b JSONB, 2256 c INT, 2257 INVERTED INDEX b_idx(b) 2258 ) 2259 ---- 2260 2261 # One path. Should generate a scan constrained on the inverted index. 2262 opt 2263 SELECT b,a FROM t5 WHERE b @> '{"a":1}' 2264 ---- 2265 index-join t5 2266 ├── columns: b:2 a:1!null 2267 ├── key: (1) 2268 ├── fd: (1)-->(2) 2269 └── scan t5@b_idx 2270 ├── columns: a:1!null 2271 ├── constraint: /2/1: [/'{"a": 1}' - /'{"a": 1}'] 2272 └── key: (1) 2273 2274 opt 2275 SELECT b,a FROM t5 WHERE b @> '{"a":[[{"b":{"c":[{"d":"e"}]}}]]}' 2276 ---- 2277 index-join t5 2278 ├── columns: b:2 a:1!null 2279 ├── key: (1) 2280 ├── fd: (1)-->(2) 2281 └── scan t5@b_idx 2282 ├── columns: a:1!null 2283 ├── constraint: /2/1: [/'{"a": [[{"b": {"c": [{"d": "e"}]}}]]}' - /'{"a": [[{"b": {"c": [{"d": "e"}]}}]]}'] 2284 └── key: (1) 2285 2286 # Two paths. Should generate a zigzag join. 2287 opt 2288 SELECT b,a FROM t5 WHERE b @> '{"a":1, "c":2}' 2289 ---- 2290 inner-join (lookup t5) 2291 ├── columns: b:2 a:1!null 2292 ├── key columns: [1] = [1] 2293 ├── lookup columns are key 2294 ├── key: (1) 2295 ├── fd: (1)-->(2) 2296 ├── inner-join (zigzag t5@b_idx t5@b_idx) 2297 │ ├── columns: a:1!null 2298 │ ├── eq columns: [1] = [1] 2299 │ ├── left fixed columns: [2] = ['{"a": 1}'] 2300 │ ├── right fixed columns: [2] = ['{"c": 2}'] 2301 │ └── filters (true) 2302 └── filters 2303 └── b:2 @> '{"a": 1, "c": 2}' [outer=(2)] 2304 2305 memo 2306 SELECT a FROM t5 WHERE b @> '{"a":1, "c":2}' 2307 ---- 2308 memo (optimized, ~14KB, required=[presentation: a:1]) 2309 ├── G1: (project G2 G3 a) 2310 │ └── [presentation: a:1] 2311 │ ├── best: (project G2 G3 a) 2312 │ └── cost: 100.29 2313 ├── G2: (select G4 G5) (lookup-join G6 G5 t5,keyCols=[1],outCols=(1,2)) (select G7 G5) 2314 │ └── [] 2315 │ ├── best: (lookup-join G6 G5 t5,keyCols=[1],outCols=(1,2)) 2316 │ └── cost: 100.15 2317 ├── G3: (projections) 2318 ├── G4: (scan t5,cols=(1,2)) 2319 │ └── [] 2320 │ ├── best: (scan t5,cols=(1,2)) 2321 │ └── cost: 1050.02 2322 ├── G5: (filters G8) 2323 ├── G6: (zigzag-join G9 t5@b_idx t5@b_idx) 2324 │ └── [] 2325 │ ├── best: (zigzag-join G9 t5@b_idx t5@b_idx) 2326 │ └── cost: 25.57 2327 ├── G7: (index-join G10 t5,cols=(1,2)) 2328 │ └── [] 2329 │ ├── best: (index-join G10 t5,cols=(1,2)) 2330 │ └── cost: 565.58 2331 ├── G8: (contains G11 G12) 2332 ├── G9: (filters) 2333 ├── G10: (scan t5@b_idx,cols=(1),constrained) 2334 │ └── [] 2335 │ ├── best: (scan t5@b_idx,cols=(1),constrained) 2336 │ └── cost: 114.45 2337 ├── G11: (variable b) 2338 └── G12: (const '{"a": 1, "c": 2}') 2339 2340 # Three or more paths. Should generate zigzag joins. 2341 opt 2342 SELECT b,a FROM t5 WHERE b @> '{"a":[{"b":"c", "d":3}, 5]}' 2343 ---- 2344 inner-join (lookup t5) 2345 ├── columns: b:2 a:1!null 2346 ├── key columns: [1] = [1] 2347 ├── lookup columns are key 2348 ├── key: (1) 2349 ├── fd: (1)-->(2) 2350 ├── inner-join (zigzag t5@b_idx t5@b_idx) 2351 │ ├── columns: a:1!null 2352 │ ├── eq columns: [1] = [1] 2353 │ ├── left fixed columns: [2] = ['{"a": [{"b": "c"}]}'] 2354 │ ├── right fixed columns: [2] = ['{"a": [{"d": 3}]}'] 2355 │ └── filters (true) 2356 └── filters 2357 └── b:2 @> '{"a": [{"b": "c", "d": 3}, 5]}' [outer=(2)] 2358 2359 # Regression test for issue where zero-column expressions could exist multiple 2360 # times in the tree, causing collisions. 2361 opt 2362 SELECT 1 FROM (VALUES (1), (1)) JOIN (VALUES (1), (1), (1)) ON true 2363 UNION ALL 2364 SELECT 1 FROM (VALUES (1), (1), (1)) JOIN (VALUES (1), (1)) ON true 2365 ---- 2366 union-all 2367 ├── columns: "?column?":7!null 2368 ├── left columns: "?column?":3 2369 ├── right columns: "?column?":6 2370 ├── cardinality: [12 - 12] 2371 ├── project 2372 │ ├── columns: "?column?":3!null 2373 │ ├── cardinality: [6 - 6] 2374 │ ├── fd: ()-->(3) 2375 │ ├── inner-join (cross) 2376 │ │ ├── cardinality: [6 - 6] 2377 │ │ ├── values 2378 │ │ │ ├── cardinality: [3 - 3] 2379 │ │ │ ├── () 2380 │ │ │ ├── () 2381 │ │ │ └── () 2382 │ │ ├── values 2383 │ │ │ ├── cardinality: [2 - 2] 2384 │ │ │ ├── () 2385 │ │ │ └── () 2386 │ │ └── filters (true) 2387 │ └── projections 2388 │ └── 1 [as="?column?":3] 2389 └── project 2390 ├── columns: "?column?":6!null 2391 ├── cardinality: [6 - 6] 2392 ├── fd: ()-->(6) 2393 ├── inner-join (cross) 2394 │ ├── cardinality: [6 - 6] 2395 │ ├── values 2396 │ │ ├── cardinality: [3 - 3] 2397 │ │ ├── () 2398 │ │ ├── () 2399 │ │ └── () 2400 │ ├── values 2401 │ │ ├── cardinality: [2 - 2] 2402 │ │ ├── () 2403 │ │ └── () 2404 │ └── filters (true) 2405 └── projections 2406 └── 1 [as="?column?":6] 2407 2408 memo 2409 SELECT 1 FROM (VALUES (1), (1)) JOIN (VALUES (1), (1), (1)) ON true 2410 UNION ALL 2411 SELECT 1 FROM (VALUES (1), (1), (1)) JOIN (VALUES (1), (1)) ON true 2412 ---- 2413 memo (optimized, ~19KB, required=[presentation: ?column?:7]) 2414 ├── G1: (union-all G2 G3) 2415 │ └── [presentation: ?column?:7] 2416 │ ├── best: (union-all G2 G3) 2417 │ └── cost: 0.82 2418 ├── G2: (project G4 G5) 2419 │ └── [] 2420 │ ├── best: (project G4 G5) 2421 │ └── cost: 0.34 2422 ├── G3: (project G6 G5) 2423 │ └── [] 2424 │ ├── best: (project G6 G5) 2425 │ └── cost: 0.34 2426 ├── G4: (inner-join G7 G8 G9) (inner-join G8 G7 G9) 2427 │ └── [] 2428 │ ├── best: (inner-join G8 G7 G9) 2429 │ └── cost: 0.21 2430 ├── G5: (projections G10) 2431 ├── G6: (inner-join G11 G12 G9) (inner-join G12 G11 G9) 2432 │ └── [] 2433 │ ├── best: (inner-join G11 G12 G9) 2434 │ └── cost: 0.21 2435 ├── G7: (values G13 id=v1) 2436 │ └── [] 2437 │ ├── best: (values G13 id=v1) 2438 │ └── cost: 0.03 2439 ├── G8: (values G14 id=v2) 2440 │ └── [] 2441 │ ├── best: (values G14 id=v2) 2442 │ └── cost: 0.04 2443 ├── G9: (filters) 2444 ├── G10: (const 1) 2445 ├── G11: (values G14 id=v3) 2446 │ └── [] 2447 │ ├── best: (values G14 id=v3) 2448 │ └── cost: 0.04 2449 ├── G12: (values G13 id=v4) 2450 │ └── [] 2451 │ ├── best: (values G13 id=v4) 2452 │ └── cost: 0.03 2453 ├── G13: (scalar-list G15 G15) 2454 ├── G14: (scalar-list G15 G15 G15) 2455 ├── G15: (tuple G16) 2456 └── G16: (scalar-list) 2457 2458 opt join-limit=3 2459 SELECT 2460 false 2461 FROM 2462 abc AS x JOIN [INSERT INTO abc (a) SELECT 1 FROM abc RETURNING 1] JOIN abc AS y ON true ON false 2463 ---- 2464 with &1 2465 ├── columns: bool:22!null 2466 ├── cardinality: [0 - 0] 2467 ├── volatile, side-effects, mutations 2468 ├── key: () 2469 ├── fd: ()-->(22) 2470 ├── project 2471 │ ├── columns: "?column?":16!null 2472 │ ├── volatile, side-effects, mutations 2473 │ ├── fd: ()-->(16) 2474 │ ├── insert abc 2475 │ │ ├── columns: abc.rowid:8!null 2476 │ │ ├── insert-mapping: 2477 │ │ │ ├── "?column?":13 => abc.a:5 2478 │ │ │ ├── column14:14 => abc.b:6 2479 │ │ │ ├── column14:14 => abc.c:7 2480 │ │ │ └── column15:15 => abc.rowid:8 2481 │ │ ├── volatile, side-effects, mutations 2482 │ │ └── project 2483 │ │ ├── columns: column14:14 column15:15 "?column?":13!null 2484 │ │ ├── volatile, side-effects 2485 │ │ ├── fd: ()-->(13,14) 2486 │ │ ├── scan abc 2487 │ │ └── projections 2488 │ │ ├── CAST(NULL AS INT8) [as=column14:14] 2489 │ │ ├── unique_rowid() [as=column15:15, volatile, side-effects] 2490 │ │ └── 1 [as="?column?":13] 2491 │ └── projections 2492 │ └── 1 [as="?column?":16] 2493 └── values 2494 ├── columns: bool:22!null 2495 ├── cardinality: [0 - 0] 2496 ├── key: () 2497 └── fd: ()-->(22) 2498 2499 opt join-limit=3 2500 SELECT 1 FROM ((VALUES (1), (1)) JOIN ((VALUES (1), (1), (1)) JOIN (VALUES (1), (1), (1), (1)) ON true) ON true) 2501 UNION ALL 2502 SELECT 1 FROM ((VALUES (1), (1)) JOIN (VALUES (1), (1), (1)) ON true) JOIN (VALUES (1), (1), (1), (1)) ON true 2503 ---- 2504 union-all 2505 ├── columns: "?column?":9!null 2506 ├── left columns: "?column?":4 2507 ├── right columns: "?column?":8 2508 ├── cardinality: [48 - 48] 2509 ├── project 2510 │ ├── columns: "?column?":4!null 2511 │ ├── cardinality: [24 - 24] 2512 │ ├── fd: ()-->(4) 2513 │ ├── inner-join (cross) 2514 │ │ ├── cardinality: [24 - 24] 2515 │ │ ├── inner-join (cross) 2516 │ │ │ ├── cardinality: [6 - 6] 2517 │ │ │ ├── values 2518 │ │ │ │ ├── cardinality: [3 - 3] 2519 │ │ │ │ ├── () 2520 │ │ │ │ ├── () 2521 │ │ │ │ └── () 2522 │ │ │ ├── values 2523 │ │ │ │ ├── cardinality: [2 - 2] 2524 │ │ │ │ ├── () 2525 │ │ │ │ └── () 2526 │ │ │ └── filters (true) 2527 │ │ ├── values 2528 │ │ │ ├── cardinality: [4 - 4] 2529 │ │ │ ├── () 2530 │ │ │ ├── () 2531 │ │ │ ├── () 2532 │ │ │ └── () 2533 │ │ └── filters (true) 2534 │ └── projections 2535 │ └── 1 [as="?column?":4] 2536 └── project 2537 ├── columns: "?column?":8!null 2538 ├── cardinality: [24 - 24] 2539 ├── fd: ()-->(8) 2540 ├── inner-join (cross) 2541 │ ├── cardinality: [24 - 24] 2542 │ ├── inner-join (cross) 2543 │ │ ├── cardinality: [6 - 6] 2544 │ │ ├── values 2545 │ │ │ ├── cardinality: [3 - 3] 2546 │ │ │ ├── () 2547 │ │ │ ├── () 2548 │ │ │ └── () 2549 │ │ ├── values 2550 │ │ │ ├── cardinality: [2 - 2] 2551 │ │ │ ├── () 2552 │ │ │ └── () 2553 │ │ └── filters (true) 2554 │ ├── values 2555 │ │ ├── cardinality: [4 - 4] 2556 │ │ ├── () 2557 │ │ ├── () 2558 │ │ ├── () 2559 │ │ └── () 2560 │ └── filters (true) 2561 └── projections 2562 └── 1 [as="?column?":8] 2563 2564 opt 2565 SELECT 1 FROM (VALUES (1), (1)) LEFT JOIN (VALUES (1), (1), (1)) ON random() = 0 2566 UNION ALL 2567 SELECT 1 FROM (VALUES (1), (1), (1)) RIGHT JOIN (VALUES (1), (1)) ON random() = 0 2568 ---- 2569 union-all 2570 ├── columns: "?column?":7!null 2571 ├── left columns: "?column?":3 2572 ├── right columns: "?column?":6 2573 ├── cardinality: [4 - 12] 2574 ├── volatile, side-effects 2575 ├── project 2576 │ ├── columns: "?column?":3!null 2577 │ ├── cardinality: [2 - 6] 2578 │ ├── volatile, side-effects 2579 │ ├── fd: ()-->(3) 2580 │ ├── left-join (cross) 2581 │ │ ├── cardinality: [2 - 6] 2582 │ │ ├── volatile, side-effects 2583 │ │ ├── values 2584 │ │ │ ├── cardinality: [2 - 2] 2585 │ │ │ ├── () 2586 │ │ │ └── () 2587 │ │ ├── select 2588 │ │ │ ├── cardinality: [0 - 3] 2589 │ │ │ ├── volatile, side-effects 2590 │ │ │ ├── values 2591 │ │ │ │ ├── cardinality: [3 - 3] 2592 │ │ │ │ ├── () 2593 │ │ │ │ ├── () 2594 │ │ │ │ └── () 2595 │ │ │ └── filters 2596 │ │ │ └── random() = 0.0 [volatile, side-effects] 2597 │ │ └── filters (true) 2598 │ └── projections 2599 │ └── 1 [as="?column?":3] 2600 └── project 2601 ├── columns: "?column?":6!null 2602 ├── cardinality: [2 - 6] 2603 ├── volatile, side-effects 2604 ├── fd: ()-->(6) 2605 ├── left-join (cross) 2606 │ ├── cardinality: [2 - 6] 2607 │ ├── volatile, side-effects 2608 │ ├── values 2609 │ │ ├── cardinality: [2 - 2] 2610 │ │ ├── () 2611 │ │ └── () 2612 │ ├── select 2613 │ │ ├── cardinality: [0 - 3] 2614 │ │ ├── volatile, side-effects 2615 │ │ ├── values 2616 │ │ │ ├── cardinality: [3 - 3] 2617 │ │ │ ├── () 2618 │ │ │ ├── () 2619 │ │ │ └── () 2620 │ │ └── filters 2621 │ │ └── random() = 0.0 [volatile, side-effects] 2622 │ └── filters (true) 2623 └── projections 2624 └── 1 [as="?column?":6] 2625 2626 exec-ddl 2627 CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY (d, e)); 2628 ---- 2629 2630 exec-ddl 2631 ALTER TABLE abc INJECT STATISTICS '[ 2632 { 2633 "columns": ["a"], 2634 "created_at": "2018-01-01 1:00:00.00000+00:00", 2635 "row_count": 100, 2636 "distinct_count": 100 2637 }, 2638 { 2639 "columns": ["a"], 2640 "created_at": "2018-01-01 1:00:00.00000+00:00", 2641 "row_count": 100, 2642 "distinct_count": 100 2643 } 2644 ]' 2645 ---- 2646 2647 exec-ddl 2648 ALTER TABLE def INJECT STATISTICS '[ 2649 { 2650 "columns": ["d"], 2651 "created_at": "2018-01-01 1:00:00.00000+00:00", 2652 "row_count": 10000, 2653 "distinct_count": 10000 2654 }, 2655 { 2656 "columns": ["e"], 2657 "created_at": "2018-01-01 1:00:00.00000+00:00", 2658 "row_count": 10000, 2659 "distinct_count": 10000 2660 } 2661 ]' 2662 ---- 2663 2664 # Test the CommuteSemiJoinRule creates an appropriate inner join. 2665 opt 2666 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f) 2667 ---- 2668 semi-join (hash) 2669 ├── columns: a:1 b:2 c:3 2670 ├── scan abc 2671 │ └── columns: a:1 b:2 c:3 2672 ├── scan def 2673 │ └── columns: f:7 2674 └── filters 2675 └── a:1 = f:7 [outer=(1,7), constraints=(/1: (/NULL - ]; /7: (/NULL - ]), fd=(1)==(7), (7)==(1)] 2676 2677 # Test that we don't commute a SemiJoin when the On conditions are not 2678 # equalities. For example, in this test we have a Lt condition. 2679 opt expect-not=CommuteSemiJoin 2680 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a < e) 2681 ---- 2682 semi-join (cross) 2683 ├── columns: a:1 b:2 c:3 2684 ├── scan abc 2685 │ └── columns: a:1 b:2 c:3 2686 ├── scan def 2687 │ └── columns: e:6!null 2688 └── filters 2689 └── a:1 < e:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ])] 2690 2691 # Test that we don't commute a SemiJoin when the On conditions are not 2692 # equalities. For example, in this test we have an Or condition. 2693 opt expect-not=CommuteSemiJoin 2694 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=d OR c=e) 2695 ---- 2696 semi-join (cross) 2697 ├── columns: a:1 b:2 c:3 2698 ├── scan abc 2699 │ └── columns: a:1 b:2 c:3 2700 ├── scan def 2701 │ ├── columns: d:5!null e:6!null 2702 │ └── key: (5,6) 2703 └── filters 2704 └── (a:1 = d:5) OR (c:3 = e:6) [outer=(1,3,5,6)] 2705 2706 opt disable=CommuteSemiJoin format=show-all 2707 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=d AND c=e) 2708 ---- 2709 semi-join (lookup def) 2710 ├── columns: a:1(int) b:2(int) c:3(int) 2711 ├── key columns: [1 3] = [5 6] 2712 ├── lookup columns are key 2713 ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(3)=10, null(3)=0] 2714 ├── cost: 507.050295 2715 ├── prune: (2) 2716 ├── interesting orderings: (+1,+2) (+2,+3) 2717 ├── scan t.public.abc 2718 │ ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) 2719 │ ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(3)=10, null(3)=1] 2720 │ ├── cost: 107.02 2721 │ ├── prune: (1-3) 2722 │ └── interesting orderings: (+1,+2) (+2,+3) 2723 └── filters (true) 2724 2725 # TODO(rytaft): See stats/join tests. Since we don't collect the stats properly 2726 # for SemiJoins, we prefer the InnerJoin plan over the SemiJoin one more times 2727 # than necessary. 2728 opt format=show-all 2729 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=d AND c=e) 2730 ---- 2731 semi-join (lookup def) 2732 ├── columns: a:1(int) b:2(int) c:3(int) 2733 ├── key columns: [1 3] = [5 6] 2734 ├── lookup columns are key 2735 ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(3)=10, null(3)=0] 2736 ├── cost: 507.050295 2737 ├── prune: (2) 2738 ├── interesting orderings: (+1,+2) (+2,+3) 2739 ├── scan t.public.abc 2740 │ ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) 2741 │ ├── stats: [rows=100, distinct(1)=100, null(1)=0, distinct(3)=10, null(3)=1] 2742 │ ├── cost: 107.02 2743 │ ├── prune: (1-3) 2744 │ └── interesting orderings: (+1,+2) (+2,+3) 2745 └── filters (true) 2746 2747 exec-ddl 2748 CREATE TABLE customers (id INT PRIMARY KEY, name STRING) 2749 ---- 2750 2751 exec-ddl 2752 CREATE TABLE orders (id INT PRIMARY KEY, cust_id INT REFERENCES customers (id), order_date DATE, INDEX (order_date) STORING (cust_id)) 2753 ---- 2754 2755 exec-ddl 2756 ALTER TABLE customers INJECT STATISTICS '[ 2757 { 2758 "columns": ["id"], 2759 "created_at": "2018-01-01 1:00:00.00000+00:00", 2760 "row_count": 100000, 2761 "distinct_count": 100000 2762 } 2763 ]' 2764 ---- 2765 2766 exec-ddl 2767 ALTER TABLE orders INJECT STATISTICS '[ 2768 { 2769 "columns": ["id"], 2770 "created_at": "2018-01-01 1:00:00.00000+00:00", 2771 "row_count": 1000000, 2772 "distinct_count": 1000000 2773 }, 2774 { 2775 "columns": ["cust_id"], 2776 "created_at": "2018-01-01 1:00:00.00000+00:00", 2777 "row_count": 1000000, 2778 "distinct_count": 10000000 2779 }, 2780 { 2781 "columns": ["order_date"], 2782 "created_at": "2018-01-01 1:00:00.00000+00:00", 2783 "row_count": 1000000, 2784 "distinct_count": 1000000 2785 } 2786 ]' 2787 ---- 2788 2789 opt disable=CommuteSemiJoin 2790 SELECT * 2791 FROM customers c 2792 WHERE EXISTS(SELECT * FROM orders o WHERE o.cust_id=c.id AND o.order_date='2019-01-01') 2793 ---- 2794 semi-join (merge) 2795 ├── columns: id:1!null name:2 2796 ├── left ordering: +1 2797 ├── right ordering: +4 2798 ├── key: (1) 2799 ├── fd: (1)-->(2) 2800 ├── scan c 2801 │ ├── columns: c.id:1!null name:2 2802 │ ├── key: (1) 2803 │ ├── fd: (1)-->(2) 2804 │ └── ordering: +1 2805 ├── sort 2806 │ ├── columns: cust_id:4 order_date:5!null 2807 │ ├── fd: ()-->(5) 2808 │ ├── ordering: +4 opt(5) [actual: +4] 2809 │ └── scan o@secondary 2810 │ ├── columns: cust_id:4 order_date:5!null 2811 │ ├── constraint: /5/3: [/'2019-01-01' - /'2019-01-01'] 2812 │ └── fd: ()-->(5) 2813 └── filters (true) 2814 2815 # The CommuteSemiJoin rule allows a much better plan because we can use 2816 # a lookup join. 2817 opt 2818 SELECT * 2819 FROM customers c 2820 WHERE EXISTS(SELECT * FROM orders o WHERE o.cust_id=c.id AND o.order_date='2019-01-01') 2821 ---- 2822 project 2823 ├── columns: id:1!null name:2 2824 ├── key: (1) 2825 ├── fd: (1)-->(2) 2826 └── inner-join (lookup customers) 2827 ├── columns: c.id:1!null name:2 cust_id:4!null 2828 ├── key columns: [4] = [1] 2829 ├── lookup columns are key 2830 ├── key: (4) 2831 ├── fd: (1)-->(2), (1)==(4), (4)==(1) 2832 ├── distinct-on 2833 │ ├── columns: cust_id:4 2834 │ ├── grouping columns: cust_id:4 2835 │ ├── key: (4) 2836 │ └── scan o@secondary 2837 │ ├── columns: cust_id:4 order_date:5!null 2838 │ ├── constraint: /5/3: [/'2019-01-01' - /'2019-01-01'] 2839 │ └── fd: ()-->(5) 2840 └── filters (true) 2841 2842 # GenerateInvertedIndexZigzagJoins is disabled in the presence of a row-level 2843 # locking clause. 2844 opt 2845 SELECT b,a FROM t5 WHERE b @> '{"a":1, "c":2}' FOR UPDATE 2846 ---- 2847 select 2848 ├── columns: b:2 a:1!null 2849 ├── volatile, side-effects 2850 ├── key: (1) 2851 ├── fd: (1)-->(2) 2852 ├── index-join t5 2853 │ ├── columns: a:1!null b:2 2854 │ ├── volatile, side-effects 2855 │ ├── key: (1) 2856 │ ├── fd: (1)-->(2) 2857 │ └── scan t5@b_idx 2858 │ ├── columns: a:1!null 2859 │ ├── constraint: /2/1: [/'{"a": 1}' - /'{"a": 1}'] 2860 │ ├── locking: for-update 2861 │ ├── volatile, side-effects 2862 │ └── key: (1) 2863 └── filters 2864 └── b:2 @> '{"a": 1, "c": 2}' [outer=(2)] 2865 2866 # -------------------------------------------------- 2867 # AssociateJoin 2868 # -------------------------------------------------- 2869 2870 exec-ddl 2871 ALTER TABLE abc INJECT STATISTICS '[ 2872 { 2873 "columns": ["a"], 2874 "created_at": "2018-05-01 1:00:00.00000+00:00", 2875 "row_count": 1000, 2876 "distinct_count": 1000 2877 } 2878 ]' 2879 ---- 2880 2881 exec-ddl 2882 ALTER TABLE stu INJECT STATISTICS '[ 2883 { 2884 "columns": ["s"], 2885 "created_at": "2018-05-01 1:00:00.00000+00:00", 2886 "row_count": 10000, 2887 "distinct_count": 1000 2888 } 2889 ]' 2890 ---- 2891 2892 exec-ddl 2893 ALTER TABLE xyz INJECT STATISTICS '[ 2894 { 2895 "columns": ["x"], 2896 "created_at": "2018-05-01 1:00:00.00000+00:00", 2897 "row_count": 1000, 2898 "distinct_count": 1000 2899 } 2900 ]' 2901 ---- 2902 2903 # Check that the equality condition abc.a = xyz.x is synthesized. 2904 opt expect=AssociateJoin 2905 SELECT * FROM abc, stu, xyz WHERE abc.a=stu.s AND stu.s=xyz.x 2906 ---- 2907 inner-join (merge) 2908 ├── columns: a:1!null b:2 c:3 s:5!null t:6!null u:7!null x:8!null y:9 z:10 2909 ├── left ordering: +5 2910 ├── right ordering: +1 2911 ├── fd: (5)==(1,8), (8)==(1,5), (1)==(5,8) 2912 ├── scan stu 2913 │ ├── columns: s:5!null t:6!null u:7!null 2914 │ ├── key: (5-7) 2915 │ └── ordering: +5 2916 ├── inner-join (merge) 2917 │ ├── columns: a:1!null b:2 c:3 x:8!null y:9 z:10 2918 │ ├── left ordering: +8 2919 │ ├── right ordering: +1 2920 │ ├── fd: (1)==(8), (8)==(1) 2921 │ ├── ordering: +(1|8) [actual: +8] 2922 │ ├── scan xyz@xy 2923 │ │ ├── columns: x:8 y:9 z:10 2924 │ │ └── ordering: +8 2925 │ ├── scan abc@ab 2926 │ │ ├── columns: a:1 b:2 c:3 2927 │ │ └── ordering: +1 2928 │ └── filters (true) 2929 └── filters (true) 2930 2931 memo expect=AssociateJoin 2932 SELECT * FROM abc, stu, xyz WHERE abc.a=stu.s AND stu.s=xyz.x 2933 ---- 2934 memo (optimized, ~38KB, required=[presentation: a:1,b:2,c:3,s:5,t:6,u:7,x:8,y:9,z:10]) 2935 ├── G1: (inner-join G2 G3 G4) (inner-join G3 G2 G4) (merge-join G2 G3 G5 inner-join,+1,+5) (merge-join G3 G2 G5 inner-join,+5,+1) (lookup-join G3 G5 abc@ab,keyCols=[5],outCols=(1-3,5-10)) (inner-join G6 G7 G8) (inner-join G9 G10 G11) (inner-join G7 G6 G8) (merge-join G6 G7 G5 inner-join,+5,+1) (inner-join G10 G9 G11) (merge-join G9 G10 G5 inner-join,+8,+5) (merge-join G7 G6 G5 inner-join,+1,+5) (lookup-join G7 G5 stu,keyCols=[1],outCols=(1-3,5-10)) (inner-join G9 G12 G13) (merge-join G10 G9 G5 inner-join,+5,+8) (lookup-join G10 G5 xyz@xy,keyCols=[5],outCols=(1-3,5-10)) (inner-join G12 G9 G13) (merge-join G9 G12 G5 inner-join,+8,+1) (merge-join G12 G9 G5 inner-join,+1,+8) (lookup-join G12 G5 xyz@xy,keyCols=[1],outCols=(1-3,5-10)) 2936 │ └── [presentation: a:1,b:2,c:3,s:5,t:6,u:7,x:8,y:9,z:10] 2937 │ ├── best: (merge-join G6="[ordering: +5]" G7="[ordering: +(1|8)]" G5 inner-join,+5,+1) 2938 │ └── cost: 12980.08 2939 ├── G2: (scan abc,cols=(1-3)) (scan abc@ab,cols=(1-3)) (scan abc@bc,cols=(1-3)) 2940 │ ├── [ordering: +1] 2941 │ │ ├── best: (scan abc@ab,cols=(1-3)) 2942 │ │ └── cost: 1070.02 2943 │ └── [] 2944 │ ├── best: (scan abc,cols=(1-3)) 2945 │ └── cost: 1070.02 2946 ├── G3: (inner-join G6 G9 G11) (inner-join G9 G6 G11) (merge-join G6 G9 G5 inner-join,+5,+8) (lookup-join G6 G5 xyz@xy,keyCols=[5],outCols=(5-10)) (merge-join G9 G6 G5 inner-join,+8,+5) (lookup-join G9 G5 stu,keyCols=[8],outCols=(5-10)) 2947 │ ├── [ordering: +(5|8)] 2948 │ │ ├── best: (merge-join G6="[ordering: +5]" G9="[ordering: +8]" G5 inner-join,+5,+8) 2949 │ │ └── cost: 11880.05 2950 │ └── [] 2951 │ ├── best: (merge-join G6="[ordering: +5]" G9="[ordering: +8]" G5 inner-join,+5,+8) 2952 │ └── cost: 11880.05 2953 ├── G4: (filters G14) 2954 ├── G5: (filters) 2955 ├── G6: (scan stu) (scan stu@uts) 2956 │ ├── [ordering: +5] 2957 │ │ ├── best: (scan stu) 2958 │ │ └── cost: 10600.02 2959 │ └── [] 2960 │ ├── best: (scan stu) 2961 │ └── cost: 10600.02 2962 ├── G7: (inner-join G9 G2 G13) (inner-join G2 G9 G13) (merge-join G9 G2 G5 inner-join,+8,+1) (lookup-join G9 G5 abc@ab,keyCols=[8],outCols=(1-3,8-10)) (merge-join G2 G9 G5 inner-join,+1,+8) (lookup-join G2 G5 xyz@xy,keyCols=[1],outCols=(1-3,8-10)) 2963 │ ├── [ordering: +(1|8)] 2964 │ │ ├── best: (merge-join G9="[ordering: +8]" G2="[ordering: +1]" G5 inner-join,+8,+1) 2965 │ │ └── cost: 2170.05 2966 │ └── [] 2967 │ ├── best: (merge-join G9="[ordering: +8]" G2="[ordering: +1]" G5 inner-join,+8,+1) 2968 │ └── cost: 2170.05 2969 ├── G8: (filters G15) 2970 ├── G9: (scan xyz,cols=(8-10)) (scan xyz@xy,cols=(8-10)) (scan xyz@yz,cols=(8-10)) 2971 │ ├── [ordering: +8] 2972 │ │ ├── best: (scan xyz@xy,cols=(8-10)) 2973 │ │ └── cost: 1070.02 2974 │ └── [] 2975 │ ├── best: (scan xyz,cols=(8-10)) 2976 │ └── cost: 1070.02 2977 ├── G10: (inner-join G6 G2 G4) (inner-join G2 G6 G4) (merge-join G6 G2 G5 inner-join,+5,+1) (lookup-join G6 G5 abc@ab,keyCols=[5],outCols=(1-3,5-7)) (merge-join G2 G6 G5 inner-join,+1,+5) (lookup-join G2 G5 stu,keyCols=[1],outCols=(1-3,5-7)) 2978 │ ├── [ordering: +(1|5)] 2979 │ │ ├── best: (merge-join G6="[ordering: +5]" G2="[ordering: +1]" G5 inner-join,+5,+1) 2980 │ │ └── cost: 11880.05 2981 │ └── [] 2982 │ ├── best: (merge-join G6="[ordering: +5]" G2="[ordering: +1]" G5 inner-join,+5,+1) 2983 │ └── cost: 11880.05 2984 ├── G11: (filters G16) 2985 ├── G12: (inner-join G2 G6 G8) (inner-join G6 G2 G8) 2986 │ ├── [ordering: +(1|5)] 2987 │ │ ├── best: (sort G12) 2988 │ │ └── cost: 14770.10 2989 │ └── [] 2990 │ ├── best: (inner-join G6 G2 G8) 2991 │ └── cost: 11912.55 2992 ├── G13: (filters G17) 2993 ├── G14: (eq G18 G19) 2994 ├── G15: (eq G19 G18) 2995 ├── G16: (eq G19 G20) 2996 ├── G17: (eq G18 G20) 2997 ├── G18: (variable a) 2998 ├── G19: (variable s) 2999 └── G20: (variable x) 3000 3001 # Regression test for #36226. 3002 exec-ddl 3003 CREATE TABLE parent1 (pid1 INT PRIMARY KEY, pa1 INT) 3004 ---- 3005 3006 exec-ddl 3007 CREATE TABLE child1 ( 3008 pid1 INT, 3009 cid1 INT, 3010 ca1 INT, 3011 PRIMARY KEY(pid1, cid1) 3012 ) 3013 INTERLEAVE IN PARENT parent1 (pid1) 3014 ---- 3015 3016 exec-ddl 3017 CREATE TABLE grandchild1 ( 3018 pid1 INT, 3019 cid1 INT, 3020 gcid1 INT, 3021 gca1 INT, 3022 PRIMARY KEY(pid1, cid1, gcid1) 3023 ) 3024 INTERLEAVE IN PARENT child1 (pid1, cid1) 3025 ---- 3026 3027 opt expect=AssociateJoin join-limit=4 3028 SELECT * FROM grandchild1 3029 JOIN child1 USING (pid1, cid1) 3030 JOIN parent1 USING (pid1) 3031 ORDER BY pid1 3032 ---- 3033 project 3034 ├── columns: pid1:1!null cid1:2!null gcid1:3!null gca1:4 ca1:7 pa1:9 3035 ├── key: (1-3) 3036 ├── fd: (1-3)-->(4), (1,2)-->(7), (1)-->(9) 3037 ├── ordering: +1 3038 └── inner-join (lookup parent1) 3039 ├── columns: grandchild1.pid1:1!null grandchild1.cid1:2!null gcid1:3!null gca1:4 child1.pid1:5!null child1.cid1:6!null ca1:7 parent1.pid1:8!null pa1:9 3040 ├── key columns: [1] = [8] 3041 ├── lookup columns are key 3042 ├── key: (3,6,8) 3043 ├── fd: (1-3)-->(4), (5,6)-->(7), (1)==(5,8), (5)==(1,8), (2)==(6), (6)==(2), (8)-->(9), (8)==(1,5) 3044 ├── ordering: +(1|5|8) [actual: +1] 3045 ├── inner-join (merge) 3046 │ ├── columns: grandchild1.pid1:1!null grandchild1.cid1:2!null gcid1:3!null gca1:4 child1.pid1:5!null child1.cid1:6!null ca1:7 3047 │ ├── left ordering: +1,+2 3048 │ ├── right ordering: +5,+6 3049 │ ├── key: (3,5,6) 3050 │ ├── fd: (1-3)-->(4), (5,6)-->(7), (1)==(5), (5)==(1), (2)==(6), (6)==(2) 3051 │ ├── ordering: +(1|5) [actual: +1] 3052 │ ├── scan grandchild1 3053 │ │ ├── columns: grandchild1.pid1:1!null grandchild1.cid1:2!null gcid1:3!null gca1:4 3054 │ │ ├── key: (1-3) 3055 │ │ ├── fd: (1-3)-->(4) 3056 │ │ └── ordering: +1,+2 3057 │ ├── scan child1 3058 │ │ ├── columns: child1.pid1:5!null child1.cid1:6!null ca1:7 3059 │ │ ├── key: (5,6) 3060 │ │ ├── fd: (5,6)-->(7) 3061 │ │ └── ordering: +5,+6 3062 │ └── filters (true) 3063 └── filters (true) 3064 3065 memo expect=AssociateJoin join-limit=4 3066 SELECT * FROM grandchild1 3067 JOIN child1 USING (pid1, cid1) 3068 JOIN parent1 USING (pid1) 3069 ORDER BY pid1 3070 ---- 3071 memo (optimized, ~33KB, required=[presentation: pid1:1,cid1:2,gcid1:3,gca1:4,ca1:7,pa1:9] [ordering: +1]) 3072 ├── G1: (project G2 G3 pid1 cid1 gcid1 gca1 ca1 pa1) 3073 │ ├── [presentation: pid1:1,cid1:2,gcid1:3,gca1:4,ca1:7,pa1:9] [ordering: +1] 3074 │ │ ├── best: (project G2="[ordering: +(1|5|8)]" G3 pid1 cid1 gcid1 gca1 ca1 pa1) 3075 │ │ └── cost: 2766.07 3076 │ └── [] 3077 │ ├── best: (project G2 G3 pid1 cid1 gcid1 gca1 ca1 pa1) 3078 │ └── cost: 2766.07 3079 ├── G2: (inner-join G4 G5 G6) (inner-join G5 G4 G6) (merge-join G4 G5 G7 inner-join,+1,+8) (lookup-join G4 G7 parent1,keyCols=[1],outCols=(1-9)) (inner-join G8 G9 G10) (inner-join G11 G12 G10) (merge-join G5 G4 G7 inner-join,+8,+1) (inner-join G9 G8 G10) (merge-join G8 G9 G7 inner-join,+1,+2,+5,+6) (inner-join G12 G11 G10) (merge-join G11 G12 G7 inner-join,+5,+6,+1,+2) (merge-join G9 G8 G7 inner-join,+5,+6,+1,+2) (lookup-join G9 G7 grandchild1,keyCols=[5 6],outCols=(1-9)) (inner-join G11 G12 G13) (inner-join G5 G4 G14) (merge-join G12 G11 G7 inner-join,+1,+2,+5,+6) (lookup-join G12 G7 child1,keyCols=[1 2],outCols=(1-9)) (inner-join G12 G11 G13) (inner-join G4 G5 G14) (merge-join G5 G4 G7 inner-join,+8,+5) (inner-join G5 G15 G6) (merge-join G4 G5 G7 inner-join,+5,+8) (lookup-join G4 G7 parent1,keyCols=[5],outCols=(1-9)) (inner-join G15 G5 G6) (merge-join G5 G15 G7 inner-join,+8,+1) (merge-join G15 G5 G7 inner-join,+1,+8) (lookup-join G15 G7 parent1,keyCols=[1],outCols=(1-9)) 3080 │ ├── [ordering: +(1|5|8)] 3081 │ │ ├── best: (lookup-join G4="[ordering: +(1|5)]" G7 parent1,keyCols=[1],outCols=(1-9)) 3082 │ │ └── cost: 2765.06 3083 │ └── [] 3084 │ ├── best: (lookup-join G4 G7 parent1,keyCols=[1],outCols=(1-9)) 3085 │ └── cost: 2765.06 3086 ├── G3: (projections) 3087 ├── G4: (inner-join G8 G11 G10) (inner-join G11 G8 G10) (merge-join G8 G11 G7 inner-join,+1,+2,+5,+6) (lookup-join G8 G7 child1,keyCols=[1 2],outCols=(1-7)) (merge-join G11 G8 G7 inner-join,+5,+6,+1,+2) (lookup-join G11 G7 grandchild1,keyCols=[5 6],outCols=(1-7)) 3088 │ ├── [ordering: +(1|5)] 3089 │ │ ├── best: (merge-join G8="[ordering: +1,+2]" G11="[ordering: +5,+6]" G7 inner-join,+1,+2,+5,+6) 3090 │ │ └── cost: 2161.05 3091 │ └── [] 3092 │ ├── best: (merge-join G8="[ordering: +1,+2]" G11="[ordering: +5,+6]" G7 inner-join,+1,+2,+5,+6) 3093 │ └── cost: 2161.05 3094 ├── G5: (scan parent1) 3095 │ ├── [ordering: +8] 3096 │ │ ├── best: (scan parent1) 3097 │ │ └── cost: 1040.02 3098 │ └── [] 3099 │ ├── best: (scan parent1) 3100 │ └── cost: 1040.02 3101 ├── G6: (filters G16) 3102 ├── G7: (filters) 3103 ├── G8: (scan grandchild1) 3104 │ ├── [ordering: +1,+2] 3105 │ │ ├── best: (scan grandchild1) 3106 │ │ └── cost: 1080.02 3107 │ ├── [ordering: +1] 3108 │ │ ├── best: (scan grandchild1) 3109 │ │ └── cost: 1080.02 3110 │ └── [] 3111 │ ├── best: (scan grandchild1) 3112 │ └── cost: 1080.02 3113 ├── G9: (inner-join G11 G5 G14) (inner-join G5 G11 G14) (merge-join G11 G5 G7 inner-join,+5,+8) (lookup-join G11 G7 parent1,keyCols=[5],outCols=(5-9)) (merge-join G5 G11 G7 inner-join,+8,+5) (lookup-join G5 G7 child1,keyCols=[8],outCols=(5-9)) 3114 │ ├── [ordering: +(5|8),+6] 3115 │ │ ├── best: (sort G9) 3116 │ │ └── cost: 2360.34 3117 │ ├── [ordering: +(5|8)] 3118 │ │ ├── best: (merge-join G11="[ordering: +5]" G5="[ordering: +8]" G7 inner-join,+5,+8) 3119 │ │ └── cost: 2130.05 3120 │ └── [] 3121 │ ├── best: (merge-join G11="[ordering: +5]" G5="[ordering: +8]" G7 inner-join,+5,+8) 3122 │ └── cost: 2130.05 3123 ├── G10: (filters G17 G18) 3124 ├── G11: (scan child1) 3125 │ ├── [ordering: +5,+6] 3126 │ │ ├── best: (scan child1) 3127 │ │ └── cost: 1060.02 3128 │ ├── [ordering: +5] 3129 │ │ ├── best: (scan child1) 3130 │ │ └── cost: 1060.02 3131 │ └── [] 3132 │ ├── best: (scan child1) 3133 │ └── cost: 1060.02 3134 ├── G12: (inner-join G8 G5 G6) (inner-join G5 G8 G6) (merge-join G8 G5 G7 inner-join,+1,+8) (lookup-join G8 G7 parent1,keyCols=[1],outCols=(1-4,8,9)) (merge-join G5 G8 G7 inner-join,+8,+1) (lookup-join G5 G7 grandchild1,keyCols=[8],outCols=(1-4,8,9)) 3135 │ ├── [ordering: +(1|8),+2] 3136 │ │ ├── best: (sort G12) 3137 │ │ └── cost: 2380.34 3138 │ ├── [ordering: +(1|8)] 3139 │ │ ├── best: (merge-join G8="[ordering: +1]" G5="[ordering: +8]" G7 inner-join,+1,+8) 3140 │ │ └── cost: 2150.05 3141 │ └── [] 3142 │ ├── best: (merge-join G8="[ordering: +1]" G5="[ordering: +8]" G7 inner-join,+1,+8) 3143 │ └── cost: 2150.05 3144 ├── G13: (filters G18 G19) 3145 ├── G14: (filters G20) 3146 ├── G15: (inner-join G8 G11 G13) (inner-join G11 G8 G13) 3147 │ ├── [ordering: +(1|5)] 3148 │ │ ├── best: (sort G15) 3149 │ │ └── cost: 2186.35 3150 │ └── [] 3151 │ ├── best: (inner-join G8 G11 G13) 3152 │ └── cost: 2171.05 3153 ├── G16: (eq G21 G22) 3154 ├── G17: (eq G21 G23) 3155 ├── G18: (eq G24 G25) 3156 ├── G19: (eq G23 G21) 3157 ├── G20: (eq G23 G22) 3158 ├── G21: (variable grandchild1.pid1) 3159 ├── G22: (variable parent1.pid1) 3160 ├── G23: (variable child1.pid1) 3161 ├── G24: (variable grandchild1.cid1) 3162 └── G25: (variable child1.cid1)