github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optgen/exprgen/testdata/join (about) 1 exec-ddl 2 CREATE TABLE abc (a INT, b INT, c INT, INDEX ab(a, b)) 3 ---- 4 5 exec-ddl 6 CREATE TABLE def (d INT, e INT, f INT) 7 ---- 8 9 expr 10 (InnerJoin 11 (Scan [ (Table "abc") (Cols "a,b,c") ]) 12 (Scan [ (Table "def") (Cols "d,e,f") ]) 13 [ (Eq (Var "a") (Var "d")) ] 14 [ ] 15 ) 16 ---- 17 inner-join (hash) 18 ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int) t.public.abc.c:3(int) t.public.def.d:5(int!null) t.public.def.e:6(int) t.public.def.f:7(int) 19 ├── stats: [rows=9801, distinct(1)=99, null(1)=0, distinct(5)=99, null(5)=0] 20 ├── cost: 2268.06 21 ├── fd: (1)==(5), (5)==(1) 22 ├── prune: (2,3,6,7) 23 ├── interesting orderings: (+1,+2) 24 ├── scan t.public.abc 25 │ ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) 26 │ ├── stats: [rows=1000, distinct(1)=100, null(1)=10] 27 │ ├── cost: 1070.02 28 │ ├── prune: (1-3) 29 │ └── interesting orderings: (+1,+2) 30 ├── scan t.public.def 31 │ ├── columns: t.public.def.d:5(int) t.public.def.e:6(int) t.public.def.f:7(int) 32 │ ├── stats: [rows=1000, distinct(5)=100, null(5)=10] 33 │ ├── cost: 1070.02 34 │ └── prune: (5-7) 35 └── filters 36 └── eq [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)] 37 ├── variable: t.public.abc.a:1 [type=int] 38 └── variable: t.public.def.d:5 [type=int] 39 40 expr 41 (MakeLookupJoin 42 (Scan [ (Table "def") (Cols "d,e") ]) 43 [ (JoinType "left-join") (Table "abc") (Index "abc@ab") (KeyCols "a") (Cols "a,b") ] 44 [ (Gt (Var "a") (Var "e")) ] 45 ) 46 ---- 47 left-join (lookup abc@ab) 48 ├── columns: t.public.abc.a:5(int) t.public.abc.b:6(int) 49 ├── key columns: [5] = [5] 50 ├── stats: [rows=3333.33333, distinct(5)=100, null(5)=33.3333333] 51 ├── cost: 41560.04 52 ├── scan t.public.def 53 │ ├── columns: t.public.def.d:1(int) t.public.def.e:2(int) 54 │ ├── stats: [rows=1000, distinct(2)=100, null(2)=10] 55 │ ├── cost: 1060.02 56 │ └── prune: (1,2) 57 └── filters 58 └── gt [type=bool, outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ])] 59 ├── variable: t.public.abc.a:5 [type=int] 60 └── variable: t.public.def.e:2 [type=int] 61 62 expr 63 (MergeJoin 64 (Sort (Scan [ (Table "abc") (Cols "a,b,c") ])) 65 (Sort (Scan [ (Table "def") (Cols "d,e,f") ])) 66 [ ] 67 [ 68 (JoinType "inner-join") 69 (LeftEq "+a") 70 (RightEq "+d") 71 (LeftOrdering "+a") 72 (RightOrdering "+d") 73 ] 74 ) 75 ---- 76 inner-join (merge) 77 ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int) t.public.abc.c:3(int) t.public.def.d:5(int!null) t.public.def.e:6(int) t.public.def.f:7(int) 78 ├── left ordering: +1 79 ├── right ordering: +5 80 ├── stats: [rows=9801, distinct(1)=99, null(1)=0, distinct(5)=99, null(5)=0] 81 ├── cost: 2696.71137 82 ├── fd: (1)==(5), (5)==(1) 83 ├── sort 84 │ ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) 85 │ ├── stats: [rows=1000, distinct(1)=100, null(1)=10] 86 │ ├── cost: 1289.34569 87 │ ├── ordering: +1 88 │ └── scan t.public.abc 89 │ ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) 90 │ ├── stats: [rows=1000, distinct(1)=100, null(1)=10] 91 │ └── cost: 1070.02 92 ├── sort 93 │ ├── columns: t.public.def.d:5(int) t.public.def.e:6(int) t.public.def.f:7(int) 94 │ ├── stats: [rows=1000, distinct(5)=100, null(5)=10] 95 │ ├── cost: 1289.34569 96 │ ├── ordering: +5 97 │ └── scan t.public.def 98 │ ├── columns: t.public.def.d:5(int) t.public.def.e:6(int) t.public.def.f:7(int) 99 │ ├── stats: [rows=1000, distinct(5)=100, null(5)=10] 100 │ └── cost: 1070.02 101 └── filters (true) 102 103 expr 104 (InnerJoinApply 105 (Sort (Scan [ (Table "abc") (Cols "a,b,c") ])) 106 (Select 107 (Scan [ (Table "def") (Cols "d,e,f") ]) 108 [ (Eq (Var "a") (Plus (Var "d") (Var "e"))) ] 109 ) 110 [ ] 111 [ ] 112 ) 113 ---- 114 inner-join-apply 115 ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) t.public.def.d:5(int) t.public.def.e:6(int) t.public.def.f:7(int) 116 ├── stats: [rows=333333.333] 117 ├── cost: 5611.39451 118 ├── prune: (7) 119 ├── sort 120 │ ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) 121 │ ├── stats: [rows=1000] 122 │ ├── cost: 1179.68784 123 │ └── scan t.public.abc 124 │ ├── columns: t.public.abc.a:1(int) t.public.abc.b:2(int) t.public.abc.c:3(int) 125 │ ├── stats: [rows=1000] 126 │ └── cost: 1070.02 127 ├── select 128 │ ├── columns: t.public.def.d:5(int) t.public.def.e:6(int) t.public.def.f:7(int) 129 │ ├── outer: (1) 130 │ ├── stats: [rows=333.333333, distinct(1)=1, null(1)=0] 131 │ ├── cost: 1080.03 132 │ ├── prune: (7) 133 │ ├── scan t.public.def 134 │ │ ├── columns: t.public.def.d:5(int) t.public.def.e:6(int) t.public.def.f:7(int) 135 │ │ ├── stats: [rows=1000] 136 │ │ ├── cost: 1070.02 137 │ │ └── prune: (5-7) 138 │ └── filters 139 │ └── eq [type=bool, outer=(1,5,6), constraints=(/1: (/NULL - ])] 140 │ ├── variable: t.public.abc.a:1 [type=int] 141 │ └── plus [type=int] 142 │ ├── variable: t.public.def.d:5 [type=int] 143 │ └── variable: t.public.def.e:6 [type=int] 144 └── filters (true) 145 146 expr 147 (IndexJoin 148 (Scan 149 [ 150 (Table "abc") 151 (Index "abc@ab") 152 (Cols "a") 153 (HardLimit 10) 154 ] 155 ) 156 [ 157 (Table (FindTable "abc")) 158 (Cols "c") 159 ] 160 ) 161 ---- 162 index-join abc 163 ├── columns: t.public.abc.c:3(int) 164 ├── cardinality: [0 - 10] 165 ├── stats: [rows=10] 166 ├── cost: 51.03 167 ├── interesting orderings: (+1) 168 └── scan t.public.abc@ab 169 ├── columns: t.public.abc.a:1(int) 170 ├── limit: 10 171 ├── stats: [rows=10] 172 ├── cost: 10.42 173 ├── prune: (1) 174 └── interesting orderings: (+1)