github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q16 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q16 9 # Parts/Supplier Relationship 10 # Finds out how many suppliers can supply parts with given attributes. It might 11 # be used, for example, to determine whether there is a sufficient number of 12 # suppliers for heavily ordered parts. 13 # 14 # Counts the number of suppliers who can supply parts that satisfy a particular 15 # customer's requirements. The customer is interested in parts of eight 16 # different sizes as long as they are not of a given type, not of a given brand, 17 # and not from a supplier who has had complaints registered at the Better 18 # Business Bureau. Results must be presented in descending count and ascending 19 # brand, type, and size. 20 # -------------------------------------------------- 21 save-tables database=tpch save-tables-prefix=q16 22 SELECT 23 p_brand, 24 p_type, 25 p_size, 26 count(DISTINCT ps_suppkey) AS supplier_cnt 27 FROM 28 partsupp, 29 part 30 WHERE 31 p_partkey = ps_partkey 32 AND p_brand <> 'Brand#45' 33 AND p_type NOT LIKE 'MEDIUM POLISHED %' 34 AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9) 35 AND ps_suppkey NOT IN ( 36 SELECT 37 s_suppkey 38 FROM 39 supplier 40 WHERE 41 s_comment LIKE '%Customer%Complaints%' 42 ) 43 GROUP BY 44 p_brand, 45 p_type, 46 p_size 47 ORDER BY 48 supplier_cnt DESC, 49 p_brand, 50 p_type, 51 p_size; 52 ---- 53 sort 54 ├── save-table-name: q16_sort_1 55 ├── columns: p_brand:9(char!null) p_type:10(varchar!null) p_size:11(int!null) supplier_cnt:22(int!null) 56 ├── stats: [rows=3315.43068, distinct(9)=8.33333333, null(9)=0, distinct(10)=150, null(10)=0, distinct(11)=8, null(11)=0, distinct(22)=3315.43068, null(22)=0, distinct(9-11)=3315.43068, null(9-11)=0] 57 ├── key: (9-11) 58 ├── fd: (9-11)-->(22) 59 ├── ordering: -22,+9,+10,+11 60 └── group-by 61 ├── save-table-name: q16_group_by_2 62 ├── columns: p_brand:9(char!null) p_type:10(varchar!null) p_size:11(int!null) count:22(int!null) 63 ├── grouping columns: p_brand:9(char!null) p_type:10(varchar!null) p_size:11(int!null) 64 ├── stats: [rows=3315.43068, distinct(9)=8.33333333, null(9)=0, distinct(10)=150, null(10)=0, distinct(11)=8, null(11)=0, distinct(22)=3315.43068, null(22)=0, distinct(9-11)=3315.43068, null(9-11)=0] 65 ├── key: (9-11) 66 ├── fd: (9-11)-->(22) 67 ├── inner-join (hash) 68 │ ├── save-table-name: q16_inner_join_3 69 │ ├── columns: ps_partkey:1(int!null) ps_suppkey:2(int!null) p_partkey:6(int!null) p_brand:9(char!null) p_type:10(varchar!null) p_size:11(int!null) 70 │ ├── stats: [rows=9606.24468, distinct(1)=3555.43444, null(1)=0, distinct(2)=6153.37633, null(2)=0, distinct(6)=3555.43444, null(6)=0, distinct(9)=8.33333333, null(9)=0, distinct(10)=150, null(10)=0, distinct(11)=8, null(11)=0, distinct(9-11)=3315.43068, null(9-11)=0] 71 │ ├── key: (2,6) 72 │ ├── fd: (6)-->(9-11), (1)==(6), (6)==(1) 73 │ ├── anti-join (merge) 74 │ │ ├── save-table-name: q16_merge_join_4 75 │ │ ├── columns: ps_partkey:1(int!null) ps_suppkey:2(int!null) 76 │ │ ├── left ordering: +2 77 │ │ ├── right ordering: +15 78 │ │ ├── stats: [rows=531592.246, distinct(1)=196758.028, null(1)=0, distinct(2)=9920, null(2)=0] 79 │ │ ├── key: (1,2) 80 │ │ ├── scan partsupp@ps_sk 81 │ │ │ ├── save-table-name: q16_scan_5 82 │ │ │ ├── columns: ps_partkey:1(int!null) ps_suppkey:2(int!null) 83 │ │ │ ├── stats: [rows=800000, distinct(1)=199241, null(1)=0, distinct(2)=9920, null(2)=0] 84 │ │ │ │ histogram(1)= 0 80 3920 80 3920 80 3920 80 3920 80 3840 160 3840 160 3920 80 3920 80 3920 80 3920 80 3920 160 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3840 160 3920 80 3920 80 3920 80 3920 80 3920 160 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3840 160 3920 80 3920 80 3920 160 3920 80 3920 160 3920 80 3920 80 3920 80 3920 80 3920 80 3920 160 3920 80 3920 80 3920 80 3920 80 3920 80 3920 160 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 3920 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 3920 160 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 4000 80 85 │ │ │ │ <--- 4 ------ 793 ------ 1856 ------ 2808 ------ 3809 ------ 4923 ------ 5975 ------ 6944 ------ 8085 ------ 8945 ------ 9831 ------ 10952 ------ 11932 ------ 12775 ------ 13849 ------ 14925 ------ 16016 ------ 16828 ------ 17768 ------ 18705 ------ 19631 ------ 20600 ------ 21491 ------ 22254 ------ 23327 ------ 24491 ------ 25470 ------ 26331 ------ 27327 ------ 28308 ------ 29359 ------ 30542 ------ 31592 ------ 32495 ------ 33408 ------ 34624 ------ 35726 ------ 36676 ------ 37637 ------ 38485 ------ 39368 ------ 40372 ------ 41034 ------ 42185 ------ 43333 ------ 44466 ------ 45440 ------ 46571 ------ 47469 ------ 48346 ------ 49379 ------ 50571 ------ 51830 ------ 52967 ------ 53673 ------ 54656 ------ 55504 ------ 56539 ------ 57393 ------ 58366 ------ 59577 ------ 60559 ------ 61676 ------ 62471 ------ 63421 ------ 64449 ------ 65409 ------ 66254 ------ 67127 ------ 68127 ------ 69177 ------ 70183 ------ 71209 ------ 72101 ------ 73102 ------ 73994 ------ 74899 ------ 76016 ------ 77098 ------ 77842 ------ 79137 ------ 80242 ------ 81364 ------ 82331 ------ 83158 ------ 84283 ------ 85282 ------ 86437 ------ 87450 ------ 88419 ------ 89493 ------ 90478 ------ 91468 ------ 92552 ------ 93200 ------ 94191 ------ 95067 ------ 96272 ------ 97228 ------ 98126 ------ 99198 ------ 100219 ------ 101057 ------ 102038 ------ 102775 ------ 103711 ------ 104623 ------ 105710 ------ 106734 ------ 107932 ------ 109255 ------ 110220 ------ 111235 ------ 112174 ------ 113260 ------ 114081 ------ 115103 ------ 115864 ------ 116794 ------ 117741 ------ 118712 ------ 119470 ------ 120528 ------ 121572 ------ 122536 ------ 123629 ------ 124404 ------ 125301 ------ 126257 ------ 127139 ------ 128267 ------ 129258 ------ 130442 ------ 131845 ------ 133164 ------ 134005 ------ 135076 ------ 135868 ------ 137297 ------ 138777 ------ 139806 ------ 140741 ------ 141896 ------ 142719 ------ 143727 ------ 144645 ------ 145510 ------ 146507 ------ 147449 ------ 148467 ------ 149635 ------ 150563 ------ 151751 ------ 152613 ------ 153416 ------ 154612 ------ 155853 ------ 156866 ------ 158311 ------ 159230 ------ 160390 ------ 161455 ------ 162555 ------ 163435 ------ 164549 ------ 165663 ------ 166891 ------ 167757 ------ 168732 ------ 169644 ------ 170532 ------ 171671 ------ 172778 ------ 173599 ------ 174321 ------ 175624 ------ 176663 ------ 177632 ------ 178555 ------ 179551 ------ 180510 ------ 181682 ------ 182648 ------ 183408 ------ 184543 ------ 185722 ------ 186713 ------ 187787 ------ 188730 ------ 189604 ------ 190711 ------ 191690 ------ 192692 ------ 193702 ------ 194685 ------ 195725 ------ 196730 ------ 197724 ------ 198701 ------ 199973 86 │ │ │ │ histogram(2)= 0 80 3920 240 3920 80 3920 160 3920 80 3920 240 3920 80 3760 320 3680 320 3920 80 3920 160 3920 240 3920 80 3920 160 3840 160 3920 80 3920 80 3760 240 3840 160 3920 80 3840 160 3680 320 3920 80 3840 160 3840 160 3760 320 3840 160 3840 160 3920 80 3840 240 3920 80 3920 80 3840 160 3760 240 3920 160 3920 80 3920 80 3920 80 3920 320 3920 80 3920 160 3840 400 3760 240 3920 160 3920 160 3600 480 3920 80 3680 320 3840 160 3840 160 3920 240 3840 160 3920 160 3920 80 3920 160 3920 80 3760 240 3920 80 3920 80 3840 320 3840 160 3840 160 3920 240 3840 480 3920 160 3840 240 3920 160 3920 160 3920 80 3840 160 3920 80 3920 80 3920 80 3920 80 3840 240 3840 240 3920 80 3840 320 3920 80 3920 80 3920 240 3840 240 3920 160 3920 80 3840 160 3840 240 3920 240 3840 80 3680 320 3920 160 3840 160 3840 80 3920 80 3840 160 3840 160 3920 80 3920 80 3840 160 3920 80 3920 160 3840 240 3840 80 3840 160 3760 160 3920 80 3920 80 3840 240 3760 240 3840 80 3920 160 3840 80 3920 80 3920 80 3920 80 3920 160 3840 80 3920 80 3760 240 3920 80 3920 160 3760 160 3920 160 3840 80 3920 160 3840 160 3840 160 3600 320 3920 160 3840 80 3920 80 3680 320 3840 240 3760 160 3920 80 3920 80 3920 80 3920 80 3920 80 3680 320 3920 160 3840 160 3760 160 3920 240 3840 160 3840 240 3600 320 3840 80 3840 80 3920 160 3760 160 3840 160 3840 320 3840 80 3840 160 3760 240 3840 80 3840 240 3760 160 3840 160 3840 160 3920 240 3760 160 3840 80 3920 160 3680 240 3840 160 3840 160 3760 240 3920 80 3920 240 3760 160 3760 240 3840 80 3840 240 3840 240 3760 320 3760 240 3840 80 3840 160 3840 240 3760 320 3760 160 3840 160 3840 160 3840 80 3760 160 3840 80 3840 160 3920 160 3840 80 3920 80 3840 160 3920 80 3840 240 3840 80 3920 80 3760 240 3920 240 3840 80 3680 240 87 │ │ │ │ <--- 2 ------ 49 ------ 90 ------ 141 ------ 183 ------ 235 ------ 278 ------ 319 ------ 360 ------ 406 ------ 458 ------ 511 ------ 561 ------ 622 ------ 674 ------ 731 ------ 781 ------ 822 ------ 882 ------ 934 ------ 988 ------ 1026 ------ 1072 ------ 1114 ------ 1188 ------ 1245 ------ 1291 ------ 1335 ------ 1380 ------ 1433 ------ 1488 ------ 1537 ------ 1590 ------ 1642 ------ 1692 ------ 1751 ------ 1807 ------ 1846 ------ 1887 ------ 1939 ------ 1994 ------ 2045 ------ 2097 ------ 2139 ------ 2190 ------ 2240 ------ 2293 ------ 2344 ------ 2385 ------ 2427 ------ 2484 ------ 2538 ------ 2601 ------ 2651 ------ 2730 ------ 2781 ------ 2825 ------ 2874 ------ 2938 ------ 2978 ------ 3034 ------ 3086 ------ 3139 ------ 3179 ------ 3231 ------ 3274 ------ 3334 ------ 3387 ------ 3431 ------ 3477 ------ 3530 ------ 3581 ------ 3633 ------ 3680 ------ 3725 ------ 3775 ------ 3820 ------ 3876 ------ 3923 ------ 3982 ------ 4036 ------ 4078 ------ 4116 ------ 4183 ------ 4227 ------ 4275 ------ 4336 ------ 4376 ------ 4424 ------ 4481 ------ 4537 ------ 4582 ------ 4630 ------ 4680 ------ 4727 ------ 4779 ------ 4828 ------ 4881 ------ 4938 ------ 4986 ------ 5040 ------ 5087 ------ 5138 ------ 5188 ------ 5237 ------ 5280 ------ 5318 ------ 5358 ------ 5405 ------ 5459 ------ 5516 ------ 5561 ------ 5615 ------ 5681 ------ 5744 ------ 5790 ------ 5847 ------ 5885 ------ 5927 ------ 5991 ------ 6042 ------ 6111 ------ 6165 ------ 6207 ------ 6259 ------ 6313 ------ 6359 ------ 6418 ------ 6471 ------ 6530 ------ 6587 ------ 6626 ------ 6672 ------ 6739 ------ 6784 ------ 6837 ------ 6886 ------ 6952 ------ 6994 ------ 7040 ------ 7081 ------ 7134 ------ 7178 ------ 7232 ------ 7280 ------ 7330 ------ 7378 ------ 7435 ------ 7486 ------ 7537 ------ 7593 ------ 7636 ------ 7680 ------ 7737 ------ 7788 ------ 7836 ------ 7877 ------ 7928 ------ 7993 ------ 8036 ------ 8083 ------ 8135 ------ 8180 ------ 8221 ------ 8263 ------ 8313 ------ 8352 ------ 8399 ------ 8453 ------ 8517 ------ 8566 ------ 8612 ------ 8664 ------ 8716 ------ 8766 ------ 8821 ------ 8871 ------ 8922 ------ 8956 ------ 9007 ------ 9050 ------ 9100 ------ 9154 ------ 9203 ------ 9246 ------ 9311 ------ 9358 ------ 9407 ------ 9470 ------ 9525 ------ 9564 ------ 9633 ------ 9672 ------ 9730 ------ 9778 ------ 9824 ------ 9868 ------ 9919 ------ 9959 ------ 10000 88 │ │ │ ├── key: (1,2) 89 │ │ │ └── ordering: +2 90 │ │ ├── select 91 │ │ │ ├── save-table-name: q16_select_6 92 │ │ │ ├── columns: s_suppkey:15(int!null) s_comment:21(varchar!null) 93 │ │ │ ├── stats: [rows=3333.33333, distinct(15)=3328.25616, null(15)=0, distinct(21)=3333.33333, null(21)=0] 94 │ │ │ ├── key: (15) 95 │ │ │ ├── fd: (15)-->(21) 96 │ │ │ ├── ordering: +15 97 │ │ │ ├── scan supplier 98 │ │ │ │ ├── save-table-name: q16_scan_7 99 │ │ │ │ ├── columns: s_suppkey:15(int!null) s_comment:21(varchar!null) 100 │ │ │ │ ├── stats: [rows=10000, distinct(15)=9920, null(15)=0, distinct(21)=9934, null(21)=0] 101 │ │ │ │ │ histogram(15)= 0 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 49 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 50 1 102 │ │ │ │ │ <--- 1 ---- 51 ---- 101 ---- 151 ---- 201 ---- 251 ---- 301 ---- 351 ---- 401 ---- 451 ---- 501 ---- 551 ---- 601 ---- 651 ---- 701 ---- 751 ---- 801 ---- 851 ---- 901 ---- 951 ---- 1001 ---- 1051 ---- 1101 ---- 1151 ---- 1201 ---- 1251 ---- 1301 ---- 1351 ---- 1401 ---- 1451 ---- 1501 ---- 1551 ---- 1601 ---- 1651 ---- 1701 ---- 1751 ---- 1801 ---- 1851 ---- 1901 ---- 1951 ---- 2001 ---- 2051 ---- 2101 ---- 2151 ---- 2201 ---- 2251 ---- 2301 ---- 2351 ---- 2401 ---- 2451 ---- 2501 ---- 2551 ---- 2601 ---- 2651 ---- 2701 ---- 2751 ---- 2801 ---- 2851 ---- 2901 ---- 2951 ---- 3001 ---- 3051 ---- 3101 ---- 3151 ---- 3201 ---- 3251 ---- 3301 ---- 3351 ---- 3401 ---- 3451 ---- 3501 ---- 3551 ---- 3601 ---- 3651 ---- 3701 ---- 3751 ---- 3801 ---- 3851 ---- 3901 ---- 3951 ---- 4001 ---- 4051 ---- 4101 ---- 4151 ---- 4201 ---- 4251 ---- 4301 ---- 4351 ---- 4401 ---- 4451 ---- 4501 ---- 4551 ---- 4601 ---- 4651 ---- 4701 ---- 4751 ---- 4801 ---- 4851 ---- 4901 ---- 4951 ---- 5001 ---- 5051 ---- 5101 ---- 5151 ---- 5201 ---- 5251 ---- 5301 ---- 5351 ---- 5401 ---- 5451 ---- 5501 ---- 5551 ---- 5601 ---- 5651 ---- 5701 ---- 5751 ---- 5801 ---- 5851 ---- 5901 ---- 5951 ---- 6001 ---- 6051 ---- 6101 ---- 6151 ---- 6201 ---- 6251 ---- 6301 ---- 6351 ---- 6401 ---- 6451 ---- 6501 ---- 6551 ---- 6601 ---- 6651 ---- 6701 ---- 6751 ---- 6801 ---- 6851 ---- 6901 ---- 6951 ---- 7001 ---- 7051 ---- 7101 ---- 7151 ---- 7201 ---- 7251 ---- 7301 ---- 7351 ---- 7401 ---- 7451 ---- 7501 ---- 7552 ---- 7603 ---- 7654 ---- 7705 ---- 7756 ---- 7807 ---- 7858 ---- 7909 ---- 7960 ---- 8011 ---- 8062 ---- 8113 ---- 8164 ---- 8215 ---- 8266 ---- 8317 ---- 8368 ---- 8419 ---- 8470 ---- 8521 ---- 8572 ---- 8623 ---- 8674 ---- 8725 ---- 8776 ---- 8827 ---- 8878 ---- 8929 ---- 8980 ---- 9031 ---- 9082 ---- 9133 ---- 9184 ---- 9235 ---- 9286 ---- 9337 ---- 9388 ---- 9439 ---- 9490 ---- 9541 ---- 9592 ---- 9643 ---- 9694 ---- 9745 ---- 9796 ---- 9847 ---- 9898 ---- 9949 ---- 10000 103 │ │ │ │ ├── key: (15) 104 │ │ │ │ ├── fd: (15)-->(21) 105 │ │ │ │ └── ordering: +15 106 │ │ │ └── filters 107 │ │ │ └── s_comment:21 LIKE '%Customer%Complaints%' [type=bool, outer=(21), constraints=(/21: (/NULL - ])] 108 │ │ └── filters (true) 109 │ ├── select 110 │ │ ├── save-table-name: q16_select_8 111 │ │ ├── columns: p_partkey:6(int!null) p_brand:9(char!null) p_type:10(varchar!null) p_size:11(int!null) 112 │ │ ├── stats: [rows=3555.55556, distinct(6)=3555.43444, null(6)=0, distinct(9)=8.33333333, null(9)=0, distinct(10)=150, null(10)=0, distinct(11)=8, null(11)=0, distinct(9,11)=66.6666667, null(9,11)=0, distinct(9-11)=3553.4368, null(9-11)=0] 113 │ │ ├── key: (6) 114 │ │ ├── fd: (6)-->(9-11) 115 │ │ ├── scan part 116 │ │ │ ├── save-table-name: q16_scan_9 117 │ │ │ ├── columns: p_partkey:6(int!null) p_brand:9(char!null) p_type:10(varchar!null) p_size:11(int!null) 118 │ │ │ ├── stats: [rows=200000, distinct(6)=199241, null(6)=0, distinct(9)=25, null(9)=0, distinct(10)=150, null(10)=0, distinct(11)=50, null(11)=0, distinct(9,11)=1250, null(9,11)=0, distinct(9-11)=187500, null(9-11)=0] 119 │ │ │ │ histogram(6)= 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 120 │ │ │ │ <--- 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 121 │ │ │ ├── key: (6) 122 │ │ │ └── fd: (6)-->(9-11) 123 │ │ └── filters 124 │ │ ├── p_brand:9 != 'Brand#45' [type=bool, outer=(9), constraints=(/9: (/NULL - /'Brand#45') [/e'Brand#45\x00' - ]; tight)] 125 │ │ ├── p_type:10 NOT LIKE 'MEDIUM POLISHED %' [type=bool, outer=(10), constraints=(/10: (/NULL - ])] 126 │ │ └── p_size:11 IN (3, 9, 14, 19, 23, 36, 45, 49) [type=bool, outer=(11), constraints=(/11: [/3 - /3] [/9 - /9] [/14 - /14] [/19 - /19] [/23 - /23] [/36 - /36] [/45 - /45] [/49 - /49]; tight)] 127 │ └── filters 128 │ └── p_partkey:6 = ps_partkey:1 [type=bool, outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 129 └── aggregations 130 └── agg-distinct [as=count:22, type=int, outer=(2)] 131 └── count [type=int] 132 └── ps_suppkey:2 [type=int] 133 134 stats table=q16_sort_1 135 ---- 136 column_names row_count distinct_count null_count 137 {p_brand} 18314 24 0 138 {p_size} 18314 8 0 139 {p_type} 18314 145 0 140 {supplier_cnt} 18314 15 0 141 ~~~~ 142 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 143 {p_brand} 3315.00 5.52 <== 8.00 3.00 <== 0.00 1.00 144 {p_size} 3315.00 5.52 <== 8.00 1.00 0.00 1.00 145 {p_type} 3315.00 5.52 <== 150.00 1.03 0.00 1.00 146 {supplier_cnt} 3315.00 5.52 <== 3315.00 221.00 <== 0.00 1.00 147 148 stats table=q16_group_by_2 149 ---- 150 column_names row_count distinct_count null_count 151 {p_brand} 18314 24 0 152 {p_size} 18314 8 0 153 {p_type} 18314 145 0 154 {count} 18314 15 0 155 ~~~~ 156 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 157 {count} 3315.00 5.52 <== 3315.00 221.00 <== 0.00 1.00 158 {p_brand} 3315.00 5.52 <== 8.00 3.00 <== 0.00 1.00 159 {p_size} 3315.00 5.52 <== 8.00 1.00 0.00 1.00 160 {p_type} 3315.00 5.52 <== 150.00 1.03 0.00 1.00 161 162 stats table=q16_inner_join_3 163 ---- 164 column_names row_count distinct_count null_count 165 {p_brand} 118274 24 0 166 {p_partkey} 118274 29433 0 167 {p_size} 118274 8 0 168 {p_type} 118274 145 0 169 {ps_partkey} 118274 29433 0 170 {ps_suppkey} 118274 9916 0 171 ~~~~ 172 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 173 {p_brand} 9606.00 12.31 <== 8.00 3.00 <== 0.00 1.00 174 {p_partkey} 9606.00 12.31 <== 3555.00 8.28 <== 0.00 1.00 175 {p_size} 9606.00 12.31 <== 8.00 1.00 0.00 1.00 176 {p_type} 9606.00 12.31 <== 150.00 1.03 0.00 1.00 177 {ps_partkey} 9606.00 12.31 <== 3555.00 8.28 <== 0.00 1.00 178 {ps_suppkey} 9606.00 12.31 <== 6153.00 1.61 0.00 1.00 179 180 stats table=q16_merge_join_4 181 ---- 182 column_names row_count distinct_count null_count 183 {ps_partkey} 799680 199241 0 184 {ps_suppkey} 799680 9916 0 185 ~~~~ 186 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 187 {ps_partkey} 531592.00 1.50 196758.00 1.01 0.00 1.00 188 {ps_suppkey} 531592.00 1.50 9920.00 1.00 0.00 1.00 189 190 stats table=q16_scan_5 191 ---- 192 column_names row_count distinct_count null_count 193 {ps_partkey} 800000 199241 0 194 {ps_suppkey} 800000 9920 0 195 ~~~~ 196 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 197 {ps_partkey} 800000.00 1.00 199241.00 1.00 0.00 1.00 198 {ps_suppkey} 800000.00 1.00 9920.00 1.00 0.00 1.00 199 200 stats table=q16_select_6 201 ---- 202 column_names row_count distinct_count null_count 203 {s_comment} 4 4 0 204 {s_suppkey} 4 4 0 205 ~~~~ 206 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 207 {s_comment} 3333.00 833.25 <== 3333.00 833.25 <== 0.00 1.00 208 {s_suppkey} 3333.00 833.25 <== 3328.00 832.00 <== 0.00 1.00 209 210 stats table=q16_scan_7 211 ---- 212 column_names row_count distinct_count null_count 213 {s_comment} 10000 9934 0 214 {s_suppkey} 10000 9920 0 215 ~~~~ 216 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 217 {s_comment} 10000.00 1.00 9934.00 1.00 0.00 1.00 218 {s_suppkey} 10000.00 1.00 9920.00 1.00 0.00 1.00 219 220 stats table=q16_select_8 221 ---- 222 column_names row_count distinct_count null_count 223 {p_brand} 29581 24 0 224 {p_partkey} 29581 29433 0 225 {p_size} 29581 8 0 226 {p_type} 29581 145 0 227 ~~~~ 228 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 229 {p_brand} 3556.00 8.32 <== 8.00 3.00 <== 0.00 1.00 230 {p_partkey} 3556.00 8.32 <== 3555.00 8.28 <== 0.00 1.00 231 {p_size} 3556.00 8.32 <== 8.00 1.00 0.00 1.00 232 {p_type} 3556.00 8.32 <== 150.00 1.03 0.00 1.00 233 234 stats table=q16_scan_9 235 ---- 236 column_names row_count distinct_count null_count 237 {p_brand} 200000 25 0 238 {p_partkey} 200000 199241 0 239 {p_size} 200000 50 0 240 {p_type} 200000 150 0 241 ~~~~ 242 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 243 {p_brand} 200000.00 1.00 25.00 1.00 0.00 1.00 244 {p_partkey} 200000.00 1.00 199241.00 1.00 0.00 1.00 245 {p_size} 200000.00 1.00 50.00 1.00 0.00 1.00 246 {p_type} 200000.00 1.00 150.00 1.00 0.00 1.00