github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpcc (about) 1 import file=tpcc_schema 2 ---- 3 4 import file=tpcc_stats_w10 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 save-tables format=hide-qual database=tpcc save-tables-prefix=new_order_01 19 SELECT w_tax FROM warehouse WHERE w_id = 1 20 ---- 21 project 22 ├── save-table-name: new_order_01_project_1 23 ├── columns: w_tax:8(decimal) 24 ├── cardinality: [0 - 1] 25 ├── stats: [rows=1, distinct(8)=1, null(8)=0] 26 ├── key: () 27 ├── fd: ()-->(8) 28 └── scan warehouse 29 ├── save-table-name: new_order_01_scan_2 30 ├── columns: w_id:1(int!null) w_tax:8(decimal) 31 ├── constraint: /1: [/1 - /1] 32 ├── cardinality: [0 - 1] 33 ├── stats: [rows=1, distinct(1)=1, null(1)=0, distinct(8)=1, null(8)=0] 34 ├── key: () 35 └── fd: ()-->(1,8) 36 37 stats table=new_order_01_scan_2 38 ---- 39 column_names row_count distinct_count null_count 40 {w_id} 1 1 0 41 {w_tax} 1 1 0 42 ~~~~ 43 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 44 {w_id} 1.00 1.00 1.00 1.00 0.00 1.00 45 {w_tax} 1.00 1.00 1.00 1.00 0.00 1.00 46 47 save-tables format=hide-qual database=tpcc save-tables-prefix=new_order_02 48 SELECT c_discount, c_last, c_credit 49 FROM customer 50 WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 50 51 ---- 52 project 53 ├── save-table-name: new_order_02_project_1 54 ├── columns: c_discount:16(decimal) c_last:6(varchar) c_credit:14(char) 55 ├── cardinality: [0 - 1] 56 ├── stats: [rows=1, distinct(6)=0.999501832, null(6)=0, distinct(14)=0.786939691, null(14)=0, distinct(16)=0.999901673, null(16)=0] 57 ├── key: () 58 ├── fd: ()-->(6,14,16) 59 └── scan customer 60 ├── save-table-name: new_order_02_scan_2 61 ├── columns: c_id:1(int!null) c_d_id:2(int!null) c_w_id:3(int!null) c_last:6(varchar) c_credit:14(char) c_discount:16(decimal) 62 ├── constraint: /3/2/1: [/1/1/50 - /1/1/50] 63 ├── cardinality: [0 - 1] 64 ├── stats: [rows=1, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(6)=0.999501832, null(6)=0, distinct(14)=0.786939691, null(14)=0, distinct(16)=0.999901673, null(16)=0, distinct(1-3)=1, null(1-3)=0] 65 ├── key: () 66 └── fd: ()-->(1-3,6,14,16) 67 68 stats table=new_order_02_scan_2 69 ---- 70 column_names row_count distinct_count null_count 71 {c_credit} 1 1 0 72 {c_d_id} 1 1 0 73 {c_discount} 1 1 0 74 {c_id} 1 1 0 75 {c_last} 1 1 0 76 {c_w_id} 1 1 0 77 ~~~~ 78 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 79 {c_credit} 1.00 1.00 1.00 1.00 0.00 1.00 80 {c_d_id} 1.00 1.00 1.00 1.00 0.00 1.00 81 {c_discount} 1.00 1.00 1.00 1.00 0.00 1.00 82 {c_id} 1.00 1.00 1.00 1.00 0.00 1.00 83 {c_last} 1.00 1.00 1.00 1.00 0.00 1.00 84 {c_w_id} 1.00 1.00 1.00 1.00 0.00 1.00 85 86 save-tables format=hide-qual database=tpcc save-tables-prefix=new_order_03 87 SELECT i_price, i_name, i_data 88 FROM item 89 WHERE i_id IN (125, 150, 175, 200, 25, 50, 75, 100, 225, 250, 275, 300) 90 ORDER BY i_id 91 ---- 92 scan item 93 ├── save-table-name: new_order_03_scan_1 94 ├── columns: i_price:4(decimal) i_name:3(varchar) i_data:5(varchar) [hidden: i_id:1(int!null)] 95 ├── constraint: /1 96 │ ├── [/25 - /25] 97 │ ├── [/50 - /50] 98 │ ├── [/75 - /75] 99 │ ├── [/100 - /100] 100 │ ├── [/125 - /125] 101 │ ├── [/150 - /150] 102 │ ├── [/175 - /175] 103 │ ├── [/200 - /200] 104 │ ├── [/225 - /225] 105 │ ├── [/250 - /250] 106 │ ├── [/275 - /275] 107 │ └── [/300 - /300] 108 ├── cardinality: [0 - 12] 109 ├── stats: [rows=12, distinct(1)=12, null(1)=0, distinct(3)=11.8957521, null(3)=0, distinct(4)=11.9934177, null(4)=0, distinct(5)=11.9945581, null(5)=0] 110 ├── key: (1) 111 ├── fd: (1)-->(3-5) 112 └── ordering: +1 113 114 stats table=new_order_03_scan_1 115 ---- 116 column_names row_count distinct_count null_count 117 {i_data} 12 12 0 118 {i_id} 12 12 0 119 {i_name} 12 12 0 120 {i_price} 12 12 0 121 ~~~~ 122 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 123 {i_data} 12.00 1.00 12.00 1.00 0.00 1.00 124 {i_id} 12.00 1.00 12.00 1.00 0.00 1.00 125 {i_name} 12.00 1.00 12.00 1.00 0.00 1.00 126 {i_price} 12.00 1.00 12.00 1.00 0.00 1.00 127 128 save-tables format=hide-qual database=tpcc save-tables-prefix=new_order_04 129 SELECT s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, s_dist_05 130 FROM stock 131 WHERE (s_i_id, s_w_id) IN ((1000, 4), (900, 4), (1100, 4), (1500, 4), (1400, 4)) 132 ORDER BY s_i_id 133 ---- 134 project 135 ├── save-table-name: new_order_04_project_1 136 ├── columns: s_quantity:3(int) s_ytd:14(int) s_order_cnt:15(int) s_remote_cnt:16(int) s_data:17(varchar) s_dist_05:8(char) [hidden: s_i_id:1(int!null)] 137 ├── cardinality: [0 - 5] 138 ├── stats: [rows=5, distinct(1)=5, null(1)=0, distinct(3)=4.86513081, null(3)=0, distinct(8)=4.8037108, null(8)=0, distinct(14)=0.993262137, null(14)=0, distinct(15)=0.993262137, null(15)=0, distinct(16)=0.993262137, null(16)=0, distinct(17)=4.99972957, null(17)=0] 139 ├── key: (1) 140 ├── fd: (1)-->(3,8,14-17) 141 ├── ordering: +1 142 └── scan stock 143 ├── save-table-name: new_order_04_scan_2 144 ├── columns: s_i_id:1(int!null) s_w_id:2(int!null) s_quantity:3(int) s_dist_05:8(char) s_ytd:14(int) s_order_cnt:15(int) s_remote_cnt:16(int) s_data:17(varchar) 145 ├── constraint: /2/1 146 │ ├── [/4/900 - /4/900] 147 │ ├── [/4/1000 - /4/1000] 148 │ ├── [/4/1100 - /4/1100] 149 │ ├── [/4/1400 - /4/1400] 150 │ └── [/4/1500 - /4/1500] 151 ├── cardinality: [0 - 5] 152 ├── stats: [rows=5, distinct(1)=5, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=4.86513081, null(3)=0, distinct(8)=4.8037108, null(8)=0, distinct(14)=0.993262137, null(14)=0, distinct(15)=0.993262137, null(15)=0, distinct(16)=0.993262137, null(16)=0, distinct(17)=4.99972957, null(17)=0, distinct(1,2)=5, null(1,2)=0] 153 ├── key: (1) 154 ├── fd: ()-->(2), (1)-->(3,8,14-17) 155 └── ordering: +1 opt(2) [actual: +1] 156 157 stats table=new_order_04_scan_2 158 ---- 159 column_names row_count distinct_count null_count 160 {s_data} 5 5 0 161 {s_dist_05} 5 5 0 162 {s_i_id} 5 5 0 163 {s_order_cnt} 5 1 0 164 {s_quantity} 5 4 0 165 {s_remote_cnt} 5 1 0 166 {s_w_id} 5 1 0 167 {s_ytd} 5 1 0 168 ~~~~ 169 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 170 {s_data} 5.00 1.00 5.00 1.00 0.00 1.00 171 {s_dist_05} 5.00 1.00 5.00 1.00 0.00 1.00 172 {s_i_id} 5.00 1.00 5.00 1.00 0.00 1.00 173 {s_order_cnt} 5.00 1.00 1.00 1.00 0.00 1.00 174 {s_quantity} 5.00 1.00 5.00 1.25 0.00 1.00 175 {s_remote_cnt} 5.00 1.00 1.00 1.00 0.00 1.00 176 {s_w_id} 5.00 1.00 1.00 1.00 0.00 1.00 177 {s_ytd} 5.00 1.00 1.00 1.00 0.00 1.00 178 179 # -------------------------------------------------- 180 # 2.5 The Payment Transaction 181 # 182 # The Payment business transaction updates the customer's balance and reflects 183 # the payment on the district and warehouse sales statistics. It represents a 184 # light-weight, read-write transaction with a high frequency of execution and 185 # stringent response time requirements to satisfy on-line users. In addition, 186 # this transaction includes non-primary key access to the CUSTOMER table. 187 # -------------------------------------------------- 188 save-tables format=hide-qual database=tpcc save-tables-prefix=payment_01 189 SELECT c_id 190 FROM customer 191 WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'ANTIABLEABLE' 192 ORDER BY c_first ASC 193 ---- 194 project 195 ├── save-table-name: payment_01_project_1 196 ├── columns: c_id:1(int!null) [hidden: c_first:4(varchar)] 197 ├── stats: [rows=3.165, distinct(1)=3.16334717, null(1)=0, distinct(4)=3.15605754, null(4)=0] 198 ├── key: (1) 199 ├── fd: (1)-->(4) 200 ├── ordering: +4 201 └── scan customer@customer_idx 202 ├── save-table-name: payment_01_scan_2 203 ├── columns: c_id:1(int!null) c_d_id:2(int!null) c_w_id:3(int!null) c_first:4(varchar) c_last:6(varchar!null) 204 ├── constraint: /3/2/6/4/1: [/1/1/'ANTIABLEABLE' - /1/1/'ANTIABLEABLE'] 205 ├── stats: [rows=3.165, distinct(1)=3.16334717, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(4)=3.15605754, null(4)=0, distinct(6)=1, null(6)=0, distinct(2,3,6)=1, null(2,3,6)=0] 206 │ histogram(3)= 0 3.165 207 │ <---- 1 - 208 ├── key: (1) 209 ├── fd: ()-->(2,3,6), (1)-->(4) 210 └── ordering: +4 opt(2,3,6) [actual: +4] 211 212 stats table=payment_01_scan_2 213 ---- 214 column_names row_count distinct_count null_count 215 {c_d_id} 2 1 0 216 {c_first} 2 2 0 217 {c_id} 2 2 0 218 {c_last} 2 1 0 219 {c_w_id} 2 1 0 220 ~~~~ 221 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 222 {c_d_id} 3.00 1.50 1.00 1.00 0.00 1.00 223 {c_first} 3.00 1.50 3.00 1.50 0.00 1.00 224 {c_id} 3.00 1.50 3.00 1.50 0.00 1.00 225 {c_last} 3.00 1.50 1.00 1.00 0.00 1.00 226 {c_w_id} 3.00 1.50 1.00 1.00 0.00 1.00 227 228 # -------------------------------------------------- 229 # 2.6 The Order Status Transaction 230 # 231 # The Order-Status business transaction queries the status of a customer's last 232 # order. It represents a mid-weight read-only database transaction with a low 233 # frequency of execution and response time requirement to satisfy on-line 234 # users. In addition, this table includes non-primary key access to the 235 # CUSTOMER table. 236 # -------------------------------------------------- 237 save-tables format=hide-qual database=tpcc save-tables-prefix=order_status_01 238 SELECT c_balance, c_first, c_middle, c_last 239 FROM customer 240 WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 50 241 ---- 242 project 243 ├── save-table-name: order_status_01_project_1 244 ├── columns: c_balance:17(decimal) c_first:4(varchar) c_middle:5(char) c_last:6(varchar) 245 ├── cardinality: [0 - 1] 246 ├── stats: [rows=1, distinct(4)=0.999106141, null(4)=0, distinct(5)=0.632121172, null(5)=0, distinct(6)=0.999501832, null(6)=0, distinct(17)=0.632121172, null(17)=0] 247 ├── key: () 248 ├── fd: ()-->(4-6,17) 249 └── scan customer 250 ├── save-table-name: order_status_01_scan_2 251 ├── columns: c_id:1(int!null) c_d_id:2(int!null) c_w_id:3(int!null) c_first:4(varchar) c_middle:5(char) c_last:6(varchar) c_balance:17(decimal) 252 ├── constraint: /3/2/1: [/1/1/50 - /1/1/50] 253 ├── cardinality: [0 - 1] 254 ├── stats: [rows=1, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(4)=0.999106141, null(4)=0, distinct(5)=0.632121172, null(5)=0, distinct(6)=0.999501832, null(6)=0, distinct(17)=0.632121172, null(17)=0, distinct(1-3)=1, null(1-3)=0] 255 ├── key: () 256 └── fd: ()-->(1-6,17) 257 258 stats table=order_status_01_scan_2 259 ---- 260 column_names row_count distinct_count null_count 261 {c_balance} 1 1 0 262 {c_d_id} 1 1 0 263 {c_first} 1 1 0 264 {c_id} 1 1 0 265 {c_last} 1 1 0 266 {c_middle} 1 1 0 267 {c_w_id} 1 1 0 268 ~~~~ 269 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 270 {c_balance} 1.00 1.00 1.00 1.00 0.00 1.00 271 {c_d_id} 1.00 1.00 1.00 1.00 0.00 1.00 272 {c_first} 1.00 1.00 1.00 1.00 0.00 1.00 273 {c_id} 1.00 1.00 1.00 1.00 0.00 1.00 274 {c_last} 1.00 1.00 1.00 1.00 0.00 1.00 275 {c_middle} 1.00 1.00 1.00 1.00 0.00 1.00 276 {c_w_id} 1.00 1.00 1.00 1.00 0.00 1.00 277 278 save-tables format=hide-qual database=tpcc save-tables-prefix=order_status_02 279 SELECT c_id, c_balance, c_first, c_middle 280 FROM customer 281 WHERE c_w_id = 2 AND c_d_id = 2 AND c_last = 'ANTIBARESE' 282 ORDER BY c_first ASC 283 ---- 284 project 285 ├── save-table-name: order_status_02_project_1 286 ├── columns: c_id:1(int!null) c_balance:17(decimal) c_first:4(varchar) c_middle:5(char) 287 ├── stats: [rows=2.853, distinct(1)=2.85165693, null(1)=0, distinct(4)=2.84573236, null(4)=0, distinct(5)=0.942329734, null(5)=0, distinct(17)=0.942329734, null(17)=0] 288 ├── key: (1) 289 ├── fd: (1)-->(4,5,17) 290 ├── ordering: +4 291 └── index-join customer 292 ├── save-table-name: order_status_02_index_join_2 293 ├── columns: c_id:1(int!null) c_d_id:2(int!null) c_w_id:3(int!null) c_first:4(varchar) c_middle:5(char) c_last:6(varchar!null) c_balance:17(decimal) 294 ├── stats: [rows=2.853, distinct(1)=2.85165693, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(4)=2.84573236, null(4)=0, distinct(5)=0.942329734, null(5)=0, distinct(6)=1, null(6)=0, distinct(17)=0.942329734, null(17)=0, distinct(2,3,6)=1, null(2,3,6)=0] 295 │ histogram(3)= 0 2.853 296 │ <---- 2 - 297 ├── key: (1) 298 ├── fd: ()-->(2,3,6), (1)-->(4,5,17) 299 ├── ordering: +4 opt(2,3,6) [actual: +4] 300 └── scan customer@customer_idx 301 ├── save-table-name: order_status_02_scan_3 302 ├── columns: c_id:1(int!null) c_d_id:2(int!null) c_w_id:3(int!null) c_first:4(varchar) c_last:6(varchar!null) 303 ├── constraint: /3/2/6/4/1: [/2/2/'ANTIBARESE' - /2/2/'ANTIBARESE'] 304 ├── stats: [rows=2.853, distinct(1)=2.85165693, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(4)=2.84573236, null(4)=0, distinct(6)=1, null(6)=0, distinct(2,3,6)=1, null(2,3,6)=0] 305 │ histogram(3)= 0 2.853 306 │ <---- 2 - 307 ├── key: (1) 308 ├── fd: ()-->(2,3,6), (1)-->(4) 309 └── ordering: +4 opt(2,3,6) [actual: +4] 310 311 stats table=order_status_02_index_join_2 312 ---- 313 column_names row_count distinct_count null_count 314 {c_balance} 3 1 0 315 {c_d_id} 3 1 0 316 {c_first} 3 3 0 317 {c_id} 3 3 0 318 {c_last} 3 1 0 319 {c_middle} 3 1 0 320 {c_w_id} 3 1 0 321 ~~~~ 322 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 323 {c_balance} 3.00 1.00 1.00 1.00 0.00 1.00 324 {c_d_id} 3.00 1.00 1.00 1.00 0.00 1.00 325 {c_first} 3.00 1.00 3.00 1.00 0.00 1.00 326 {c_id} 3.00 1.00 3.00 1.00 0.00 1.00 327 {c_last} 3.00 1.00 1.00 1.00 0.00 1.00 328 {c_middle} 3.00 1.00 1.00 1.00 0.00 1.00 329 {c_w_id} 3.00 1.00 1.00 1.00 0.00 1.00 330 331 save-tables format=hide-qual database=tpcc save-tables-prefix=order_status_03 332 SELECT o_id, o_entry_d, o_carrier_id 333 FROM "order" 334 WHERE o_w_id = 4 AND o_d_id = 3 AND o_c_id = 10 335 ORDER BY o_id DESC 336 LIMIT 1 337 ---- 338 project 339 ├── save-table-name: order_status_03_project_1 340 ├── columns: o_id:1(int!null) o_entry_d:5(timestamp) o_carrier_id:6(int) 341 ├── cardinality: [0 - 1] 342 ├── stats: [rows=0.986644468, distinct(1)=0.986483809, null(1)=0, distinct(5)=0.627174982, null(5)=0, distinct(6)=0.943691252, null(6)=0.29599334] 343 ├── key: () 344 ├── fd: ()-->(1,5,6) 345 └── scan "order"@order_idx 346 ├── save-table-name: order_status_03_scan_2 347 ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) o_c_id:4(int!null) o_entry_d:5(timestamp) o_carrier_id:6(int) 348 ├── constraint: /3/2/4/-1: [/4/3/10 - /4/3/10] 349 ├── limit: 1 350 ├── stats: [rows=0.986644468, distinct(1)=0.986483809, null(1)=0, distinct(2)=0.986644468, null(2)=0, distinct(3)=0.986644468, null(3)=0, distinct(4)=0.986644468, null(4)=0, distinct(5)=0.627174982, null(5)=0, distinct(6)=0.943691252, null(6)=0.29599334] 351 ├── key: () 352 └── fd: ()-->(1-6) 353 354 save-tables format=hide-qual database=tpcc save-tables-prefix=order_status_04 355 SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d 356 FROM order_line 357 WHERE ol_w_id = 1 AND ol_d_id = 1 AND ol_o_id = 1000 358 ---- 359 project 360 ├── save-table-name: order_status_04_project_1 361 ├── columns: ol_i_id:5(int!null) ol_supply_w_id:6(int) ol_quantity:8(int) ol_amount:9(decimal) ol_delivery_d:7(timestamp) 362 ├── stats: [rows=9.40430419, distinct(5)=9.40387521, null(5)=0, distinct(6)=6.09540837, null(6)=0, distinct(7)=1.98184869, null(7)=2.81742891, distinct(8)=0.999917632, null(8)=0, distinct(9)=9.40424383, null(9)=0] 363 └── scan order_line 364 ├── save-table-name: order_status_04_scan_2 365 ├── columns: ol_o_id:1(int!null) ol_d_id:2(int!null) ol_w_id:3(int!null) ol_i_id:5(int!null) ol_supply_w_id:6(int) ol_delivery_d:7(timestamp) ol_quantity:8(int) ol_amount:9(decimal) 366 ├── constraint: /3/2/-1/4: [/1/1/1000 - /1/1/1000] 367 ├── stats: [rows=9.40430419, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=9.40387521, null(5)=0, distinct(6)=6.09540837, null(6)=0, distinct(7)=1.98184869, null(7)=2.81742891, distinct(8)=0.999917632, null(8)=0, distinct(9)=9.40424383, null(9)=0, distinct(1-3)=1, null(1-3)=0] 368 │ histogram(3)= 0 9.4043 369 │ <---- 1 -- 370 └── fd: ()-->(1-3) 371 372 stats table=order_status_04_scan_2 373 ---- 374 column_names row_count distinct_count null_count 375 {ol_amount} 12 1 0 376 {ol_d_id} 12 1 0 377 {ol_delivery_d} 12 1 0 378 {ol_i_id} 12 12 0 379 {ol_o_id} 12 1 0 380 {ol_quantity} 12 1 0 381 {ol_supply_w_id} 12 1 0 382 {ol_w_id} 12 1 0 383 ~~~~ 384 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 385 {ol_amount} 9.00 1.33 9.00 9.00 <== 0.00 1.00 386 {ol_d_id} 9.00 1.33 1.00 1.00 0.00 1.00 387 {ol_delivery_d} 9.00 1.33 2.00 2.00 <== 3.00 +Inf <== 388 {ol_i_id} 9.00 1.33 9.00 1.33 0.00 1.00 389 {ol_o_id} 9.00 1.33 1.00 1.00 0.00 1.00 390 {ol_quantity} 9.00 1.33 1.00 1.00 0.00 1.00 391 {ol_supply_w_id} 9.00 1.33 6.00 6.00 <== 0.00 1.00 392 {ol_w_id} 9.00 1.33 1.00 1.00 0.00 1.00 393 394 # -------------------------------------------------- 395 # 2.7 The Delivery Transaction 396 # 397 # The Delivery business transaction consists of processing a batch of 10 new 398 # (not yet delivered) orders. Each order is processed (delivered) in full 399 # within the scope of a read-write database transaction. The number of orders 400 # delivered as a group (or batched) within the same database transaction is 401 # implementation specific. The business transaction, comprised of one or more 402 # (up to 10) database transactions, has a low frequency of execution and must 403 # complete within a relaxed response time requirement. 404 # 405 # The Delivery transaction is intended to be executed in deferred mode through 406 # a queuing mechanism, rather than interactively, with terminal response 407 # indicating transaction completion. The result of the deferred execution is 408 # recorded into a result file. 409 # -------------------------------------------------- 410 save-tables format=hide-qual database=tpcc save-tables-prefix=delivery_01 411 SELECT no_o_id 412 FROM new_order 413 WHERE no_w_id = 7 AND no_d_id = 6 414 ORDER BY no_o_id ASC 415 LIMIT 1 416 ---- 417 project 418 ├── save-table-name: delivery_01_project_1 419 ├── columns: no_o_id:1(int!null) 420 ├── cardinality: [0 - 1] 421 ├── stats: [rows=1, distinct(1)=0.999681502, null(1)=0] 422 ├── key: () 423 ├── fd: ()-->(1) 424 └── scan new_order 425 ├── save-table-name: delivery_01_scan_2 426 ├── columns: no_o_id:1(int!null) no_d_id:2(int!null) no_w_id:3(int!null) 427 ├── constraint: /3/2/1: [/7/6 - /7/6] 428 ├── limit: 1 429 ├── stats: [rows=1, distinct(1)=0.999681502, null(1)=0, distinct(2)=0.632336252, null(2)=0, distinct(3)=0.632336252, null(3)=0] 430 ├── key: () 431 └── fd: ()-->(1-3) 432 433 stats table=delivery_01_scan_2 434 ---- 435 column_names row_count distinct_count null_count 436 {no_d_id} 1 1 0 437 {no_o_id} 1 1 0 438 {no_w_id} 1 1 0 439 ~~~~ 440 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 441 {no_d_id} 1.00 1.00 1.00 1.00 0.00 1.00 442 {no_o_id} 1.00 1.00 1.00 1.00 0.00 1.00 443 {no_w_id} 1.00 1.00 1.00 1.00 0.00 1.00 444 445 save-tables format=hide-qual database=tpcc save-tables-prefix=delivery_02 446 SELECT sum(ol_amount) 447 FROM order_line 448 WHERE ol_w_id = 8 AND ol_d_id = 6 AND ol_o_id = 1000 449 ---- 450 scalar-group-by 451 ├── save-table-name: delivery_02_scalar_group_by_1 452 ├── columns: sum:11(decimal) 453 ├── cardinality: [1 - 1] 454 ├── stats: [rows=1, distinct(11)=1, null(11)=0] 455 ├── key: () 456 ├── fd: ()-->(11) 457 ├── scan order_line 458 │ ├── save-table-name: delivery_02_scan_2 459 │ ├── columns: ol_o_id:1(int!null) ol_d_id:2(int!null) ol_w_id:3(int!null) ol_amount:9(decimal) 460 │ ├── constraint: /3/2/-1/4: [/8/6/1000 - /8/6/1000] 461 │ ├── stats: [rows=10.0934032, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(9)=10.0933337, null(9)=0, distinct(1-3)=1, null(1-3)=0] 462 │ │ histogram(3)= 0 10.093 463 │ │ <---- 8 -- 464 │ └── fd: ()-->(1-3) 465 └── aggregations 466 └── sum [as=sum:11, type=decimal, outer=(9)] 467 └── ol_amount:9 [type=decimal] 468 469 stats table=delivery_02_scan_2 470 ---- 471 column_names row_count distinct_count null_count 472 {ol_amount} 7 1 0 473 {ol_d_id} 7 1 0 474 {ol_o_id} 7 1 0 475 {ol_w_id} 7 1 0 476 ~~~~ 477 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 478 {ol_amount} 10.00 1.43 10.00 10.00 <== 0.00 1.00 479 {ol_d_id} 10.00 1.43 1.00 1.00 0.00 1.00 480 {ol_o_id} 10.00 1.43 1.00 1.00 0.00 1.00 481 {ol_w_id} 10.00 1.43 1.00 1.00 0.00 1.00 482 483 stats table=delivery_02_scalar_group_by_1 484 ---- 485 column_names row_count distinct_count null_count 486 {sum} 1 1 0 487 ~~~~ 488 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 489 {sum} 1.00 1.00 1.00 1.00 0.00 1.00 490 491 # -------------------------------------------------- 492 # 2.8 The Stock-Level Transaction 493 # 494 # The Stock-Level business transaction determines the number of recently sold 495 # items that have a stock level below a specified threshold. It represents a 496 # heavy read-only database transaction with a low frequency of execution, a 497 # relaxed response time requirement, and relaxed consistency requirements. 498 # -------------------------------------------------- 499 save-tables format=hide-qual database=tpcc save-tables-prefix=stock_level_01 500 SELECT d_next_o_id 501 FROM district 502 WHERE d_w_id = 4 AND d_id = 9 503 ---- 504 project 505 ├── save-table-name: stock_level_01_project_1 506 ├── columns: d_next_o_id:11(int) 507 ├── cardinality: [0 - 1] 508 ├── stats: [rows=1, distinct(11)=0.633967659, null(11)=0] 509 ├── key: () 510 ├── fd: ()-->(11) 511 └── scan district 512 ├── save-table-name: stock_level_01_scan_2 513 ├── columns: d_id:1(int!null) d_w_id:2(int!null) d_next_o_id:11(int) 514 ├── constraint: /2/1: [/4/9 - /4/9] 515 ├── cardinality: [0 - 1] 516 ├── stats: [rows=1, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(11)=0.633967659, null(11)=0, distinct(1,2)=1, null(1,2)=0] 517 ├── key: () 518 └── fd: ()-->(1,2,11) 519 520 stats table=stock_level_01_scan_2 521 ---- 522 column_names row_count distinct_count null_count 523 {d_id} 1 1 0 524 {d_next_o_id} 1 1 0 525 {d_w_id} 1 1 0 526 ~~~~ 527 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 528 {d_id} 1.00 1.00 1.00 1.00 0.00 1.00 529 {d_next_o_id} 1.00 1.00 1.00 1.00 0.00 1.00 530 {d_w_id} 1.00 1.00 1.00 1.00 0.00 1.00 531 532 save-tables format=hide-qual database=tpcc save-tables-prefix=stock_level_02 533 SELECT count(DISTINCT s_i_id) 534 FROM order_line 535 JOIN stock 536 ON s_i_id=ol_i_id AND s_w_id=ol_w_id 537 WHERE ol_w_id = 1 538 AND ol_d_id = 1 539 AND ol_o_id BETWEEN 1000 - 20 AND 1000 - 1 540 AND s_quantity < 15 541 ---- 542 scalar-group-by 543 ├── save-table-name: stock_level_02_scalar_group_by_1 544 ├── columns: count:28(int!null) 545 ├── cardinality: [1 - 1] 546 ├── stats: [rows=1, distinct(28)=1, null(28)=0] 547 ├── key: () 548 ├── fd: ()-->(28) 549 ├── distinct-on 550 │ ├── save-table-name: stock_level_02_distinct_on_2 551 │ ├── columns: s_i_id:11(int!null) 552 │ ├── grouping columns: s_i_id:11(int!null) 553 │ ├── stats: [rows=187.914589, distinct(11)=187.914589, null(11)=0] 554 │ ├── key: (11) 555 │ └── inner-join (lookup stock) 556 │ ├── save-table-name: stock_level_02_lookup_join_3 557 │ ├── columns: ol_o_id:1(int!null) ol_d_id:2(int!null) ol_w_id:3(int!null) ol_i_id:5(int!null) s_i_id:11(int!null) s_w_id:12(int!null) s_quantity:13(int!null) 558 │ ├── key columns: [3 5] = [12 11] 559 │ ├── lookup columns are key 560 │ ├── stats: [rows=218.87081, distinct(1)=19.9996466, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=187.914589, null(5)=0, distinct(11)=187.914589, null(11)=0, distinct(12)=1, null(12)=0, distinct(13)=30.3110384, null(13)=0] 561 │ ├── fd: ()-->(2,3,12), (11)-->(13), (5)==(11), (11)==(5), (3)==(12), (12)==(3) 562 │ ├── scan order_line 563 │ │ ├── save-table-name: stock_level_02_scan_4 564 │ │ ├── columns: ol_o_id:1(int!null) ol_d_id:2(int!null) ol_w_id:3(int!null) ol_i_id:5(int!null) 565 │ │ ├── constraint: /3/2/-1/4: [/1/1/999 - /1/1/980] 566 │ │ ├── stats: [rows=188.086084, distinct(1)=20, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=187.914589, null(5)=0, distinct(2,3)=1, null(2,3)=0, distinct(1-3)=20, null(1-3)=0] 567 │ │ │ histogram(3)= 0 188.09 568 │ │ │ <---- 1 -- 569 │ │ └── fd: ()-->(2,3) 570 │ └── filters 571 │ ├── s_w_id:12 = 1 [type=bool, outer=(12), constraints=(/12: [/1 - /1]; tight), fd=()-->(12)] 572 │ └── s_quantity:13 < 15 [type=bool, outer=(13), constraints=(/13: (/NULL - /14]; tight)] 573 └── aggregations 574 └── count-rows [as=count:28, type=int] 575 576 stats table=stock_level_02_scan_4 577 ---- 578 column_names row_count distinct_count null_count 579 {ol_d_id} 193 1 0 580 {ol_i_id} 193 193 0 581 {ol_o_id} 193 20 0 582 {ol_w_id} 193 1 0 583 ~~~~ 584 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 585 {ol_d_id} 188.00 1.03 1.00 1.00 0.00 1.00 586 {ol_i_id} 188.00 1.03 188.00 1.03 0.00 1.00 587 {ol_o_id} 188.00 1.03 20.00 1.00 0.00 1.00 588 {ol_w_id} 188.00 1.03 1.00 1.00 0.00 1.00 589 590 stats table=stock_level_02_lookup_join_3 591 ---- 592 column_names row_count distinct_count null_count 593 {ol_d_id} 15 1 0 594 {ol_i_id} 15 15 0 595 {ol_o_id} 15 11 0 596 {ol_w_id} 15 1 0 597 {s_i_id} 15 15 0 598 {s_quantity} 15 5 0 599 {s_w_id} 15 1 0 600 ~~~~ 601 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 602 {ol_d_id} 219.00 14.60 <== 1.00 1.00 0.00 1.00 603 {ol_i_id} 219.00 14.60 <== 188.00 12.53 <== 0.00 1.00 604 {ol_o_id} 219.00 14.60 <== 20.00 1.82 0.00 1.00 605 {ol_w_id} 219.00 14.60 <== 1.00 1.00 0.00 1.00 606 {s_i_id} 219.00 14.60 <== 188.00 12.53 <== 0.00 1.00 607 {s_quantity} 219.00 14.60 <== 30.00 6.00 <== 0.00 1.00 608 {s_w_id} 219.00 14.60 <== 1.00 1.00 0.00 1.00 609 610 # TODO(radu): add stock_level_02_distinct_on_2. 611 612 stats table=stock_level_02_scalar_group_by_1 613 ---- 614 column_names row_count distinct_count null_count 615 {count} 1 1 0 616 ~~~~ 617 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 618 {count} 1.00 1.00 1.00 1.00 0.00 1.00 619 620 # -------------------------------------------------- 621 # Consistency Queries 622 # 623 # These queries run after TPCC in order to check database consistency. 624 # They are not part of the benchmark itself. 625 # -------------------------------------------------- 626 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_01 627 SELECT count(*) 628 FROM warehouse 629 FULL OUTER JOIN 630 ( 631 SELECT d_w_id, sum(d_ytd) as sum_d_ytd 632 FROM district 633 GROUP BY d_w_id 634 ) 635 ON (w_id = d_w_id) 636 WHERE w_ytd != sum_d_ytd 637 ---- 638 scalar-group-by 639 ├── save-table-name: consistency_01_scalar_group_by_1 640 ├── columns: count:22(int!null) 641 ├── cardinality: [1 - 1] 642 ├── stats: [rows=1, distinct(22)=1, null(22)=0] 643 ├── key: () 644 ├── fd: ()-->(22) 645 ├── inner-join (merge) 646 │ ├── save-table-name: consistency_01_merge_join_2 647 │ ├── columns: w_id:1(int!null) w_ytd:9(decimal!null) d_w_id:11(int!null) sum:21(decimal!null) 648 │ ├── left ordering: +1 649 │ ├── right ordering: +11 650 │ ├── stats: [rows=3.33333333, distinct(1)=3.33333333, null(1)=0, distinct(9)=1, null(9)=0, distinct(11)=3.33333333, null(11)=0, distinct(21)=3.33333333, null(21)=0] 651 │ ├── key: (11) 652 │ ├── fd: (1)-->(9), (11)-->(21), (1)==(11), (11)==(1) 653 │ ├── scan warehouse 654 │ │ ├── save-table-name: consistency_01_scan_3 655 │ │ ├── columns: w_id:1(int!null) w_ytd:9(decimal) 656 │ │ ├── stats: [rows=10, distinct(1)=10, null(1)=0, distinct(9)=1, null(9)=0] 657 │ │ │ histogram(1)= 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 658 │ │ │ <--- 0 --- 1 --- 2 --- 3 --- 4 --- 5 --- 6 --- 7 --- 8 --- 9 659 │ │ ├── key: (1) 660 │ │ ├── fd: (1)-->(9) 661 │ │ └── ordering: +1 662 │ ├── group-by 663 │ │ ├── save-table-name: consistency_01_group_by_4 664 │ │ ├── columns: d_w_id:11(int!null) sum:21(decimal) 665 │ │ ├── grouping columns: d_w_id:11(int!null) 666 │ │ ├── stats: [rows=10, distinct(11)=10, null(11)=0, distinct(21)=10, null(21)=0] 667 │ │ ├── key: (11) 668 │ │ ├── fd: (11)-->(21) 669 │ │ ├── ordering: +11 670 │ │ ├── scan district 671 │ │ │ ├── save-table-name: consistency_01_scan_5 672 │ │ │ ├── columns: d_w_id:11(int!null) d_ytd:19(decimal) 673 │ │ │ ├── stats: [rows=100, distinct(11)=10, null(11)=0, distinct(19)=1, null(19)=0] 674 │ │ │ │ histogram(11)= 0 10 0 10 0 10 0 10 0 10 0 10 0 10 0 10 0 10 0 10 675 │ │ │ │ <--- 0 --- 1 --- 2 --- 3 --- 4 --- 5 --- 6 --- 7 --- 8 --- 9 676 │ │ │ └── ordering: +11 677 │ │ └── aggregations 678 │ │ └── sum [as=sum:21, type=decimal, outer=(19)] 679 │ │ └── d_ytd:19 [type=decimal] 680 │ └── filters 681 │ └── w_ytd:9 != sum:21 [type=bool, outer=(9,21), constraints=(/9: (/NULL - ]; /21: (/NULL - ])] 682 └── aggregations 683 └── count-rows [as=count_rows:22, type=int] 684 685 stats table=consistency_01_group_by_4 686 ---- 687 column_names row_count distinct_count null_count 688 {d_w_id} 10 10 0 689 {sum} 10 1 0 690 ~~~~ 691 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 692 {d_w_id} 10.00 1.00 10.00 1.00 0.00 1.00 693 {sum} 10.00 1.00 10.00 10.00 <== 0.00 1.00 694 695 stats table=consistency_01_merge_join_2 696 ---- 697 column_names row_count distinct_count null_count 698 {d_w_id} 0 0 0 699 {sum} 0 0 0 700 {w_id} 0 0 0 701 {w_ytd} 0 0 0 702 ~~~~ 703 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 704 {d_w_id} 3.00 +Inf <== 3.00 +Inf <== 0.00 1.00 705 {sum} 3.00 +Inf <== 3.00 +Inf <== 0.00 1.00 706 {w_id} 3.00 +Inf <== 3.00 +Inf <== 0.00 1.00 707 {w_ytd} 3.00 +Inf <== 1.00 +Inf <== 0.00 1.00 708 709 stats table=consistency_01_scalar_group_by_1 710 ---- 711 column_names row_count distinct_count null_count 712 {count} 1 1 0 713 ~~~~ 714 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 715 {count} 1.00 1.00 1.00 1.00 0.00 1.00 716 717 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_02 718 SELECT d_next_o_id 719 FROM district 720 ORDER BY d_w_id, d_id 721 ---- 722 scan district 723 ├── save-table-name: consistency_02_scan_1 724 ├── columns: d_next_o_id:11(int) [hidden: d_id:1(int!null) d_w_id:2(int!null)] 725 ├── stats: [rows=100, distinct(1)=10, null(1)=0, distinct(2)=10, null(2)=0, distinct(11)=1, null(11)=0] 726 │ histogram(2)= 0 10 0 10 0 10 0 10 0 10 0 10 0 10 0 10 0 10 0 10 727 │ <--- 0 --- 1 --- 2 --- 3 --- 4 --- 5 --- 6 --- 7 --- 8 --- 9 728 ├── key: (1,2) 729 ├── fd: (1,2)-->(11) 730 └── ordering: +2,+1 731 732 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_03 733 SELECT max(no_o_id) 734 FROM new_order 735 GROUP BY no_d_id, no_w_id 736 ORDER BY no_w_id, no_d_id 737 ---- 738 group-by 739 ├── save-table-name: consistency_03_group_by_1 740 ├── columns: max:4(int!null) [hidden: no_d_id:2(int!null) no_w_id:3(int!null)] 741 ├── grouping columns: no_d_id:2(int!null) no_w_id:3(int!null) 742 ├── stats: [rows=100, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(4)=100, null(4)=0, distinct(2,3)=100, null(2,3)=0] 743 ├── key: (2,3) 744 ├── fd: (2,3)-->(4) 745 ├── ordering: +3,+2 746 ├── scan new_order 747 │ ├── save-table-name: consistency_03_scan_2 748 │ ├── columns: no_o_id:1(int!null) no_d_id:2(int!null) no_w_id:3(int!null) 749 │ ├── stats: [rows=90000, distinct(1)=900, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(2,3)=100, null(2,3)=0] 750 │ │ histogram(3)= 0 9333 0 9189 0 9261 0 9162 0 9306 0 8442 0 8946 0 8532 0 9135 0 8694 751 │ │ <--- 0 ---- 1 ---- 2 ---- 3 ---- 4 ---- 5 ---- 6 ---- 7 ---- 8 ---- 9 - 752 │ ├── key: (1-3) 753 │ └── ordering: +3,+2 754 └── aggregations 755 └── max [as=max:4, type=int, outer=(1)] 756 └── no_o_id:1 [type=int] 757 758 stats table=consistency_03_group_by_1 759 ---- 760 column_names row_count distinct_count null_count 761 {max} 100 1 0 762 {no_d_id} 100 10 0 763 {no_w_id} 100 10 0 764 ~~~~ 765 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 766 {max} 100.00 1.00 100.00 100.00 <== 0.00 1.00 767 {no_d_id} 100.00 1.00 10.00 1.00 0.00 1.00 768 {no_w_id} 100.00 1.00 10.00 1.00 0.00 1.00 769 770 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_04 771 SELECT max(o_id) 772 FROM "order" 773 GROUP BY o_d_id, o_w_id 774 ORDER BY o_w_id, o_d_id 775 ---- 776 group-by 777 ├── save-table-name: consistency_04_group_by_1 778 ├── columns: max:9(int!null) [hidden: o_d_id:2(int!null) o_w_id:3(int!null)] 779 ├── grouping columns: o_d_id:2(int!null) o_w_id:3(int!null) 780 ├── stats: [rows=100, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(9)=100, null(9)=0, distinct(2,3)=100, null(2,3)=0] 781 ├── key: (2,3) 782 ├── fd: (2,3)-->(9) 783 ├── ordering: +3,+2 784 ├── scan "order"@order_idx 785 │ ├── save-table-name: consistency_04_scan_2 786 │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) 787 │ ├── stats: [rows=300000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(2,3)=100, null(2,3)=0] 788 │ │ histogram(3)= 0 29280 0 29310 0 31560 0 30000 0 29220 0 30720 0 31110 0 30510 0 29070 0 29220 789 │ │ <---- 0 ----- 1 ----- 2 ----- 3 ----- 4 ----- 5 ----- 6 ----- 7 ----- 8 ----- 9 - 790 │ ├── key: (1-3) 791 │ └── ordering: +3,+2 792 └── aggregations 793 └── max [as=max:9, type=int, outer=(1)] 794 └── o_id:1 [type=int] 795 796 stats table=consistency_04_group_by_1 797 ---- 798 column_names row_count distinct_count null_count 799 {max} 100 1 0 800 {o_d_id} 100 10 0 801 {o_w_id} 100 10 0 802 ~~~~ 803 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 804 {max} 100.00 1.00 100.00 100.00 <== 0.00 1.00 805 {o_d_id} 100.00 1.00 10.00 1.00 0.00 1.00 806 {o_w_id} 100.00 1.00 10.00 1.00 0.00 1.00 807 808 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_05 809 SELECT count(*) 810 FROM 811 ( 812 SELECT max(no_o_id) - min(no_o_id) - count(*) AS nod 813 FROM new_order 814 GROUP BY no_w_id, no_d_id 815 ) 816 WHERE nod != -1 817 ---- 818 scalar-group-by 819 ├── save-table-name: consistency_05_scalar_group_by_1 820 ├── columns: count:8(int!null) 821 ├── cardinality: [1 - 1] 822 ├── stats: [rows=1, distinct(8)=1, null(8)=0] 823 ├── key: () 824 ├── fd: ()-->(8) 825 ├── select 826 │ ├── save-table-name: consistency_05_select_2 827 │ ├── columns: no_d_id:2(int!null) no_w_id:3(int!null) max:4(int!null) min:5(int!null) count_rows:6(int!null) 828 │ ├── stats: [rows=33.3333333, distinct(2)=9.8265847, null(2)=0, distinct(3)=9.8265847, null(3)=0, distinct(4)=33.3333333, null(4)=0, distinct(5)=33.3333333, null(5)=0, distinct(6)=33.3333333, null(6)=0] 829 │ ├── key: (2,3) 830 │ ├── fd: (2,3)-->(4-6) 831 │ ├── group-by 832 │ │ ├── save-table-name: consistency_05_group_by_3 833 │ │ ├── columns: no_d_id:2(int!null) no_w_id:3(int!null) max:4(int!null) min:5(int!null) count_rows:6(int!null) 834 │ │ ├── grouping columns: no_d_id:2(int!null) no_w_id:3(int!null) 835 │ │ ├── internal-ordering: +3,+2 836 │ │ ├── stats: [rows=100, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(4)=100, null(4)=0, distinct(5)=100, null(5)=0, distinct(6)=100, null(6)=0, distinct(2,3)=100, null(2,3)=0] 837 │ │ ├── key: (2,3) 838 │ │ ├── fd: (2,3)-->(4-6) 839 │ │ ├── scan new_order 840 │ │ │ ├── save-table-name: consistency_05_scan_4 841 │ │ │ ├── columns: no_o_id:1(int!null) no_d_id:2(int!null) no_w_id:3(int!null) 842 │ │ │ ├── stats: [rows=90000, distinct(1)=900, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(2,3)=100, null(2,3)=0] 843 │ │ │ │ histogram(3)= 0 9333 0 9189 0 9261 0 9162 0 9306 0 8442 0 8946 0 8532 0 9135 0 8694 844 │ │ │ │ <--- 0 ---- 1 ---- 2 ---- 3 ---- 4 ---- 5 ---- 6 ---- 7 ---- 8 ---- 9 - 845 │ │ │ ├── key: (1-3) 846 │ │ │ └── ordering: +3,+2 847 │ │ └── aggregations 848 │ │ ├── max [as=max:4, type=int, outer=(1)] 849 │ │ │ └── no_o_id:1 [type=int] 850 │ │ ├── min [as=min:5, type=int, outer=(1)] 851 │ │ │ └── no_o_id:1 [type=int] 852 │ │ └── count-rows [as=count_rows:6, type=int] 853 │ └── filters 854 │ └── ((max:4 - min:5) - count_rows:6) != -1 [type=bool, outer=(4-6)] 855 └── aggregations 856 └── count-rows [as=count_rows:8, type=int] 857 858 stats table=consistency_05_group_by_3 859 ---- 860 column_names row_count distinct_count null_count 861 {count_rows} 100 1 0 862 {max} 100 1 0 863 {min} 100 1 0 864 {no_d_id} 100 10 0 865 {no_w_id} 100 10 0 866 ~~~~ 867 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 868 {count_rows} 100.00 1.00 100.00 100.00 <== 0.00 1.00 869 {max} 100.00 1.00 100.00 100.00 <== 0.00 1.00 870 {min} 100.00 1.00 100.00 100.00 <== 0.00 1.00 871 {no_d_id} 100.00 1.00 10.00 1.00 0.00 1.00 872 {no_w_id} 100.00 1.00 10.00 1.00 0.00 1.00 873 874 stats table=consistency_05_select_2 875 ---- 876 column_names row_count distinct_count null_count 877 {count_rows} 0 0 0 878 {max} 0 0 0 879 {min} 0 0 0 880 {no_d_id} 0 0 0 881 {no_w_id} 0 0 0 882 ~~~~ 883 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 884 {count_rows} 33.00 +Inf <== 33.00 +Inf <== 0.00 1.00 885 {max} 33.00 +Inf <== 33.00 +Inf <== 0.00 1.00 886 {min} 33.00 +Inf <== 33.00 +Inf <== 0.00 1.00 887 {no_d_id} 33.00 +Inf <== 10.00 +Inf <== 0.00 1.00 888 {no_w_id} 33.00 +Inf <== 10.00 +Inf <== 0.00 1.00 889 890 stats table=consistency_05_scalar_group_by_1 891 ---- 892 column_names row_count distinct_count null_count 893 {count} 1 1 0 894 ~~~~ 895 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 896 {count} 1.00 1.00 1.00 1.00 0.00 1.00 897 898 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_06 899 SELECT sum(o_ol_cnt) 900 FROM "order" 901 GROUP BY o_w_id, o_d_id 902 ORDER BY o_w_id, o_d_id 903 ---- 904 group-by 905 ├── save-table-name: consistency_06_group_by_1 906 ├── columns: sum:9(decimal) [hidden: o_d_id:2(int!null) o_w_id:3(int!null)] 907 ├── grouping columns: o_d_id:2(int!null) o_w_id:3(int!null) 908 ├── stats: [rows=100, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(9)=100, null(9)=0, distinct(2,3)=100, null(2,3)=0] 909 ├── key: (2,3) 910 ├── fd: (2,3)-->(9) 911 ├── ordering: +3,+2 912 ├── scan "order" 913 │ ├── save-table-name: consistency_06_scan_2 914 │ ├── columns: o_d_id:2(int!null) o_w_id:3(int!null) o_ol_cnt:7(int) 915 │ ├── stats: [rows=300000, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(7)=11, null(7)=0, distinct(2,3)=100, null(2,3)=0] 916 │ │ histogram(3)= 0 29280 0 29310 0 31560 0 30000 0 29220 0 30720 0 31110 0 30510 0 29070 0 29220 917 │ │ <---- 0 ----- 1 ----- 2 ----- 3 ----- 4 ----- 5 ----- 6 ----- 7 ----- 8 ----- 9 - 918 │ └── ordering: +3,+2 919 └── aggregations 920 └── sum [as=sum:9, type=decimal, outer=(7)] 921 └── o_ol_cnt:7 [type=int] 922 923 stats table=consistency_06_group_by_1 924 ---- 925 column_names row_count distinct_count null_count 926 {o_d_id} 100 10 0 927 {o_w_id} 100 10 0 928 {sum} 100 93 0 929 ~~~~ 930 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 931 {o_d_id} 100.00 1.00 10.00 1.00 0.00 1.00 932 {o_w_id} 100.00 1.00 10.00 1.00 0.00 1.00 933 {sum} 100.00 1.00 100.00 1.08 0.00 1.00 934 935 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_07 936 SELECT count(*) 937 FROM order_line 938 GROUP BY ol_w_id, ol_d_id 939 ORDER BY ol_w_id, ol_d_id 940 ---- 941 sort 942 ├── save-table-name: consistency_07_sort_1 943 ├── columns: count:11(int!null) [hidden: ol_d_id:2(int!null) ol_w_id:3(int!null)] 944 ├── stats: [rows=100, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(11)=100, null(11)=0, distinct(2,3)=100, null(2,3)=0] 945 ├── key: (2,3) 946 ├── fd: (2,3)-->(11) 947 ├── ordering: +3,+2 948 └── group-by 949 ├── save-table-name: consistency_07_group_by_2 950 ├── columns: ol_d_id:2(int!null) ol_w_id:3(int!null) count_rows:11(int!null) 951 ├── grouping columns: ol_d_id:2(int!null) ol_w_id:3(int!null) 952 ├── stats: [rows=100, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(11)=100, null(11)=0, distinct(2,3)=100, null(2,3)=0] 953 ├── key: (2,3) 954 ├── fd: (2,3)-->(11) 955 ├── scan order_line@order_line_stock_fk_idx 956 │ ├── save-table-name: consistency_07_scan_3 957 │ ├── columns: ol_d_id:2(int!null) ol_w_id:3(int!null) 958 │ └── stats: [rows=3001222, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(2,3)=100, null(2,3)=0] 959 │ histogram(3)= 0 3.1213e+05 0 2.7851e+05 0 2.9892e+05 0 3.0732e+05 0 2.9892e+05 0 2.9622e+05 0 3.1363e+05 0 2.8392e+05 0 2.9892e+05 0 3.1273e+05 960 │ <------ 0 ---------- 1 ---------- 2 ---------- 3 ---------- 4 ---------- 5 ---------- 6 ---------- 7 ---------- 8 ---------- 9 ---- 961 └── aggregations 962 └── count-rows [as=count_rows:11, type=int] 963 964 stats table=consistency_07_group_by_2 965 ---- 966 column_names row_count distinct_count null_count 967 {count_rows} 100 93 0 968 {ol_d_id} 100 10 0 969 {ol_w_id} 100 10 0 970 ~~~~ 971 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 972 {count_rows} 100.00 1.00 100.00 1.08 0.00 1.00 973 {ol_d_id} 100.00 1.00 10.00 1.00 0.00 1.00 974 {ol_w_id} 100.00 1.00 10.00 1.00 0.00 1.00 975 976 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_08 977 (SELECT no_w_id, no_d_id, no_o_id FROM new_order) 978 EXCEPT ALL 979 (SELECT o_w_id, o_d_id, o_id FROM "order" WHERE o_carrier_id IS NULL) 980 ---- 981 except-all 982 ├── save-table-name: consistency_08_except_all_1 983 ├── columns: no_w_id:3(int!null) no_d_id:2(int!null) no_o_id:1(int!null) 984 ├── left columns: no_w_id:3(int!null) no_d_id:2(int!null) no_o_id:1(int!null) 985 ├── right columns: o_w_id:6(int) o_d_id:5(int) o_id:4(int) 986 ├── stats: [rows=90000, distinct(1)=900, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0] 987 ├── scan new_order 988 │ ├── save-table-name: consistency_08_scan_2 989 │ ├── columns: no_o_id:1(int!null) no_d_id:2(int!null) no_w_id:3(int!null) 990 │ ├── stats: [rows=90000, distinct(1)=900, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0] 991 │ │ histogram(3)= 0 9333 0 9189 0 9261 0 9162 0 9306 0 8442 0 8946 0 8532 0 9135 0 8694 992 │ │ <--- 0 ---- 1 ---- 2 ---- 3 ---- 4 ---- 5 ---- 6 ---- 7 ---- 8 ---- 9 - 993 │ └── key: (1-3) 994 └── project 995 ├── save-table-name: consistency_08_project_3 996 ├── columns: o_id:4(int!null) o_d_id:5(int!null) o_w_id:6(int!null) 997 ├── stats: [rows=90000, distinct(4)=2999, null(4)=0, distinct(5)=10, null(5)=0, distinct(6)=10, null(6)=0] 998 ├── key: (4-6) 999 └── select 1000 ├── save-table-name: consistency_08_select_4 1001 ├── columns: o_id:4(int!null) o_d_id:5(int!null) o_w_id:6(int!null) o_carrier_id:9(int) 1002 ├── stats: [rows=90000, distinct(4)=2999, null(4)=0, distinct(5)=10, null(5)=0, distinct(6)=10, null(6)=0, distinct(9)=1, null(9)=90000] 1003 ├── key: (4-6) 1004 ├── fd: ()-->(9) 1005 ├── scan "order"@order_idx 1006 │ ├── save-table-name: consistency_08_scan_5 1007 │ ├── columns: o_id:4(int!null) o_d_id:5(int!null) o_w_id:6(int!null) o_carrier_id:9(int) 1008 │ ├── stats: [rows=300000, distinct(4)=2999, null(4)=0, distinct(5)=10, null(5)=0, distinct(6)=10, null(6)=0, distinct(9)=11, null(9)=90000] 1009 │ │ histogram(6)= 0 29280 0 29310 0 31560 0 30000 0 29220 0 30720 0 31110 0 30510 0 29070 0 29220 1010 │ │ <---- 0 ----- 1 ----- 2 ----- 3 ----- 4 ----- 5 ----- 6 ----- 7 ----- 8 ----- 9 - 1011 │ ├── key: (4-6) 1012 │ └── fd: (4-6)-->(9) 1013 └── filters 1014 └── o_carrier_id:9 IS NULL [type=bool, outer=(9), constraints=(/9: [/NULL - /NULL]; tight), fd=()-->(9)] 1015 1016 stats table=consistency_08_select_4 1017 ---- 1018 column_names row_count distinct_count null_count 1019 {o_carrier_id} 90000 1 90000 1020 {o_d_id} 90000 10 0 1021 {o_id} 90000 900 0 1022 {o_w_id} 90000 10 0 1023 ~~~~ 1024 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1025 {o_carrier_id} 90000.00 1.00 1.00 1.00 90000.00 1.00 1026 {o_d_id} 90000.00 1.00 10.00 1.00 0.00 1.00 1027 {o_id} 90000.00 1.00 2999.00 3.33 <== 0.00 1.00 1028 {o_w_id} 90000.00 1.00 10.00 1.00 0.00 1.00 1029 1030 stats table=consistency_08_except_all_1 1031 ---- 1032 column_names row_count distinct_count null_count 1033 {no_d_id} 0 0 0 1034 {no_o_id} 0 0 0 1035 {no_w_id} 0 0 0 1036 ~~~~ 1037 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1038 {no_d_id} 90000.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1039 {no_o_id} 90000.00 +Inf <== 900.00 +Inf <== 0.00 1.00 1040 {no_w_id} 90000.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1041 1042 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_09 1043 (SELECT o_w_id, o_d_id, o_id FROM "order" WHERE o_carrier_id IS NULL) 1044 EXCEPT ALL 1045 (SELECT no_w_id, no_d_id, no_o_id FROM new_order) 1046 ---- 1047 except-all 1048 ├── save-table-name: consistency_09_except_all_1 1049 ├── columns: o_w_id:3(int!null) o_d_id:2(int!null) o_id:1(int!null) 1050 ├── left columns: o_w_id:3(int!null) o_d_id:2(int!null) o_id:1(int!null) 1051 ├── right columns: no_w_id:11(int) no_d_id:10(int) no_o_id:9(int) 1052 ├── stats: [rows=90000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0] 1053 ├── project 1054 │ ├── save-table-name: consistency_09_project_2 1055 │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) 1056 │ ├── stats: [rows=90000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0] 1057 │ ├── key: (1-3) 1058 │ └── select 1059 │ ├── save-table-name: consistency_09_select_3 1060 │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) o_carrier_id:6(int) 1061 │ ├── stats: [rows=90000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(6)=1, null(6)=90000] 1062 │ ├── key: (1-3) 1063 │ ├── fd: ()-->(6) 1064 │ ├── scan "order"@order_idx 1065 │ │ ├── save-table-name: consistency_09_scan_4 1066 │ │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) o_carrier_id:6(int) 1067 │ │ ├── stats: [rows=300000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(6)=11, null(6)=90000] 1068 │ │ │ histogram(3)= 0 29280 0 29310 0 31560 0 30000 0 29220 0 30720 0 31110 0 30510 0 29070 0 29220 1069 │ │ │ <---- 0 ----- 1 ----- 2 ----- 3 ----- 4 ----- 5 ----- 6 ----- 7 ----- 8 ----- 9 - 1070 │ │ ├── key: (1-3) 1071 │ │ └── fd: (1-3)-->(6) 1072 │ └── filters 1073 │ └── o_carrier_id:6 IS NULL [type=bool, outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1074 └── scan new_order 1075 ├── save-table-name: consistency_09_scan_5 1076 ├── columns: no_o_id:9(int!null) no_d_id:10(int!null) no_w_id:11(int!null) 1077 ├── stats: [rows=90000, distinct(9)=900, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0] 1078 │ histogram(11)= 0 9333 0 9189 0 9261 0 9162 0 9306 0 8442 0 8946 0 8532 0 9135 0 8694 1079 │ <--- 0 ---- 1 ---- 2 ---- 3 ---- 4 ---- 5 ---- 6 ---- 7 ---- 8 ---- 9 - 1080 └── key: (9-11) 1081 1082 stats table=consistency_09_except_all_1 1083 ---- 1084 column_names row_count distinct_count null_count 1085 {o_d_id} 0 0 0 1086 {o_id} 0 0 0 1087 {o_w_id} 0 0 0 1088 ~~~~ 1089 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1090 {o_d_id} 90000.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1091 {o_id} 90000.00 +Inf <== 2999.00 +Inf <== 0.00 1.00 1092 {o_w_id} 90000.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1093 1094 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_10 1095 ( 1096 SELECT o_w_id, o_d_id, o_id, o_ol_cnt 1097 FROM "order" 1098 ORDER BY o_w_id, o_d_id, o_id DESC 1099 ) 1100 EXCEPT ALL 1101 ( 1102 SELECT ol_w_id, ol_d_id, ol_o_id, count(*) 1103 FROM order_line 1104 GROUP BY (ol_w_id, ol_d_id, ol_o_id) 1105 ORDER BY ol_w_id, ol_d_id, ol_o_id DESC 1106 ) 1107 ---- 1108 except-all 1109 ├── save-table-name: consistency_10_except_all_1 1110 ├── columns: o_w_id:3(int!null) o_d_id:2(int!null) o_id:1(int!null) o_ol_cnt:7(int) 1111 ├── left columns: o_w_id:3(int!null) o_d_id:2(int!null) o_id:1(int!null) o_ol_cnt:7(int) 1112 ├── right columns: ol_w_id:11(int) ol_d_id:10(int) ol_o_id:9(int) count_rows:19(int) 1113 ├── stats: [rows=300000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(7)=11, null(7)=0] 1114 ├── scan "order" 1115 │ ├── save-table-name: consistency_10_scan_2 1116 │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) o_ol_cnt:7(int) 1117 │ ├── stats: [rows=300000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(7)=11, null(7)=0] 1118 │ │ histogram(3)= 0 29280 0 29310 0 31560 0 30000 0 29220 0 30720 0 31110 0 30510 0 29070 0 29220 1119 │ │ <---- 0 ----- 1 ----- 2 ----- 3 ----- 4 ----- 5 ----- 6 ----- 7 ----- 8 ----- 9 - 1120 │ ├── key: (1-3) 1121 │ └── fd: (1-3)-->(7) 1122 └── group-by 1123 ├── save-table-name: consistency_10_group_by_3 1124 ├── columns: ol_o_id:9(int!null) ol_d_id:10(int!null) ol_w_id:11(int!null) count_rows:19(int!null) 1125 ├── grouping columns: ol_o_id:9(int!null) ol_d_id:10(int!null) ol_w_id:11(int!null) 1126 ├── stats: [rows=295745, distinct(9)=2999, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0, distinct(19)=295745, null(19)=0, distinct(9-11)=295745, null(9-11)=0] 1127 ├── key: (9-11) 1128 ├── fd: (9-11)-->(19) 1129 ├── scan order_line@order_line_stock_fk_idx 1130 │ ├── save-table-name: consistency_10_scan_4 1131 │ ├── columns: ol_o_id:9(int!null) ol_d_id:10(int!null) ol_w_id:11(int!null) 1132 │ └── stats: [rows=3001222, distinct(9)=2999, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0, distinct(9-11)=295745, null(9-11)=0] 1133 │ histogram(11)= 0 3.1213e+05 0 2.7851e+05 0 2.9892e+05 0 3.0732e+05 0 2.9892e+05 0 2.9622e+05 0 3.1363e+05 0 2.8392e+05 0 2.9892e+05 0 3.1273e+05 1134 │ <------ 0 ---------- 1 ---------- 2 ---------- 3 ---------- 4 ---------- 5 ---------- 6 ---------- 7 ---------- 8 ---------- 9 ---- 1135 └── aggregations 1136 └── count-rows [as=count_rows:19, type=int] 1137 1138 stats table=consistency_10_group_by_3 1139 ---- 1140 column_names row_count distinct_count null_count 1141 {count_rows} 300000 11 0 1142 {ol_d_id} 300000 10 0 1143 {ol_o_id} 300000 2999 0 1144 {ol_w_id} 300000 10 0 1145 ~~~~ 1146 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1147 {count_rows} 295745.00 1.01 295745.00 26885.91 <== 0.00 1.00 1148 {ol_d_id} 295745.00 1.01 10.00 1.00 0.00 1.00 1149 {ol_o_id} 295745.00 1.01 2999.00 1.00 0.00 1.00 1150 {ol_w_id} 295745.00 1.01 10.00 1.00 0.00 1.00 1151 1152 stats table=consistency_10_except_all_1 1153 ---- 1154 column_names row_count distinct_count null_count 1155 {o_d_id} 0 0 0 1156 {o_id} 0 0 0 1157 {o_ol_cnt} 0 0 0 1158 {o_w_id} 0 0 0 1159 ~~~~ 1160 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1161 {o_d_id} 300000.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1162 {o_id} 300000.00 +Inf <== 2999.00 +Inf <== 0.00 1.00 1163 {o_ol_cnt} 300000.00 +Inf <== 11.00 +Inf <== 0.00 1.00 1164 {o_w_id} 300000.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1165 1166 save-tables format=hide-qual database=tpcc save-tables-prefix=consistency_11 1167 ( 1168 SELECT ol_w_id, ol_d_id, ol_o_id, count(*) 1169 FROM order_line 1170 GROUP BY (ol_w_id, ol_d_id, ol_o_id) 1171 ORDER BY ol_w_id, ol_d_id, ol_o_id DESC 1172 ) 1173 EXCEPT ALL 1174 ( 1175 SELECT o_w_id, o_d_id, o_id, o_ol_cnt 1176 FROM "order" 1177 ORDER BY o_w_id, o_d_id, o_id DESC 1178 ) 1179 ---- 1180 except-all 1181 ├── save-table-name: consistency_11_except_all_1 1182 ├── columns: ol_w_id:3(int!null) ol_d_id:2(int!null) ol_o_id:1(int!null) count:11(int) 1183 ├── left columns: ol_w_id:3(int!null) ol_d_id:2(int!null) ol_o_id:1(int!null) count_rows:11(int) 1184 ├── right columns: o_w_id:14(int) o_d_id:13(int) o_id:12(int) o_ol_cnt:18(int) 1185 ├── stats: [rows=295745, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(11)=295745, null(11)=0] 1186 ├── group-by 1187 │ ├── save-table-name: consistency_11_group_by_2 1188 │ ├── columns: ol_o_id:1(int!null) ol_d_id:2(int!null) ol_w_id:3(int!null) count_rows:11(int!null) 1189 │ ├── grouping columns: ol_o_id:1(int!null) ol_d_id:2(int!null) ol_w_id:3(int!null) 1190 │ ├── stats: [rows=295745, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(11)=295745, null(11)=0, distinct(1-3)=295745, null(1-3)=0] 1191 │ ├── key: (1-3) 1192 │ ├── fd: (1-3)-->(11) 1193 │ ├── scan order_line@order_line_stock_fk_idx 1194 │ │ ├── save-table-name: consistency_11_scan_3 1195 │ │ ├── columns: ol_o_id:1(int!null) ol_d_id:2(int!null) ol_w_id:3(int!null) 1196 │ │ └── stats: [rows=3001222, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(1-3)=295745, null(1-3)=0] 1197 │ │ histogram(3)= 0 3.1213e+05 0 2.7851e+05 0 2.9892e+05 0 3.0732e+05 0 2.9892e+05 0 2.9622e+05 0 3.1363e+05 0 2.8392e+05 0 2.9892e+05 0 3.1273e+05 1198 │ │ <------ 0 ---------- 1 ---------- 2 ---------- 3 ---------- 4 ---------- 5 ---------- 6 ---------- 7 ---------- 8 ---------- 9 ---- 1199 │ └── aggregations 1200 │ └── count-rows [as=count_rows:11, type=int] 1201 └── scan "order" 1202 ├── save-table-name: consistency_11_scan_4 1203 ├── columns: o_id:12(int!null) o_d_id:13(int!null) o_w_id:14(int!null) o_ol_cnt:18(int) 1204 ├── stats: [rows=300000, distinct(12)=2999, null(12)=0, distinct(13)=10, null(13)=0, distinct(14)=10, null(14)=0, distinct(18)=11, null(18)=0] 1205 │ histogram(14)= 0 29280 0 29310 0 31560 0 30000 0 29220 0 30720 0 31110 0 30510 0 29070 0 29220 1206 │ <---- 0 ----- 1 ----- 2 ----- 3 ----- 4 ----- 5 ----- 6 ----- 7 ----- 8 ----- 9 - 1207 ├── key: (12-14) 1208 └── fd: (12-14)-->(18) 1209 1210 stats table=consistency_11_except_all_1 1211 ---- 1212 column_names row_count distinct_count null_count 1213 {count} 0 0 0 1214 {ol_d_id} 0 0 0 1215 {ol_o_id} 0 0 0 1216 {ol_w_id} 0 0 0 1217 ~~~~ 1218 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1219 {count} 295745.00 +Inf <== 295745.00 +Inf <== 0.00 1.00 1220 {ol_d_id} 295745.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1221 {ol_o_id} 295745.00 +Inf <== 2999.00 +Inf <== 0.00 1.00 1222 {ol_w_id} 295745.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1223 1224 save-tables database=tpcc save-tables-prefix=consistency_12 1225 SELECT count(*) 1226 FROM 1227 ( 1228 SELECT o_w_id, o_d_id, o_id 1229 FROM "order" 1230 WHERE o_carrier_id IS NULL 1231 ) 1232 FULL OUTER JOIN 1233 ( 1234 SELECT ol_w_id, ol_d_id, ol_o_id 1235 FROM order_line 1236 WHERE ol_delivery_d IS NULL 1237 ) 1238 ON (ol_w_id = o_w_id AND ol_d_id = o_d_id AND ol_o_id = o_id) 1239 WHERE ol_o_id IS NULL OR o_id IS NULL 1240 ---- 1241 scalar-group-by 1242 ├── save-table-name: consistency_12_scalar_group_by_1 1243 ├── columns: count:19(int!null) 1244 ├── cardinality: [1 - 1] 1245 ├── stats: [rows=1, distinct(19)=1, null(19)=0] 1246 ├── key: () 1247 ├── fd: ()-->(19) 1248 ├── select 1249 │ ├── save-table-name: consistency_12_select_2 1250 │ ├── columns: o_id:1(int) o_d_id:2(int) o_w_id:3(int) ol_o_id:9(int) ol_d_id:10(int) ol_w_id:11(int) 1251 │ ├── stats: [rows=299711.333, distinct(1)=2999, null(1)=209767.952, distinct(2)=10, null(2)=209767.952, distinct(3)=10, null(3)=209767.952, distinct(9)=2999, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0] 1252 │ ├── full-join (hash) 1253 │ │ ├── save-table-name: consistency_12_full_join_3 1254 │ │ ├── columns: o_id:1(int) o_d_id:2(int) o_w_id:3(int) ol_o_id:9(int) ol_d_id:10(int) ol_w_id:11(int) 1255 │ │ ├── stats: [rows=899134, distinct(1)=2999, null(1)=629303.857, distinct(2)=10, null(2)=629303.857, distinct(3)=10, null(3)=629303.857, distinct(9)=2999, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0] 1256 │ │ ├── project 1257 │ │ │ ├── save-table-name: consistency_12_project_4 1258 │ │ │ ├── columns: ol_o_id:9(int!null) ol_d_id:10(int!null) ol_w_id:11(int!null) 1259 │ │ │ ├── stats: [rows=899134, distinct(9)=2999, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0] 1260 │ │ │ └── select 1261 │ │ │ ├── save-table-name: consistency_12_select_5 1262 │ │ │ ├── columns: ol_o_id:9(int!null) ol_d_id:10(int!null) ol_w_id:11(int!null) ol_delivery_d:15(timestamp) 1263 │ │ │ ├── stats: [rows=899134, distinct(9)=2999, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0, distinct(15)=1, null(15)=899134] 1264 │ │ │ ├── fd: ()-->(15) 1265 │ │ │ ├── scan order_line 1266 │ │ │ │ ├── save-table-name: consistency_12_scan_6 1267 │ │ │ │ ├── columns: ol_o_id:9(int!null) ol_d_id:10(int!null) ol_w_id:11(int!null) ol_delivery_d:15(timestamp) 1268 │ │ │ │ └── stats: [rows=3001222, distinct(9)=2999, null(9)=0, distinct(10)=10, null(10)=0, distinct(11)=10, null(11)=0, distinct(15)=2, null(15)=899134] 1269 │ │ │ │ histogram(11)= 0 3.1213e+05 0 2.7851e+05 0 2.9892e+05 0 3.0732e+05 0 2.9892e+05 0 2.9622e+05 0 3.1363e+05 0 2.8392e+05 0 2.9892e+05 0 3.1273e+05 1270 │ │ │ │ <------ 0 ---------- 1 ---------- 2 ---------- 3 ---------- 4 ---------- 5 ---------- 6 ---------- 7 ---------- 8 ---------- 9 ---- 1271 │ │ │ └── filters 1272 │ │ │ └── ol_delivery_d:15 IS NULL [type=bool, outer=(15), constraints=(/15: [/NULL - /NULL]; tight), fd=()-->(15)] 1273 │ │ ├── project 1274 │ │ │ ├── save-table-name: consistency_12_project_7 1275 │ │ │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) 1276 │ │ │ ├── stats: [rows=90000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0] 1277 │ │ │ ├── key: (1-3) 1278 │ │ │ └── select 1279 │ │ │ ├── save-table-name: consistency_12_select_8 1280 │ │ │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) o_carrier_id:6(int) 1281 │ │ │ ├── stats: [rows=90000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(6)=1, null(6)=90000] 1282 │ │ │ ├── key: (1-3) 1283 │ │ │ ├── fd: ()-->(6) 1284 │ │ │ ├── scan "order"@order_idx 1285 │ │ │ │ ├── save-table-name: consistency_12_scan_9 1286 │ │ │ │ ├── columns: o_id:1(int!null) o_d_id:2(int!null) o_w_id:3(int!null) o_carrier_id:6(int) 1287 │ │ │ │ ├── stats: [rows=300000, distinct(1)=2999, null(1)=0, distinct(2)=10, null(2)=0, distinct(3)=10, null(3)=0, distinct(6)=11, null(6)=90000] 1288 │ │ │ │ │ histogram(3)= 0 29280 0 29310 0 31560 0 30000 0 29220 0 30720 0 31110 0 30510 0 29070 0 29220 1289 │ │ │ │ │ <---- 0 ----- 1 ----- 2 ----- 3 ----- 4 ----- 5 ----- 6 ----- 7 ----- 8 ----- 9 - 1290 │ │ │ │ ├── key: (1-3) 1291 │ │ │ │ └── fd: (1-3)-->(6) 1292 │ │ │ └── filters 1293 │ │ │ └── o_carrier_id:6 IS NULL [type=bool, outer=(6), constraints=(/6: [/NULL - /NULL]; tight), fd=()-->(6)] 1294 │ │ └── filters 1295 │ │ ├── ol_w_id:11 = o_w_id:3 [type=bool, outer=(3,11), constraints=(/3: (/NULL - ]; /11: (/NULL - ]), fd=(3)==(11), (11)==(3)] 1296 │ │ ├── ol_d_id:10 = o_d_id:2 [type=bool, outer=(2,10), constraints=(/2: (/NULL - ]; /10: (/NULL - ]), fd=(2)==(10), (10)==(2)] 1297 │ │ └── ol_o_id:9 = o_id:1 [type=bool, outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 1298 │ └── filters 1299 │ └── (ol_o_id:9 IS NULL) OR (o_id:1 IS NULL) [type=bool, outer=(1,9)] 1300 └── aggregations 1301 └── count-rows [as=count_rows:19, type=int] 1302 1303 stats table=consistency_12_select_5 1304 ---- 1305 column_names row_count distinct_count null_count 1306 {ol_d_id} 899134 10 0 1307 {ol_delivery_d} 899134 1 899134 1308 {ol_o_id} 899134 900 0 1309 {ol_w_id} 899134 10 0 1310 ~~~~ 1311 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1312 {ol_d_id} 899134.00 1.00 10.00 1.00 0.00 1.00 1313 {ol_delivery_d} 899134.00 1.00 1.00 1.00 899134.00 1.00 1314 {ol_o_id} 899134.00 1.00 2999.00 3.33 <== 0.00 1.00 1315 {ol_w_id} 899134.00 1.00 10.00 1.00 0.00 1.00 1316 1317 stats table=consistency_12_select_8 1318 ---- 1319 column_names row_count distinct_count null_count 1320 {o_carrier_id} 90000 1 90000 1321 {o_d_id} 90000 10 0 1322 {o_id} 90000 900 0 1323 {o_w_id} 90000 10 0 1324 ~~~~ 1325 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1326 {o_carrier_id} 90000.00 1.00 1.00 1.00 90000.00 1.00 1327 {o_d_id} 90000.00 1.00 10.00 1.00 0.00 1.00 1328 {o_id} 90000.00 1.00 2999.00 3.33 <== 0.00 1.00 1329 {o_w_id} 90000.00 1.00 10.00 1.00 0.00 1.00 1330 1331 stats table=consistency_12_full_join_3 1332 ---- 1333 column_names row_count distinct_count null_count 1334 {o_d_id} 899134 10 0 1335 {o_id} 899134 900 0 1336 {o_w_id} 899134 10 0 1337 {ol_d_id} 899134 10 0 1338 {ol_o_id} 899134 900 0 1339 {ol_w_id} 899134 10 0 1340 ~~~~ 1341 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1342 {o_d_id} 899134.00 1.00 10.00 1.00 629304.00 +Inf <== 1343 {o_id} 899134.00 1.00 2999.00 3.33 <== 629304.00 +Inf <== 1344 {o_w_id} 899134.00 1.00 10.00 1.00 629304.00 +Inf <== 1345 {ol_d_id} 899134.00 1.00 10.00 1.00 0.00 1.00 1346 {ol_o_id} 899134.00 1.00 2999.00 3.33 <== 0.00 1.00 1347 {ol_w_id} 899134.00 1.00 10.00 1.00 0.00 1.00 1348 1349 stats table=consistency_12_select_2 1350 ---- 1351 column_names row_count distinct_count null_count 1352 {o_d_id} 0 0 0 1353 {o_id} 0 0 0 1354 {o_w_id} 0 0 0 1355 {ol_d_id} 0 0 0 1356 {ol_o_id} 0 0 0 1357 {ol_w_id} 0 0 0 1358 ~~~~ 1359 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1360 {o_d_id} 299711.00 +Inf <== 10.00 +Inf <== 209768.00 +Inf <== 1361 {o_id} 299711.00 +Inf <== 2999.00 +Inf <== 209768.00 +Inf <== 1362 {o_w_id} 299711.00 +Inf <== 10.00 +Inf <== 209768.00 +Inf <== 1363 {ol_d_id} 299711.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1364 {ol_o_id} 299711.00 +Inf <== 2999.00 +Inf <== 0.00 1.00 1365 {ol_w_id} 299711.00 +Inf <== 10.00 +Inf <== 0.00 1.00 1366 1367 stats table=consistency_12_scalar_group_by_1 1368 ---- 1369 column_names row_count distinct_count null_count 1370 {count} 1 1 0 1371 ~~~~ 1372 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 1373 {count} 1.00 1.00 1.00 1.00 0.00 1.00