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