github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats_quality/tpch/q03 (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q3 9 # Shipping Priority 10 # Retrieves the 10 unshipped orders with the highest value. 11 # 12 # Retrieves the shipping priority and potential revenue, defined as the sum of 13 # l_extendedprice * (1-l_discount), of the orders having the largest revenue 14 # among those that had not been shipped as of a given date. Orders are listed in 15 # decreasing order of revenue. If more than 10 unshipped orders exist, only the 16 # 10 orders with the largest revenue are listed. 17 # -------------------------------------------------- 18 save-tables database=tpch save-tables-prefix=q3 19 SELECT 20 l_orderkey, 21 sum(l_extendedprice * (1 - l_discount)) AS revenue, 22 o_orderdate, 23 o_shippriority 24 FROM 25 customer, 26 orders, 27 lineitem 28 WHERE 29 c_mktsegment = 'BUILDING' 30 AND c_custkey = o_custkey 31 AND l_orderkey = o_orderkey 32 AND o_orderDATE < DATE '1995-03-15' 33 AND l_shipdate > DATE '1995-03-15' 34 GROUP BY 35 l_orderkey, 36 o_orderdate, 37 o_shippriority 38 ORDER BY 39 revenue DESC, 40 o_orderdate 41 LIMIT 10; 42 ---- 43 limit 44 ├── save-table-name: q3_limit_1 45 ├── columns: l_orderkey:18(int!null) revenue:35(float!null) o_orderdate:13(date!null) o_shippriority:16(int!null) 46 ├── internal-ordering: -35,+13 47 ├── cardinality: [0 - 10] 48 ├── stats: [rows=10, distinct(13)=10, null(13)=0, distinct(16)=10, null(16)=0, distinct(18)=10, null(18)=0, distinct(35)=10, null(35)=0] 49 ├── key: (18) 50 ├── fd: (18)-->(13,16,35) 51 ├── ordering: -35,+13 52 ├── sort 53 │ ├── save-table-name: q3_sort_2 54 │ ├── columns: o_orderdate:13(date!null) o_shippriority:16(int!null) l_orderkey:18(int!null) sum:35(float!null) 55 │ ├── stats: [rows=359560.406, distinct(13)=359560.406, null(13)=0, distinct(16)=359560.406, null(16)=0, distinct(18)=359560.406, null(18)=0, distinct(35)=359560.406, null(35)=0] 56 │ ├── key: (18) 57 │ ├── fd: (18)-->(13,16,35) 58 │ ├── ordering: -35,+13 59 │ ├── limit hint: 10.00 60 │ └── group-by 61 │ ├── save-table-name: q3_group_by_3 62 │ ├── columns: o_orderdate:13(date!null) o_shippriority:16(int!null) l_orderkey:18(int!null) sum:35(float!null) 63 │ ├── grouping columns: l_orderkey:18(int!null) 64 │ ├── stats: [rows=359560.406, distinct(13)=359560.406, null(13)=0, distinct(16)=359560.406, null(16)=0, distinct(18)=359560.406, null(18)=0, distinct(35)=359560.406, null(35)=0] 65 │ ├── key: (18) 66 │ ├── fd: (18)-->(13,16,35) 67 │ ├── project 68 │ │ ├── save-table-name: q3_project_4 69 │ │ ├── columns: column34:34(float!null) o_orderdate:13(date!null) o_shippriority:16(int!null) l_orderkey:18(int!null) 70 │ │ ├── stats: [rows=493779.215, distinct(13)=1169, null(13)=0, distinct(16)=1, null(16)=0, distinct(18)=359560.406, null(18)=0, distinct(34)=410295.908, null(34)=0] 71 │ │ ├── fd: (18)-->(13,16) 72 │ │ ├── inner-join (lookup lineitem) 73 │ │ │ ├── save-table-name: q3_lookup_join_5 74 │ │ │ ├── columns: c_custkey:1(int!null) c_mktsegment:7(char!null) o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) o_shippriority:16(int!null) l_orderkey:18(int!null) l_extendedprice:23(float!null) l_discount:24(float!null) l_shipdate:28(date!null) 75 │ │ │ ├── key columns: [9] = [18] 76 │ │ │ ├── stats: [rows=493779.215, distinct(1)=29974.3087, null(1)=0, distinct(7)=1, null(7)=0, distinct(9)=359560.406, null(9)=0, distinct(10)=29974.3087, null(10)=0, distinct(13)=1169, null(13)=0, distinct(16)=1, null(16)=0, distinct(18)=359560.406, null(18)=0, distinct(23)=363943.091, null(23)=0, distinct(24)=11, null(24)=0, distinct(28)=1352, null(28)=0, distinct(23,24)=410295.908, null(23,24)=0] 77 │ │ │ ├── fd: ()-->(7), (9)-->(10,13,16), (9)==(18), (18)==(9), (1)==(10), (10)==(1) 78 │ │ │ ├── inner-join (hash) 79 │ │ │ │ ├── save-table-name: q3_inner_join_6 80 │ │ │ │ ├── columns: c_custkey:1(int!null) c_mktsegment:7(char!null) o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) o_shippriority:16(int!null) 81 │ │ │ │ ├── stats: [rows=220818.994, distinct(1)=29974.3087, null(1)=0, distinct(7)=1, null(7)=0, distinct(9)=190731.989, null(9)=0, distinct(10)=29974.3087, null(10)=0, distinct(13)=1169, null(13)=0, distinct(16)=1, null(16)=0] 82 │ │ │ │ ├── key: (9) 83 │ │ │ │ ├── fd: ()-->(7), (9)-->(10,13,16), (1)==(10), (10)==(1) 84 │ │ │ │ ├── select 85 │ │ │ │ │ ├── save-table-name: q3_select_7 86 │ │ │ │ │ ├── columns: o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) o_shippriority:16(int!null) 87 │ │ │ │ │ ├── stats: [rows=734900, distinct(9)=734900, null(9)=0, distinct(10)=99841.9545, null(10)=0, distinct(13)=1169, null(13)=0, distinct(16)=1, null(16)=0] 88 │ │ │ │ │ │ histogram(13)= 0 450 6600 900 6300 1350 7200 300 6750 1050 7350 300 7350 150 7200 300 7350 1200 6900 750 6600 900 6750 900 6300 1350 7350 750 7050 450 6900 900 7350 150 7200 450 7050 750 6750 750 6900 750 7350 600 6750 900 6750 750 7050 450 7050 900 6900 1050 6600 900 7350 750 6900 1050 6750 750 6750 900 7050 450 7050 750 7200 450 7050 750 6300 1800 6600 750 7200 1200 7050 600 7050 450 6600 1050 7050 450 6900 450 7200 450 6750 600 6600 900 7050 1200 6600 1200 7050 750 6600 900 6750 600 7200 450 6900 600 6600 1350 6900 1050 6300 1050 6750 1050 6900 450 7050 600 6900 450 7200 1500 7200 300 6450 900 7200 1050 6600 900 7050 600 6900 900 6600 900 6600 750 6900 1050 6450 900 7050 1350 6750 1050 6150 1500 7200 600 6900 600 6900 450 7050 300 6600 750 7200 600 6750 600 7200 300 7200 1350 7050 1200 6600 1050 7050 900 7050 450 6450 1050 6900 450 7050 1050 6750 900 6750 1200 6750 600 7200 1050 6150 1500 2400 800 89 │ │ │ │ │ │ <--- '1992-01-01' ------ '1992-01-11' ------ '1992-01-23' ------ '1992-02-04' ------ '1992-02-19' ------ '1992-03-03' ------ '1992-03-18' ------ '1992-03-29' ------ '1992-04-11' ------ '1992-04-23' ------ '1992-05-08' ------ '1992-05-20' ------ '1992-05-30' ------ '1992-06-11' ------ '1992-06-24' ------ '1992-07-05' ------ '1992-07-18' ------ '1992-07-30' ------ '1992-08-11' ------ '1992-08-23' ------ '1992-09-07' ------ '1992-09-21' ------ '1992-10-04' ------ '1992-10-18' ------ '1992-10-28' ------ '1992-11-07' ------ '1992-11-18' ------ '1992-11-30' ------ '1992-12-10' ------ '1992-12-25' ------ '1993-01-06' ------ '1993-01-19' ------ '1993-02-01' ------ '1993-02-14' ------ '1993-02-24' ------ '1993-03-08' ------ '1993-03-19' ------ '1993-04-03' ------ '1993-04-14' ------ '1993-04-25' ------ '1993-05-12' ------ '1993-05-25' ------ '1993-06-10' ------ '1993-06-18' ------ '1993-07-01' ------ '1993-07-12' ------ '1993-07-24' ------ '1993-08-05' ------ '1993-08-16' ------ '1993-08-29' ------ '1993-09-08' ------ '1993-09-19' ------ '1993-10-01' ------ '1993-10-13' ------ '1993-10-22' ------ '1993-11-04' ------ '1993-11-16' ------ '1993-11-30' ------ '1993-12-16' ------ '1993-12-29' ------ '1994-01-12' ------ '1994-01-23' ------ '1994-02-04' ------ '1994-02-16' ------ '1994-03-01' ------ '1994-03-14' ------ '1994-03-24' ------ '1994-04-06' ------ '1994-04-20' ------ '1994-05-01' ------ '1994-05-12' ------ '1994-05-23' ------ '1994-06-03' ------ '1994-06-16' ------ '1994-06-25' ------ '1994-07-08' ------ '1994-07-19' ------ '1994-08-01' ------ '1994-08-14' ------ '1994-08-25' ------ '1994-09-06' ------ '1994-09-18' ------ '1994-10-01' ------ '1994-10-17' ------ '1994-10-30' ------ '1994-11-10' ------ '1994-11-22' ------ '1994-12-02' ------ '1994-12-16' ------ '1994-12-30' ------ '1995-01-13' ------ '1995-01-25' ------ '1995-02-04' ------ '1995-02-16' ------ '1995-02-26' ------ '1995-03-10' ------ '1995-03-14' 90 │ │ │ │ │ ├── key: (9) 91 │ │ │ │ │ ├── fd: (9)-->(10,13,16) 92 │ │ │ │ │ ├── scan orders 93 │ │ │ │ │ │ ├── save-table-name: q3_scan_8 94 │ │ │ │ │ │ ├── columns: o_orderkey:9(int!null) o_custkey:10(int!null) o_orderdate:13(date!null) o_shippriority:16(int!null) 95 │ │ │ │ │ │ ├── stats: [rows=1500000, distinct(9)=1500000, null(9)=0, distinct(10)=99846, null(10)=0, distinct(13)=2406, null(13)=0, distinct(16)=1, null(16)=0] 96 │ │ │ │ │ │ │ histogram(9)= 0 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 97 │ │ │ │ │ │ │ <--- 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 98 │ │ │ │ │ │ │ histogram(10)= 0 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7200 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7200 300 7350 150 7350 150 7350 150 7350 150 7200 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7200 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 300 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7350 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 300 7350 150 7500 150 7500 150 7500 150 7500 150 7350 300 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7500 150 7350 300 7500 150 7500 150 7500 150 7500 150 7500 150 7350 300 7500 150 99 │ │ │ │ │ │ │ <--- 8 ------ 763 ------ 1477 ------ 2353 ------ 3229 ------ 4261 ------ 5191 ------ 5924 ------ 6406 ------ 7126 ------ 7870 ------ 8545 ------ 9301 ------ 9911 ------ 10591 ------ 11236 ------ 11854 ------ 12622 ------ 13426 ------ 14036 ------ 14708 ------ 15391 ------ 16138 ------ 16925 ------ 17681 ------ 18388 ------ 19156 ------ 20015 ------ 20660 ------ 21632 ------ 22472 ------ 23260 ------ 24055 ------ 24649 ------ 25352 ------ 25952 ------ 26774 ------ 27358 ------ 28070 ------ 28586 ------ 29296 ------ 30028 ------ 30629 ------ 31400 ------ 32102 ------ 32884 ------ 33796 ------ 34646 ------ 35479 ------ 36161 ------ 36811 ------ 37612 ------ 38314 ------ 39145 ------ 39844 ------ 40591 ------ 41254 ------ 41902 ------ 42496 ------ 43237 ------ 43948 ------ 44690 ------ 45481 ------ 46448 ------ 47242 ------ 47884 ------ 48676 ------ 49441 ------ 50155 ------ 50857 ------ 51463 ------ 52321 ------ 53014 ------ 53587 ------ 54413 ------ 55019 ------ 55771 ------ 56482 ------ 57314 ------ 58163 ------ 58894 ------ 59638 ------ 60478 ------ 61450 ------ 62417 ------ 63176 ------ 63944 ------ 64577 ------ 65344 ------ 66218 ------ 67021 ------ 67703 ------ 68477 ------ 69271 ------ 70073 ------ 70696 ------ 71512 ------ 72430 ------ 73345 ------ 74149 ------ 74780 ------ 75508 ------ 76273 ------ 76865 ------ 77666 ------ 78427 ------ 79042 ------ 79801 ------ 80557 ------ 81163 ------ 81833 ------ 82534 ------ 83368 ------ 84157 ------ 84938 ------ 85495 ------ 86257 ------ 87143 ------ 87998 ------ 88777 ------ 89566 ------ 90292 ------ 91099 ------ 91757 ------ 92401 ------ 93362 ------ 94252 ------ 94771 ------ 95527 ------ 96233 ------ 96952 ------ 97844 ------ 98411 ------ 99067 ------ 99691 ------ 100357 ------ 101009 ------ 101734 ------ 102569 ------ 103381 ------ 104114 ------ 105070 ------ 105884 ------ 106651 ------ 107393 ------ 107995 ------ 108772 ------ 109628 ------ 110383 ------ 111040 ------ 111658 ------ 112556 ------ 113338 ------ 114062 ------ 114895 ------ 115744 ------ 116554 ------ 117250 ------ 117967 ------ 118741 ------ 119540 ------ 120514 ------ 121333 ------ 122111 ------ 122989 ------ 123763 ------ 124735 ------ 125401 ------ 126022 ------ 126779 ------ 127618 ------ 128332 ------ 129167 ------ 129917 ------ 130669 ------ 131330 ------ 131911 ------ 132704 ------ 133553 ------ 134317 ------ 134960 ------ 135688 ------ 136519 ------ 137353 ------ 138061 ------ 138697 ------ 139468 ------ 140218 ------ 140942 ------ 141644 ------ 142415 ------ 143263 ------ 144352 ------ 145099 ------ 145811 ------ 146761 ------ 147643 ------ 148591 ------ 149164 ------ 149995 100 │ │ │ │ │ │ │ histogram(13)= 0 450 6600 900 6300 1350 7200 300 6750 1050 7350 300 7350 150 7200 300 7350 1200 6900 750 6600 900 6750 900 6300 1350 7350 750 7050 450 6900 900 7350 150 7200 450 7050 750 6750 750 6900 750 7350 600 6750 900 6750 750 7050 450 7050 900 6900 1050 6600 900 7350 750 6900 1050 6750 750 6750 900 7050 450 7050 750 7200 450 7050 750 6300 1800 6600 750 7200 1200 7050 600 7050 450 6600 1050 7050 450 6900 450 7200 450 6750 600 6600 900 7050 1200 6600 1200 7050 750 6600 900 6750 600 7200 450 6900 600 6600 1350 6900 1050 6300 1050 6750 1050 6900 450 7050 600 6900 450 7200 1500 7200 300 6450 900 7200 1050 6600 900 7050 600 6900 900 6600 900 6600 750 6900 1050 6450 900 7050 1350 6750 1050 6150 1500 7200 600 6900 600 6900 450 7050 300 6600 750 7200 600 6750 600 7200 300 7200 1350 7050 1200 6600 1050 7050 900 7050 450 6450 1050 6900 450 7050 1050 6750 900 6750 1200 6750 600 7200 1050 6150 1500 7200 300 7050 300 6750 900 6900 450 7200 1050 7200 450 7050 600 6750 750 6150 1200 7200 1200 6750 750 7050 450 7200 600 6450 1050 6600 600 7050 750 7050 150 6600 1200 6150 1050 6450 1050 6150 1050 6750 450 7050 300 5700 1500 7050 600 6300 1050 6900 750 6600 750 7050 1050 6600 750 6750 1050 7050 450 6900 900 7050 600 6600 750 6750 900 6900 750 6750 600 6300 1050 6750 600 6900 750 7050 900 6750 600 7050 450 6900 750 6600 600 7050 1050 6900 900 6900 750 6450 900 6750 600 6750 450 7050 450 7050 150 6600 1050 6900 600 6750 600 6300 900 6450 750 6600 600 6900 450 7050 750 6450 750 6300 900 6600 750 6450 1650 6450 750 6450 750 6900 450 5850 1350 6450 600 6300 1350 6750 750 6900 600 6900 1350 6000 1650 6600 750 6600 900 6300 1050 6300 750 6600 900 6000 900 6000 900 6600 450 6750 450 6450 750 6750 300 6450 450 6300 900 6450 450 6300 600 6300 750 6300 900 6750 1050 5850 1200 6150 1050 6300 750 6300 600 6600 750 6000 900 5550 1200 6300 450 5700 600 6000 450 101 │ │ │ │ │ │ │ <--- '1992-01-01' ------ '1992-01-11' ------ '1992-01-23' ------ '1992-02-04' ------ '1992-02-19' ------ '1992-03-03' ------ '1992-03-18' ------ '1992-03-29' ------ '1992-04-11' ------ '1992-04-23' ------ '1992-05-08' ------ '1992-05-20' ------ '1992-05-30' ------ '1992-06-11' ------ '1992-06-24' ------ '1992-07-05' ------ '1992-07-18' ------ '1992-07-30' ------ '1992-08-11' ------ '1992-08-23' ------ '1992-09-07' ------ '1992-09-21' ------ '1992-10-04' ------ '1992-10-18' ------ '1992-10-28' ------ '1992-11-07' ------ '1992-11-18' ------ '1992-11-30' ------ '1992-12-10' ------ '1992-12-25' ------ '1993-01-06' ------ '1993-01-19' ------ '1993-02-01' ------ '1993-02-14' ------ '1993-02-24' ------ '1993-03-08' ------ '1993-03-19' ------ '1993-04-03' ------ '1993-04-14' ------ '1993-04-25' ------ '1993-05-12' ------ '1993-05-25' ------ '1993-06-10' ------ '1993-06-18' ------ '1993-07-01' ------ '1993-07-12' ------ '1993-07-24' ------ '1993-08-05' ------ '1993-08-16' ------ '1993-08-29' ------ '1993-09-08' ------ '1993-09-19' ------ '1993-10-01' ------ '1993-10-13' ------ '1993-10-22' ------ '1993-11-04' ------ '1993-11-16' ------ '1993-11-30' ------ '1993-12-16' ------ '1993-12-29' ------ '1994-01-12' ------ '1994-01-23' ------ '1994-02-04' ------ '1994-02-16' ------ '1994-03-01' ------ '1994-03-14' ------ '1994-03-24' ------ '1994-04-06' ------ '1994-04-20' ------ '1994-05-01' ------ '1994-05-12' ------ '1994-05-23' ------ '1994-06-03' ------ '1994-06-16' ------ '1994-06-25' ------ '1994-07-08' ------ '1994-07-19' ------ '1994-08-01' ------ '1994-08-14' ------ '1994-08-25' ------ '1994-09-06' ------ '1994-09-18' ------ '1994-10-01' ------ '1994-10-17' ------ '1994-10-30' ------ '1994-11-10' ------ '1994-11-22' ------ '1994-12-02' ------ '1994-12-16' ------ '1994-12-30' ------ '1995-01-13' ------ '1995-01-25' ------ '1995-02-04' ------ '1995-02-16' ------ '1995-02-26' ------ '1995-03-10' ------ '1995-03-20' ------ '1995-04-02' ------ '1995-04-15' ------ '1995-04-28' ------ '1995-05-10' ------ '1995-05-25' ------ '1995-06-09' ------ '1995-06-20' ------ '1995-07-01' ------ '1995-07-13' ------ '1995-07-21' ------ '1995-08-01' ------ '1995-08-13' ------ '1995-08-25' ------ '1995-09-06' ------ '1995-09-18' ------ '1995-10-01' ------ '1995-10-11' ------ '1995-10-23' ------ '1995-11-01' ------ '1995-11-13' ------ '1995-11-26' ------ '1995-12-08' ------ '1995-12-22' ------ '1996-01-06' ------ '1996-01-18' ------ '1996-01-30' ------ '1996-02-13' ------ '1996-02-25' ------ '1996-03-09' ------ '1996-03-21' ------ '1996-04-04' ------ '1996-04-14' ------ '1996-04-27' ------ '1996-05-10' ------ '1996-05-22' ------ '1996-06-02' ------ '1996-06-12' ------ '1996-06-23' ------ '1996-07-07' ------ '1996-07-19' ------ '1996-08-01' ------ '1996-08-14' ------ '1996-08-25' ------ '1996-09-06' ------ '1996-09-20' ------ '1996-10-04' ------ '1996-10-15' ------ '1996-10-28' ------ '1996-11-11' ------ '1996-11-22' ------ '1996-12-04' ------ '1996-12-18' ------ '1996-12-29' ------ '1997-01-10' ------ '1997-01-22' ------ '1997-02-01' ------ '1997-02-13' ------ '1997-02-24' ------ '1997-03-09' ------ '1997-03-24' ------ '1997-04-04' ------ '1997-04-16' ------ '1997-04-26' ------ '1997-05-07' ------ '1997-05-19' ------ '1997-05-29' ------ '1997-06-08' ------ '1997-06-22' ------ '1997-07-05' ------ '1997-07-16' ------ '1997-07-28' ------ '1997-08-12' ------ '1997-08-23' ------ '1997-09-04' ------ '1997-09-17' ------ '1997-09-30' ------ '1997-10-09' ------ '1997-10-19' ------ '1997-10-31' ------ '1997-11-14' ------ '1997-11-24' ------ '1997-12-09' ------ '1997-12-24' ------ '1998-01-04' ------ '1998-01-16' ------ '1998-01-28' ------ '1998-02-07' ------ '1998-02-17' ------ '1998-03-01' ------ '1998-03-12' ------ '1998-03-24' ------ '1998-04-03' ------ '1998-04-14' ------ '1998-04-24' ------ '1998-05-03' ------ '1998-05-14' ------ '1998-05-26' ------ '1998-06-08' ------ '1998-06-20' ------ '1998-06-30' ------ '1998-07-11' ------ '1998-07-22' ------ '1998-08-02' 102 │ │ │ │ │ │ ├── key: (9) 103 │ │ │ │ │ │ └── fd: (9)-->(10,13,16) 104 │ │ │ │ │ └── filters 105 │ │ │ │ │ └── o_orderdate:13 < '1995-03-15' [type=bool, outer=(13), constraints=(/13: (/NULL - /'1995-03-14']; tight)] 106 │ │ │ │ ├── select 107 │ │ │ │ │ ├── save-table-name: q3_select_9 108 │ │ │ │ │ ├── columns: c_custkey:1(int!null) c_mktsegment:7(char!null) 109 │ │ │ │ │ ├── stats: [rows=30000, distinct(1)=29974.3087, null(1)=0, distinct(7)=1, null(7)=0] 110 │ │ │ │ │ ├── key: (1) 111 │ │ │ │ │ ├── fd: ()-->(7) 112 │ │ │ │ │ ├── scan customer 113 │ │ │ │ │ │ ├── save-table-name: q3_scan_10 114 │ │ │ │ │ │ ├── columns: c_custkey:1(int!null) c_mktsegment:7(char!null) 115 │ │ │ │ │ │ ├── stats: [rows=150000, distinct(1)=148813, null(1)=0, distinct(7)=5, null(7)=0] 116 │ │ │ │ │ │ │ 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 117 │ │ │ │ │ │ │ <--- 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 118 │ │ │ │ │ │ ├── key: (1) 119 │ │ │ │ │ │ └── fd: (1)-->(7) 120 │ │ │ │ │ └── filters 121 │ │ │ │ │ └── c_mktsegment:7 = 'BUILDING' [type=bool, outer=(7), constraints=(/7: [/'BUILDING' - /'BUILDING']; tight), fd=()-->(7)] 122 │ │ │ │ └── filters 123 │ │ │ │ └── c_custkey:1 = o_custkey:10 [type=bool, outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 124 │ │ │ └── filters 125 │ │ │ └── l_shipdate:28 > '1995-03-15' [type=bool, outer=(28), constraints=(/28: [/'1995-03-16' - ]; tight)] 126 │ │ └── projections 127 │ │ └── l_extendedprice:23 * (1.0 - l_discount:24) [as=column34:34, type=float, outer=(23,24)] 128 │ └── aggregations 129 │ ├── sum [as=sum:35, type=float, outer=(34)] 130 │ │ └── column34:34 [type=float] 131 │ ├── const-agg [as=o_orderdate:13, type=date, outer=(13)] 132 │ │ └── o_orderdate:13 [type=date] 133 │ └── const-agg [as=o_shippriority:16, type=int, outer=(16)] 134 │ └── o_shippriority:16 [type=int] 135 └── 10 [type=int] 136 137 stats table=q3_limit_1 138 ---- 139 column_names row_count distinct_count null_count 140 {l_orderkey} 10 10 0 141 {o_orderdate} 10 8 0 142 {o_shippriority} 10 1 0 143 {revenue} 10 10 0 144 ~~~~ 145 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 146 {l_orderkey} 10.00 1.00 10.00 1.00 0.00 1.00 147 {o_orderdate} 10.00 1.00 10.00 1.25 0.00 1.00 148 {o_shippriority} 10.00 1.00 10.00 10.00 <== 0.00 1.00 149 {revenue} 10.00 1.00 10.00 1.00 0.00 1.00 150 151 stats table=q3_sort_2 152 ---- 153 column_names row_count distinct_count null_count 154 {l_orderkey} 0 0 0 155 {o_orderdate} 0 0 0 156 {o_shippriority} 0 0 0 157 {sum} 0 0 0 158 ~~~~ 159 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 160 {l_orderkey} 359560.00 +Inf <== 359560.00 +Inf <== 0.00 1.00 161 {o_orderdate} 359560.00 +Inf <== 359560.00 +Inf <== 0.00 1.00 162 {o_shippriority} 359560.00 +Inf <== 359560.00 +Inf <== 0.00 1.00 163 {sum} 359560.00 +Inf <== 359560.00 +Inf <== 0.00 1.00 164 165 stats table=q3_group_by_3 166 ---- 167 column_names row_count distinct_count null_count 168 {l_orderkey} 11620 11611 0 169 {o_orderdate} 11620 120 0 170 {o_shippriority} 11620 1 0 171 {sum} 11620 11601 0 172 ~~~~ 173 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 174 {l_orderkey} 359560.00 30.94 <== 359560.00 30.97 <== 0.00 1.00 175 {o_orderdate} 359560.00 30.94 <== 359560.00 2996.33 <== 0.00 1.00 176 {o_shippriority} 359560.00 30.94 <== 359560.00 359560.00 <== 0.00 1.00 177 {sum} 359560.00 30.94 <== 359560.00 30.99 <== 0.00 1.00 178 179 stats table=q3_project_4 180 ---- 181 column_names row_count distinct_count null_count 182 {column34} 30519 30424 0 183 {l_orderkey} 30519 11611 0 184 {o_orderdate} 30519 120 0 185 {o_shippriority} 30519 1 0 186 ~~~~ 187 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 188 {column34} 493779.00 16.18 <== 410296.00 13.49 <== 0.00 1.00 189 {l_orderkey} 493779.00 16.18 <== 359560.00 30.97 <== 0.00 1.00 190 {o_orderdate} 493779.00 16.18 <== 1169.00 9.74 <== 0.00 1.00 191 {o_shippriority} 493779.00 16.18 <== 1.00 1.00 0.00 1.00 192 193 stats table=q3_lookup_join_5 194 ---- 195 column_names row_count distinct_count null_count 196 {c_custkey} 30519 8643 0 197 {c_mktsegment} 30519 1 0 198 {l_discount} 30519 11 0 199 {l_extendedprice} 30519 30042 0 200 {l_orderkey} 30519 11611 0 201 {l_shipdate} 30519 120 0 202 {o_custkey} 30519 8643 0 203 {o_orderdate} 30519 120 0 204 {o_orderkey} 30519 11611 0 205 {o_shippriority} 30519 1 0 206 ~~~~ 207 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 208 {c_custkey} 493779.00 16.18 <== 29974.00 3.47 <== 0.00 1.00 209 {c_mktsegment} 493779.00 16.18 <== 1.00 1.00 0.00 1.00 210 {l_discount} 493779.00 16.18 <== 11.00 1.00 0.00 1.00 211 {l_extendedprice} 493779.00 16.18 <== 363943.00 12.11 <== 0.00 1.00 212 {l_orderkey} 493779.00 16.18 <== 359560.00 30.97 <== 0.00 1.00 213 {l_shipdate} 493779.00 16.18 <== 1352.00 11.27 <== 0.00 1.00 214 {o_custkey} 493779.00 16.18 <== 29974.00 3.47 <== 0.00 1.00 215 {o_orderdate} 493779.00 16.18 <== 1169.00 9.74 <== 0.00 1.00 216 {o_orderkey} 493779.00 16.18 <== 359560.00 30.97 <== 0.00 1.00 217 {o_shippriority} 493779.00 16.18 <== 1.00 1.00 0.00 1.00 218 219 stats table=q3_inner_join_6 220 ---- 221 column_names row_count distinct_count null_count 222 {c_custkey} 147126 20129 0 223 {c_mktsegment} 147126 1 0 224 {o_custkey} 147126 20129 0 225 {o_orderdate} 147126 1169 0 226 {o_orderkey} 147126 145236 0 227 {o_shippriority} 147126 1 0 228 ~~~~ 229 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 230 {c_custkey} 220819.00 1.50 29974.00 1.49 0.00 1.00 231 {c_mktsegment} 220819.00 1.50 1.00 1.00 0.00 1.00 232 {o_custkey} 220819.00 1.50 29974.00 1.49 0.00 1.00 233 {o_orderdate} 220819.00 1.50 1169.00 1.00 0.00 1.00 234 {o_orderkey} 220819.00 1.50 190732.00 1.31 0.00 1.00 235 {o_shippriority} 220819.00 1.50 1.00 1.00 0.00 1.00 236 237 stats table=q3_select_7 238 ---- 239 column_names row_count distinct_count null_count 240 {o_custkey} 727305 99492 0 241 {o_orderdate} 727305 1169 0 242 {o_orderkey} 727305 730170 0 243 {o_shippriority} 727305 1 0 244 ~~~~ 245 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 246 {o_custkey} 734900.00 1.01 99842.00 1.00 0.00 1.00 247 {o_orderdate} 734900.00 1.01 1169.00 1.00 0.00 1.00 248 {o_orderkey} 734900.00 1.01 734900.00 1.01 0.00 1.00 249 {o_shippriority} 734900.00 1.01 1.00 1.00 0.00 1.00 250 251 stats table=q3_scan_8 252 ---- 253 column_names row_count distinct_count null_count 254 {o_custkey} 1500000 99846 0 255 {o_orderdate} 1500000 2406 0 256 {o_orderkey} 1500000 1527270 0 257 {o_shippriority} 1500000 1 0 258 ~~~~ 259 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 260 {o_custkey} 1500000.00 1.00 99846.00 1.00 0.00 1.00 261 {o_orderdate} 1500000.00 1.00 2406.00 1.00 0.00 1.00 262 {o_orderkey} 1500000.00 1.00 1500000.00 1.02 0.00 1.00 263 {o_shippriority} 1500000.00 1.00 1.00 1.00 0.00 1.00 264 265 stats table=q3_select_9 266 ---- 267 column_names row_count distinct_count null_count 268 {c_custkey} 30142 30101 0 269 {c_mktsegment} 30142 1 0 270 ~~~~ 271 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 272 {c_custkey} 30000.00 1.00 29974.00 1.00 0.00 1.00 273 {c_mktsegment} 30000.00 1.00 1.00 1.00 0.00 1.00 274 275 stats table=q3_scan_10 276 ---- 277 column_names row_count distinct_count null_count 278 {c_custkey} 150000 148813 0 279 {c_mktsegment} 150000 5 0 280 ~~~~ 281 column_names row_count_est row_count_err distinct_count_est distinct_count_err null_count_est null_count_err 282 {c_custkey} 150000.00 1.00 148813.00 1.00 0.00 1.00 283 {c_mktsegment} 150000.00 1.00 5.00 1.00 0.00 1.00