github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/ruleprops/orderings (about) 1 # Tests for InterestingOrderings property. 2 3 exec-ddl 4 CREATE TABLE abc (a INT, b INT, c INT, INDEX (a, b), UNIQUE INDEX (c)) 5 ---- 6 7 # Scan operator. 8 opt 9 SELECT * FROM abc 10 ---- 11 scan abc 12 ├── columns: a:1 b:2 c:3 13 ├── lax-key: (1-3) 14 ├── fd: (3)~~>(1,2) 15 ├── prune: (1-3) 16 └── interesting orderings: (+1,+2) (+3) 17 18 opt 19 SELECT a, c FROM abc 20 ---- 21 scan abc 22 ├── columns: a:1 c:3 23 ├── lax-key: (1,3) 24 ├── fd: (3)~~>(1) 25 ├── prune: (1,3) 26 └── interesting orderings: (+1) (+3) 27 28 opt 29 SELECT b, c FROM abc 30 ---- 31 scan abc 32 ├── columns: b:2 c:3 33 ├── lax-key: (2,3) 34 ├── fd: (3)~~>(2) 35 ├── prune: (2,3) 36 └── interesting orderings: (+3) 37 38 39 # Project operator (we use build instead of opt). 40 build 41 SELECT a, c FROM abc 42 ---- 43 project 44 ├── columns: a:1 c:3 45 ├── lax-key: (1,3) 46 ├── fd: (3)~~>(1) 47 ├── prune: (1,3) 48 ├── interesting orderings: (+1) (+3) 49 └── scan abc 50 ├── columns: a:1 b:2 c:3 rowid:4!null 51 ├── key: (4) 52 ├── fd: (4)-->(1-3), (3)~~>(1,2,4) 53 ├── prune: (1-4) 54 └── interesting orderings: (+4) (+1,+2,+4) (+3,+4) 55 56 build 57 SELECT b, c FROM abc 58 ---- 59 project 60 ├── columns: b:2 c:3 61 ├── lax-key: (2,3) 62 ├── fd: (3)~~>(2) 63 ├── prune: (2,3) 64 ├── interesting orderings: (+3) 65 └── scan abc 66 ├── columns: a:1 b:2 c:3 rowid:4!null 67 ├── key: (4) 68 ├── fd: (4)-->(1-3), (3)~~>(1,2,4) 69 ├── prune: (1-4) 70 └── interesting orderings: (+4) (+1,+2,+4) (+3,+4) 71 72 # GroupBy operator. 73 opt 74 SELECT min(b), a FROM abc GROUP BY a 75 ---- 76 group-by 77 ├── columns: min:5 a:1 78 ├── grouping columns: a:1 79 ├── internal-ordering: +1 80 ├── key: (1) 81 ├── fd: (1)-->(5) 82 ├── prune: (5) 83 ├── interesting orderings: (+1) 84 ├── scan abc@secondary 85 │ ├── columns: a:1 b:2 86 │ ├── ordering: +1 87 │ ├── prune: (1,2) 88 │ └── interesting orderings: (+1,+2) 89 └── aggregations 90 └── min [as=min:5, outer=(2)] 91 └── b:2 92 93 opt 94 SELECT min(b), c FROM abc GROUP BY c 95 ---- 96 group-by 97 ├── columns: min:5 c:3 98 ├── grouping columns: c:3 99 ├── key: (3) 100 ├── fd: (3)-->(5) 101 ├── prune: (5) 102 ├── interesting orderings: (+3) 103 ├── scan abc 104 │ ├── columns: b:2 c:3 105 │ ├── lax-key: (2,3) 106 │ ├── fd: (3)~~>(2) 107 │ ├── prune: (2,3) 108 │ └── interesting orderings: (+3) 109 └── aggregations 110 └── min [as=min:5, outer=(2)] 111 └── b:2 112 113 # GroupBy with required ordering. 114 opt 115 SELECT array_agg(a), b, c FROM (SELECT * FROM abc ORDER BY b, a) GROUP BY b, c 116 ---- 117 group-by 118 ├── columns: array_agg:5 b:2 c:3 119 ├── grouping columns: b:2 c:3 120 ├── internal-ordering: +1 opt(2,3) 121 ├── key: (2,3) 122 ├── fd: (3)~~>(2), (2,3)-->(5) 123 ├── prune: (5) 124 ├── sort 125 │ ├── columns: a:1 b:2 c:3 126 │ ├── lax-key: (1-3) 127 │ ├── fd: (3)~~>(1,2) 128 │ ├── ordering: +1 opt(2,3) [actual: +1] 129 │ ├── prune: (1-3) 130 │ ├── interesting orderings: (+1,+2) (+3) 131 │ └── scan abc 132 │ ├── columns: a:1 b:2 c:3 133 │ ├── lax-key: (1-3) 134 │ ├── fd: (3)~~>(1,2) 135 │ ├── prune: (1-3) 136 │ └── interesting orderings: (+1,+2) (+3) 137 └── aggregations 138 └── array-agg [as=array_agg:5, outer=(1)] 139 └── a:1 140 141 # Scalar GroupBy case. 142 opt 143 SELECT max(a), min(b), sum(c) FROM abc 144 ---- 145 scalar-group-by 146 ├── columns: max:5 min:6 sum:7 147 ├── cardinality: [1 - 1] 148 ├── key: () 149 ├── fd: ()-->(5-7) 150 ├── prune: (5-7) 151 ├── scan abc 152 │ ├── columns: a:1 b:2 c:3 153 │ ├── lax-key: (1-3) 154 │ ├── fd: (3)~~>(1,2) 155 │ ├── prune: (1-3) 156 │ └── interesting orderings: (+1,+2) (+3) 157 └── aggregations 158 ├── max [as=max:5, outer=(1)] 159 │ └── a:1 160 ├── min [as=min:6, outer=(2)] 161 │ └── b:2 162 └── sum [as=sum:7, outer=(3)] 163 └── c:3 164 165 # LookupJoin operator. 166 opt 167 SELECT * FROM abc WHERE a = 1 168 ---- 169 index-join abc 170 ├── columns: a:1!null b:2 c:3 171 ├── lax-key: (2,3) 172 ├── fd: ()-->(1), (3)~~>(2) 173 ├── prune: (2,3) 174 ├── interesting orderings: (+4) (+1,+2,+4) 175 └── scan abc@secondary 176 ├── columns: a:1!null b:2 rowid:4!null 177 ├── constraint: /1/2/4: [/1 - /1] 178 ├── key: (4) 179 ├── fd: ()-->(1), (4)-->(2) 180 ├── prune: (1,2,4) 181 └── interesting orderings: (+4) (+1,+2,+4) 182 183 # Limit operator. 184 opt 185 SELECT * FROM abc ORDER BY a LIMIT 10 186 ---- 187 index-join abc 188 ├── columns: a:1 b:2 c:3 189 ├── cardinality: [0 - 10] 190 ├── lax-key: (1-3) 191 ├── fd: (3)~~>(1,2) 192 ├── ordering: +1 193 ├── prune: (2,3) 194 ├── interesting orderings: (+1,+2) 195 └── scan abc@secondary 196 ├── columns: a:1 b:2 rowid:4!null 197 ├── limit: 10 198 ├── key: (4) 199 ├── fd: (4)-->(1,2) 200 ├── ordering: +1 201 ├── prune: (1,2,4) 202 └── interesting orderings: (+4) (+1,+2,+4) 203 204 opt 205 SELECT * FROM abc ORDER BY b LIMIT 10 206 ---- 207 limit 208 ├── columns: a:1 b:2 c:3 209 ├── internal-ordering: +2 210 ├── cardinality: [0 - 10] 211 ├── lax-key: (1-3) 212 ├── fd: (3)~~>(1,2) 213 ├── ordering: +2 214 ├── prune: (1,3) 215 ├── interesting orderings: (+2) 216 ├── sort 217 │ ├── columns: a:1 b:2 c:3 218 │ ├── lax-key: (1-3) 219 │ ├── fd: (3)~~>(1,2) 220 │ ├── ordering: +2 221 │ ├── limit hint: 10.00 222 │ ├── prune: (1-3) 223 │ ├── interesting orderings: (+1,+2) (+3) 224 │ └── scan abc 225 │ ├── columns: a:1 b:2 c:3 226 │ ├── lax-key: (1-3) 227 │ ├── fd: (3)~~>(1,2) 228 │ ├── prune: (1-3) 229 │ └── interesting orderings: (+1,+2) (+3) 230 └── 10 231 232 opt 233 SELECT * FROM abc ORDER BY a OFFSET 10 234 ---- 235 offset 236 ├── columns: a:1 b:2 c:3 237 ├── internal-ordering: +1 238 ├── lax-key: (1-3) 239 ├── fd: (3)~~>(1,2) 240 ├── ordering: +1 241 ├── prune: (2,3) 242 ├── interesting orderings: (+1,+2) 243 ├── sort 244 │ ├── columns: a:1 b:2 c:3 245 │ ├── lax-key: (1-3) 246 │ ├── fd: (3)~~>(1,2) 247 │ ├── ordering: +1 248 │ ├── prune: (1-3) 249 │ ├── interesting orderings: (+1,+2) (+3) 250 │ └── scan abc 251 │ ├── columns: a:1 b:2 c:3 252 │ ├── lax-key: (1-3) 253 │ ├── fd: (3)~~>(1,2) 254 │ ├── prune: (1-3) 255 │ └── interesting orderings: (+1,+2) (+3) 256 └── 10 257 258 exec-ddl 259 CREATE TABLE xyz (x INT, y INT, z INT, INDEX(z), UNIQUE INDEX(x,y)) 260 ---- 261 262 # Join operator. 263 opt 264 SELECT * FROM abc JOIN xyz ON a=x 265 ---- 266 inner-join (hash) 267 ├── columns: a:1!null b:2 c:3 x:5!null y:6 z:7 268 ├── lax-key: (2,3,5-7) 269 ├── fd: (3)~~>(1,2), (5,6)~~>(7), (1)==(5), (5)==(1) 270 ├── prune: (2,3,6,7) 271 ├── interesting orderings: (+1,+2) (+3) (+7) (+5,+6) 272 ├── scan abc 273 │ ├── columns: a:1 b:2 c:3 274 │ ├── lax-key: (1-3) 275 │ ├── fd: (3)~~>(1,2) 276 │ ├── prune: (1-3) 277 │ └── interesting orderings: (+1,+2) (+3) 278 ├── scan xyz 279 │ ├── columns: x:5 y:6 z:7 280 │ ├── lax-key: (5-7) 281 │ ├── fd: (5,6)~~>(7) 282 │ ├── prune: (5-7) 283 │ └── interesting orderings: (+7) (+5,+6) 284 └── filters 285 └── a:1 = x:5 [outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]