github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q10 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q10 9 # Returned Item Reporting 10 # Identifies customers who might be having problems with the parts that are 11 # shipped to them. 12 # 13 # Finds the top 20 customers, in terms of their effect on lost revenue for a 14 # given quarter, who have returned parts. The query considers only parts that 15 # were ordered in the specified quarter. The query lists the customer's name, 16 # address, nation, phone number, account balance, comment information and 17 # revenue lost. The customers are listed in descending order of lost revenue. 18 # Revenue lost is defined as sum(l_extendedprice*(1-l_discount)) for all 19 # qualifying lineitems. 20 # -------------------------------------------------- 21 save-tables database=tpch save-tables-prefix=q10 22 SELECT 23 c_custkey, 24 c_name, 25 sum(l_extendedprice * (1 - l_discount)) AS revenue, 26 c_acctbal, 27 n_name, 28 c_address, 29 c_phone, 30 c_comment 31 FROM 32 customer, 33 orders, 34 lineitem, 35 nation 36 WHERE 37 c_custkey = o_custkey 38 AND l_orderkey = o_orderkey 39 AND o_orderDATE >= DATE '1993-10-01' 40 AND o_orderDATE < DATE '1993-10-01' + INTERVAL '3' MONTH 41 AND l_returnflag = 'R' 42 AND c_nationkey = n_nationkey 43 GROUP BY 44 c_custkey, 45 c_name, 46 c_acctbal, 47 c_phone, 48 n_name, 49 c_address, 50 c_comment 51 ORDER BY 52 revenue DESC 53 LIMIT 20; 54 ---- 55 limit 56 ├── save-table-name: q10_limit_1 57 ├── columns: c_custkey:1(int!null) c_name:2(varchar!null) revenue:39(float!null) c_acctbal:6(float!null) n_name:35(char!null) c_address:3(varchar!null) c_phone:5(char!null) c_comment:8(varchar!null) 58 ├── internal-ordering: -39 59 ├── cardinality: [0 - 20] 60 ├── stats: [rows=20, distinct(1)=20, null(1)=0, distinct(2)=20, null(2)=0, distinct(3)=20, null(3)=0, distinct(5)=20, null(5)=0, distinct(6)=20, null(6)=0, distinct(8)=20, null(8)=0, distinct(35)=20, null(35)=0, distinct(39)=20, null(39)=0] 61 ├── key: (1) 62 ├── fd: (1)-->(2,3,5,6,8,35,39) 63 ├── ordering: -39 64 ├── sort 65 │ ├── save-table-name: q10_sort_2 66 │ ├── columns: c_custkey:1(int!null) c_name:2(varchar!null) c_address:3(varchar!null) c_phone:5(char!null) c_acctbal:6(float!null) c_comment:8(varchar!null) n_name:35(char!null) sum:39(float!null) 67 │ ├── stats: [rows=42917.9526, distinct(1)=42917.9526, null(1)=0, distinct(2)=42917.9526, null(2)=0, distinct(3)=42917.9526, null(3)=0, distinct(5)=42917.9526, null(5)=0, distinct(6)=42917.9526, null(6)=0, distinct(8)=42917.9526, null(8)=0, distinct(35)=42917.9526, null(35)=0, distinct(39)=42917.9526, null(39)=0] 68 │ ├── key: (1) 69 │ ├── fd: (1)-->(2,3,5,6,8,35,39) 70 │ ├── ordering: -39 71 │ ├── limit hint: 20.00 72 │ └── group-by 73 │ ├── save-table-name: q10_group_by_3 74 │ ├── columns: c_custkey:1(int!null) c_name:2(varchar!null) c_address:3(varchar!null) c_phone:5(char!null) c_acctbal:6(float!null) c_comment:8(varchar!null) n_name:35(char!null) sum:39(float!null) 75 │ ├── grouping columns: c_custkey:1(int!null) 76 │ ├── stats: [rows=42917.9526, distinct(1)=42917.9526, null(1)=0, distinct(2)=42917.9526, null(2)=0, distinct(3)=42917.9526, null(3)=0, distinct(5)=42917.9526, null(5)=0, distinct(6)=42917.9526, null(6)=0, distinct(8)=42917.9526, null(8)=0, distinct(35)=42917.9526, null(35)=0, distinct(39)=42917.9526, null(39)=0] 77 │ ├── key: (1) 78 │ ├── fd: (1)-->(2,3,5,6,8,35,39) 79 │ ├── project 80 │ │ ├── save-table-name: q10_project_4 81 │ │ ├── columns: column38:38(float!null) c_custkey:1(int!null) c_name:2(varchar!null) c_address:3(varchar!null) c_phone:5(char!null) c_acctbal:6(float!null) c_comment:8(varchar!null) n_name:35(char!null) 82 │ │ ├── stats: [rows=91240.8317, distinct(1)=42917.9526, null(1)=0, distinct(2)=68356.4353, null(2)=0, distinct(3)=68348.5807, null(3)=0, distinct(5)=68356.4353, null(5)=0, distinct(6)=67126.327, null(6)=0, distinct(8)=68271.7501, null(8)=0, distinct(35)=25, null(35)=0, distinct(38)=88236.775, null(38)=0] 83 │ │ ├── fd: (1)-->(2,3,5,6,8,35) 84 │ │ ├── inner-join (hash) 85 │ │ │ ├── save-table-name: q10_inner_join_5 86 │ │ │ ├── columns: c_custkey:1(int!null) c_name:2(varchar!null) c_address:3(varchar!null) c_nationkey:4(int!null) c_phone:5(char!null) c_acctbal:6(float!null) c_comment:8(varchar!null) o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) l_orderkey:18(int!null) l_extendedprice:23(float!null) l_discount:24(float!null) l_returnflag:26(char!null) n_nationkey:34(int!null) n_name:35(char!null) 87 │ │ │ ├── stats: [rows=91240.8317, distinct(1)=42917.9526, null(1)=0, distinct(2)=68356.4353, null(2)=0, distinct(3)=68348.5807, null(3)=0, distinct(4)=25, null(4)=0, distinct(5)=68356.4353, null(5)=0, distinct(6)=67126.327, null(6)=0, distinct(8)=68271.7501, null(8)=0, distinct(9)=44561.4774, null(9)=0, distinct(10)=42917.9526, null(10)=0, distinct(13)=92, null(13)=0, distinct(18)=44561.4774, null(18)=0, distinct(23)=86214.4137, null(23)=0, distinct(24)=11, null(24)=0, distinct(26)=1, null(26)=0, distinct(34)=25, null(34)=0, distinct(35)=25, null(35)=0, distinct(23,24)=88236.775, null(23,24)=0] 88 │ │ │ ├── fd: ()-->(26), (1)-->(2-6,8), (9)-->(10,13), (34)-->(35), (9)==(18), (18)==(9), (1)==(10), (10)==(1), (4)==(34), (34)==(4) 89 │ │ │ ├── inner-join (hash) 90 │ │ │ │ ├── save-table-name: q10_inner_join_6 91 │ │ │ │ ├── columns: c_custkey:1(int!null) c_name:2(varchar!null) c_address:3(varchar!null) c_nationkey:4(int!null) c_phone:5(char!null) c_acctbal:6(float!null) c_comment:8(varchar!null) o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) l_orderkey:18(int!null) l_extendedprice:23(float!null) l_discount:24(float!null) l_returnflag:26(char!null) 92 │ │ │ │ ├── stats: [rows=91240.8317, distinct(1)=37710.0545, null(1)=0, distinct(2)=68356.5955, null(2)=0, distinct(3)=68348.7409, null(3)=0, distinct(4)=25, null(4)=0, distinct(5)=68356.5955, null(5)=0, distinct(6)=67126.4808, null(6)=0, distinct(8)=68271.9098, null(8)=0, distinct(9)=44561.5336, null(9)=0, distinct(10)=37710.0545, null(10)=0, distinct(13)=92, null(13)=0, distinct(18)=44561.5336, null(18)=0, distinct(23)=56207.9759, null(23)=0, distinct(24)=11, null(24)=0, distinct(26)=1, null(26)=0] 93 │ │ │ │ ├── fd: ()-->(26), (9)-->(10,13), (9)==(18), (18)==(9), (1)-->(2-6,8), (1)==(10), (10)==(1) 94 │ │ │ │ ├── scan customer 95 │ │ │ │ │ ├── save-table-name: q10_scan_7 96 │ │ │ │ │ ├── columns: c_custkey:1(int!null) c_name:2(varchar!null) c_address:3(varchar!null) c_nationkey:4(int!null) c_phone:5(char!null) c_acctbal:6(float!null) c_comment:8(varchar!null) 97 │ │ │ │ │ ├── stats: [rows=150000, distinct(1)=148813, null(1)=0, distinct(2)=150000, null(2)=0, distinct(3)=149937, null(3)=0, distinct(4)=25, null(4)=0, distinct(5)=150000, null(5)=0, distinct(6)=140628, null(6)=0, distinct(8)=149323, null(8)=0] 98 │ │ │ │ │ │ histogram(1)= 0 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 735 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 750 15 99 │ │ │ │ │ │ <--- 2 ----- 776 ----- 1422 ----- 2189 ----- 2973 ----- 3583 ----- 4390 ----- 5154 ----- 5962 ----- 6965 ----- 7596 ----- 8303 ----- 9167 ----- 9833 ----- 10695 ----- 11397 ----- 11979 ----- 12651 ----- 13397 ----- 14144 ----- 14951 ----- 15698 ----- 16460 ----- 17203 ----- 17846 ----- 18462 ----- 19390 ----- 20189 ----- 20852 ----- 21642 ----- 22379 ----- 23009 ----- 23856 ----- 24734 ----- 25473 ----- 26231 ----- 26978 ----- 27654 ----- 28276 ----- 29054 ----- 29727 ----- 30527 ----- 31177 ----- 32126 ----- 32984 ----- 33684 ----- 34316 ----- 35070 ----- 35703 ----- 36397 ----- 37156 ----- 37709 ----- 38488 ----- 39131 ----- 39740 ----- 40736 ----- 41459 ----- 42388 ----- 42999 ----- 43844 ----- 44571 ----- 45428 ----- 46283 ----- 46979 ----- 47712 ----- 48708 ----- 49487 ----- 50275 ----- 51131 ----- 51836 ----- 52652 ----- 53389 ----- 54179 ----- 54861 ----- 55609 ----- 56492 ----- 57284 ----- 57917 ----- 58793 ----- 59665 ----- 60285 ----- 60840 ----- 61523 ----- 62354 ----- 63178 ----- 63933 ----- 64642 ----- 65282 ----- 65864 ----- 66755 ----- 67407 ----- 68099 ----- 68875 ----- 69638 ----- 70304 ----- 71016 ----- 71830 ----- 72742 ----- 73590 ----- 74434 ----- 75274 ----- 75861 ----- 76547 ----- 77252 ----- 77978 ----- 78650 ----- 79313 ----- 79925 ----- 80677 ----- 81497 ----- 82205 ----- 82962 ----- 83879 ----- 84815 ----- 85521 ----- 86272 ----- 87140 ----- 87759 ----- 88634 ----- 89452 ----- 90192 ----- 90920 ----- 91756 ----- 92690 ----- 93299 ----- 93950 ----- 94812 ----- 95569 ----- 96295 ----- 96904 ----- 97499 ----- 98144 ----- 98764 ----- 99582 ----- 100453 ----- 101098 ----- 101892 ----- 102700 ----- 103419 ----- 104297 ----- 105040 ----- 105864 ----- 106498 ----- 107196 ----- 108022 ----- 108731 ----- 109398 ----- 110145 ----- 110849 ----- 111758 ----- 112501 ----- 113222 ----- 114019 ----- 114904 ----- 115693 ----- 116350 ----- 116955 ----- 117581 ----- 118366 ----- 119159 ----- 119902 ----- 120535 ----- 121321 ----- 121993 ----- 122769 ----- 123504 ----- 124225 ----- 124992 ----- 125632 ----- 126685 ----- 127641 ----- 128303 ----- 129042 ----- 129589 ----- 130548 ----- 131374 ----- 132325 ----- 133042 ----- 133883 ----- 134716 ----- 135520 ----- 136173 ----- 136858 ----- 137584 ----- 138381 ----- 139162 ----- 139923 ----- 140738 ----- 141557 ----- 142287 ----- 143002 ----- 143794 ----- 144420 ----- 145276 ----- 146100 ----- 146977 ----- 147821 ----- 148440 ----- 149247 ----- 149978 100 │ │ │ │ │ │ histogram(4)= 0 6255 0 5190 0 6345 0 5505 0 5520 0 6585 0 6405 0 5895 0 5640 0 5775 0 5745 0 6630 0 6300 0 6255 0 6105 0 5760 0 6270 0 5850 0 6195 0 6330 0 6150 0 5820 0 5790 0 5985 0 5700 101 │ │ │ │ │ │ <--- 0 ---- 1 ---- 2 ---- 3 ---- 4 ---- 5 ---- 6 ---- 7 ---- 8 ---- 9 ---- 10 --- 11 --- 12 --- 13 --- 14 --- 15 --- 16 --- 17 --- 18 --- 19 --- 20 --- 21 --- 22 --- 23 --- 24 102 │ │ │ │ │ ├── key: (1) 103 │ │ │ │ │ └── fd: (1)-->(2-6,8) 104 │ │ │ │ ├── inner-join (lookup lineitem) 105 │ │ │ │ │ ├── save-table-name: q10_lookup_join_8 106 │ │ │ │ │ ├── columns: o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) l_orderkey:18(int!null) l_extendedprice:23(float!null) l_discount:24(float!null) l_returnflag:26(char!null) 107 │ │ │ │ │ ├── key columns: [9] = [18] 108 │ │ │ │ │ ├── stats: [rows=90518.8126, distinct(9)=55061.5385, null(9)=0, distinct(10)=37710.0545, null(10)=0, distinct(13)=92, null(13)=0, distinct(18)=55061.5385, null(18)=0, distinct(23)=85913.1168, null(23)=0, distinct(24)=11, null(24)=0, distinct(26)=1, null(26)=0] 109 │ │ │ │ │ ├── fd: ()-->(26), (9)-->(10,13), (9)==(18), (18)==(9) 110 │ │ │ │ │ ├── index-join orders 111 │ │ │ │ │ │ ├── save-table-name: q10_index_join_9 112 │ │ │ │ │ │ ├── columns: o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) 113 │ │ │ │ │ │ ├── stats: [rows=55061.5385, distinct(9)=55061.5385, null(9)=0, distinct(10)=42917.9526, null(10)=0, distinct(13)=92, null(13)=0] 114 │ │ │ │ │ │ │ histogram(13)= 0 450 6900 600 6600 1350 6900 1050 6300 1050 6750 1050 6900 450 7050 600 530.77 530.77 115 │ │ │ │ │ │ │ <--- '1993-10-01' ------ '1993-10-13' ------ '1993-10-22' ------ '1993-11-04' ------ '1993-11-16' ------ '1993-11-30' ------ '1993-12-16' ------ '1993-12-29' -------- '1993-12-31' 116 │ │ │ │ │ │ ├── key: (9) 117 │ │ │ │ │ │ ├── fd: (9)-->(10,13) 118 │ │ │ │ │ │ └── scan orders@o_od 119 │ │ │ │ │ │ ├── save-table-name: q10_scan_10 120 │ │ │ │ │ │ ├── columns: o_orderkey:9(int!null) o_orderdate:13(date!null) 121 │ │ │ │ │ │ ├── constraint: /13/9: [/'1993-10-01' - /'1993-12-31'] 122 │ │ │ │ │ │ ├── stats: [rows=55061.5385, distinct(9)=55061.5385, null(9)=0, distinct(13)=92, null(13)=0] 123 │ │ │ │ │ │ │ histogram(9)= 0 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 269.8 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 275.31 5.5062 124 │ │ │ │ │ │ │ <--- 998 -------- 26593 ------- 56613 ------- 85827 ------- 115587 ------- 140512 ------- 167076 ------- 198882 ------- 228580 ------- 262243 ------- 292064 ------- 316161 ------- 344194 ------- 368550 ------- 402722 ------- 434529 ------- 468807 ------- 495107 ------- 526338 ------- 563239 ------- 597733 ------- 641894 ------- 665730 ------- 688742 ------- 721767 ------- 750050 ------- 787879 ------- 814565 ------- 837381 ------- 866657 ------- 902855 ------- 927172 ------- 952772 ------- 975840 ------- 1008646 ------- 1032066 ------- 1074656 ------- 1104896 ------- 1138369 ------- 1166689 ------- 1194912 ------- 1225222 ------- 1253284 ------- 1284803 ------- 1318499 ------- 1348609 ------- 1376609 ------- 1404933 ------- 1435745 ------- 1460325 ------- 1491648 ------- 1518852 ------- 1542567 ------- 1574277 ------- 1597958 ------- 1624964 ------- 1647555 ------- 1683651 ------- 1717189 ------- 1750563 ------- 1784775 ------- 1812449 ------- 1838850 ------- 1867751 ------- 1891783 ------- 1918790 ------- 1949827 ------- 1986021 ------- 2020134 ------- 2045829 ------- 2077894 ------- 2118912 ------- 2143264 ------- 2176486 ------- 2204838 ------- 2234146 ------- 2260484 ------- 2288512 ------- 2317121 ------- 2344321 ------- 2362567 ------- 2396133 ------- 2423623 ------- 2454884 ------- 2483879 ------- 2520162 ------- 2545574 ------- 2571874 ------- 2608417 ------- 2637477 ------- 2665607 ------- 2692231 ------- 2724261 ------- 2764451 ------- 2798656 ------- 2824775 ------- 2858690 ------- 2881990 ------- 2911814 ------- 2945056 ------- 2981315 ------- 3010306 ------- 3032320 ------- 3064389 ------- 3084166 ------- 3108832 ------- 3136032 ------- 3168484 ------- 3204039 ------- 3229922 ------- 3256549 ------- 3283169 ------- 3315236 ------- 3346756 ------- 3375879 ------- 3408007 ------- 3441827 ------- 3471204 ------- 3505414 ------- 3527938 ------- 3562561 ------- 3598630 ------- 3627270 ------- 3659266 ------- 3686468 ------- 3713505 ------- 3750817 ------- 3777696 ------- 3808129 ------- 3834533 ------- 3871968 ------- 3904931 ------- 3934215 ------- 3960643 ------- 3990336 ------- 4023203 ------- 4057826 ------- 4090691 ------- 4121216 ------- 4159681 ------- 4190726 ------- 4222624 ------- 4246567 ------- 4283653 ------- 4314339 ------- 4342592 ------- 4373829 ------- 4402372 ------- 4431332 ------- 4464452 ------- 4496327 -------- 4526789 -------- 4551109 -------- 4582401 -------- 4615974 -------- 4648482 -------- 4669601 -------- 4705891 -------- 4732869 -------- 4766145 -------- 4805862 -------- 4840677 -------- 4866787 -------- 4895332 -------- 4928100 -------- 4956323 -------- 4992161 -------- 5027008 -------- 5065797 -------- 5099011 -------- 5130592 -------- 5163463 -------- 5197314 -------- 5224994 -------- 5253892 -------- 5277638 -------- 5307105 -------- 5335750 -------- 5369828 -------- 5402528 -------- 5434183 -------- 5464227 -------- 5491072 -------- 5520679 -------- 5543047 -------- 5576708 -------- 5614951 -------- 5646055 -------- 5674721 -------- 5700295 -------- 5732066 -------- 5761255 -------- 5791233 -------- 5819651 -------- 5852291 -------- 5880258 -------- 5909062 -------- 5943111 -------- 5973926 -------- 5998752 125 │ │ │ │ │ │ │ histogram(13)= 0 450 6900 600 6600 1350 6900 1050 6300 1050 6750 1050 6900 450 7050 600 530.77 530.77 126 │ │ │ │ │ │ │ <--- '1993-10-01' ------ '1993-10-13' ------ '1993-10-22' ------ '1993-11-04' ------ '1993-11-16' ------ '1993-11-30' ------ '1993-12-16' ------ '1993-12-29' -------- '1993-12-31' 127 │ │ │ │ │ │ ├── key: (9) 128 │ │ │ │ │ │ └── fd: (9)-->(13) 129 │ │ │ │ │ └── filters 130 │ │ │ │ │ └── l_returnflag:26 = 'R' [type=bool, outer=(26), constraints=(/26: [/'R' - /'R']; tight), fd=()-->(26)] 131 │ │ │ │ └── filters 132 │ │ │ │ └── c_custkey:1 = o_custkey:10 [type=bool, outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 133 │ │ │ ├── scan nation 134 │ │ │ │ ├── save-table-name: q10_scan_11 135 │ │ │ │ ├── columns: n_nationkey:34(int!null) n_name:35(char!null) 136 │ │ │ │ ├── stats: [rows=25, distinct(34)=25, null(34)=0, distinct(35)=25, null(35)=0] 137 │ │ │ │ │ histogram(34)= 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 138 │ │ │ │ │ <--- 0 --- 1 --- 2 --- 3 --- 4 --- 5 --- 6 --- 7 --- 8 --- 9 --- 10 --- 11 --- 12 --- 13 --- 14 --- 15 --- 16 --- 17 --- 18 --- 19 --- 20 --- 21 --- 22 --- 23 --- 24 139 │ │ │ │ ├── key: (34) 140 │ │ │ │ └── fd: (34)-->(35) 141 │ │ │ └── filters 142 │ │ │ └── c_nationkey:4 = n_nationkey:34 [type=bool, outer=(4,34), constraints=(/4: (/NULL - ]; /34: (/NULL - ]), fd=(4)==(34), (34)==(4)] 143 │ │ └── projections 144 │ │ └── l_extendedprice:23 * (1.0 - l_discount:24) [as=column38:38, type=float, outer=(23,24)] 145 │ └── aggregations 146 │ ├── sum [as=sum:39, type=float, outer=(38)] 147 │ │ └── column38:38 [type=float] 148 │ ├── const-agg [as=c_name:2, type=varchar, outer=(2)] 149 │ │ └── c_name:2 [type=varchar] 150 │ ├── const-agg [as=c_address:3, type=varchar, outer=(3)] 151 │ │ └── c_address:3 [type=varchar] 152 │ ├── const-agg [as=c_phone:5, type=char, outer=(5)] 153 │ │ └── c_phone:5 [type=char] 154 │ ├── const-agg [as=c_acctbal:6, type=float, outer=(6)] 155 │ │ └── c_acctbal:6 [type=float] 156 │ ├── const-agg [as=c_comment:8, type=varchar, outer=(8)] 157 │ │ └── c_comment:8 [type=varchar] 158 │ └── const-agg [as=n_name:35, type=char, outer=(35)] 159 │ └── n_name:35 [type=char] 160 └── 20 [type=int] 161 162 stats table=q10_limit_1 163 ---- 164 column_names row_count distinct_count null_count 165 {c_acctbal} 20 20 0 166 {c_address} 20 20 0 167 {c_comment} 20 20 0 168 {c_custkey} 20 20 0 169 {c_name} 20 20 0 170 {c_phone} 20 20 0 171 {n_name} 20 13 0 172 {revenue} 20 20 0 173 ~~~~ 174 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 175 {c_acctbal} 20.00 1.00 20.00 1.00 0.00 1.00 176 {c_address} 20.00 1.00 20.00 1.00 0.00 1.00 177 {c_comment} 20.00 1.00 20.00 1.00 0.00 1.00 178 {c_custkey} 20.00 1.00 20.00 1.00 0.00 1.00 179 {c_name} 20.00 1.00 20.00 1.00 0.00 1.00 180 {c_phone} 20.00 1.00 20.00 1.00 0.00 1.00 181 {n_name} 20.00 1.00 20.00 1.54 0.00 1.00 182 {revenue} 20.00 1.00 20.00 1.00 0.00 1.00 183 184 stats table=q10_sort_2 185 ---- 186 column_names row_count distinct_count null_count 187 {c_acctbal} 0 0 0 188 {c_address} 0 0 0 189 {c_comment} 0 0 0 190 {c_custkey} 0 0 0 191 {c_name} 0 0 0 192 {c_phone} 0 0 0 193 {n_name} 0 0 0 194 {sum} 0 0 0 195 ~~~~ 196 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 197 {c_acctbal} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 198 {c_address} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 199 {c_comment} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 200 {c_custkey} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 201 {c_name} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 202 {c_phone} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 203 {n_name} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 204 {sum} 42918.00 +Inf <== 42918.00 +Inf <== 0.00 1.00 205 206 stats table=q10_group_by_3 207 ---- 208 column_names row_count distinct_count null_count 209 {c_acctbal} 37967 37658 0 210 {c_address} 37967 38065 0 211 {c_comment} 37967 38086 0 212 {c_custkey} 37967 37904 0 213 {c_name} 37967 37859 0 214 {c_phone} 37967 38026 0 215 {n_name} 37967 25 0 216 {sum} 37967 37934 0 217 ~~~~ 218 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 219 {c_acctbal} 42918.00 1.13 42918.00 1.14 0.00 1.00 220 {c_address} 42918.00 1.13 42918.00 1.13 0.00 1.00 221 {c_comment} 42918.00 1.13 42918.00 1.13 0.00 1.00 222 {c_custkey} 42918.00 1.13 42918.00 1.13 0.00 1.00 223 {c_name} 42918.00 1.13 42918.00 1.13 0.00 1.00 224 {c_phone} 42918.00 1.13 42918.00 1.13 0.00 1.00 225 {n_name} 42918.00 1.13 42918.00 1716.72 <== 0.00 1.00 226 {sum} 42918.00 1.13 42918.00 1.13 0.00 1.00 227 228 stats table=q10_project_4 229 ---- 230 column_names row_count distinct_count null_count 231 {c_acctbal} 114705 37658 0 232 {c_address} 114705 38065 0 233 {c_comment} 114705 38086 0 234 {c_custkey} 114705 37904 0 235 {c_name} 114705 37859 0 236 {c_phone} 114705 38026 0 237 {column38} 114705 114608 0 238 {n_name} 114705 25 0 239 ~~~~ 240 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 241 {c_acctbal} 91241.00 1.26 67126.00 1.78 0.00 1.00 242 {c_address} 91241.00 1.26 68349.00 1.80 0.00 1.00 243 {c_comment} 91241.00 1.26 68272.00 1.79 0.00 1.00 244 {c_custkey} 91241.00 1.26 42918.00 1.13 0.00 1.00 245 {c_name} 91241.00 1.26 68356.00 1.81 0.00 1.00 246 {c_phone} 91241.00 1.26 68356.00 1.80 0.00 1.00 247 {column38} 91241.00 1.26 88237.00 1.30 0.00 1.00 248 {n_name} 91241.00 1.26 25.00 1.00 0.00 1.00 249 250 stats table=q10_inner_join_5 251 ---- 252 column_names row_count distinct_count null_count 253 {c_acctbal} 114705 37658 0 254 {c_address} 114705 38065 0 255 {c_comment} 114705 38086 0 256 {c_custkey} 114705 37904 0 257 {c_name} 114705 37859 0 258 {c_nationkey} 114705 25 0 259 {c_phone} 114705 38026 0 260 {l_discount} 114705 11 0 261 {l_extendedprice} 114705 106228 0 262 {l_orderkey} 114705 48516 0 263 {l_returnflag} 114705 1 0 264 {n_name} 114705 25 0 265 {n_nationkey} 114705 25 0 266 {o_custkey} 114705 37904 0 267 {o_orderdate} 114705 92 0 268 {o_orderkey} 114705 48516 0 269 ~~~~ 270 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 271 {c_acctbal} 91241.00 1.26 67126.00 1.78 0.00 1.00 272 {c_address} 91241.00 1.26 68349.00 1.80 0.00 1.00 273 {c_comment} 91241.00 1.26 68272.00 1.79 0.00 1.00 274 {c_custkey} 91241.00 1.26 42918.00 1.13 0.00 1.00 275 {c_name} 91241.00 1.26 68356.00 1.81 0.00 1.00 276 {c_nationkey} 91241.00 1.26 25.00 1.00 0.00 1.00 277 {c_phone} 91241.00 1.26 68356.00 1.80 0.00 1.00 278 {l_discount} 91241.00 1.26 11.00 1.00 0.00 1.00 279 {l_extendedprice} 91241.00 1.26 86214.00 1.23 0.00 1.00 280 {l_orderkey} 91241.00 1.26 44561.00 1.09 0.00 1.00 281 {l_returnflag} 91241.00 1.26 1.00 1.00 0.00 1.00 282 {n_name} 91241.00 1.26 25.00 1.00 0.00 1.00 283 {n_nationkey} 91241.00 1.26 25.00 1.00 0.00 1.00 284 {o_custkey} 91241.00 1.26 42918.00 1.13 0.00 1.00 285 {o_orderdate} 91241.00 1.26 92.00 1.00 0.00 1.00 286 {o_orderkey} 91241.00 1.26 44561.00 1.09 0.00 1.00 287 288 stats table=q10_inner_join_6 289 ---- 290 column_names row_count distinct_count null_count 291 {c_acctbal} 114705 37658 0 292 {c_address} 114705 38065 0 293 {c_comment} 114705 38086 0 294 {c_custkey} 114705 37904 0 295 {c_name} 114705 37859 0 296 {c_nationkey} 114705 25 0 297 {c_phone} 114705 38026 0 298 {l_discount} 114705 11 0 299 {l_extendedprice} 114705 106228 0 300 {l_orderkey} 114705 48516 0 301 {l_returnflag} 114705 1 0 302 {o_custkey} 114705 37904 0 303 {o_orderdate} 114705 92 0 304 {o_orderkey} 114705 48516 0 305 ~~~~ 306 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 307 {c_acctbal} 91241.00 1.26 67126.00 1.78 0.00 1.00 308 {c_address} 91241.00 1.26 68349.00 1.80 0.00 1.00 309 {c_comment} 91241.00 1.26 68272.00 1.79 0.00 1.00 310 {c_custkey} 91241.00 1.26 37710.00 1.01 0.00 1.00 311 {c_name} 91241.00 1.26 68357.00 1.81 0.00 1.00 312 {c_nationkey} 91241.00 1.26 25.00 1.00 0.00 1.00 313 {c_phone} 91241.00 1.26 68357.00 1.80 0.00 1.00 314 {l_discount} 91241.00 1.26 11.00 1.00 0.00 1.00 315 {l_extendedprice} 91241.00 1.26 56208.00 1.89 0.00 1.00 316 {l_orderkey} 91241.00 1.26 44562.00 1.09 0.00 1.00 317 {l_returnflag} 91241.00 1.26 1.00 1.00 0.00 1.00 318 {o_custkey} 91241.00 1.26 37710.00 1.01 0.00 1.00 319 {o_orderdate} 91241.00 1.26 92.00 1.00 0.00 1.00 320 {o_orderkey} 91241.00 1.26 44562.00 1.09 0.00 1.00 321 322 stats table=q10_scan_7 323 ---- 324 column_names row_count distinct_count null_count 325 {c_acctbal} 150000 140628 0 326 {c_address} 150000 149937 0 327 {c_comment} 150000 149323 0 328 {c_custkey} 150000 148813 0 329 {c_name} 150000 151126 0 330 {c_nationkey} 150000 25 0 331 {c_phone} 150000 150872 0 332 ~~~~ 333 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 334 {c_acctbal} 150000.00 1.00 140628.00 1.00 0.00 1.00 335 {c_address} 150000.00 1.00 149937.00 1.00 0.00 1.00 336 {c_comment} 150000.00 1.00 149323.00 1.00 0.00 1.00 337 {c_custkey} 150000.00 1.00 148813.00 1.00 0.00 1.00 338 {c_name} 150000.00 1.00 150000.00 1.01 0.00 1.00 339 {c_nationkey} 150000.00 1.00 25.00 1.00 0.00 1.00 340 {c_phone} 150000.00 1.00 150000.00 1.01 0.00 1.00 341 342 stats table=q10_lookup_join_8 343 ---- 344 column_names row_count distinct_count null_count 345 {l_discount} 114705 11 0 346 {l_extendedprice} 114705 106228 0 347 {l_orderkey} 114705 48516 0 348 {l_returnflag} 114705 1 0 349 {o_custkey} 114705 37904 0 350 {o_orderdate} 114705 92 0 351 {o_orderkey} 114705 48516 0 352 ~~~~ 353 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 354 {l_discount} 90519.00 1.27 11.00 1.00 0.00 1.00 355 {l_extendedprice} 90519.00 1.27 85913.00 1.24 0.00 1.00 356 {l_orderkey} 90519.00 1.27 55062.00 1.13 0.00 1.00 357 {l_returnflag} 90519.00 1.27 1.00 1.00 0.00 1.00 358 {o_custkey} 90519.00 1.27 37710.00 1.01 0.00 1.00 359 {o_orderdate} 90519.00 1.27 92.00 1.00 0.00 1.00 360 {o_orderkey} 90519.00 1.27 55062.00 1.13 0.00 1.00 361 362 stats table=q10_index_join_9 363 ---- 364 column_names row_count distinct_count null_count 365 {o_custkey} 57069 42598 0 366 {o_orderdate} 57069 92 0 367 {o_orderkey} 57069 56240 0 368 ~~~~ 369 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 370 {o_custkey} 55062.00 1.04 42918.00 1.01 0.00 1.00 371 {o_orderdate} 55062.00 1.04 92.00 1.00 0.00 1.00 372 {o_orderkey} 55062.00 1.04 55062.00 1.02 0.00 1.00 373 374 stats table=q10_scan_11 375 ---- 376 column_names row_count distinct_count null_count 377 {n_name} 25 25 0 378 {n_nationkey} 25 25 0 379 ~~~~ 380 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 381 {n_name} 25.00 1.00 25.00 1.00 0.00 1.00 382 {n_nationkey} 25.00 1.00 25.00 1.00 0.00 1.00