github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q17 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q17 9 # Small-Quantity-Order Revenue 10 # Determines how much average yearly revenue would be lost if orders were no 11 # longer filled for small quantities of certain parts. This may reduce overhead 12 # expenses by concentrating sales on larger shipments. 13 # 14 # Considers parts of a given brand and with a given container type and 15 # determines the average lineitem quantity of such parts ordered for all orders 16 # (past and pending) in the 7-year database. What would be the average yearly 17 # gross (undiscounted) loss in revenue if orders for these parts with a quantity 18 # of less than 20% of this average were no longer taken? 19 # 20 # TODO: 21 # 1. Allow Select to be pushed below Ordinality used to add key column 22 # -------------------------------------------------- 23 save-tables database=tpch save-tables-prefix=q17 24 SELECT 25 sum(l_extendedprice) / 7.0 AS avg_yearly 26 FROM 27 lineitem, 28 part 29 WHERE 30 p_partkey = l_partkey 31 AND p_brand = 'Brand#23' 32 AND p_container = 'MED BOX' 33 AND l_quantity < ( 34 SELECT 35 0.2 * avg(l_quantity) 36 FROM 37 lineitem 38 WHERE 39 l_partkey = p_partkey 40 ); 41 ---- 42 project 43 ├── save-table-name: q17_project_1 44 ├── columns: avg_yearly:45(float) 45 ├── cardinality: [1 - 1] 46 ├── stats: [rows=1, distinct(45)=1, null(45)=0] 47 ├── key: () 48 ├── fd: ()-->(45) 49 ├── scalar-group-by 50 │ ├── save-table-name: q17_scalar_group_by_2 51 │ ├── columns: sum:44(float) 52 │ ├── cardinality: [1 - 1] 53 │ ├── stats: [rows=1, distinct(44)=1, null(44)=0] 54 │ ├── key: () 55 │ ├── fd: ()-->(44) 56 │ ├── inner-join (lookup lineitem) 57 │ │ ├── save-table-name: q17_lookup_join_3 58 │ │ ├── columns: l_partkey:2(int!null) l_quantity:5(float!null) l_extendedprice:6(float!null) p_partkey:17(int!null) "?column?":43(float!null) 59 │ │ ├── key columns: [1 4] = [1 4] 60 │ │ ├── lookup columns are key 61 │ │ ├── stats: [rows=2008.02163, distinct(2)=199.999619, null(2)=0, distinct(5)=50, null(5)=0, distinct(6)=2005.84759, null(6)=0, distinct(17)=199.999619, null(17)=0, distinct(43)=199.999619, null(43)=0] 62 │ │ ├── fd: (17)-->(43), (2)==(17), (17)==(2) 63 │ │ ├── inner-join (lookup lineitem@l_pk) 64 │ │ │ ├── save-table-name: q17_lookup_join_4 65 │ │ │ ├── columns: l_orderkey:1(int!null) l_partkey:2(int!null) l_linenumber:4(int!null) p_partkey:17(int!null) "?column?":43(float) 66 │ │ │ ├── key columns: [17] = [2] 67 │ │ │ ├── stats: [rows=6024.06489, distinct(1)=6012.21509, null(1)=0, distinct(2)=199.999619, null(2)=0, distinct(4)=7, null(4)=0, distinct(17)=199.999619, null(17)=0, distinct(43)=199.999619, null(43)=0] 68 │ │ │ ├── key: (1,4) 69 │ │ │ ├── fd: (17)-->(43), (1,4)-->(2), (2)==(17), (17)==(2) 70 │ │ │ ├── project 71 │ │ │ │ ├── save-table-name: q17_project_5 72 │ │ │ │ ├── columns: "?column?":43(float) p_partkey:17(int!null) 73 │ │ │ │ ├── stats: [rows=199.999619, distinct(17)=199.999619, null(17)=0, distinct(43)=199.999619, null(43)=0] 74 │ │ │ │ ├── key: (17) 75 │ │ │ │ ├── fd: (17)-->(43) 76 │ │ │ │ ├── group-by 77 │ │ │ │ │ ├── save-table-name: q17_group_by_6 78 │ │ │ │ │ ├── columns: p_partkey:17(int!null) avg:42(float) 79 │ │ │ │ │ ├── grouping columns: p_partkey:17(int!null) 80 │ │ │ │ │ ├── internal-ordering: +17 opt(20,23) 81 │ │ │ │ │ ├── stats: [rows=199.999619, distinct(17)=199.999619, null(17)=0, distinct(42)=199.999619, null(42)=0] 82 │ │ │ │ │ ├── key: (17) 83 │ │ │ │ │ ├── fd: (17)-->(42) 84 │ │ │ │ │ ├── left-join (lookup lineitem) 85 │ │ │ │ │ │ ├── save-table-name: q17_lookup_join_7 86 │ │ │ │ │ │ ├── columns: p_partkey:17(int!null) p_brand:20(char!null) p_container:23(char!null) l_partkey:27(int) l_quantity:30(float) 87 │ │ │ │ │ │ ├── key columns: [26 29] = [26 29] 88 │ │ │ │ │ │ ├── lookup columns are key 89 │ │ │ │ │ │ ├── stats: [rows=6024.07637, distinct(17)=199.999619, null(17)=0, distinct(20)=1, null(20)=0, distinct(23)=1, null(23)=0, distinct(27)=199.999619, null(27)=0, distinct(30)=50, null(30)=0] 90 │ │ │ │ │ │ ├── fd: ()-->(20,23) 91 │ │ │ │ │ │ ├── ordering: +17 opt(20,23) [actual: +17] 92 │ │ │ │ │ │ ├── left-join (lookup lineitem@l_pk) 93 │ │ │ │ │ │ │ ├── save-table-name: q17_lookup_join_8 94 │ │ │ │ │ │ │ ├── columns: p_partkey:17(int!null) p_brand:20(char!null) p_container:23(char!null) l_orderkey:26(int) l_partkey:27(int) l_linenumber:29(int) 95 │ │ │ │ │ │ │ ├── key columns: [17] = [27] 96 │ │ │ │ │ │ │ ├── stats: [rows=6024.07637, distinct(17)=199.999619, null(17)=0, distinct(20)=1, null(20)=0, distinct(23)=1, null(23)=0, distinct(26)=6012.22652, null(26)=0, distinct(27)=199.999619, null(27)=0, distinct(29)=7, null(29)=0] 97 │ │ │ │ │ │ │ ├── key: (17,26,29) 98 │ │ │ │ │ │ │ ├── fd: ()-->(20,23), (26,29)-->(27) 99 │ │ │ │ │ │ │ ├── ordering: +17 opt(20,23) [actual: +17] 100 │ │ │ │ │ │ │ ├── select 101 │ │ │ │ │ │ │ │ ├── save-table-name: q17_select_9 102 │ │ │ │ │ │ │ │ ├── columns: p_partkey:17(int!null) p_brand:20(char!null) p_container:23(char!null) 103 │ │ │ │ │ │ │ │ ├── stats: [rows=200, distinct(17)=199.999619, null(17)=0, distinct(20)=1, null(20)=0, distinct(23)=1, null(23)=0, distinct(20,23)=1, null(20,23)=0] 104 │ │ │ │ │ │ │ │ ├── key: (17) 105 │ │ │ │ │ │ │ │ ├── fd: ()-->(20,23) 106 │ │ │ │ │ │ │ │ ├── ordering: +17 opt(20,23) [actual: +17] 107 │ │ │ │ │ │ │ │ ├── scan part 108 │ │ │ │ │ │ │ │ │ ├── save-table-name: q17_scan_10 109 │ │ │ │ │ │ │ │ │ ├── columns: p_partkey:17(int!null) p_brand:20(char!null) p_container:23(char!null) 110 │ │ │ │ │ │ │ │ │ ├── stats: [rows=200000, distinct(17)=199241, null(17)=0, distinct(20)=25, null(20)=0, distinct(23)=40, null(23)=0, distinct(20,23)=1000, null(20,23)=0] 111 │ │ │ │ │ │ │ │ │ │ histogram(17)= 0 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 980 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 1000 20 112 │ │ │ │ │ │ │ │ │ │ <--- 27 ----- 1110 ----- 2241 ----- 3086 ----- 4134 ----- 5302 ----- 6222 ----- 7308 ----- 8249 ----- 9171 ----- 10049 ----- 10958 ----- 11832 ----- 13025 ----- 14063 ----- 14953 ----- 16249 ----- 17419 ----- 18363 ----- 19412 ----- 20257 ----- 21190 ----- 22110 ----- 23045 ----- 23956 ----- 24962 ----- 25942 ----- 26990 ----- 27934 ----- 28876 ----- 29513 ----- 30326 ----- 31259 ----- 32300 ----- 33577 ----- 34550 ----- 35562 ----- 36498 ----- 37475 ----- 38584 ----- 39641 ----- 40548 ----- 41605 ----- 42527 ----- 43612 ----- 44702 ----- 45701 ----- 46726 ----- 47795 ----- 48935 ----- 50152 ----- 51183 ----- 52001 ----- 52904 ----- 53868 ----- 54808 ----- 55986 ----- 57155 ----- 58516 ----- 59526 ----- 60557 ----- 61547 ----- 62369 ----- 63672 ----- 64583 ----- 65360 ----- 66147 ----- 67201 ----- 68142 ----- 69145 ----- 70209 ----- 71141 ----- 71923 ----- 73031 ----- 73987 ----- 74974 ----- 76170 ----- 77138 ----- 77849 ----- 78931 ----- 79832 ----- 80761 ----- 81843 ----- 82834 ----- 84032 ----- 85072 ----- 86287 ----- 87302 ----- 88422 ----- 89432 ----- 90550 ----- 91463 ----- 92249 ----- 93385 ----- 94789 ----- 96013 ----- 96893 ----- 98000 ----- 99008 ----- 100166 ----- 101263 ----- 102351 ----- 103236 ----- 104121 ----- 105363 ----- 106329 ----- 107325 ----- 108231 ----- 109054 ----- 110019 ----- 111185 ----- 112112 ----- 112908 ----- 113904 ----- 114785 ----- 115410 ----- 116526 ----- 117559 ----- 118310 ----- 119073 ----- 120034 ----- 120817 ----- 121744 ----- 122566 ----- 123720 ----- 124813 ----- 125835 ----- 126622 ----- 127651 ----- 128328 ----- 129315 ----- 130244 ----- 131450 ----- 132439 ----- 133288 ----- 134164 ----- 135298 ----- 136347 ----- 137243 ----- 138256 ----- 139427 ----- 140374 ----- 141371 ----- 142302 ----- 143322 ----- 144335 ----- 145333 ----- 146212 ----- 147321 ----- 148591 ----- 149594 ------ 150514 ------ 151361 ------ 152059 ------ 153070 ------ 154059 ------ 155259 ------ 156473 ------ 157690 ------ 158703 ------ 159675 ------ 160597 ------ 161668 ------ 162737 ------ 163955 ------ 164942 ------ 165924 ------ 167059 ------ 167866 ------ 169034 ------ 169935 ------ 170712 ------ 171806 ------ 172841 ------ 174078 ------ 175347 ------ 176430 ------ 177346 ------ 178566 ------ 179515 ------ 180677 ------ 181729 ------ 182983 ------ 183814 ------ 184892 ------ 185696 ------ 186611 ------ 187744 ------ 188974 ------ 189911 ------ 190671 ------ 191607 ------ 192820 ------ 193789 ------ 195057 ------ 196224 ------ 197231 ------ 198281 ------ 199119 ------ 199999 113 │ │ │ │ │ │ │ │ │ ├── key: (17) 114 │ │ │ │ │ │ │ │ │ ├── fd: (17)-->(20,23) 115 │ │ │ │ │ │ │ │ │ └── ordering: +17 opt(20,23) [actual: +17] 116 │ │ │ │ │ │ │ │ └── filters 117 │ │ │ │ │ │ │ │ ├── p_brand:20 = 'Brand#23' [type=bool, outer=(20), constraints=(/20: [/'Brand#23' - /'Brand#23']; tight), fd=()-->(20)] 118 │ │ │ │ │ │ │ │ └── p_container:23 = 'MED BOX' [type=bool, outer=(23), constraints=(/23: [/'MED BOX' - /'MED BOX']; tight), fd=()-->(23)] 119 │ │ │ │ │ │ │ └── filters (true) 120 │ │ │ │ │ │ └── filters (true) 121 │ │ │ │ │ └── aggregations 122 │ │ │ │ │ └── avg [as=avg:42, type=float, outer=(30)] 123 │ │ │ │ │ └── l_quantity:30 [type=float] 124 │ │ │ │ └── projections 125 │ │ │ │ └── avg:42 * 0.2 [as="?column?":43, type=float, outer=(42)] 126 │ │ │ └── filters (true) 127 │ │ └── filters 128 │ │ └── l_quantity:5 < "?column?":43 [type=bool, outer=(5,43), constraints=(/5: (/NULL - ]; /43: (/NULL - ])] 129 │ └── aggregations 130 │ └── sum [as=sum:44, type=float, outer=(6)] 131 │ └── l_extendedprice:6 [type=float] 132 └── projections 133 └── sum:44 / 7.0 [as=avg_yearly:45, type=float, outer=(44)] 134 135 stats table=q17_project_1 136 ---- 137 column_names row_count distinct_count null_count 138 {avg_yearly} 1 1 0 139 ~~~~ 140 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 141 {avg_yearly} 1.00 1.00 1.00 1.00 0.00 1.00 142 143 stats table=q17_scalar_group_by_2 144 ---- 145 column_names row_count distinct_count null_count 146 {sum} 1 1 0 147 ~~~~ 148 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 149 {sum} 1.00 1.00 1.00 1.00 0.00 1.00 150 151 stats table=q17_lookup_join_3 152 ---- 153 column_names row_count distinct_count null_count 154 {?column?} 587 185 0 155 {l_extendedprice} 587 430 0 156 {l_partkey} 587 195 0 157 {l_quantity} 587 6 0 158 {p_partkey} 587 195 0 159 ~~~~ 160 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 161 {?column?} 2008.00 3.42 <== 200.00 1.08 0.00 1.00 162 {l_extendedprice} 2008.00 3.42 <== 2006.00 4.67 <== 0.00 1.00 163 {l_partkey} 2008.00 3.42 <== 200.00 1.03 0.00 1.00 164 {l_quantity} 2008.00 3.42 <== 50.00 8.33 <== 0.00 1.00 165 {p_partkey} 2008.00 3.42 <== 200.00 1.03 0.00 1.00 166 167 stats table=q17_lookup_join_4 168 ---- 169 column_names row_count distinct_count null_count 170 {?column?} 6088 194 0 171 {l_linenumber} 6088 7 0 172 {l_orderkey} 6088 6116 0 173 {l_partkey} 6088 204 0 174 {p_partkey} 6088 204 0 175 ~~~~ 176 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 177 {?column?} 6024.00 1.01 200.00 1.03 0.00 1.00 178 {l_linenumber} 6024.00 1.01 7.00 1.00 0.00 1.00 179 {l_orderkey} 6024.00 1.01 6012.00 1.02 0.00 1.00 180 {l_partkey} 6024.00 1.01 200.00 1.02 0.00 1.00 181 {p_partkey} 6024.00 1.01 200.00 1.02 0.00 1.00 182 183 stats table=q17_project_5 184 ---- 185 column_names row_count distinct_count null_count 186 {?column?} 204 194 0 187 {p_partkey} 204 204 0 188 ~~~~ 189 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 190 {?column?} 200.00 1.02 200.00 1.03 0.00 1.00 191 {p_partkey} 200.00 1.02 200.00 1.02 0.00 1.00 192 193 stats table=q17_group_by_6 194 ---- 195 column_names row_count distinct_count null_count 196 {avg} 204 194 0 197 {p_partkey} 204 204 0 198 ~~~~ 199 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 200 {avg} 200.00 1.02 200.00 1.03 0.00 1.00 201 {p_partkey} 200.00 1.02 200.00 1.02 0.00 1.00 202 203 stats table=q17_lookup_join_7 204 ---- 205 column_names row_count distinct_count null_count 206 {l_partkey} 6088 204 0 207 {l_quantity} 6088 50 0 208 {p_brand} 6088 1 0 209 {p_container} 6088 1 0 210 {p_partkey} 6088 204 0 211 ~~~~ 212 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 213 {l_partkey} 6024.00 1.01 200.00 1.02 0.00 1.00 214 {l_quantity} 6024.00 1.01 50.00 1.00 0.00 1.00 215 {p_brand} 6024.00 1.01 1.00 1.00 0.00 1.00 216 {p_container} 6024.00 1.01 1.00 1.00 0.00 1.00 217 {p_partkey} 6024.00 1.01 200.00 1.02 0.00 1.00 218 219 stats table=q17_lookup_join_8 220 ---- 221 column_names row_count distinct_count null_count 222 {l_linenumber} 6088 7 0 223 {l_orderkey} 6088 6116 0 224 {l_partkey} 6088 204 0 225 {p_brand} 6088 1 0 226 {p_container} 6088 1 0 227 {p_partkey} 6088 204 0 228 ~~~~ 229 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 230 {l_linenumber} 6024.00 1.01 7.00 1.00 0.00 1.00 231 {l_orderkey} 6024.00 1.01 6012.00 1.02 0.00 1.00 232 {l_partkey} 6024.00 1.01 200.00 1.02 0.00 1.00 233 {p_brand} 6024.00 1.01 1.00 1.00 0.00 1.00 234 {p_container} 6024.00 1.01 1.00 1.00 0.00 1.00 235 {p_partkey} 6024.00 1.01 200.00 1.02 0.00 1.00 236 237 stats table=q17_select_9 238 ---- 239 column_names row_count distinct_count null_count 240 {p_brand} 204 1 0 241 {p_container} 204 1 0 242 {p_partkey} 204 204 0 243 ~~~~ 244 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 245 {p_brand} 200.00 1.02 1.00 1.00 0.00 1.00 246 {p_container} 200.00 1.02 1.00 1.00 0.00 1.00 247 {p_partkey} 200.00 1.02 200.00 1.02 0.00 1.00 248 249 stats table=q17_scan_10 250 ---- 251 column_names row_count distinct_count null_count 252 {p_brand} 200000 25 0 253 {p_container} 200000 40 0 254 {p_partkey} 200000 199241 0 255 ~~~~ 256 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 257 {p_brand} 200000.00 1.00 25.00 1.00 0.00 1.00 258 {p_container} 200000.00 1.00 40.00 1.00 0.00 1.00 259 {p_partkey} 200000.00 1.00 199241.00 1.00 0.00 1.00