github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/tpcc (about) 1 import file=tpcc_schema 2 ---- 3 4 import file=tpcc_stats_w100 5 ---- 6 7 # -------------------------------------------------- 8 # 2.4 The New Order Transaction 9 # 10 # The New-Order business transaction consists of entering a complete order 11 # through a single database transaction. It represents a mid-weight, read-write 12 # transaction with a high frequency of execution and stringent response time 13 # requirements to satisfy on-line users. This transaction is the backbone of 14 # the workload. It is designed to place a variable load on the system to 15 # reflect on-line database activity as typically found in production 16 # environments. 17 # -------------------------------------------------- 18 opt format=hide-qual 19 UPDATE district 20 SET d_next_o_id = d_next_o_id + 1 21 WHERE d_w_id = 10 AND d_id = 5 22 RETURNING d_tax, d_next_o_id 23 ---- 24 project 25 ├── columns: d_tax:9 d_next_o_id:11 26 ├── cardinality: [0 - 1] 27 ├── volatile, side-effects, mutations 28 ├── key: () 29 ├── fd: ()-->(9,11) 30 └── update district 31 ├── columns: d_id:1!null d_w_id:2!null d_tax:9 d_next_o_id:11 32 ├── fetch columns: d_id:12 d_w_id:13 d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 d_ytd:21 d_next_o_id:22 33 ├── update-mapping: 34 │ └── d_next_o_id_new:23 => d_next_o_id:11 35 ├── cardinality: [0 - 1] 36 ├── volatile, side-effects, mutations 37 ├── key: () 38 ├── fd: ()-->(1,2,9,11) 39 └── project 40 ├── columns: d_next_o_id_new:23 d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 d_ytd:21 d_next_o_id:22 41 ├── cardinality: [0 - 1] 42 ├── key: () 43 ├── fd: ()-->(12-23) 44 ├── scan district 45 │ ├── columns: d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 d_ytd:21 d_next_o_id:22 46 │ ├── constraint: /13/12: [/10/5 - /10/5] 47 │ ├── cardinality: [0 - 1] 48 │ ├── key: () 49 │ └── fd: ()-->(12-22) 50 └── projections 51 └── d_next_o_id:22 + 1 [as=d_next_o_id_new:23, outer=(22)] 52 53 opt format=hide-qual 54 SELECT w_tax FROM warehouse WHERE w_id = 10 55 ---- 56 project 57 ├── columns: w_tax:8 58 ├── cardinality: [0 - 1] 59 ├── key: () 60 ├── fd: ()-->(8) 61 └── scan warehouse 62 ├── columns: w_id:1!null w_tax:8 63 ├── constraint: /1: [/10 - /10] 64 ├── cardinality: [0 - 1] 65 ├── key: () 66 └── fd: ()-->(1,8) 67 68 opt format=hide-qual 69 SELECT c_discount, c_last, c_credit 70 FROM customer 71 WHERE c_w_id = 10 AND c_d_id = 100 AND c_id = 50 72 ---- 73 project 74 ├── columns: c_discount:16 c_last:6 c_credit:14 75 ├── cardinality: [0 - 1] 76 ├── key: () 77 ├── fd: ()-->(6,14,16) 78 └── scan customer 79 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_last:6 c_credit:14 c_discount:16 80 ├── constraint: /3/2/1: [/10/100/50 - /10/100/50] 81 ├── cardinality: [0 - 1] 82 ├── key: () 83 └── fd: ()-->(1-3,6,14,16) 84 85 opt format=hide-qual 86 SELECT i_price, i_name, i_data 87 FROM item 88 WHERE i_id IN (125, 150, 175, 200, 25, 50, 75, 100, 225, 250, 275, 300) 89 ORDER BY i_id 90 ---- 91 scan item 92 ├── columns: i_price:4 i_name:3 i_data:5 [hidden: i_id:1!null] 93 ├── constraint: /1 94 │ ├── [/25 - /25] 95 │ ├── [/50 - /50] 96 │ ├── [/75 - /75] 97 │ ├── [/100 - /100] 98 │ ├── [/125 - /125] 99 │ ├── [/150 - /150] 100 │ ├── [/175 - /175] 101 │ ├── [/200 - /200] 102 │ ├── [/225 - /225] 103 │ ├── [/250 - /250] 104 │ ├── [/275 - /275] 105 │ └── [/300 - /300] 106 ├── cardinality: [0 - 12] 107 ├── key: (1) 108 ├── fd: (1)-->(3-5) 109 └── ordering: +1 110 111 opt format=hide-qual 112 SELECT s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, s_dist_05 113 FROM stock 114 WHERE (s_i_id, s_w_id) IN ((1000, 4), (900, 4), (1100, 4), (1500, 4), (1400, 4)) 115 ORDER BY s_i_id 116 ---- 117 project 118 ├── columns: s_quantity:3 s_ytd:14 s_order_cnt:15 s_remote_cnt:16 s_data:17 s_dist_05:8 [hidden: s_i_id:1!null] 119 ├── cardinality: [0 - 5] 120 ├── key: (1) 121 ├── fd: (1)-->(3,8,14-17) 122 ├── ordering: +1 123 └── scan stock 124 ├── columns: s_i_id:1!null s_w_id:2!null s_quantity:3 s_dist_05:8 s_ytd:14 s_order_cnt:15 s_remote_cnt:16 s_data:17 125 ├── constraint: /2/1 126 │ ├── [/4/900 - /4/900] 127 │ ├── [/4/1000 - /4/1000] 128 │ ├── [/4/1100 - /4/1100] 129 │ ├── [/4/1400 - /4/1400] 130 │ └── [/4/1500 - /4/1500] 131 ├── cardinality: [0 - 5] 132 ├── key: (1) 133 ├── fd: ()-->(2), (1)-->(3,8,14-17) 134 └── ordering: +1 opt(2) [actual: +1] 135 136 opt format=hide-qual 137 INSERT INTO "order" (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) 138 VALUES (100, 5, 10, 50, '2019-08-26 16:50:41', 10, 1) 139 ---- 140 insert "order" 141 ├── columns: <none> 142 ├── insert-mapping: 143 │ ├── column1:9 => o_id:1 144 │ ├── column2:10 => o_d_id:2 145 │ ├── column3:11 => o_w_id:3 146 │ ├── column4:12 => o_c_id:4 147 │ ├── column5:13 => o_entry_d:5 148 │ ├── column16:16 => o_carrier_id:6 149 │ ├── column6:14 => o_ol_cnt:7 150 │ └── column7:15 => o_all_local:8 151 ├── input binding: &1 152 ├── cardinality: [0 - 0] 153 ├── volatile, side-effects, mutations 154 ├── values 155 │ ├── columns: column1:9!null column2:10!null column3:11!null column4:12!null column5:13!null column6:14!null column7:15!null column16:16 156 │ ├── cardinality: [1 - 1] 157 │ ├── key: () 158 │ ├── fd: ()-->(9-16) 159 │ └── (100, 5, 10, 50, '2019-08-26 16:50:41+00:00', 10, 1, NULL) 160 └── f-k-checks 161 └── f-k-checks-item: order(o_w_id,o_d_id,o_c_id) -> customer(c_w_id,c_d_id,c_id) 162 └── anti-join (lookup customer) 163 ├── columns: column3:17!null column2:18!null column4:19!null 164 ├── key columns: [17 18 19] = [22 21 20] 165 ├── lookup columns are key 166 ├── cardinality: [0 - 1] 167 ├── key: () 168 ├── fd: ()-->(17-19) 169 ├── with-scan &1 170 │ ├── columns: column3:17!null column2:18!null column4:19!null 171 │ ├── mapping: 172 │ │ ├── column3:11 => column3:17 173 │ │ ├── column2:10 => column2:18 174 │ │ └── column4:12 => column4:19 175 │ ├── cardinality: [1 - 1] 176 │ ├── key: () 177 │ └── fd: ()-->(17-19) 178 └── filters (true) 179 180 opt format=hide-qual 181 INSERT INTO new_order (no_o_id, no_d_id, no_w_id) VALUES (2000, 100, 10) 182 ---- 183 insert new_order 184 ├── columns: <none> 185 ├── insert-mapping: 186 │ ├── column1:4 => no_o_id:1 187 │ ├── column2:5 => no_d_id:2 188 │ └── column3:6 => no_w_id:3 189 ├── input binding: &1 190 ├── cardinality: [0 - 0] 191 ├── volatile, side-effects, mutations 192 ├── values 193 │ ├── columns: column1:4!null column2:5!null column3:6!null 194 │ ├── cardinality: [1 - 1] 195 │ ├── key: () 196 │ ├── fd: ()-->(4-6) 197 │ └── (2000, 100, 10) 198 └── f-k-checks 199 └── f-k-checks-item: new_order(no_w_id,no_d_id,no_o_id) -> order(o_w_id,o_d_id,o_id) 200 └── anti-join (lookup order) 201 ├── columns: column3:7!null column2:8!null column1:9!null 202 ├── key columns: [7 8 9] = [12 11 10] 203 ├── lookup columns are key 204 ├── cardinality: [0 - 1] 205 ├── key: () 206 ├── fd: ()-->(7-9) 207 ├── with-scan &1 208 │ ├── columns: column3:7!null column2:8!null column1:9!null 209 │ ├── mapping: 210 │ │ ├── column3:6 => column3:7 211 │ │ ├── column2:5 => column2:8 212 │ │ └── column1:4 => column1:9 213 │ ├── cardinality: [1 - 1] 214 │ ├── key: () 215 │ └── fd: ()-->(7-9) 216 └── filters (true) 217 218 opt format=hide-qual 219 UPDATE 220 stock 221 SET 222 s_quantity 223 = CASE (s_i_id, s_w_id) 224 WHEN (6823, 0) THEN 26 225 WHEN (7853, 0) THEN 10 226 WHEN (8497, 0) THEN 62 227 WHEN (10904, 0) THEN 54 228 WHEN (16152, 0) THEN 80 229 WHEN (41382, 0) THEN 18 230 WHEN (55952, 0) THEN 56 231 WHEN (64817, 0) THEN 26 232 WHEN (66335, 0) THEN 30 233 WHEN (76567, 0) THEN 71 234 WHEN (81680, 0) THEN 51 235 WHEN (89641, 0) THEN 51 236 WHEN (89905, 0) THEN 77 237 ELSE crdb_internal.force_error('', 'unknown case') 238 END, 239 s_ytd 240 = CASE (s_i_id, s_w_id) 241 WHEN (6823, 0) THEN 6 242 WHEN (7853, 0) THEN 9 243 WHEN (8497, 0) THEN 13 244 WHEN (10904, 0) THEN 1 245 WHEN (16152, 0) THEN 2 246 WHEN (41382, 0) THEN 3 247 WHEN (55952, 0) THEN 10 248 WHEN (64817, 0) THEN 31 249 WHEN (66335, 0) THEN 9 250 WHEN (76567, 0) THEN 7 251 WHEN (81680, 0) THEN 4 252 WHEN (89641, 0) THEN 13 253 WHEN (89905, 0) THEN 20 254 END, 255 s_order_cnt 256 = CASE (s_i_id, s_w_id) 257 WHEN (6823, 0) THEN 1 258 WHEN (7853, 0) THEN 1 259 WHEN (8497, 0) THEN 2 260 WHEN (10904, 0) THEN 1 261 WHEN (16152, 0) THEN 1 262 WHEN (41382, 0) THEN 1 263 WHEN (55952, 0) THEN 1 264 WHEN (64817, 0) THEN 4 265 WHEN (66335, 0) THEN 2 266 WHEN (76567, 0) THEN 1 267 WHEN (81680, 0) THEN 1 268 WHEN (89641, 0) THEN 2 269 WHEN (89905, 0) THEN 4 270 END, 271 s_remote_cnt 272 = CASE (s_i_id, s_w_id) 273 WHEN (6823, 0) THEN 0 274 WHEN (7853, 0) THEN 0 275 WHEN (8497, 0) THEN 0 276 WHEN (10904, 0) THEN 0 277 WHEN (16152, 0) THEN 0 278 WHEN (41382, 0) THEN 0 279 WHEN (55952, 0) THEN 0 280 WHEN (64817, 0) THEN 0 281 WHEN (66335, 0) THEN 0 282 WHEN (76567, 0) THEN 0 283 WHEN (81680, 0) THEN 0 284 WHEN (89641, 0) THEN 0 285 WHEN (89905, 0) THEN 0 286 END 287 WHERE 288 (s_i_id, s_w_id) 289 IN ( 290 (6823, 0), 291 (7853, 0), 292 (8497, 0), 293 (10904, 0), 294 (16152, 0), 295 (41382, 0), 296 (55952, 0), 297 (64817, 0), 298 (66335, 0), 299 (76567, 0), 300 (81680, 0), 301 (89641, 0), 302 (89905, 0) 303 ) 304 ---- 305 update stock 306 ├── columns: <none> 307 ├── fetch columns: s_i_id:18 s_w_id:19 s_quantity:20 s_dist_01:21 s_dist_02:22 s_dist_03:23 s_dist_04:24 s_dist_05:25 s_dist_06:26 s_dist_07:27 s_dist_08:28 s_dist_09:29 s_dist_10:30 s_ytd:31 s_order_cnt:32 s_remote_cnt:33 s_data:34 308 ├── update-mapping: 309 │ ├── s_quantity_new:35 => s_quantity:3 310 │ ├── s_ytd_new:36 => s_ytd:14 311 │ ├── s_order_cnt_new:37 => s_order_cnt:15 312 │ └── s_remote_cnt_new:38 => s_remote_cnt:16 313 ├── cardinality: [0 - 0] 314 ├── volatile, side-effects, mutations 315 └── project 316 ├── columns: s_quantity_new:35 s_ytd_new:36 s_order_cnt_new:37 s_remote_cnt_new:38 s_i_id:18!null s_w_id:19!null s_quantity:20 s_dist_01:21 s_dist_02:22 s_dist_03:23 s_dist_04:24 s_dist_05:25 s_dist_06:26 s_dist_07:27 s_dist_08:28 s_dist_09:29 s_dist_10:30 s_ytd:31 s_order_cnt:32 s_remote_cnt:33 s_data:34 317 ├── cardinality: [0 - 13] 318 ├── volatile, side-effects 319 ├── key: (18) 320 ├── fd: ()-->(19), (18)-->(20-35), (18)-->(36-38) 321 ├── scan stock 322 │ ├── columns: s_i_id:18!null s_w_id:19!null s_quantity:20 s_dist_01:21 s_dist_02:22 s_dist_03:23 s_dist_04:24 s_dist_05:25 s_dist_06:26 s_dist_07:27 s_dist_08:28 s_dist_09:29 s_dist_10:30 s_ytd:31 s_order_cnt:32 s_remote_cnt:33 s_data:34 323 │ ├── constraint: /19/18 324 │ │ ├── [/0/6823 - /0/6823] 325 │ │ ├── [/0/7853 - /0/7853] 326 │ │ ├── [/0/8497 - /0/8497] 327 │ │ ├── [/0/10904 - /0/10904] 328 │ │ ├── [/0/16152 - /0/16152] 329 │ │ ├── [/0/41382 - /0/41382] 330 │ │ ├── [/0/55952 - /0/55952] 331 │ │ ├── [/0/64817 - /0/64817] 332 │ │ ├── [/0/66335 - /0/66335] 333 │ │ ├── [/0/76567 - /0/76567] 334 │ │ ├── [/0/81680 - /0/81680] 335 │ │ ├── [/0/89641 - /0/89641] 336 │ │ └── [/0/89905 - /0/89905] 337 │ ├── cardinality: [0 - 13] 338 │ ├── key: (18) 339 │ └── fd: ()-->(19), (18)-->(20-34) 340 └── projections 341 ├── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 26 WHEN (7853, 0) THEN 10 WHEN (8497, 0) THEN 62 WHEN (10904, 0) THEN 54 WHEN (16152, 0) THEN 80 WHEN (41382, 0) THEN 18 WHEN (55952, 0) THEN 56 WHEN (64817, 0) THEN 26 WHEN (66335, 0) THEN 30 WHEN (76567, 0) THEN 71 WHEN (81680, 0) THEN 51 WHEN (89641, 0) THEN 51 WHEN (89905, 0) THEN 77 ELSE crdb_internal.force_error('', 'unknown case') END [as=s_quantity_new:35, outer=(18,19), volatile, side-effects] 342 ├── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 6 WHEN (7853, 0) THEN 9 WHEN (8497, 0) THEN 13 WHEN (10904, 0) THEN 1 WHEN (16152, 0) THEN 2 WHEN (41382, 0) THEN 3 WHEN (55952, 0) THEN 10 WHEN (64817, 0) THEN 31 WHEN (66335, 0) THEN 9 WHEN (76567, 0) THEN 7 WHEN (81680, 0) THEN 4 WHEN (89641, 0) THEN 13 WHEN (89905, 0) THEN 20 END [as=s_ytd_new:36, outer=(18,19)] 343 ├── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 1 WHEN (7853, 0) THEN 1 WHEN (8497, 0) THEN 2 WHEN (10904, 0) THEN 1 WHEN (16152, 0) THEN 1 WHEN (41382, 0) THEN 1 WHEN (55952, 0) THEN 1 WHEN (64817, 0) THEN 4 WHEN (66335, 0) THEN 2 WHEN (76567, 0) THEN 1 WHEN (81680, 0) THEN 1 WHEN (89641, 0) THEN 2 WHEN (89905, 0) THEN 4 END [as=s_order_cnt_new:37, outer=(18,19)] 344 └── CASE (s_i_id:18, s_w_id:19) WHEN (6823, 0) THEN 0 WHEN (7853, 0) THEN 0 WHEN (8497, 0) THEN 0 WHEN (10904, 0) THEN 0 WHEN (16152, 0) THEN 0 WHEN (41382, 0) THEN 0 WHEN (55952, 0) THEN 0 WHEN (64817, 0) THEN 0 WHEN (66335, 0) THEN 0 WHEN (76567, 0) THEN 0 WHEN (81680, 0) THEN 0 WHEN (89641, 0) THEN 0 WHEN (89905, 0) THEN 0 END [as=s_remote_cnt_new:38, outer=(18,19)] 345 346 opt format=hide-qual 347 INSERT INTO order_line 348 (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) 349 VALUES 350 (3045, 2, 10, 3, 648, 0, 9, 394.470000, 'YhgLRrwsmd68P2bElAgrnp8u'), 351 (3045, 2, 10, 5, 25393, 0, 10, 830.600000, 'dLXe0YhgLRrwsmd68P2bElAg'), 352 (3045, 2, 10, 1, 47887, 0, 9, 204.390000, 'Xe0YhgLRrwsmd68P2bElAgrn'), 353 (3045, 2, 10, 2, 52000, 0, 6, 561.660000, 'ElAgrnp8ueWNXJpBB0ObpVWo'), 354 (3045, 2, 10, 4, 56624, 0, 6, 273.360000, 'RsaCXoEzmssaF9m9cdLXe0Yh'), 355 (3045, 2, 10, 6, 92966, 0, 4, 366.760000, 'saCXoEzmssaF9m9cdLXe0Yhg') 356 ---- 357 insert order_line 358 ├── columns: <none> 359 ├── insert-mapping: 360 │ ├── column1:11 => ol_o_id:1 361 │ ├── column2:12 => ol_d_id:2 362 │ ├── column3:13 => ol_w_id:3 363 │ ├── column4:14 => ol_number:4 364 │ ├── column5:15 => ol_i_id:5 365 │ ├── column6:16 => ol_supply_w_id:6 366 │ ├── column20:20 => ol_delivery_d:7 367 │ ├── column7:17 => ol_quantity:8 368 │ ├── ol_amount:21 => order_line.ol_amount:9 369 │ └── column9:19 => ol_dist_info:10 370 ├── input binding: &1 371 ├── cardinality: [0 - 0] 372 ├── volatile, side-effects, mutations 373 ├── project 374 │ ├── columns: ol_amount:21 column20:20 column1:11!null column2:12!null column3:13!null column4:14!null column5:15!null column6:16!null column7:17!null column9:19!null 375 │ ├── cardinality: [6 - 6] 376 │ ├── immutable 377 │ ├── fd: ()-->(20) 378 │ ├── values 379 │ │ ├── columns: column1:11!null column2:12!null column3:13!null column4:14!null column5:15!null column6:16!null column7:17!null column8:18!null column9:19!null 380 │ │ ├── cardinality: [6 - 6] 381 │ │ ├── (3045, 2, 10, 3, 648, 0, 9, 394.470000, 'YhgLRrwsmd68P2bElAgrnp8u') 382 │ │ ├── (3045, 2, 10, 5, 25393, 0, 10, 830.600000, 'dLXe0YhgLRrwsmd68P2bElAg') 383 │ │ ├── (3045, 2, 10, 1, 47887, 0, 9, 204.390000, 'Xe0YhgLRrwsmd68P2bElAgrn') 384 │ │ ├── (3045, 2, 10, 2, 52000, 0, 6, 561.660000, 'ElAgrnp8ueWNXJpBB0ObpVWo') 385 │ │ ├── (3045, 2, 10, 4, 56624, 0, 6, 273.360000, 'RsaCXoEzmssaF9m9cdLXe0Yh') 386 │ │ └── (3045, 2, 10, 6, 92966, 0, 4, 366.760000, 'saCXoEzmssaF9m9cdLXe0Yhg') 387 │ └── projections 388 │ ├── crdb_internal.round_decimal_values(column8:18, 2) [as=ol_amount:21, outer=(18), immutable] 389 │ └── CAST(NULL AS TIMESTAMP) [as=column20:20] 390 └── f-k-checks 391 ├── f-k-checks-item: order_line(ol_w_id,ol_d_id,ol_o_id) -> order(o_w_id,o_d_id,o_id) 392 │ └── anti-join (lookup order) 393 │ ├── columns: column3:22!null column2:23!null column1:24!null 394 │ ├── key columns: [22 23 24] = [27 26 25] 395 │ ├── lookup columns are key 396 │ ├── cardinality: [0 - 6] 397 │ ├── with-scan &1 398 │ │ ├── columns: column3:22!null column2:23!null column1:24!null 399 │ │ ├── mapping: 400 │ │ │ ├── column3:13 => column3:22 401 │ │ │ ├── column2:12 => column2:23 402 │ │ │ └── column1:11 => column1:24 403 │ │ └── cardinality: [6 - 6] 404 │ └── filters (true) 405 └── f-k-checks-item: order_line(ol_supply_w_id,ol_i_id) -> stock(s_w_id,s_i_id) 406 └── anti-join (lookup stock@stock_item_fk_idx) 407 ├── columns: column6:33!null column5:34!null 408 ├── key columns: [34 33] = [35 36] 409 ├── lookup columns are key 410 ├── cardinality: [0 - 6] 411 ├── with-scan &1 412 │ ├── columns: column6:33!null column5:34!null 413 │ ├── mapping: 414 │ │ ├── column6:16 => column6:33 415 │ │ └── column5:15 => column5:34 416 │ └── cardinality: [6 - 6] 417 └── filters (true) 418 419 # -------------------------------------------------- 420 # 2.5 The Payment Transaction 421 # 422 # The Payment business transaction updates the customer's balance and reflects 423 # the payment on the district and warehouse sales statistics. It represents a 424 # light-weight, read-write transaction with a high frequency of execution and 425 # stringent response time requirements to satisfy on-line users. In addition, 426 # this transaction includes non-primary key access to the CUSTOMER table. 427 # -------------------------------------------------- 428 opt format=hide-qual 429 UPDATE warehouse SET w_ytd = w_ytd + 3860.61 WHERE w_id = 10 430 RETURNING w_name, w_street_1, w_street_2, w_city, w_state, w_zip 431 ---- 432 project 433 ├── columns: w_name:2 w_street_1:3 w_street_2:4 w_city:5 w_state:6 w_zip:7 434 ├── cardinality: [0 - 1] 435 ├── volatile, side-effects, mutations 436 ├── key: () 437 ├── fd: ()-->(2-7) 438 └── update warehouse 439 ├── columns: w_id:1!null w_name:2 w_street_1:3 w_street_2:4 w_city:5 w_state:6 w_zip:7 440 ├── fetch columns: w_id:10 w_name:11 w_street_1:12 w_street_2:13 w_city:14 w_state:15 w_zip:16 w_tax:17 warehouse.w_ytd:18 441 ├── update-mapping: 442 │ └── w_ytd:20 => warehouse.w_ytd:9 443 ├── cardinality: [0 - 1] 444 ├── volatile, side-effects, mutations 445 ├── key: () 446 ├── fd: ()-->(1-7) 447 └── project 448 ├── columns: w_ytd:20 w_id:10!null w_name:11 w_street_1:12 w_street_2:13 w_city:14 w_state:15 w_zip:16 w_tax:17 warehouse.w_ytd:18 449 ├── cardinality: [0 - 1] 450 ├── immutable 451 ├── key: () 452 ├── fd: ()-->(10-18,20) 453 ├── scan warehouse 454 │ ├── columns: w_id:10!null w_name:11 w_street_1:12 w_street_2:13 w_city:14 w_state:15 w_zip:16 w_tax:17 warehouse.w_ytd:18 455 │ ├── constraint: /10: [/10 - /10] 456 │ ├── cardinality: [0 - 1] 457 │ ├── key: () 458 │ └── fd: ()-->(10-18) 459 └── projections 460 └── crdb_internal.round_decimal_values(warehouse.w_ytd:18 + 3860.61, 2) [as=w_ytd:20, outer=(18), immutable] 461 462 opt format=hide-qual 463 UPDATE district SET d_ytd = d_ytd + 3860.61 WHERE (d_w_id = 10) AND (d_id = 5) 464 RETURNING d_name, d_street_1, d_street_2, d_city, d_state, d_zip 465 ---- 466 project 467 ├── columns: d_name:3 d_street_1:4 d_street_2:5 d_city:6 d_state:7 d_zip:8 468 ├── cardinality: [0 - 1] 469 ├── volatile, side-effects, mutations 470 ├── key: () 471 ├── fd: ()-->(3-8) 472 └── update district 473 ├── columns: d_id:1!null d_w_id:2!null d_name:3 d_street_1:4 d_street_2:5 d_city:6 d_state:7 d_zip:8 474 ├── fetch columns: d_id:12 d_w_id:13 d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 district.d_ytd:21 d_next_o_id:22 475 ├── update-mapping: 476 │ └── d_ytd:24 => district.d_ytd:10 477 ├── cardinality: [0 - 1] 478 ├── volatile, side-effects, mutations 479 ├── key: () 480 ├── fd: ()-->(1-8) 481 └── project 482 ├── columns: d_ytd:24 d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 district.d_ytd:21 d_next_o_id:22 483 ├── cardinality: [0 - 1] 484 ├── immutable 485 ├── key: () 486 ├── fd: ()-->(12-22,24) 487 ├── scan district 488 │ ├── columns: d_id:12!null d_w_id:13!null d_name:14 d_street_1:15 d_street_2:16 d_city:17 d_state:18 d_zip:19 d_tax:20 district.d_ytd:21 d_next_o_id:22 489 │ ├── constraint: /13/12: [/10/5 - /10/5] 490 │ ├── cardinality: [0 - 1] 491 │ ├── key: () 492 │ └── fd: ()-->(12-22) 493 └── projections 494 └── crdb_internal.round_decimal_values(district.d_ytd:21 + 3860.61, 2) [as=d_ytd:24, outer=(21), immutable] 495 496 opt format=hide-qual 497 SELECT c_id 498 FROM customer 499 WHERE c_w_id = 10 AND c_d_id = 100 AND c_last = 'Smith' 500 ORDER BY c_first ASC 501 ---- 502 project 503 ├── columns: c_id:1!null [hidden: c_first:4] 504 ├── key: (1) 505 ├── fd: (1)-->(4) 506 ├── ordering: +4 507 └── scan customer@customer_idx 508 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_last:6!null 509 ├── constraint: /3/2/6/4/1: [/10/100/'Smith' - /10/100/'Smith'] 510 ├── key: (1) 511 ├── fd: ()-->(2,3,6), (1)-->(4) 512 └── ordering: +4 opt(2,3,6) [actual: +4] 513 514 opt format=hide-qual 515 UPDATE customer 516 SET (c_balance, c_ytd_payment, c_payment_cnt, c_data) 517 = ( 518 c_balance - (3860.61:::FLOAT8)::DECIMAL, 519 c_ytd_payment + (3860.61:::FLOAT8)::DECIMAL, 520 c_payment_cnt + 1, 521 CASE c_credit 522 WHEN 'BC' 523 THEN "left"( 524 c_id::STRING 525 || c_d_id::STRING 526 || c_w_id::STRING 527 || (5:::INT8)::STRING 528 || (10:::INT8)::STRING 529 || (3860.61:::FLOAT8)::STRING 530 || c_data, 531 500 532 ) 533 ELSE c_data 534 END 535 ) 536 WHERE 537 (c_w_id = 10 AND c_d_id = 5) AND c_id = 1343 538 RETURNING 539 c_first, 540 c_middle, 541 c_last, 542 c_street_1, 543 c_street_2, 544 c_city, 545 c_state, 546 c_zip, 547 c_phone, 548 c_since, 549 c_credit, 550 c_credit_lim, 551 c_discount, 552 c_balance, 553 CASE c_credit WHEN 'BC' THEN "left"(c_data, 200) ELSE '' END 554 ---- 555 project 556 ├── columns: c_first:4 c_middle:5 c_last:6 c_street_1:7 c_street_2:8 c_city:9 c_state:10 c_zip:11 c_phone:12 c_since:13 c_credit:14 c_credit_lim:15 c_discount:16 c_balance:17 case:49 557 ├── cardinality: [0 - 1] 558 ├── volatile, side-effects, mutations 559 ├── key: () 560 ├── fd: ()-->(4-17,49) 561 ├── update customer 562 │ ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_middle:5 c_last:6 c_street_1:7 c_street_2:8 c_city:9 c_state:10 c_zip:11 c_phone:12 c_since:13 c_credit:14 c_credit_lim:15 c_discount:16 customer.c_balance:17 c_data:21 563 │ ├── fetch columns: c_id:22 c_d_id:23 c_w_id:24 c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 customer.c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 564 │ ├── update-mapping: 565 │ │ ├── c_balance:47 => customer.c_balance:17 566 │ │ ├── c_ytd_payment:48 => customer.c_ytd_payment:18 567 │ │ ├── c_payment_cnt_new:45 => c_payment_cnt:19 568 │ │ └── c_data_new:46 => c_data:21 569 │ ├── cardinality: [0 - 1] 570 │ ├── volatile, side-effects, mutations 571 │ ├── key: () 572 │ ├── fd: ()-->(1-17,21) 573 │ └── project 574 │ ├── columns: c_balance:47 c_ytd_payment:48 c_payment_cnt_new:45 c_data_new:46 c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 customer.c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 575 │ ├── cardinality: [0 - 1] 576 │ ├── immutable 577 │ ├── key: () 578 │ ├── fd: ()-->(22-42,45-48) 579 │ ├── scan customer 580 │ │ ├── columns: c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 customer.c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 581 │ │ ├── constraint: /24/23/22: [/10/5/1343 - /10/5/1343] 582 │ │ ├── cardinality: [0 - 1] 583 │ │ ├── key: () 584 │ │ └── fd: ()-->(22-42) 585 │ └── projections 586 │ ├── crdb_internal.round_decimal_values(customer.c_balance:38 - 3860.61, 2) [as=c_balance:47, outer=(38), immutable] 587 │ ├── crdb_internal.round_decimal_values(customer.c_ytd_payment:39 + 3860.61, 2) [as=c_ytd_payment:48, outer=(39), immutable] 588 │ ├── c_payment_cnt:40 + 1 [as=c_payment_cnt_new:45, outer=(40)] 589 │ └── CASE c_credit:35 WHEN 'BC' THEN left((((((c_id:22::STRING || c_d_id:23::STRING) || c_w_id:24::STRING) || '5') || '10') || '3860.61') || c_data:42, 500) ELSE c_data:42 END [as=c_data_new:46, outer=(22-24,35,42), immutable] 590 └── projections 591 └── CASE c_credit:14 WHEN 'BC' THEN left(c_data:21, 200) ELSE '' END [as=case:49, outer=(14,21), immutable] 592 593 opt format=hide-qual 594 INSERT INTO history 595 (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_amount, h_date, h_data) 596 VALUES 597 (1343, 5, 10, 5, 10, 3860.61, '2019-08-26 16:50:41', '8 Kdcgphy3') 598 ---- 599 insert history 600 ├── columns: <none> 601 ├── insert-mapping: 602 │ ├── column18:18 => rowid:1 603 │ ├── column1:10 => h_c_id:2 604 │ ├── column2:11 => h_c_d_id:3 605 │ ├── column3:12 => h_c_w_id:4 606 │ ├── column4:13 => h_d_id:5 607 │ ├── column5:14 => h_w_id:6 608 │ ├── column7:16 => h_date:7 609 │ ├── h_amount:19 => history.h_amount:8 610 │ └── column8:17 => h_data:9 611 ├── input binding: &1 612 ├── cardinality: [0 - 0] 613 ├── volatile, side-effects, mutations 614 ├── values 615 │ ├── columns: column1:10!null column2:11!null column3:12!null column4:13!null column5:14!null column7:16!null column8:17!null column18:18 h_amount:19!null 616 │ ├── cardinality: [1 - 1] 617 │ ├── volatile, side-effects 618 │ ├── key: () 619 │ ├── fd: ()-->(10-14,16-19) 620 │ └── (1343, 5, 10, 5, 10, '2019-08-26 16:50:41+00:00', '8 Kdcgphy3', gen_random_uuid(), 3860.61) 621 └── f-k-checks 622 ├── f-k-checks-item: history(h_c_w_id,h_c_d_id,h_c_id) -> customer(c_w_id,c_d_id,c_id) 623 │ └── anti-join (lookup customer) 624 │ ├── columns: column3:20!null column2:21!null column1:22!null 625 │ ├── key columns: [20 21 22] = [25 24 23] 626 │ ├── lookup columns are key 627 │ ├── cardinality: [0 - 1] 628 │ ├── key: () 629 │ ├── fd: ()-->(20-22) 630 │ ├── with-scan &1 631 │ │ ├── columns: column3:20!null column2:21!null column1:22!null 632 │ │ ├── mapping: 633 │ │ │ ├── column3:12 => column3:20 634 │ │ │ ├── column2:11 => column2:21 635 │ │ │ └── column1:10 => column1:22 636 │ │ ├── cardinality: [1 - 1] 637 │ │ ├── key: () 638 │ │ └── fd: ()-->(20-22) 639 │ └── filters (true) 640 └── f-k-checks-item: history(h_w_id,h_d_id) -> district(d_w_id,d_id) 641 └── anti-join (lookup district) 642 ├── columns: column5:44!null column4:45!null 643 ├── key columns: [44 45] = [47 46] 644 ├── lookup columns are key 645 ├── cardinality: [0 - 1] 646 ├── key: () 647 ├── fd: ()-->(44,45) 648 ├── with-scan &1 649 │ ├── columns: column5:44!null column4:45!null 650 │ ├── mapping: 651 │ │ ├── column5:14 => column5:44 652 │ │ └── column4:13 => column4:45 653 │ ├── cardinality: [1 - 1] 654 │ ├── key: () 655 │ └── fd: ()-->(44,45) 656 └── filters (true) 657 658 # -------------------------------------------------- 659 # 2.6 The Order Status Transaction 660 # 661 # The Order-Status business transaction queries the status of a customer's last 662 # order. It represents a mid-weight read-only database transaction with a low 663 # frequency of execution and response time requirement to satisfy on-line 664 # users. In addition, this table includes non-primary key access to the 665 # CUSTOMER table. 666 # -------------------------------------------------- 667 opt format=hide-qual 668 SELECT c_balance, c_first, c_middle, c_last 669 FROM customer 670 WHERE c_w_id = 10 AND c_d_id = 100 AND c_id = 50 671 ---- 672 project 673 ├── columns: c_balance:17 c_first:4 c_middle:5 c_last:6 674 ├── cardinality: [0 - 1] 675 ├── key: () 676 ├── fd: ()-->(4-6,17) 677 └── scan customer 678 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_middle:5 c_last:6 c_balance:17 679 ├── constraint: /3/2/1: [/10/100/50 - /10/100/50] 680 ├── cardinality: [0 - 1] 681 ├── key: () 682 └── fd: ()-->(1-6,17) 683 684 opt format=hide-qual 685 SELECT c_id, c_balance, c_first, c_middle 686 FROM customer 687 WHERE c_w_id = 10 AND c_d_id = 100 AND c_last = 'Smith' 688 ORDER BY c_first ASC 689 ---- 690 project 691 ├── columns: c_id:1!null c_balance:17 c_first:4 c_middle:5 692 ├── key: (1) 693 ├── fd: (1)-->(4,5,17) 694 ├── ordering: +4 695 └── index-join customer 696 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_middle:5 c_last:6!null c_balance:17 697 ├── key: (1) 698 ├── fd: ()-->(2,3,6), (1)-->(4,5,17) 699 ├── ordering: +4 opt(2,3,6) [actual: +4] 700 └── scan customer@customer_idx 701 ├── columns: c_id:1!null c_d_id:2!null c_w_id:3!null c_first:4 c_last:6!null 702 ├── constraint: /3/2/6/4/1: [/10/100/'Smith' - /10/100/'Smith'] 703 ├── key: (1) 704 ├── fd: ()-->(2,3,6), (1)-->(4) 705 └── ordering: +4 opt(2,3,6) [actual: +4] 706 707 opt format=hide-qual 708 SELECT o_id, o_entry_d, o_carrier_id 709 FROM "order" 710 WHERE o_w_id = 10 AND o_d_id = 100 AND o_c_id = 50 711 ORDER BY o_id DESC 712 LIMIT 1 713 ---- 714 project 715 ├── columns: o_id:1!null o_entry_d:5 o_carrier_id:6 716 ├── cardinality: [0 - 1] 717 ├── key: () 718 ├── fd: ()-->(1,5,6) 719 └── scan "order"@order_idx 720 ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_c_id:4!null o_entry_d:5 o_carrier_id:6 721 ├── constraint: /3/2/4/-1: [/10/100/50 - /10/100/50] 722 ├── limit: 1 723 ├── key: () 724 └── fd: ()-->(1-6) 725 726 opt format=hide-qual 727 SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d 728 FROM order_line 729 WHERE ol_w_id = 10 AND ol_d_id = 100 AND ol_o_id = 1000 730 ---- 731 project 732 ├── columns: ol_i_id:5!null ol_supply_w_id:6 ol_quantity:8 ol_amount:9 ol_delivery_d:7 733 └── scan order_line 734 ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_i_id:5!null ol_supply_w_id:6 ol_delivery_d:7 ol_quantity:8 ol_amount:9 735 ├── constraint: /3/2/-1/4: [/10/100/1000 - /10/100/1000] 736 └── fd: ()-->(1-3) 737 738 # -------------------------------------------------- 739 # 2.7 The Delivery Transaction 740 # 741 # The Delivery business transaction consists of processing a batch of 10 new 742 # (not yet delivered) orders. Each order is processed (delivered) in full 743 # within the scope of a read-write database transaction. The number of orders 744 # delivered as a group (or batched) within the same database transaction is 745 # implementation specific. The business transaction, comprised of one or more 746 # (up to 10) database transactions, has a low frequency of execution and must 747 # complete within a relaxed response time requirement. 748 # 749 # The Delivery transaction is intended to be executed in deferred mode through 750 # a queuing mechanism, rather than interactively, with terminal response 751 # indicating transaction completion. The result of the deferred execution is 752 # recorded into a result file. 753 # -------------------------------------------------- 754 opt format=hide-qual 755 SELECT no_o_id 756 FROM new_order 757 WHERE no_w_id = 10 AND no_d_id = 100 758 ORDER BY no_o_id ASC 759 LIMIT 1 760 ---- 761 project 762 ├── columns: no_o_id:1!null 763 ├── cardinality: [0 - 1] 764 ├── key: () 765 ├── fd: ()-->(1) 766 └── scan new_order 767 ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 768 ├── constraint: /3/2/1: [/10/100 - /10/100] 769 ├── limit: 1 770 ├── key: () 771 └── fd: ()-->(1-3) 772 773 opt format=hide-qual 774 SELECT sum(ol_amount) 775 FROM order_line 776 WHERE ol_w_id = 10 AND ol_d_id = 100 AND ol_o_id = 1000 777 ---- 778 scalar-group-by 779 ├── columns: sum:11 780 ├── cardinality: [1 - 1] 781 ├── key: () 782 ├── fd: ()-->(11) 783 ├── scan order_line 784 │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_amount:9 785 │ ├── constraint: /3/2/-1/4: [/10/100/1000 - /10/100/1000] 786 │ └── fd: ()-->(1-3) 787 └── aggregations 788 └── sum [as=sum:11, outer=(9)] 789 └── ol_amount:9 790 791 opt format=hide-qual 792 UPDATE "order" 793 SET o_carrier_id = 10 794 WHERE o_w_id = 10 795 AND (o_d_id, o_id) IN ( 796 (10, 2167), 797 (5, 2167), 798 (6, 2167), 799 (9, 2167), 800 (4, 2167), 801 (7, 2167), 802 (8, 2167), 803 (1, 2167), 804 (2, 2167), 805 (3, 2167) 806 ) 807 RETURNING 808 o_d_id, o_c_id 809 ---- 810 project 811 ├── columns: o_d_id:2!null o_c_id:4 812 ├── cardinality: [0 - 10] 813 ├── volatile, side-effects, mutations 814 ├── key: (2) 815 ├── fd: (2)-->(4) 816 └── update "order" 817 ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_c_id:4 818 ├── fetch columns: o_id:9 o_d_id:10 o_w_id:11 o_c_id:12 o_entry_d:13 o_carrier_id:14 o_ol_cnt:15 o_all_local:16 819 ├── update-mapping: 820 │ └── o_carrier_id_new:17 => o_carrier_id:6 821 ├── cardinality: [0 - 10] 822 ├── volatile, side-effects, mutations 823 ├── key: (2) 824 ├── fd: ()-->(1,3), (2)-->(4) 825 └── project 826 ├── columns: o_carrier_id_new:17!null o_id:9!null o_d_id:10!null o_w_id:11!null o_c_id:12 o_entry_d:13 o_carrier_id:14 o_ol_cnt:15 o_all_local:16 827 ├── cardinality: [0 - 10] 828 ├── key: (10) 829 ├── fd: ()-->(9,11,17), (10)-->(12-16) 830 ├── scan "order" 831 │ ├── columns: o_id:9!null o_d_id:10!null o_w_id:11!null o_c_id:12 o_entry_d:13 o_carrier_id:14 o_ol_cnt:15 o_all_local:16 832 │ ├── constraint: /11/10/-9 833 │ │ ├── [/10/1/2167 - /10/1/2167] 834 │ │ ├── [/10/2/2167 - /10/2/2167] 835 │ │ ├── [/10/3/2167 - /10/3/2167] 836 │ │ ├── [/10/4/2167 - /10/4/2167] 837 │ │ ├── [/10/5/2167 - /10/5/2167] 838 │ │ ├── [/10/6/2167 - /10/6/2167] 839 │ │ ├── [/10/7/2167 - /10/7/2167] 840 │ │ ├── [/10/8/2167 - /10/8/2167] 841 │ │ ├── [/10/9/2167 - /10/9/2167] 842 │ │ └── [/10/10/2167 - /10/10/2167] 843 │ ├── cardinality: [0 - 10] 844 │ ├── key: (10) 845 │ └── fd: ()-->(9,11), (10)-->(12-16) 846 └── projections 847 └── 10 [as=o_carrier_id_new:17] 848 849 opt format=hide-qual 850 UPDATE customer 851 SET c_delivery_cnt = c_delivery_cnt + 1, 852 c_balance = c_balance + CASE c_d_id 853 WHEN 6 THEN 57214.780000 854 WHEN 8 THEN 67755.430000 855 WHEN 1 THEN 51177.840000 856 WHEN 2 THEN 73840.700000 857 WHEN 4 THEN 45906.990000 858 WHEN 9 THEN 32523.760000 859 WHEN 10 THEN 20240.200000 860 WHEN 3 THEN 75299.790000 861 WHEN 5 THEN 56543.340000 862 WHEN 7 THEN 67157.940000 863 END 864 WHERE c_w_id = 10 AND (c_d_id, c_id) IN ( 865 (1, 1405), 866 (2, 137), 867 (3, 309), 868 (7, 2377), 869 (8, 2106), 870 (10, 417), 871 (4, 98), 872 (5, 1683), 873 (6, 2807), 874 (9, 1412) 875 ) 876 ---- 877 update customer 878 ├── columns: <none> 879 ├── fetch columns: c_id:22 c_d_id:23 c_w_id:24 c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 880 ├── update-mapping: 881 │ ├── c_balance:45 => customer.c_balance:17 882 │ └── c_delivery_cnt_new:43 => c_delivery_cnt:20 883 ├── cardinality: [0 - 0] 884 ├── volatile, side-effects, mutations 885 └── project 886 ├── columns: c_balance:45 c_delivery_cnt_new:43 c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 887 ├── cardinality: [0 - 10] 888 ├── immutable 889 ├── key: (22,23) 890 ├── fd: ()-->(24), (22,23)-->(25-42,45), (41)-->(43) 891 ├── scan customer 892 │ ├── columns: c_id:22!null c_d_id:23!null c_w_id:24!null c_first:25 c_middle:26 c_last:27 c_street_1:28 c_street_2:29 c_city:30 c_state:31 c_zip:32 c_phone:33 c_since:34 c_credit:35 c_credit_lim:36 c_discount:37 customer.c_balance:38 c_ytd_payment:39 c_payment_cnt:40 c_delivery_cnt:41 c_data:42 893 │ ├── constraint: /24/23/22 894 │ │ ├── [/10/1/1405 - /10/1/1405] 895 │ │ ├── [/10/2/137 - /10/2/137] 896 │ │ ├── [/10/3/309 - /10/3/309] 897 │ │ ├── [/10/4/98 - /10/4/98] 898 │ │ ├── [/10/5/1683 - /10/5/1683] 899 │ │ ├── [/10/6/2807 - /10/6/2807] 900 │ │ ├── [/10/7/2377 - /10/7/2377] 901 │ │ ├── [/10/8/2106 - /10/8/2106] 902 │ │ ├── [/10/9/1412 - /10/9/1412] 903 │ │ └── [/10/10/417 - /10/10/417] 904 │ ├── cardinality: [0 - 10] 905 │ ├── key: (22,23) 906 │ └── fd: ()-->(24), (22,23)-->(25-42) 907 └── projections 908 ├── crdb_internal.round_decimal_values(customer.c_balance:38 + CASE c_d_id:23 WHEN 6 THEN 57214.780000 WHEN 8 THEN 67755.430000 WHEN 1 THEN 51177.840000 WHEN 2 THEN 73840.700000 WHEN 4 THEN 45906.990000 WHEN 9 THEN 32523.760000 WHEN 10 THEN 20240.200000 WHEN 3 THEN 75299.790000 WHEN 5 THEN 56543.340000 WHEN 7 THEN 67157.940000 END, 2) [as=c_balance:45, outer=(23,38), immutable] 909 └── c_delivery_cnt:41 + 1 [as=c_delivery_cnt_new:43, outer=(41)] 910 911 opt format=hide-qual 912 DELETE FROM new_order 913 WHERE no_w_id = 10 AND (no_d_id, no_o_id) IN ( 914 (10, 2167), 915 (5, 2167), 916 (6, 2167), 917 (9, 2167), 918 (4, 2167), 919 (7, 2167), 920 (8, 2167), 921 (1, 2167), 922 (2, 2167), 923 (3, 2167) 924 ) 925 ---- 926 delete new_order 927 ├── columns: <none> 928 ├── fetch columns: no_o_id:4 no_d_id:5 no_w_id:6 929 ├── cardinality: [0 - 0] 930 ├── volatile, side-effects, mutations 931 └── scan new_order 932 ├── columns: no_o_id:4!null no_d_id:5!null no_w_id:6!null 933 ├── constraint: /6/5/4 934 │ ├── [/10/1/2167 - /10/1/2167] 935 │ ├── [/10/2/2167 - /10/2/2167] 936 │ ├── [/10/3/2167 - /10/3/2167] 937 │ ├── [/10/4/2167 - /10/4/2167] 938 │ ├── [/10/5/2167 - /10/5/2167] 939 │ ├── [/10/6/2167 - /10/6/2167] 940 │ ├── [/10/7/2167 - /10/7/2167] 941 │ ├── [/10/8/2167 - /10/8/2167] 942 │ ├── [/10/9/2167 - /10/9/2167] 943 │ └── [/10/10/2167 - /10/10/2167] 944 ├── cardinality: [0 - 10] 945 ├── key: (5) 946 └── fd: ()-->(4,6) 947 948 949 opt format=hide-qual 950 UPDATE order_line 951 SET ol_delivery_d = '2019-08-26 16:50:41' 952 WHERE ol_w_id = 10 AND (ol_d_id, ol_o_id) IN ( 953 (10, 2167), 954 (5, 2167), 955 (6, 2167), 956 (9, 2167), 957 (4, 2167), 958 (7, 2167), 959 (8, 2167), 960 (1, 2167), 961 (2, 2167), 962 (3, 2167) 963 ) 964 ---- 965 update order_line 966 ├── columns: <none> 967 ├── fetch columns: ol_o_id:11 ol_d_id:12 ol_w_id:13 ol_number:14 ol_i_id:15 ol_supply_w_id:16 ol_delivery_d:17 ol_quantity:18 ol_amount:19 ol_dist_info:20 968 ├── update-mapping: 969 │ └── ol_delivery_d_new:21 => ol_delivery_d:7 970 ├── cardinality: [0 - 0] 971 ├── volatile, side-effects, mutations 972 └── project 973 ├── columns: ol_delivery_d_new:21!null ol_o_id:11!null ol_d_id:12!null ol_w_id:13!null ol_number:14!null ol_i_id:15!null ol_supply_w_id:16 ol_delivery_d:17 ol_quantity:18 ol_amount:19 ol_dist_info:20 974 ├── key: (12,14) 975 ├── fd: ()-->(11,13,21), (12,14)-->(15-20) 976 ├── scan order_line 977 │ ├── columns: ol_o_id:11!null ol_d_id:12!null ol_w_id:13!null ol_number:14!null ol_i_id:15!null ol_supply_w_id:16 ol_delivery_d:17 ol_quantity:18 ol_amount:19 ol_dist_info:20 978 │ ├── constraint: /13/12/-11/14 979 │ │ ├── [/10/1/2167 - /10/1/2167] 980 │ │ ├── [/10/2/2167 - /10/2/2167] 981 │ │ ├── [/10/3/2167 - /10/3/2167] 982 │ │ ├── [/10/4/2167 - /10/4/2167] 983 │ │ ├── [/10/5/2167 - /10/5/2167] 984 │ │ ├── [/10/6/2167 - /10/6/2167] 985 │ │ ├── [/10/7/2167 - /10/7/2167] 986 │ │ ├── [/10/8/2167 - /10/8/2167] 987 │ │ ├── [/10/9/2167 - /10/9/2167] 988 │ │ └── [/10/10/2167 - /10/10/2167] 989 │ ├── key: (12,14) 990 │ └── fd: ()-->(11,13), (12,14)-->(15-20) 991 └── projections 992 └── '2019-08-26 16:50:41+00:00' [as=ol_delivery_d_new:21] 993 994 # -------------------------------------------------- 995 # 2.8 The Stock-Level Transaction 996 # 997 # The Stock-Level business transaction determines the number of recently sold 998 # items that have a stock level below a specified threshold. It represents a 999 # heavy read-only database transaction with a low frequency of execution, a 1000 # relaxed response time requirement, and relaxed consistency requirements. 1001 # -------------------------------------------------- 1002 opt format=hide-qual 1003 SELECT d_next_o_id 1004 FROM district 1005 WHERE d_w_id = 10 AND d_id = 100 1006 ---- 1007 project 1008 ├── columns: d_next_o_id:11 1009 ├── cardinality: [0 - 1] 1010 ├── key: () 1011 ├── fd: ()-->(11) 1012 └── scan district 1013 ├── columns: d_id:1!null d_w_id:2!null d_next_o_id:11 1014 ├── constraint: /2/1: [/10/100 - /10/100] 1015 ├── cardinality: [0 - 1] 1016 ├── key: () 1017 └── fd: ()-->(1,2,11) 1018 1019 opt format=hide-qual 1020 SELECT count(DISTINCT s_i_id) 1021 FROM order_line 1022 JOIN stock 1023 ON s_i_id=ol_i_id AND s_w_id=ol_w_id 1024 WHERE ol_w_id = 10 1025 AND ol_d_id = 100 1026 AND ol_o_id BETWEEN 1000 - 20 AND 1000 - 1 1027 AND s_quantity < 15 1028 ---- 1029 scalar-group-by 1030 ├── columns: count:28!null 1031 ├── cardinality: [1 - 1] 1032 ├── key: () 1033 ├── fd: ()-->(28) 1034 ├── distinct-on 1035 │ ├── columns: s_i_id:11!null 1036 │ ├── grouping columns: s_i_id:11!null 1037 │ ├── key: (11) 1038 │ └── inner-join (lookup stock) 1039 │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_i_id:5!null s_i_id:11!null s_w_id:12!null s_quantity:13!null 1040 │ ├── key columns: [3 5] = [12 11] 1041 │ ├── lookup columns are key 1042 │ ├── fd: ()-->(2,3,12), (11)-->(13), (5)==(11), (11)==(5), (3)==(12), (12)==(3) 1043 │ ├── scan order_line 1044 │ │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null ol_i_id:5!null 1045 │ │ ├── constraint: /3/2/-1/4: [/10/100/999 - /10/100/980] 1046 │ │ └── fd: ()-->(2,3) 1047 │ └── filters 1048 │ ├── s_w_id:12 = 10 [outer=(12), constraints=(/12: [/10 - /10]; tight), fd=()-->(12)] 1049 │ └── s_quantity:13 < 15 [outer=(13), constraints=(/13: (/NULL - /14]; tight)] 1050 └── aggregations 1051 └── count-rows [as=count:28] 1052 1053 # -------------------------------------------------- 1054 # Consistency Queries 1055 # 1056 # These queries run after TPCC in order to check database consistency. 1057 # They are not part of the benchmark itself. 1058 # -------------------------------------------------- 1059 opt format=hide-qual 1060 SELECT count(*) 1061 FROM warehouse 1062 FULL OUTER JOIN 1063 ( 1064 SELECT d_w_id, sum(d_ytd) as sum_d_ytd 1065 FROM district 1066 GROUP BY d_w_id 1067 ) 1068 ON (w_id = d_w_id) 1069 WHERE w_ytd != sum_d_ytd 1070 ---- 1071 scalar-group-by 1072 ├── columns: count:22!null 1073 ├── cardinality: [1 - 1] 1074 ├── key: () 1075 ├── fd: ()-->(22) 1076 ├── inner-join (merge) 1077 │ ├── columns: w_id:1!null w_ytd:9!null d_w_id:11!null sum:21!null 1078 │ ├── left ordering: +1 1079 │ ├── right ordering: +11 1080 │ ├── key: (11) 1081 │ ├── fd: (1)-->(9), (11)-->(21), (1)==(11), (11)==(1) 1082 │ ├── scan warehouse 1083 │ │ ├── columns: w_id:1!null w_ytd:9 1084 │ │ ├── key: (1) 1085 │ │ ├── fd: (1)-->(9) 1086 │ │ └── ordering: +1 1087 │ ├── group-by 1088 │ │ ├── columns: d_w_id:11!null sum:21 1089 │ │ ├── grouping columns: d_w_id:11!null 1090 │ │ ├── key: (11) 1091 │ │ ├── fd: (11)-->(21) 1092 │ │ ├── ordering: +11 1093 │ │ ├── scan district 1094 │ │ │ ├── columns: d_w_id:11!null d_ytd:19 1095 │ │ │ └── ordering: +11 1096 │ │ └── aggregations 1097 │ │ └── sum [as=sum:21, outer=(19)] 1098 │ │ └── d_ytd:19 1099 │ └── filters 1100 │ └── w_ytd:9 != sum:21 [outer=(9,21), constraints=(/9: (/NULL - ]; /21: (/NULL - ])] 1101 └── aggregations 1102 └── count-rows [as=count_rows:22] 1103 1104 opt format=hide-qual 1105 SELECT d_next_o_id 1106 FROM district 1107 ORDER BY d_w_id, d_id 1108 ---- 1109 scan district 1110 ├── columns: d_next_o_id:11 [hidden: d_id:1!null d_w_id:2!null] 1111 ├── key: (1,2) 1112 ├── fd: (1,2)-->(11) 1113 └── ordering: +2,+1 1114 1115 opt format=hide-qual 1116 SELECT max(no_o_id) 1117 FROM new_order 1118 GROUP BY no_d_id, no_w_id 1119 ORDER BY no_w_id, no_d_id 1120 ---- 1121 group-by 1122 ├── columns: max:4!null [hidden: no_d_id:2!null no_w_id:3!null] 1123 ├── grouping columns: no_d_id:2!null no_w_id:3!null 1124 ├── key: (2,3) 1125 ├── fd: (2,3)-->(4) 1126 ├── ordering: +3,+2 1127 ├── scan new_order 1128 │ ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 1129 │ ├── key: (1-3) 1130 │ └── ordering: +3,+2 1131 └── aggregations 1132 └── max [as=max:4, outer=(1)] 1133 └── no_o_id:1 1134 1135 opt format=hide-qual 1136 SELECT max(o_id) 1137 FROM "order" 1138 GROUP BY o_d_id, o_w_id 1139 ORDER BY o_w_id, o_d_id 1140 ---- 1141 group-by 1142 ├── columns: max:9!null [hidden: o_d_id:2!null o_w_id:3!null] 1143 ├── grouping columns: o_d_id:2!null o_w_id:3!null 1144 ├── key: (2,3) 1145 ├── fd: (2,3)-->(9) 1146 ├── ordering: +3,+2 1147 ├── scan "order"@order_idx 1148 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null 1149 │ ├── key: (1-3) 1150 │ └── ordering: +3,+2 1151 └── aggregations 1152 └── max [as=max:9, outer=(1)] 1153 └── o_id:1 1154 1155 opt format=hide-qual 1156 SELECT count(*) 1157 FROM 1158 ( 1159 SELECT max(no_o_id) - min(no_o_id) - count(*) AS nod 1160 FROM new_order 1161 GROUP BY no_w_id, no_d_id 1162 ) 1163 WHERE nod != -1 1164 ---- 1165 scalar-group-by 1166 ├── columns: count:8!null 1167 ├── cardinality: [1 - 1] 1168 ├── key: () 1169 ├── fd: ()-->(8) 1170 ├── select 1171 │ ├── columns: no_d_id:2!null no_w_id:3!null max:4!null min:5!null count_rows:6!null 1172 │ ├── key: (2,3) 1173 │ ├── fd: (2,3)-->(4-6) 1174 │ ├── group-by 1175 │ │ ├── columns: no_d_id:2!null no_w_id:3!null max:4!null min:5!null count_rows:6!null 1176 │ │ ├── grouping columns: no_d_id:2!null no_w_id:3!null 1177 │ │ ├── internal-ordering: +3,+2 1178 │ │ ├── key: (2,3) 1179 │ │ ├── fd: (2,3)-->(4-6) 1180 │ │ ├── scan new_order 1181 │ │ │ ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 1182 │ │ │ ├── key: (1-3) 1183 │ │ │ └── ordering: +3,+2 1184 │ │ └── aggregations 1185 │ │ ├── max [as=max:4, outer=(1)] 1186 │ │ │ └── no_o_id:1 1187 │ │ ├── min [as=min:5, outer=(1)] 1188 │ │ │ └── no_o_id:1 1189 │ │ └── count-rows [as=count_rows:6] 1190 │ └── filters 1191 │ └── ((max:4 - min:5) - count_rows:6) != -1 [outer=(4-6)] 1192 └── aggregations 1193 └── count-rows [as=count_rows:8] 1194 1195 opt format=hide-qual 1196 SELECT sum(o_ol_cnt) 1197 FROM "order" 1198 GROUP BY o_w_id, o_d_id 1199 ORDER BY o_w_id, o_d_id 1200 ---- 1201 group-by 1202 ├── columns: sum:9 [hidden: o_d_id:2!null o_w_id:3!null] 1203 ├── grouping columns: o_d_id:2!null o_w_id:3!null 1204 ├── key: (2,3) 1205 ├── fd: (2,3)-->(9) 1206 ├── ordering: +3,+2 1207 ├── scan "order" 1208 │ ├── columns: o_d_id:2!null o_w_id:3!null o_ol_cnt:7 1209 │ └── ordering: +3,+2 1210 └── aggregations 1211 └── sum [as=sum:9, outer=(7)] 1212 └── o_ol_cnt:7 1213 1214 opt format=hide-qual 1215 SELECT count(*) 1216 FROM order_line 1217 GROUP BY ol_w_id, ol_d_id 1218 ORDER BY ol_w_id, ol_d_id 1219 ---- 1220 sort 1221 ├── columns: count:11!null [hidden: ol_d_id:2!null ol_w_id:3!null] 1222 ├── key: (2,3) 1223 ├── fd: (2,3)-->(11) 1224 ├── ordering: +3,+2 1225 └── group-by 1226 ├── columns: ol_d_id:2!null ol_w_id:3!null count_rows:11!null 1227 ├── grouping columns: ol_d_id:2!null ol_w_id:3!null 1228 ├── key: (2,3) 1229 ├── fd: (2,3)-->(11) 1230 ├── scan order_line@order_line_stock_fk_idx 1231 │ └── columns: ol_d_id:2!null ol_w_id:3!null 1232 └── aggregations 1233 └── count-rows [as=count_rows:11] 1234 1235 opt format=hide-qual 1236 (SELECT no_w_id, no_d_id, no_o_id FROM new_order) 1237 EXCEPT ALL 1238 (SELECT o_w_id, o_d_id, o_id FROM "order" WHERE o_carrier_id IS NULL) 1239 ---- 1240 except-all 1241 ├── columns: no_w_id:3!null no_d_id:2!null no_o_id:1!null 1242 ├── left columns: no_w_id:3!null no_d_id:2!null no_o_id:1!null 1243 ├── right columns: o_w_id:6 o_d_id:5 o_id:4 1244 ├── scan new_order 1245 │ ├── columns: no_o_id:1!null no_d_id:2!null no_w_id:3!null 1246 │ └── key: (1-3) 1247 └── project 1248 ├── columns: o_id:4!null o_d_id:5!null o_w_id:6!null 1249 ├── key: (4-6) 1250 └── select 1251 ├── columns: o_id:4!null o_d_id:5!null o_w_id:6!null o_carrier_id:9 1252 ├── key: (4-6) 1253 ├── fd: ()-->(9) 1254 ├── scan "order"@order_idx 1255 │ ├── columns: o_id:4!null o_d_id:5!null o_w_id:6!null o_carrier_id:9 1256 │ ├── key: (4-6) 1257 │ └── fd: (4-6)-->(9) 1258 └── filters 1259 └── o_carrier_id:9 IS NULL [outer=(9), constraints=(/9: [/NULL - /NULL]; tight), fd=()-->(9)] 1260 1261 opt format=hide-qual 1262 (SELECT o_w_id, o_d_id, o_id FROM "order" WHERE o_carrier_id IS NULL) 1263 EXCEPT ALL 1264 (SELECT no_w_id, no_d_id, no_o_id FROM new_order) 1265 ---- 1266 except-all 1267 ├── columns: o_w_id:3!null o_d_id:2!null o_id:1!null 1268 ├── left columns: o_w_id:3!null o_d_id:2!null o_id:1!null 1269 ├── right columns: no_w_id:11 no_d_id:10 no_o_id:9 1270 ├── project 1271 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null 1272 │ ├── key: (1-3) 1273 │ └── select 1274 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1275 │ ├── key: (1-3) 1276 │ ├── fd: ()-->(6) 1277 │ ├── scan "order"@order_idx 1278 │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1279 │ │ ├── key: (1-3) 1280 │ │ └── fd: (1-3)-->(6) 1281 │ └── filters 1282 │ └── o_carrier_id:6 IS NULL [outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1283 └── scan new_order 1284 ├── columns: no_o_id:9!null no_d_id:10!null no_w_id:11!null 1285 └── key: (9-11) 1286 1287 opt format=hide-qual 1288 ( 1289 SELECT o_w_id, o_d_id, o_id, o_ol_cnt 1290 FROM "order" 1291 ORDER BY o_w_id, o_d_id, o_id DESC 1292 ) 1293 EXCEPT ALL 1294 ( 1295 SELECT ol_w_id, ol_d_id, ol_o_id, count(*) 1296 FROM order_line 1297 GROUP BY (ol_w_id, ol_d_id, ol_o_id) 1298 ORDER BY ol_w_id, ol_d_id, ol_o_id DESC 1299 ) 1300 ---- 1301 except-all 1302 ├── columns: o_w_id:3!null o_d_id:2!null o_id:1!null o_ol_cnt:7 1303 ├── left columns: o_w_id:3!null o_d_id:2!null o_id:1!null o_ol_cnt:7 1304 ├── right columns: ol_w_id:11 ol_d_id:10 ol_o_id:9 count_rows:19 1305 ├── scan "order" 1306 │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_ol_cnt:7 1307 │ ├── key: (1-3) 1308 │ └── fd: (1-3)-->(7) 1309 └── group-by 1310 ├── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null count_rows:19!null 1311 ├── grouping columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null 1312 ├── key: (9-11) 1313 ├── fd: (9-11)-->(19) 1314 ├── scan order_line@order_line_stock_fk_idx 1315 │ └── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null 1316 └── aggregations 1317 └── count-rows [as=count_rows:19] 1318 1319 opt format=hide-qual 1320 ( 1321 SELECT ol_w_id, ol_d_id, ol_o_id, count(*) 1322 FROM order_line 1323 GROUP BY (ol_w_id, ol_d_id, ol_o_id) 1324 ORDER BY ol_w_id, ol_d_id, ol_o_id DESC 1325 ) 1326 EXCEPT ALL 1327 ( 1328 SELECT o_w_id, o_d_id, o_id, o_ol_cnt 1329 FROM "order" 1330 ORDER BY o_w_id, o_d_id, o_id DESC 1331 ) 1332 ---- 1333 except-all 1334 ├── columns: ol_w_id:3!null ol_d_id:2!null ol_o_id:1!null count:11 1335 ├── left columns: ol_w_id:3!null ol_d_id:2!null ol_o_id:1!null count_rows:11 1336 ├── right columns: o_w_id:14 o_d_id:13 o_id:12 o_ol_cnt:18 1337 ├── group-by 1338 │ ├── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null count_rows:11!null 1339 │ ├── grouping columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null 1340 │ ├── key: (1-3) 1341 │ ├── fd: (1-3)-->(11) 1342 │ ├── scan order_line@order_line_stock_fk_idx 1343 │ │ └── columns: ol_o_id:1!null ol_d_id:2!null ol_w_id:3!null 1344 │ └── aggregations 1345 │ └── count-rows [as=count_rows:11] 1346 └── scan "order" 1347 ├── columns: o_id:12!null o_d_id:13!null o_w_id:14!null o_ol_cnt:18 1348 ├── key: (12-14) 1349 └── fd: (12-14)-->(18) 1350 1351 opt format=hide-qual 1352 SELECT count(*) 1353 FROM 1354 ( 1355 SELECT o_w_id, o_d_id, o_id 1356 FROM "order" 1357 WHERE o_carrier_id IS NULL 1358 ) 1359 FULL OUTER JOIN 1360 ( 1361 SELECT ol_w_id, ol_d_id, ol_o_id 1362 FROM order_line 1363 WHERE ol_delivery_d IS NULL 1364 ) 1365 ON (ol_w_id = o_w_id AND ol_d_id = o_d_id AND ol_o_id = o_id) 1366 WHERE ol_o_id IS NULL OR o_id IS NULL 1367 ---- 1368 scalar-group-by 1369 ├── columns: count:19!null 1370 ├── cardinality: [1 - 1] 1371 ├── key: () 1372 ├── fd: ()-->(19) 1373 ├── select 1374 │ ├── columns: o_id:1 o_d_id:2 o_w_id:3 ol_o_id:9 ol_d_id:10 ol_w_id:11 1375 │ ├── full-join (merge) 1376 │ │ ├── columns: o_id:1 o_d_id:2 o_w_id:3 ol_o_id:9 ol_d_id:10 ol_w_id:11 1377 │ │ ├── left ordering: +3,+2,-1 1378 │ │ ├── right ordering: +11,+10,-9 1379 │ │ ├── project 1380 │ │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null 1381 │ │ │ ├── key: (1-3) 1382 │ │ │ ├── ordering: +3,+2,-1 1383 │ │ │ └── select 1384 │ │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1385 │ │ │ ├── key: (1-3) 1386 │ │ │ ├── fd: ()-->(6) 1387 │ │ │ ├── ordering: +3,+2,-1 opt(6) [actual: +3,+2,-1] 1388 │ │ │ ├── scan "order" 1389 │ │ │ │ ├── columns: o_id:1!null o_d_id:2!null o_w_id:3!null o_carrier_id:6 1390 │ │ │ │ ├── key: (1-3) 1391 │ │ │ │ ├── fd: (1-3)-->(6) 1392 │ │ │ │ └── ordering: +3,+2,-1 opt(6) [actual: +3,+2,-1] 1393 │ │ │ └── filters 1394 │ │ │ └── o_carrier_id:6 IS NULL [outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1395 │ │ ├── project 1396 │ │ │ ├── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null 1397 │ │ │ ├── ordering: +11,+10,-9 1398 │ │ │ └── select 1399 │ │ │ ├── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null ol_delivery_d:15 1400 │ │ │ ├── fd: ()-->(15) 1401 │ │ │ ├── ordering: +11,+10,-9 opt(15) [actual: +11,+10,-9] 1402 │ │ │ ├── scan order_line 1403 │ │ │ │ ├── columns: ol_o_id:9!null ol_d_id:10!null ol_w_id:11!null ol_delivery_d:15 1404 │ │ │ │ └── ordering: +11,+10,-9 opt(15) [actual: +11,+10,-9] 1405 │ │ │ └── filters 1406 │ │ │ └── ol_delivery_d:15 IS NULL [outer=(15), constraints=(/15: [/NULL - /NULL]; tight), fd=()-->(15)] 1407 │ │ └── filters (true) 1408 │ └── filters 1409 │ └── (ol_o_id:9 IS NULL) OR (o_id:1 IS NULL) [outer=(1,9)] 1410 └── aggregations 1411 └── count-rows [as=count_rows:19]