github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q12 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q12 9 # Shipping Modes and Order Priority 10 # Determines whether selecting less expensive modes of shipping is negatively 11 # affecting the critical-priority orders by causing more parts to be received by 12 # customers after the committed date. 13 # 14 # Counts, by ship mode, for lineitems actually received by customers in a given 15 # year, the number of lineitems belonging to orders for which the l_receiptdate 16 # exceeds the l_commitdate for two different specified ship modes. Only 17 # lineitems that were actually shipped before the l_commitdate are considered. 18 # The late lineitems are partitioned into two groups, those with priority URGENT 19 # or HIGH, and those with a priority other than URGENT or HIGH. 20 # -------------------------------------------------- 21 save-tables database=tpch save-tables-prefix=q12 22 SELECT 23 l_shipmode, 24 sum(CASE 25 WHEN o_orderpriority = '1-URGENT' 26 OR o_orderpriority = '2-HIGH' 27 THEN 1 28 ELSE 0 29 END) AS high_line_count, 30 sum(CASE 31 WHEN o_orderpriority <> '1-URGENT' 32 AND o_orderpriority <> '2-HIGH' 33 THEN 1 34 ELSE 0 35 END) AS low_line_count 36 FROM 37 orders, 38 lineitem 39 WHERE 40 o_orderkey = l_orderkey 41 AND l_shipmode IN ('MAIL', 'SHIP') 42 AND l_commitdate < l_receiptdate 43 AND l_shipdate < l_commitdate 44 AND l_receiptdate >= DATE '1994-01-01' 45 AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR 46 GROUP BY 47 l_shipmode 48 ORDER BY 49 l_shipmode; 50 ---- 51 sort 52 ├── save-table-name: q12_sort_1 53 ├── columns: l_shipmode:24(char!null) high_line_count:27(decimal!null) low_line_count:29(decimal!null) 54 ├── stats: [rows=2, distinct(24)=2, null(24)=0, distinct(27)=2, null(27)=0, distinct(29)=2, null(29)=0] 55 ├── key: (24) 56 ├── fd: (24)-->(27,29) 57 ├── ordering: +24 58 └── group-by 59 ├── save-table-name: q12_group_by_2 60 ├── columns: l_shipmode:24(char!null) sum:27(decimal!null) sum:29(decimal!null) 61 ├── grouping columns: l_shipmode:24(char!null) 62 ├── stats: [rows=2, distinct(24)=2, null(24)=0, distinct(27)=2, null(27)=0, distinct(29)=2, null(29)=0] 63 ├── key: (24) 64 ├── fd: (24)-->(27,29) 65 ├── project 66 │ ├── save-table-name: q12_project_3 67 │ ├── columns: column26:26(int!null) column28:28(int!null) l_shipmode:24(char!null) 68 │ ├── stats: [rows=29822.8417, distinct(24)=2, null(24)=0, distinct(26)=5, null(26)=0, distinct(28)=5, null(28)=0] 69 │ ├── inner-join (lookup orders) 70 │ │ ├── save-table-name: q12_lookup_join_4 71 │ │ ├── columns: o_orderkey:1(int!null) o_orderpriority:6(char!null) l_orderkey:10(int!null) l_shipdate:20(date!null) l_commitdate:21(date!null) l_receiptdate:22(date!null) l_shipmode:24(char!null) 72 │ │ ├── key columns: [10] = [1] 73 │ │ ├── lookup columns are key 74 │ │ ├── stats: [rows=29822.8417, distinct(1)=29606.4627, null(1)=0, distinct(6)=5, null(6)=0, distinct(10)=29606.4627, null(10)=0, distinct(20)=2525.98116, null(20)=0, distinct(21)=2465.9862, null(21)=0, distinct(22)=365, null(22)=0, distinct(24)=2, null(24)=0] 75 │ │ ├── fd: (1)-->(6), (1)==(10), (10)==(1) 76 │ │ ├── select 77 │ │ │ ├── save-table-name: q12_select_5 78 │ │ │ ├── columns: l_orderkey:10(int!null) l_shipdate:20(date!null) l_commitdate:21(date!null) l_receiptdate:22(date!null) l_shipmode:24(char!null) 79 │ │ │ ├── stats: [rows=29822.8417, distinct(10)=29606.4627, null(10)=0, distinct(20)=2526, null(20)=0, distinct(21)=2466, null(21)=0, distinct(22)=365, null(22)=0, distinct(24)=2, null(24)=0, distinct(22,24)=730, null(22,24)=0] 80 │ │ │ │ histogram(22)= 0 0 826.55 57.145 876.38 95.242 895.43 38.097 895.43 76.193 876.38 95.242 914.48 57.145 838.29 133.34 914.48 57.145 781.11 152.39 914.48 38.097 857.33 95.242 838.29 228.61 819.24 152.39 914.48 133.34 914.48 114.29 800.19 133.34 857.33 152.39 876.38 114.29 895.43 133.34 914.48 57.145 857.33 76.193 914.48 57.145 857.33 95.242 895.43 76.193 895.43 76.193 857.33 76.193 819.24 114.29 838.29 114.29 914.48 114.29 819.24 133.34 609.65 76.207 81 │ │ │ │ <--- '1993-12-31' -------- '1994-01-13' -------- '1994-01-26' -------- '1994-02-08' -------- '1994-02-19' -------- '1994-03-04' -------- '1994-03-14' -------- '1994-03-26' -------- '1994-04-08' -------- '1994-04-21' -------- '1994-05-03' -------- '1994-05-14' -------- '1994-05-24' -------- '1994-06-03' -------- '1994-06-15' -------- '1994-06-27' -------- '1994-07-07' -------- '1994-07-16' -------- '1994-07-26' -------- '1994-08-10' -------- '1994-08-22' -------- '1994-09-03' -------- '1994-09-15' -------- '1994-09-27' -------- '1994-10-11' -------- '1994-10-22' -------- '1994-11-02' -------- '1994-11-14' -------- '1994-11-26' -------- '1994-12-10' -------- '1994-12-22' -------- '1994-12-31' 82 │ │ │ ├── index-join lineitem 83 │ │ │ │ ├── save-table-name: q12_index_join_6 84 │ │ │ │ ├── columns: l_orderkey:10(int!null) l_shipdate:20(date!null) l_commitdate:21(date!null) l_receiptdate:22(date!null) l_shipmode:24(char!null) 85 │ │ │ │ ├── stats: [rows=939419.512, distinct(10)=744923.472, null(10)=0, distinct(20)=2526, null(20)=0, distinct(21)=2466, null(21)=0, distinct(22)=365, null(22)=0, distinct(24)=7, null(24)=0] 86 │ │ │ │ └── scan lineitem@l_rd 87 │ │ │ │ ├── save-table-name: q12_scan_7 88 │ │ │ │ ├── columns: l_orderkey:10(int!null) l_linenumber:13(int!null) l_receiptdate:22(date!null) 89 │ │ │ │ ├── constraint: /22/10/13: [/'1994-01-01' - /'1994-12-31'] 90 │ │ │ │ ├── stats: [rows=939419.512, distinct(10)=744923.472, null(10)=0, distinct(13)=7, null(13)=0, distinct(22)=365, null(22)=0] 91 │ │ │ │ │ histogram(10)= 0 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4603 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 4697.1 93.923 92 │ │ │ │ │ <--- 326 ------- 28929 ------ 50503 ------ 89793 ------ 115938 ------ 146944 ------ 176768 ------ 211201 ------ 237860 ------ 266885 ------ 297604 ------ 330021 ------ 365889 ------ 398951 ------ 426117 ------ 451328 ------ 472134 ------ 499590 ------ 529284 ------ 557254 ------ 589154 ------ 619394 ------ 642951 ------ 670113 ------ 692931 ------ 721157 ------ 751687 ------ 777766 ------ 804582 ------ 836740 ------ 868868 ------ 898912 ------ 922500 ------ 946403 ------ 984870 ------ 1007936 ------ 1030117 ------ 1062275 ------ 1093572 ------ 1120709 ------ 1150981 ------ 1182786 ------ 1206406 ------ 1234116 ------ 1260961 ------ 1290502 ------ 1329510 ------ 1355426 ------ 1381313 ------ 1409796 ------ 1445254 ------ 1479233 ------ 1504935 ------ 1531079 ------ 1559650 ------ 1583616 ------ 1617504 ------ 1655749 ------ 1685185 ------ 1718183 ------ 1747716 ------ 1772131 ------ 1802372 ------ 1833315 ------ 1862403 ------ 1897894 ------ 1922819 ------ 1954405 ------ 1979329 ------ 2009859 ------ 2041670 ------ 2070851 ------ 2093828 ------ 2127973 ------ 2167777 ------ 2194883 ------ 2227814 ------ 2262437 ------ 2296353 ------ 2321024 ------ 2346051 ------ 2376257 ------ 2404932 ------ 2446273 ------ 2474081 ------ 2504515 ------ 2535302 ------ 2561413 ------ 2592737 ------ 2616801 ------ 2646112 ------ 2676546 ------ 2702116 ------ 2732454 ------ 2765382 ------ 2799495 ------ 2828866 ------ 2868737 ------ 2910625 ------ 2938464 ------ 2963140 ------ 3003302 ------ 3043264 ------ 3069123 ------ 3095909 ------ 3126693 ------ 3160485 ------ 3196039 ------ 3229504 ------ 3259712 ------ 3286439 ------ 3318852 ------ 3346821 ------ 3370119 ------ 3395204 ------ 3425888 ------ 3448611 ------ 3476130 ------ 3502372 ------ 3529474 ------ 3556390 ------ 3583553 ------ 3612550 ------ 3647875 ------ 3679140 ------ 3702661 ------ 3738017 ------ 3778050 ------ 3806114 ------ 3839074 ------ 3872805 ------ 3905697 ------ 3926212 ------ 3959841 ------ 3997281 ------ 4033861 ------ 4063591 ------ 4097831 ------ 4124807 ------ 4158656 ------ 4195748 ------ 4234274 ------ 4269952 ------ 4298949 ------ 4332806 ------ 4364705 ------ 4398246 ------ 4430695 ------ 4466403 ------ 4494662 ------ 4524420 -------- 4558561 -------- 4601092 -------- 4632871 -------- 4658694 -------- 4690501 -------- 4728066 -------- 4758657 -------- 4788294 -------- 4818597 -------- 4855874 -------- 4890913 -------- 4915366 -------- 4940709 -------- 4972357 -------- 4995298 -------- 5019523 -------- 5043329 -------- 5077376 -------- 5109920 -------- 5136582 -------- 5161152 -------- 5191846 -------- 5219973 -------- 5251015 -------- 5282021 -------- 5312355 -------- 5343207 -------- 5381318 -------- 5416163 -------- 5445382 -------- 5476933 -------- 5509185 -------- 5539237 -------- 5566818 -------- 5588739 -------- 5620481 -------- 5644001 -------- 5667010 -------- 5689476 -------- 5724709 -------- 5755398 -------- 5790598 -------- 5819425 -------- 5846341 -------- 5874656 -------- 5908067 -------- 5933572 -------- 5962659 -------- 5999971 93 │ │ │ │ │ histogram(22)= 0 0 26035 1800 27605 3000 28205 1200 28205 2400 27605 3000 28805 1800 26405 4200 28805 1800 24604 4800 28805 1200 27005 3000 26405 7201 25805 4800 28805 4200 28805 3600 25205 4200 27005 4800 27605 3600 28205 4200 28805 1800 27005 2400 28805 1800 27005 3000 28205 2400 28205 2400 27005 2400 25805 3600 26405 3600 28805 3600 25805 4200 19203 2400.4 94 │ │ │ │ │ <--- '1993-12-31' ------- '1994-01-13' ------- '1994-01-26' ------- '1994-02-08' ------- '1994-02-19' ------- '1994-03-04' ------- '1994-03-14' ------- '1994-03-26' ------- '1994-04-08' ------- '1994-04-21' ------- '1994-05-03' ------- '1994-05-14' ------- '1994-05-24' ------- '1994-06-03' ------- '1994-06-15' ------- '1994-06-27' ------- '1994-07-07' ------- '1994-07-16' ------- '1994-07-26' ------- '1994-08-10' ------- '1994-08-22' ------- '1994-09-03' ------- '1994-09-15' ------- '1994-09-27' ------- '1994-10-11' ------- '1994-10-22' ------- '1994-11-02' ------- '1994-11-14' ------- '1994-11-26' ------- '1994-12-10' ------- '1994-12-22' ------- '1994-12-31' 95 │ │ │ │ ├── key: (10,13) 96 │ │ │ │ └── fd: (10,13)-->(22) 97 │ │ │ └── filters 98 │ │ │ ├── l_shipmode:24 IN ('MAIL', 'SHIP') [type=bool, outer=(24), constraints=(/24: [/'MAIL' - /'MAIL'] [/'SHIP' - /'SHIP']; tight)] 99 │ │ │ ├── l_commitdate:21 < l_receiptdate:22 [type=bool, outer=(21,22), constraints=(/21: (/NULL - ]; /22: (/NULL - ])] 100 │ │ │ └── l_shipdate:20 < l_commitdate:21 [type=bool, outer=(20,21), constraints=(/20: (/NULL - ]; /21: (/NULL - ])] 101 │ │ └── filters (true) 102 │ └── projections 103 │ ├── CASE WHEN (o_orderpriority:6 = '1-URGENT') OR (o_orderpriority:6 = '2-HIGH') THEN 1 ELSE 0 END [as=column26:26, type=int, outer=(6)] 104 │ └── CASE WHEN (o_orderpriority:6 != '1-URGENT') AND (o_orderpriority:6 != '2-HIGH') THEN 1 ELSE 0 END [as=column28:28, type=int, outer=(6)] 105 └── aggregations 106 ├── sum [as=sum:27, type=decimal, outer=(26)] 107 │ └── column26:26 [type=int] 108 └── sum [as=sum:29, type=decimal, outer=(28)] 109 └── column28:28 [type=int] 110 111 stats table=q12_sort_1 112 ---- 113 column_names row_count distinct_count null_count 114 {high_line_count} 2 2 0 115 {l_shipmode} 2 2 0 116 {low_line_count} 2 2 0 117 ~~~~ 118 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 119 {high_line_count} 2.00 1.00 2.00 1.00 0.00 1.00 120 {l_shipmode} 2.00 1.00 2.00 1.00 0.00 1.00 121 {low_line_count} 2.00 1.00 2.00 1.00 0.00 1.00 122 123 stats table=q12_group_by_2 124 ---- 125 column_names row_count distinct_count null_count 126 {l_shipmode} 2 2 0 127 {sum_1} 2 2 0 128 {sum} 2 2 0 129 ~~~~ 130 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 131 {l_shipmode} 2.00 1.00 2.00 1.00 0.00 1.00 132 {sum} 2.00 1.00 2.00 1.00 0.00 1.00 133 {sum_1} 2.00 1.00 2.00 1.00 0.00 1.00 134 135 stats table=q12_project_3 136 ---- 137 column_names row_count distinct_count null_count 138 {column26} 30988 2 0 139 {column28} 30988 2 0 140 {l_shipmode} 30988 2 0 141 ~~~~ 142 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 143 {column26} 29823.00 1.04 5.00 2.50 <== 0.00 1.00 144 {column28} 29823.00 1.04 5.00 2.50 <== 0.00 1.00 145 {l_shipmode} 29823.00 1.04 2.00 1.00 0.00 1.00 146 147 stats table=q12_lookup_join_4 148 ---- 149 column_names row_count distinct_count null_count 150 {l_commitdate} 30988 392 0 151 {l_orderkey} 30988 28828 0 152 {l_receiptdate} 30988 365 0 153 {l_shipdate} 30988 391 0 154 {l_shipmode} 30988 2 0 155 {o_orderkey} 30988 28828 0 156 {o_orderpriority} 30988 5 0 157 ~~~~ 158 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 159 {l_commitdate} 29823.00 1.04 2466.00 6.29 <== 0.00 1.00 160 {l_orderkey} 29823.00 1.04 29606.00 1.03 0.00 1.00 161 {l_receiptdate} 29823.00 1.04 365.00 1.00 0.00 1.00 162 {l_shipdate} 29823.00 1.04 2526.00 6.46 <== 0.00 1.00 163 {l_shipmode} 29823.00 1.04 2.00 1.00 0.00 1.00 164 {o_orderkey} 29823.00 1.04 29606.00 1.03 0.00 1.00 165 {o_orderpriority} 29823.00 1.04 5.00 1.00 0.00 1.00 166 167 stats table=q12_select_5 168 ---- 169 column_names row_count distinct_count null_count 170 {l_commitdate} 30988 392 0 171 {l_orderkey} 30988 28828 0 172 {l_receiptdate} 30988 365 0 173 {l_shipdate} 30988 391 0 174 {l_shipmode} 30988 2 0 175 ~~~~ 176 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 177 {l_commitdate} 29823.00 1.04 2466.00 6.29 <== 0.00 1.00 178 {l_orderkey} 29823.00 1.04 29606.00 1.03 0.00 1.00 179 {l_receiptdate} 29823.00 1.04 365.00 1.00 0.00 1.00 180 {l_shipdate} 29823.00 1.04 2526.00 6.46 <== 0.00 1.00 181 {l_shipmode} 29823.00 1.04 2.00 1.00 0.00 1.00 182 183 stats table=q12_index_join_6 184 ---- 185 column_names row_count distinct_count null_count 186 {l_commitdate} 909844 560 0 187 {l_orderkey} 909844 267788 0 188 {l_receiptdate} 909844 365 0 189 {l_shipdate} 909844 394 0 190 {l_shipmode} 909844 7 0 191 ~~~~ 192 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 193 {l_commitdate} 939420.00 1.03 2466.00 4.40 <== 0.00 1.00 194 {l_orderkey} 939420.00 1.03 744923.00 2.78 <== 0.00 1.00 195 {l_receiptdate} 939420.00 1.03 365.00 1.00 0.00 1.00 196 {l_shipdate} 939420.00 1.03 2526.00 6.41 <== 0.00 1.00 197 {l_shipmode} 939420.00 1.03 7.00 1.00 0.00 1.00