#line 2 "tq/1.sql" SELECT l_returnflag, l_linestatus, SUM(l_quantity) AS sum_qty, SUM(l_extendedprice) AS sum_base_price, SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price, SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, AVG(l_quantity) AS avg_qty, AVG(l_extendedprice) AS avg_price, AVG(l_discount) AS avg_disc, COUNT(*) AS count_order FROM lineitem WHERE l_shipdate <= DATEADD('day', -90, CAST ('1998-12-01' AS DATE)) GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus { time 6.6e-06% fanout 1 input 1 rows time 3.9e-06% fanout 1 input 1 rows { fork time 4.6e-06% fanout 1 input 1 rows { fork time 54% fanout 5.91599e+08 input 1 rows LINEITEM 5.9e+08 rows(.L_RETURNFLAG, .L_LINESTATUS, .L_QUANTITY, .L_EXTENDEDPRICE, .L_DISCOUNT, .L_TAX) L_SHIPDATE <= time 46% fanout 0 input 5.91599e+08 rows Precode: 0: temp := artm 1 - .L_DISCOUNT 4: temp := artm .L_EXTENDEDPRICE * temp 8: temp := artm 1 + .L_TAX 12: temp := artm temp * temp 16: BReturn 0 Sort (.L_RETURNFLAG, .L_LINESTATUS) -> (.L_QUANTITY, .L_EXTENDEDPRICE, temp, temp, inc, .L_DISCOUNT) } time 0.00017% fanout 4 input 1 rows group by read node (.L_RETURNFLAG, .L_LINESTATUS, sum_qty, sum_base_price, sum_disc_price, sum_charge, count_order, aggregate) time 0.00012% fanout 0 input 4 rows Precode: 0: avg_qty := artm sum_qty / count_order 4: avg_price := artm sum_base_price / count_order 8: avg_disc := artm aggregate / count_order 12: BReturn 0 Sort (.L_RETURNFLAG, .L_LINESTATUS) -> (sum_qty, sum_base_price, sum_disc_price, sum_charge, avg_qty, avg_price, avg_disc, count_order) } time 2.8e-05% fanout 4 input 1 rows Key from temp (.L_RETURNFLAG, .L_LINESTATUS, sum_qty, sum_base_price, sum_disc_price, sum_charge, avg_qty, avg_price, avg_disc, count_order) time 1.6e-05% fanout 0 input 4 rows SELECT (.L_RETURNFLAG, .L_LINESTATUS, sum_qty, sum_base_price, sum_disc_price, sum_charge, avg_qty, avg_price, avg_disc, count_order) } 48948.84 ms CPU, 2079 ms 2354.4% CPU #line 2 "tq/2.sql" SELECT TOP 100 s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment FROM part, supplier, partsupp, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND p_size = 15 AND p_type LIKE '%BRASS' AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' AND ps_supplycost = ( SELECT MIN(ps_supplycost) FROM partsupp, supplier, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' ) ORDER BY s_acctbal DESC, n_name, s_name, p_partkey { time 8.8e-05% fanout 1 input 1 rows time 0.53% fanout 1 input 1 rows { hash filler Subquery 27 { time 0.00094% fanout 1 input 1 rows REGION 1 rows(t10.R_REGIONKEY) R_NAME = time 0.00035% fanout 5 input 1 rows NATION 5 rows(t9.N_NATIONKEY) N_REGIONKEY = t10.R_REGIONKEY time 1.3% fanout 40107 input 5 rows SUPPLIER 4e+04 rows(t8.S_SUPPKEY) S_NATIONKEY = t9.N_NATIONKEY After code: 0: t8.S_SUPPKEY := := artm t8.S_SUPPKEY 4: BReturn 0 time 0.054% fanout 0 input 200535 rows Sort hf 49 (t8.S_SUPPKEY) } } time 0.00019% fanout 1 input 1 rows { fork time 19% fanout 79591 input 1 rows PART 7.8e+04 rows(.P_PARTKEY) P_TYPE LIKE LIKE , P_SIZE = 15 time 45% fanout 0.591889 input 79591 rows Precode: 0: { time 0.079% fanout 1 input 79591 rows time 0.096% fanout 1 input 79591 rows { fork time 23% fanout 0.801912 input 79591 rows PARTSUPP 4 rows(.PS_SUPPKEY, .PS_SUPPLYCOST) inlined PS_PARTKEY = k_.P_PARTKEY hash partition+bloom by 62 (tmp)hash join merged always card 0.2 -> () time 3.6% fanout 0 input 63825 rows Hash source 49 merged into ts not partitionable 0.2 rows(.PS_SUPPKEY) -> () After code: 0: min min.PS_SUPPLYCOSTset no set_ctr 5: BReturn 0 } After code: 0: aggregate := := artm min 4: BReturn 0 time 0.15% fanout 0 input 79591 rows Subquery SELECT(aggregate) } 8: BReturn 0 PARTSUPP 5.7e-08 rows(.PS_SUPPKEY) inlined PS_PARTKEY = k_.P_PARTKEY PS_SUPPLYCOST = k_scalar time 7.3% fanout 0.381307 input 47109 rows SUPPLIER unq 0.9 rows (.S_ACCTBAL, .S_NATIONKEY, .S_NAME, .S_SUPPKEY) inlined S_SUPPKEY = .PS_SUPPKEY top k on S_ACCTBAL time 0.081% fanout 1 input 17963 rows NATION unq 1 rows (.N_REGIONKEY, .N_NAME) inlined N_NATIONKEY = .S_NATIONKEY time 0.057% fanout 0.999889 input 17963 rows REGION unq 0.2 rows () inlined R_REGIONKEY = .N_REGIONKEY R_NAME = time 0.57% fanout 0 input 17961 rows Sort (.S_ACCTBAL, .N_NAME, .S_NAME, .P_PARTKEY) -> (.S_SUPPKEY) } time 0.0013% fanout 100 input 1 rows top order by read (.S_ACCTBAL, .S_NAME, .N_NAME, .S_SUPPKEY, .P_PARTKEY) time 0.016% fanout 1 input 100 rows PART unq 1 rows (.P_MFGR) inlined P_PARTKEY = .P_PARTKEY time 0.043% fanout 1 input 100 rows SUPPLIER unq 1 rows (.S_PHONE, .S_ADDRESS, .S_COMMENT) inlined S_SUPPKEY = k_.S_SUPPKEY time 0.0042% fanout 0 input 100 rows SELECT (.S_ACCTBAL, .S_NAME, .N_NAME, .P_PARTKEY, .P_MFGR, .S_ADDRESS, .S_PHONE, .S_COMMENT) } 1636.35 ms CPU, 146 ms 1120.8% CPU #line 2 "tq/3.sql" SELECT TOP 10 l_orderkey, SUM(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriority FROM customer, orders, lineitem WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < CAST ('1995-03-15' AS DATE) AND l_shipdate > CAST ('1995-03-15' AS DATE) GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue DESC, o_orderdate { time 5.1e-06% fanout 1 input 1 rows time 0.81% fanout 1 input 1 rows { hash filler time 0.76% fanout 3.00019e+06 input 1 rows CUSTOMER 3e+06 rows(.C_CUSTKEY) C_MKTSEGMENT = time 0.066% fanout 0 input 3.00019e+06 rows Sort hf 34 (.C_CUSTKEY) } time 1.1e-05% fanout 1 input 1 rows { fork time 7.3e-06% fanout 1 input 1 rows { fork time 27% fanout 1.45679e+07 input 1 rows ORDERS 7.3e+07 rows(.O_CUSTKEY, .O_ORDERKEY, .O_ORDERDATE, .O_SHIPPRIORITY) O_ORDERDATE < hash partition+bloom by 41 (tmp)hash join merged always card 0.19 -> () time 0.0074% fanout 1 input 1.45679e+07 rows Hash source 34 merged into ts 0.19 rows(.O_CUSTKEY) -> () time 18% fanout 0.20508 input 1.45679e+07 rows LINEITEM 2.5 rows(.L_ORDERKEY, .L_EXTENDEDPRICE, .L_DISCOUNT) inlined L_ORDERKEY = .O_ORDERKEY L_SHIPDATE > time 2.5% fanout 1 input 2.98758e+06 rows Precode: 0: temp := artm 1 - .L_DISCOUNT 4: temp := artm .L_EXTENDEDPRICE * temp 8: BReturn 0 Stage 2 time 1.1% fanout 0 input 2.98758e+06 rows Sort (q_.L_ORDERKEY, .O_ORDERDATE, .O_SHIPPRIORITY) -> (temp) } time 0.38% fanout 1.13104e+06 input 1 rows group by read node (.L_ORDERKEY, .O_ORDERDATE, .O_SHIPPRIORITY, revenue)in each partition slice time 0.46% fanout 0 input 1.13104e+06 rows Sort (revenue, .O_ORDERDATE) -> (.L_ORDERKEY, .O_SHIPPRIORITY) } time 1.6e-05% fanout 10 input 1 rows top order by read (.L_ORDERKEY, revenue, .O_ORDERDATE, .O_SHIPPRIORITY) time 2.1e-05% fanout 0 input 10 rows SELECT (.L_ORDERKEY, revenue, .O_ORDERDATE, .O_SHIPPRIORITY) } 19808.05 ms CPU, 1050 ms 1886.5% CPU #line 2 "tq/4.sql" SELECT o_orderpriority, COUNT(*) AS order_count FROM orders WHERE o_orderdate >= CAST ('1993-07-01' AS DATE) AND o_orderdate < DATEADD ('month', 3, CAST ('1993-07-01' AS DATE)) AND EXISTS ( SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate ) GROUP BY o_orderpriority ORDER BY o_orderpriority { time 1.6e-05% fanout 1 input 1 rows time 9.3e-06% fanout 1 input 1 rows { fork time 1.2e-05% fanout 1 input 1 rows { fork time 14% fanout 5.73378e+06 input 1 rows ORDERS 5.2e+04 rows(.O_ORDERKEY, .O_ORDERPRIORITY) O_ORDERDATE >= < time 41% fanout 0.916923 input 5.73378e+06 rows END Node After test: 0: if ({ time 0.63% fanout 1 input 5.73378e+06 rows time 36% fanout 4.00005 input 5.73378e+06 rows LINEITEM 0.018 rows(.L_COMMITDATE, .L_RECEIPTDATE) inlined L_ORDERKEY = k_.O_ORDERKEY time 3.6% fanout 0.632127 input 2.29354e+07 rows END Node After test: 0: if (.L_COMMITDATE < .L_RECEIPTDATE) then 4 else 5 unkn 5 4: BReturn 1 5: BReturn 0 time 0.94% fanout 0 input 1.44981e+07 rows Subquery SELECT( ) } ) then 4 else 5 unkn 5 4: BReturn 1 5: BReturn 0 time 3.6% fanout 0 input 5.25743e+06 rows Sort (.O_ORDERPRIORITY) -> (inc) } time 0.00028% fanout 5 input 1 rows group by read node (.O_ORDERPRIORITY, order_count) time 0.00032% fanout 0 input 5 rows Sort (.O_ORDERPRIORITY) -> (order_count) } time 7e-05% fanout 5 input 1 rows Key from temp (.O_ORDERPRIORITY, order_count) time 4e-05% fanout 0 input 5 rows SELECT (.O_ORDERPRIORITY, order_count) } 8509.482 ms CPU, 375 ms 2269.2% CPU #line 2 "tq/5.sql" SELECT n_name, SUM(l_extendedprice * (1 - l_discount)) AS revenue FROM customer, orders, lineitem, supplier, nation, region WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA' AND o_orderdate >= CAST ('1994-01-01' AS DATE) AND o_orderdate < DATEADD('year', 1, CAST ('1994-01-01' AS DATE)) GROUP BY n_name ORDER BY revenue DESC { time 8.7e-06% fanout 1 input 1 rows time 1.3% fanout 1 input 1 rows { hash filler Subquery 27 { time 6.8e-05% fanout 1 input 1 rows REGION 1 rows(t6.R_REGIONKEY) R_NAME = time 4.7e-05% fanout 5 input 1 rows NATION 5 rows(t5.N_NATIONKEY, t5.N_NAME) N_REGIONKEY = t6.R_REGIONKEY time 3.6% fanout 599952 input 5 rows CUSTOMER 6e+05 rows(t1.C_CUSTKEY, t1.C_NATIONKEY) C_NATIONKEY = t5.N_NATIONKEY After code: 0: t1.C_CUSTKEY := := artm t1.C_CUSTKEY 4: t1.C_NATIONKEY := := artm t1.C_NATIONKEY 8: t5.N_NAME := := artm t5.N_NAME 12: BReturn 0 time 0.79% fanout 0 input 2.99976e+06 rows Sort hf 53 (t1.C_CUSTKEY, t1.C_NATIONKEY) -> (t5.N_NAME) } } time 0.056% fanout 1 input 1 rows { hash filler Subquery 68 { time 0.00014% fanout 1 input 1 rows REGION 1 rows(t6.R_REGIONKEY) R_NAME = time 4e-05% fanout 5 input 1 rows NATION 5 rows(t5.N_NATIONKEY) N_REGIONKEY = t6.R_REGIONKEY time 0.17% fanout 39977.8 input 5 rows SUPPLIER 4e+04 rows(t4.S_SUPPKEY, t4.S_NATIONKEY) S_NATIONKEY = t5.N_NATIONKEY After code: 0: t4.S_SUPPKEY := := artm t4.S_SUPPKEY 4: t4.S_NATIONKEY := := artm t4.S_NATIONKEY 8: BReturn 0 time 0.023% fanout 0 input 199889 rows Sort hf 92 (t4.S_SUPPKEY) -> (t4.S_NATIONKEY) } } time 1.2e-05% fanout 1 input 1 rows { fork time 1e-05% fanout 1 input 1 rows { fork time 13% fanout 2.27608e+07 input 1 rows ORDERS 2.3e+07 rows(.O_ORDERKEY, .O_CUSTKEY) O_ORDERDATE >= < time 70% fanout 0.799616 input 2.27608e+07 rows LINEITEM 4.6 rows(.L_SUPPKEY, .L_EXTENDEDPRICE, .L_DISCOUNT) inlined L_ORDERKEY = .O_ORDERKEY hash partition+bloom by 105 (tmp)hash join merged always card 0.2 -> (.S_NATIONKEY) time 0.47% fanout 1 input 1.81999e+07 rows Precode: 0: temp := artm 1 - .L_DISCOUNT 4: temp := artm .L_EXTENDEDPRICE * temp 8: BReturn 0 Hash source 92 merged into ts 0.2 rows(.L_SUPPKEY) -> (.S_NATIONKEY) time 9.9% fanout 0.0400135 input 1.81999e+07 rows Hash source 53 0.008 rows(k_.O_CUSTKEY, .S_NATIONKEY) -> (.N_NAME) time 0.65% fanout 0 input 728242 rows Sort (.N_NAME) -> (temp) } time 0.00021% fanout 5 input 1 rows group by read node (.N_NAME, revenue) time 0.00021% fanout 0 input 5 rows Sort (revenue) -> (.N_NAME) } time 3.9e-05% fanout 5 input 1 rows Key from temp (.N_NAME, revenue) time 2.7e-05% fanout 0 input 5 rows SELECT (.N_NAME, revenue) } 23133.52 ms CPU, 1445 ms 1600.9% CPU #line 2 "tq/6.sql" SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= CAST ('1994-01-01' AS DATE) AND l_shipdate < DATEADD ('year', 1, CAST ('1994-01-01' AS DATE)) AND l_discount BETWEEN .06 - 0.01 AND .06 + 0.010001 AND l_quantity < 24 { time 1.4e-05% fanout 1 input 1 rows time 6e-05% fanout 1 input 1 rows Precode: 0: temp := artm 0.06 - 0.01 4: temp := artm 0.06 + 0.010001 8: BReturn 0 { fork time 1e+02% fanout 0 input 1 rows LINEITEM 3.7e+06 rows(.L_EXTENDEDPRICE, .L_DISCOUNT) L_QUANTITY < 24 , L_DISCOUNT >= CAST <= CAST , L_SHIPDATE >= < After code: 0: temp := artm .L_EXTENDEDPRICE * .L_DISCOUNT 4: sum revenuetemp 9: BReturn 0 } time 3.3e-05% fanout 0 input 1 rows SELECT (revenue) } 15932.95 ms CPU, 689 ms 2312.5% CPU #line 2 "tq/7.sql" SELECT supp_nation, cust_nation, l_year, SUM(volume) AS revenue FROM ( SELECT n1.n_name AS supp_nation, n2.n_name AS cust_nation, EXTRACT(YEAR FROM l_shipdate) AS l_year, l_extendedprice * (1 - l_discount) AS volume FROM supplier, lineitem TABLE OPTION (LOOP), orders, customer, nation n1, nation n2 WHERE s_suppkey = l_suppkey AND o_orderkey = l_orderkey AND c_custkey = o_custkey AND s_nationkey = n1.n_nationkey AND c_nationkey = n2.n_nationkey AND ( (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE') ) AND l_shipdate BETWEEN CAST ('1995-01-01' AS DATE) AND CAST ('1996-12-31' AS DATE) ) AS shipping GROUP BY supp_nation, cust_nation, l_year ORDER BY supp_nation, cust_nation, l_year { time 1.1e-05% fanout 1 input 1 rows time 0.41% fanout 1 input 1 rows { hash filler Subquery 27 { time 8.7e-05% fanout 25 input 1 rows NATION 2 rows(t14.N_NAME, t14.N_NATIONKEY) time 3e-05% fanout 0.08 input 25 rows END Node After test: 0: if (t14.N_NAME = ) then 8 else 4 unkn 4 4: if (t14.N_NAME = ) then 8 else 9 unkn 9 8: BReturn 1 9: BReturn 0 time 1% fanout 600070 input 2 rows CUSTOMER 6e+05 rows(t12.C_CUSTKEY) C_NATIONKEY = k_t14.N_NATIONKEY After code: 0: t12.C_CUSTKEY := := artm t12.C_CUSTKEY 4: t14.N_NAME := := artm t14.N_NAME 8: BReturn 0 time 0.18% fanout 0 input 1.20014e+06 rows Sort hf 48 (t12.C_CUSTKEY) -> (t14.N_NAME) } } time 0.02% fanout 1 input 1 rows { hash filler Subquery 60 { time 0.00013% fanout 25 input 1 rows NATION 2 rows(t13.N_NAME, t13.N_NATIONKEY) time 4e-05% fanout 0.08 input 25 rows END Node After test: 0: if (t13.N_NAME = ) then 8 else 4 unkn 4 4: if (t13.N_NAME = ) then 8 else 9 unkn 9 8: BReturn 1 9: BReturn 0 time 0.062% fanout 40085.5 input 2 rows SUPPLIER 4e+04 rows(t9.S_SUPPKEY) S_NATIONKEY = k_t13.N_NATIONKEY After code: 0: t9.S_SUPPKEY := := artm t9.S_SUPPKEY 4: t13.N_NAME := := artm t13.N_NAME 8: BReturn 0 time 0.012% fanout 0 input 80171 rows Sort hf 81 (t9.S_SUPPKEY) -> (t13.N_NAME) } } time 1.1e-05% fanout 1 input 1 rows { fork time 8.9e-06% fanout 1 input 1 rows { fork time 78% fanout 1.46191e+07 input 1 rows LINEITEM 1.8e+08 rows(t3.L_SUPPKEY, t3.L_ORDERKEY, t3.L_SHIPDATE, t3.L_EXTENDEDPRICE, t3.L_DISCOUNT) L_SHIPDATE >= <= hash partition+bloom by 91 (tmp)hash join merged always card 0.078 -> (supp_nation) time 0.29% fanout 1 input 1.46191e+07 rows Precode: 0: temp := artm 1 - t3.L_DISCOUNT 4: temp := artm t3.L_EXTENDEDPRICE * temp 8: BReturn 0 Hash source 81 merged into ts 0.078 rows(t3.L_SUPPKEY) -> (supp_nation) time 18% fanout 0.0799802 input 1.46191e+07 rows ORDERS unq 1 rows (t4.O_CUSTKEY) inlined O_ORDERKEY = t3.L_ORDERKEY hash partition+bloom by 58 (tmp)hash join merged always card 0.00024 -> (cust_nation) time 0.0079% fanout 1 input 1.16924e+06 rows Hash source 48 merged into ts 0.00024 rows(t4.O_CUSTKEY) -> (cust_nation) time 1.5% fanout 0.499054 input 1.16924e+06 rows END Node After test: 0: if (supp_nation = ) then 4 else 8 unkn 8 4: if (cust_nation = ) then 16 else 8 unkn 8 8: if (supp_nation = ) then 12 else 17 unkn 17 12: if (cust_nation = ) then 16 else 17 unkn 17 16: BReturn 1 17: BReturn 0 time 1.1% fanout 0 input 583512 rows Precode: 0: l_year := Call YEAR (t3.L_SHIPDATE) 5: BReturn 0 Sort (supp_nation, cust_nation, l_year) -> (temp) } time 0.00012% fanout 4 input 1 rows group by read node (supp_nation, cust_nation, l_year, revenue) time 0.00019% fanout 0 input 4 rows Sort (supp_nation, cust_nation, l_year) -> (revenue) } time 3.9e-05% fanout 4 input 1 rows Key from temp (supp_nation, cust_nation, l_year, revenue) time 2.3e-05% fanout 0 input 4 rows SELECT (supp_nation, cust_nation, l_year, revenue) } 25055.22 ms CPU, 1298 ms 1930.3% CPU #line 2 "tq/8.sql" SELECT o_year, SUM(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / SUM(volume) AS mkt_share FROM ( SELECT EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n2.n_name AS nation FROM part, supplier, lineitem, orders, customer, nation n1, nation n2, region WHERE p_partkey = l_partkey AND s_suppkey = l_suppkey AND l_orderkey = o_orderkey AND o_custkey = c_custkey AND c_nationkey = n1.n_nationkey AND n1.n_regionkey = r_regionkey AND r_name = 'AMERICA' AND s_nationkey = n2.n_nationkey AND o_orderdate BETWEEN CAST ('1995-01-01' AS DATE) AND CAST ('1996-12-31' AS DATE) AND p_type = 'ECONOMY ANODIZED STEEL' ) AS all_nations GROUP BY o_year ORDER BY o_year { time 1.4e-05% fanout 1 input 1 rows time 0.00029% fanout 1 input 1 rows { hash filler time 0.0001% fanout 25 input 1 rows NATION 25 rows(t8.N_NATIONKEY, t8.N_NAME) time 3.2e-05% fanout 0 input 25 rows Sort hf 35 (t8.N_NATIONKEY) -> (t8.N_NAME) } time 0.05% fanout 1 input 1 rows { hash filler time 1.9% fanout 134258 input 1 rows PART 1.3e+05 rows(t2.P_PARTKEY) P_TYPE = time 0.0084% fanout 0 input 134258 rows Sort hf 48 (t2.P_PARTKEY) } time 1.7% fanout 1 input 1 rows { hash filler Subquery 57 { time 0.00012% fanout 1 input 1 rows REGION 1 rows(t18.R_REGIONKEY) R_NAME = time 5.9e-05% fanout 5 input 1 rows NATION 5 rows(t16.N_NATIONKEY) N_REGIONKEY = t18.R_REGIONKEY time 3.3% fanout 600381 input 5 rows CUSTOMER 6e+05 rows(t15.C_CUSTKEY) C_NATIONKEY = t16.N_NATIONKEY After code: 0: t15.C_CUSTKEY := := artm t15.C_CUSTKEY 4: BReturn 0 time 0.13% fanout 0 input 3.0019e+06 rows Sort hf 79 (t15.C_CUSTKEY) } } time 2.7e-05% fanout 1 input 1 rows { fork time 1.4e-05% fanout 1 input 1 rows { fork time 46% fanout 9.12141e+06 input 1 rows ORDERS 4.6e+07 rows(t5.O_CUSTKEY, t5.O_ORDERKEY, t5.O_ORDERDATE) O_ORDERDATE >= <= hash partition+bloom by 92 (tmp)hash join merged always card 0.19 -> () time 0.0091% fanout 1 input 9.12141e+06 rows Hash source 79 merged into ts 0.19 rows(t5.O_CUSTKEY) -> () time 41% fanout 0.0268403 input 9.12141e+06 rows LINEITEM 4.6 rows(t4.L_PARTKEY, t4.L_SUPPKEY, t4.L_EXTENDEDPRICE, t4.L_DISCOUNT) inlined L_ORDERKEY = t5.O_ORDERKEY hash partition+bloom by 55 (tmp)hash join merged always card 0.0066 -> () time 0.035% fanout 1 input 244821 rows Precode: 0: temp := artm 1 - t4.L_DISCOUNT 4: temp := artm t4.L_EXTENDEDPRICE * temp 8: BReturn 0 Hash source 48 merged into ts 0.0066 rows(t4.L_PARTKEY) -> () time 5.6% fanout 1 input 244821 rows SUPPLIER unq 1 rows (t3.S_NATIONKEY) inlined S_SUPPKEY = t4.L_SUPPKEY hash partition+bloom by 39 (tmp)hash join merged always card 1 -> (t8.N_NAME) time 0.0062% fanout 1 input 244821 rows Hash source 35 merged into ts 1 rows(t3.S_NATIONKEY) -> (t8.N_NAME) time 0.65% fanout 0 input 244821 rows Precode: 0: o_year := Call year (t5.O_ORDERDATE) 5: if (t8.N_NAME = ) then 9 else 22 unkn 22 9: temp := artm 1 - t4.L_DISCOUNT 13: temp := artm t4.L_EXTENDEDPRICE * temp 17: callretSearchedCASE := := artm temp 21: Jump 26 (level=0) 22: callretSearchedCASE := := artm 0 26: BReturn 0 Sort (o_year) -> (callretSearchedCASE, temp) } time 0.00019% fanout 2 input 1 rows group by read node (o_year, aggregate, aggregate) time 0.00022% fanout 0 input 2 rows Precode: 0: mkt_share := artm aggregate / aggregate 4: BReturn 0 Sort (o_year) -> (mkt_share) } time 0.00017% fanout 2 input 1 rows Key from temp (o_year, mkt_share) time 4.2e-05% fanout 0 input 2 rows SELECT (o_year, mkt_share) } 17046.6 ms CPU, 1042 ms 1636% CPU #line 2 "tq/9.sql" SELECT nation, o_year, SUM(amount) AS sum_profit FROM ( SELECT n_name AS nation, EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE s_suppkey = l_suppkey AND ps_suppkey = l_suppkey AND ps_partkey = l_partkey AND p_partkey = l_partkey AND o_orderkey = l_orderkey AND s_nationkey = n_nationkey AND p_name LIKE '%green%' ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC { time 2.7e-06% fanout 1 input 1 rows time 5.9e-05% fanout 1 input 1 rows { hash filler time 1.8e-05% fanout 25 input 1 rows NATION 25 rows(t7.N_NATIONKEY, nation) time 7.2e-06% fanout 0 input 25 rows Sort hf 35 (t7.N_NATIONKEY) -> (nation) } time 0.062% fanout 1 input 1 rows { hash filler time 0.013% fanout 1e+06 input 1 rows SUPPLIER 1e+06 rows(t3.S_SUPPKEY, t3.S_NATIONKEY) time 0.029% fanout 0 input 1e+06 rows Sort hf 49 (t3.S_SUPPKEY) -> (t3.S_NATIONKEY) } time 0.52% fanout 1 input 1 rows { hash filler Subquery 58 { time 2% fanout 1.08798e+06 input 1 rows PART 1.1e+06 rows(t9.P_PARTKEY) P_NAME LIKE LIKE time 1.4% fanout 4 input 1.08798e+06 rows PARTSUPP 4 rows(t12.PS_SUPPKEY, t12.PS_PARTKEY, t12.PS_SUPPLYCOST) inlined PS_PARTKEY = t9.P_PARTKEY After code: 0: t12.PS_SUPPKEY := := artm t12.PS_SUPPKEY 4: t12.PS_PARTKEY := := artm t12.PS_PARTKEY 8: t12.PS_SUPPLYCOST := := artm t12.PS_SUPPLYCOST 12: BReturn 0 time 0.32% fanout 0 input 4.35193e+06 rows Sort hf 81 (t12.PS_SUPPKEY, t12.PS_PARTKEY) -> (t12.PS_SUPPLYCOST) } } time 0.14% fanout 1 input 1 rows { hash filler time 1.9% fanout 1.08798e+06 input 1 rows PART 1.1e+06 rows(t2.P_PARTKEY) P_NAME LIKE LIKE time 0.013% fanout 0 input 1.08798e+06 rows Sort hf 100 (t2.P_PARTKEY) } time 6e-06% fanout 1 input 1 rows { fork time 2.6e-06% fanout 1 input 1 rows { fork time 56% fanout 3.26365e+07 input 1 rows LINEITEM 6e+08 rows(t4.L_PARTKEY, t4.L_ORDERKEY, t4.L_SUPPKEY, t4.L_EXTENDEDPRICE, t4.L_DISCOUNT, t4.L_QUANTITY) hash partition+bloom by 107 (tmp)hash join merged always card 0.054 -> () hash partition+bloom by 56 (tmp)hash join merged always card 1 -> (t3.S_NATIONKEY) time 0.18% fanout 1 input 3.26365e+07 rows Precode: 0: temp := artm 1 - t4.L_DISCOUNT 4: temp := artm t4.L_EXTENDEDPRICE * temp 8: BReturn 0 Hash source 100 merged into ts 0.054 rows(t4.L_PARTKEY) -> () time 16% fanout 1 input 3.26365e+07 rows Hash source 81 0.054 rows(t4.L_SUPPKEY, t4.L_PARTKEY) -> (t5.PS_SUPPLYCOST) time 0.46% fanout 1 input 3.26365e+07 rows Precode: 0: temp := artm t5.PS_SUPPLYCOST * t4.L_QUANTITY 4: temp := artm temp - temp 8: BReturn 0 Hash source 49 merged into ts 1 rows(k_t4.L_SUPPKEY) -> (t3.S_NATIONKEY) time 6.7% fanout 1 input 3.26365e+07 rows ORDERS unq 1 rows (t6.O_ORDERDATE) inlined O_ORDERKEY = k_t4.L_ORDERKEY time 3.7% fanout 1 input 3.26365e+07 rows Hash source 35 1 rows(k_t3.S_NATIONKEY) -> (nation) time 9.7% fanout 0 input 3.26365e+07 rows Precode: 0: o_year := Call year (t6.O_ORDERDATE) 5: BReturn 0 Sort (nation, o_year) -> (temp) } time 4.9e-05% fanout 175 input 1 rows group by read node (nation, o_year, sum_profit) time 0.00037% fanout 0 input 175 rows Sort (nation, o_year) -> (sum_profit) } time 2.9e-05% fanout 175 input 1 rows Key from temp (nation, o_year, sum_profit) time 6e-05% fanout 0 input 175 rows SELECT (nation, o_year, sum_profit) } 87539.85 ms CPU, 4532 ms 1931.6% CPU #line 4 "tq/10.sql" SELECT TOP 20 c_custkey, c_name, SUM(l_extendedprice * (1 - l_discount)) AS revenue, c_acctbal, n_name, c_address, c_phone, c_comment FROM customer, orders, lineitem, nation WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate >= CAST ('1993-10-01' AS DATE) AND o_orderdate < DATEADD ('month', 3, CAST ('1993-10-01' AS DATE)) AND l_returnflag = 'R' AND c_nationkey = n_nationkey GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment ORDER BY revenue DESC { time 2.4e-06% fanout 1 input 1 rows time 9.1e-05% fanout 1 input 1 rows { hash filler time 1.8e-05% fanout 25 input 1 rows NATION 25 rows(.N_NATIONKEY, .N_NAME) time 5.8e-06% fanout 0 input 25 rows Sort hf 35 (.N_NATIONKEY) -> (.N_NAME) } time 3e-06% fanout 1 input 1 rows { fork time 2.5e-06% fanout 1 input 1 rows { fork time 4.5% fanout 5.73038e+06 input 1 rows ORDERS 5.8e+06 rows(.O_ORDERKEY, .O_CUSTKEY) O_ORDERDATE >= < time 5.9% fanout 2.00042 input 5.73038e+06 rows LINEITEM 1.1 rows(.L_EXTENDEDPRICE, .L_DISCOUNT) inlined L_ORDERKEY = .O_ORDERKEY L_RETURNFLAG = time 33% fanout 1 input 1.14632e+07 rows Precode: 0: temp := artm 1 - .L_DISCOUNT 4: temp := artm .L_EXTENDEDPRICE * temp 8: BReturn 0 CUSTOMER unq 0.96 rows (.C_NATIONKEY, .C_CUSTKEY) inlined C_CUSTKEY = k_.O_CUSTKEY hash partition+bloom by 39 (tmp)hash join merged always card 1 -> (.N_NAME) time 0.003% fanout 1 input 1.14632e+07 rows Hash source 35 merged into ts 1 rows(.C_NATIONKEY) -> (.N_NAME) time 2.7% fanout 1 input 1.14632e+07 rows Stage 2 time 2.5% fanout 0 input 1.14632e+07 rows Sort (q_.C_CUSTKEY, .N_NAME) -> (temp) } time 0.66% fanout 3.88422e+06 input 1 rows group by read node (.C_CUSTKEY, .N_NAME, revenue)in each partition slice time 0.71% fanout 0 input 3.88422e+06 rows Sort (revenue) -> (.N_NAME, .C_CUSTKEY) } time 8.1e-06% fanout 20 input 1 rows top order by read (revenue, .N_NAME, .C_CUSTKEY) time 0.00044% fanout 1 input 20 rows CUSTOMER unq 0.96 rows (.C_PHONE, .C_NAME, .C_ACCTBAL, .C_ADDRESS, .C_COMMENT) inlined C_CUSTKEY = .C_CUSTKEY time 2.8e-05% fanout 0 input 20 rows SELECT (.C_CUSTKEY, .C_NAME, revenue, .C_ACCTBAL, .N_NAME, .C_ADDRESS, .C_PHONE, .C_COMMENT) } 42172.33 ms CPU, 1890 ms 2231.3% CPU #line 2 "tq/11.sql" SELECT TOP 10 ps_partkey, SUM(ps_supplycost * ps_availqty) AS value FROM partsupp, supplier, nation WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_name = 'GERMANY' GROUP BY ps_partkey HAVING SUM(ps_supplycost * ps_availqty) > ( SELECT SUM(ps_supplycost * ps_availqty) * 0.0001000000e-2 FROM partsupp, supplier, nation WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_name = 'GERMANY' ) ORDER BY value DESC { time 2.2e-05% fanout 1 input 1 rows time 0.027% fanout 1 input 1 rows { hash filler Subquery 27 { time 0.00019% fanout 1 input 1 rows NATION 1 rows(t7.N_NATIONKEY) N_NAME = time 0.083% fanout 40045 input 1 rows SUPPLIER 4e+04 rows(t6.S_SUPPKEY) S_NATIONKEY = t7.N_NATIONKEY After code: 0: t6.S_SUPPKEY := := artm t6.S_SUPPKEY 4: BReturn 0 time 0.0028% fanout 0 input 40045 rows Sort hf 46 (t6.S_SUPPKEY) } } time 0.027% fanout 1 input 1 rows { hash filler Subquery 58 { time 0.00011% fanout 1 input 1 rows NATION 1 rows(t3.N_NATIONKEY) N_NAME = time 0.082% fanout 40045 input 1 rows SUPPLIER 4e+04 rows(t2.S_SUPPKEY) S_NATIONKEY = t3.N_NATIONKEY After code: 0: t2.S_SUPPKEY := := artm t2.S_SUPPKEY 4: BReturn 0 time 0.0028% fanout 0 input 40045 rows Sort hf 77 (t2.S_SUPPKEY) } } time 2.1% fanout 1 input 1 rows Precode: 0: { time 1.3e-05% fanout 1 input 1 rows time 7e-05% fanout 1 input 1 rows { fork time 38% fanout 3.2036e+06 input 1 rows PARTSUPP 8e+07 rows(.PS_SUPPKEY, .PS_SUPPLYCOST, .PS_AVAILQTY) hash partition+bloom by 56 (tmp)hash join merged always card 0.04 -> () time 3.7% fanout 0 input 3.2036e+06 rows Precode: 0: temp := artm .PS_SUPPLYCOST * .PS_AVAILQTY 4: BReturn 0 Hash source 46 merged into ts not partitionable 0.04 rows(.PS_SUPPKEY) -> () After code: 0: sum sumtemp 5: BReturn 0 } After code: 0: temp := artm sum * 1e-06 4: aggregate := := artm temp 8: BReturn 0 time 3.3e-05% fanout 0 input 1 rows Subquery SELECT(aggregate) } 8: BReturn 0 { fork time 46% fanout 3.2036e+06 input 1 rows PARTSUPP 8e+07 rows(.PS_SUPPKEY, .PS_PARTKEY, .PS_SUPPLYCOST, .PS_AVAILQTY) hash partition+bloom by 87 (tmp)hash join merged always card 0.04 -> () time 0.17% fanout 1 input 3.2036e+06 rows Precode: 0: temp := artm .PS_SUPPLYCOST * .PS_AVAILQTY 4: BReturn 0 Hash source 77 merged into ts 0.04 rows(.PS_SUPPKEY) -> () time 6.4% fanout 3.74579e-05 input 3.2036e+06 rows Sort streaming with duplicates (.PS_PARTKEY) -> (temp, scalar) time 2.8% fanout 25140.4 input 120 rows group by read node (.PS_PARTKEY, value, scalar) END Node After test: 0: if (value > scalar) then 4 else 5 unkn 5 4: BReturn 1 5: BReturn 0 time 0.2% fanout 0 input 92698 rows Sort (value) -> (.PS_PARTKEY) } time 5.3e-05% fanout 10 input 1 rows top order by read (.PS_PARTKEY, value) time 7.7e-05% fanout 0 input 10 rows SELECT (.PS_PARTKEY, value) } 9233.305 ms CPU, 444 ms 2079.6% CPU #line 2 "tq/12.sql" SELECT l_shipmode, SUM(CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) AS high_line_count, SUM(CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' THEN 1 ELSE 0 END) AS low_line_count FROM orders, lineitem WHERE o_orderkey = l_orderkey AND l_shipmode IN ('MAIL', 'SHIP') AND l_commitdate < l_receiptdate AND l_shipdate < l_commitdate AND l_receiptdate >= CAST ('1994-01-01' AS DATE) AND l_receiptdate < DATEADD ('year', 1, CAST ('1994-01-01' AS DATE)) GROUP BY l_shipmode ORDER BY l_shipmode { time 1.1e-05% fanout 1 input 1 rows time 0.00041% fanout 1 input 1 rows Precode: 0: chash_in_init := Call chash_in_init ( 182 , $29 "chash_in_tree", 0 , 0 , , ) 5: BReturn 0 { fork time 1.2e-05% fanout 1 input 1 rows { fork time 86% fanout 2.60039e+07 input 1 rows LINEITEM 2.3e+06 rows(.L_COMMITDATE, .L_RECEIPTDATE, .L_SHIPDATE, .L_ORDERKEY, .L_SHIPMODE) L_RECEIPTDATE >= < hash partition+bloom by 0 () time 3.9% fanout 0.119803 input 2.60039e+07 rows END Node After test: 0: if (.L_COMMITDATE < .L_RECEIPTDATE) then 4 else 9 unkn 9 4: if (.L_SHIPDATE < .L_COMMITDATE) then 8 else 9 unkn 9 8: BReturn 1 9: BReturn 0 time 5.1% fanout 1 input 3.11534e+06 rows ORDERS unq 1 rows (.O_ORDERPRIORITY) inlined O_ORDERKEY = k_.L_ORDERKEY time 4.7% fanout 0 input 3.11534e+06 rows Precode: 0: if (.O_ORDERPRIORITY = ) then 8 else 4 unkn 4 4: if (.O_ORDERPRIORITY = ) then 8 else 13 unkn 13 8: callretSearchedCASE := := artm 1 12: Jump 17 (level=0) 13: callretSearchedCASE := := artm 0 17: if (.O_ORDERPRIORITY = ) then 30 else 21 unkn 30 21: if (.O_ORDERPRIORITY = ) then 30 else 25 unkn 30 25: callretSearchedCASE := := artm 1 29: Jump 34 (level=0) 30: callretSearchedCASE := := artm 0 34: BReturn 0 Sort (.L_SHIPMODE) -> (callretSearchedCASE, callretSearchedCASE) } time 0.00013% fanout 2 input 1 rows group by read node (.L_SHIPMODE, high_line_count, low_line_count) time 0.51% fanout 0 input 2 rows Sort (.L_SHIPMODE) -> (high_line_count, low_line_count) } time 7.7e-05% fanout 2 input 1 rows Key from temp (.L_SHIPMODE, high_line_count, low_line_count) time 2.4e-05% fanout 0 input 2 rows SELECT (.L_SHIPMODE, high_line_count, low_line_count) } 20496.43 ms CPU, 981 ms 2089.3% CPU #line 2 "tq/13.sql" SELECT c_count, COUNT(*) AS custdist FROM ( SELECT c_custkey, COUNT(o_orderkey) AS c_count FROM (SELECT * FROM customer LEFT OUTER JOIN orders ON c_custkey = o_custkey AND o_comment NOT LIKE '%special%requests%') c_customer GROUP BY c_custkey ) c_orders GROUP BY c_count ORDER BY custdist DESC, c_count DESC { time 8.4e-07% fanout 1 input 1 rows time 0.057% fanout 1 input 1 rows { hash filler time 0.28% fanout 1.5e+07 input 1 rows CUSTOMER 1.5e+07 rows(t3.C_CUSTKEY) time 0.85% fanout 1 input 1.5e+07 rows Stage 2 time 0.07% fanout 0 input 1.5e+07 rows Sort hf 34 (q_t3.C_CUSTKEY) } time 2e-06% fanout 1 input 1 rows time 1.3e-06% fanout 1 input 1 rows { fork time 1.2e-06% fanout 1 input 1 rows { fork time 1e-06% fanout 1 input 1 rows { fork END Node time 0.038% fanout 1 input 1 rows outer { time 10% fanout 1.48382e+08 input 1 rows ORDERS 1.5e+08 rows(t4.O_CUSTKEY, t4.O_ORDERKEY) O_COMMENT LIKE LIKE hash partition+bloom by 66 () time 9.3% fanout 1 input 1.48382e+08 rows Stage 2 time 13% fanout 1 input 1.48382e+08 rows Hash source 34 not partitionable 0.96 rows(q_t4.O_CUSTKEY) -> () right oj, key out ssls: (t3.C_CUSTKEY) After code: 0: t3.C_CUSTKEY := := artm t4.O_CUSTKEY 4: BReturn 0 time 2.4% fanout 1.0337 input 1.48382e+08 rows end of outer} set_ctr out: (t4.O_CUSTKEY, t4.O_ORDERKEY) shadow: (t4.O_CUSTKEY, t4.O_ORDERKEY) time 13% fanout 0 input 1.53383e+08 rows Precode: 0: isnotnull := Call isnotnull (t4.O_ORDERKEY) 5: BReturn 0 Sort (t3.C_CUSTKEY) -> (isnotnull) } time 0.44% fanout 1.5e+07 input 1 rows group by read node (t3.C_CUSTKEY, aggregate)in each partition slice After code: 0: c_custkey := := artm t3.C_CUSTKEY 4: c_count := := artm aggregate 8: BReturn 0 time 0.0025% fanout 1 input 1.5e+07 rows Subquery SELECT(c_custkey, c_count) time 0.31% fanout 0 input 1.5e+07 rows Sort (c_count) -> (inc) } time 1.3e-05% fanout 45 input 1 rows group by read node (c_count, custdist) time 4.1e-05% fanout 0 input 45 rows Sort (custdist, c_count) } time 5.7e-06% fanout 45 input 1 rows Key from temp (c_count, custdist) time 5.1e-06% fanout 0 input 45 rows SELECT (c_count, custdist) } 112074.4 ms CPU, 5559 ms 2016.1% CPU #line 2 "tq/14.sql" SELECT 100.00 * SUM(CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0 END) / SUM(l_extendedprice * (1 - l_discount)) AS promo_revenue FROM lineitem, part WHERE l_partkey = p_partkey AND l_shipdate >= CAST ('1995-09-01' AS DATE) AND l_shipdate < DATEADD ('month', 1, CAST ('1995-09-01' AS DATE)) { time 1e-05% fanout 1 input 1 rows time 6% fanout 1 input 1 rows { hash filler time 38% fanout 7.48426e+06 input 1 rows LINEITEM 7.5e+06 rows(.L_PARTKEY, .L_EXTENDEDPRICE, .L_DISCOUNT) L_SHIPDATE >= < time 2.1% fanout 0 input 7.48426e+06 rows Sort hf 36 (.L_PARTKEY) -> (.L_DISCOUNT, .L_EXTENDEDPRICE) } time 0.00026% fanout 1 input 1 rows { fork time 15% fanout 6.69723e+06 input 1 rows PART 2e+07 rows(.P_PARTKEY, .P_TYPE) hash partition+bloom by 43 () time 39% fanout 0 input 6.69723e+06 rows Hash source 36 0.36 rows(.P_PARTKEY) -> (.L_DISCOUNT, .L_EXTENDEDPRICE) After code: 0: if (.P_TYPE LIKE ) then 4 else 17 unkn 17 4: temp := artm 1 - .L_DISCOUNT 8: temp := artm .L_EXTENDEDPRICE * temp 12: callretSearchedCASE := := artm temp 16: Jump 21 (level=0) 17: callretSearchedCASE := := artm 0 21: temp := artm 1 - .L_DISCOUNT 25: temp := artm .L_EXTENDEDPRICE * temp 29: sum sumcallretSearchedCASE 34: sum sumtemp 39: BReturn 0 } After code: 0: temp := artm 100 * sum 4: promo_revenue := artm temp / sum 8: BReturn 0 time 2.7e-05% fanout 0 input 1 rows SELECT (promo_revenue) } 18548.64 ms CPU, 1615 ms 1148.5% CPU drop view revenue0 { Node } 1.474402 ms CPU, 2 ms 73.72% CPU #line 5 "tq/15.sql" CREATE VIEW revenue0 (supplier_no, total_revenue) AS SELECT l_suppkey, SUM(l_extendedprice * (1 - l_discount)) FROM lineitem WHERE l_shipdate >= CAST ('1996-01-01' AS DATE) AND l_shipdate < DATEADD ('month', 3, CAST ('1996-01-01' AS DATE)) GROUP BY l_suppkey { Node Node } 8.678348 ms CPU, 9 ms 96.426% CPU #line 20 "tq/15.sql" SELECT s_suppkey, s_name, s_address, s_phone, total_revenue FROM supplier, revenue0 WHERE s_suppkey = supplier_no AND total_revenue = ( SELECT MAX(total_revenue) FROM revenue0 ) ORDER BY s_suppkey { time 2.4e-06% fanout 1 input 1 rows time 1% fanout 1 input 1 rows Precode: 0: { time 1.1e-06% fanout 1 input 1 rows time 1.2e-06% fanout 1 input 1 rows time 5.7e-06% fanout 1 input 1 rows { fork time 3.5e-06% fanout 1 input 1 rows { fork time 14% fanout 2.26865e+07 input 1 rows LINEITEM 2.4e+07 rows(t6.L_SUPPKEY, t6.L_EXTENDEDPRICE, t6.L_DISCOUNT) L_SHIPDATE >= < time 4.6% fanout 1 input 2.26865e+07 rows Precode: 0: temp := artm 1 - t6.L_DISCOUNT 4: temp := artm t6.L_EXTENDEDPRICE * temp 8: BReturn 0 Stage 2 time 5% fanout 0 input 2.26865e+07 rows Sort (q_t6.L_SUPPKEY) -> (temp) } time 0.14% fanout 1e+06 input 1 rows group by read node (t6.L_SUPPKEY, aggregate)in each partition slice After code: 0: supplier_no := := artm t6.L_SUPPKEY 4: total_revenue := := artm aggregate 8: BReturn 0 time 0.21% fanout 0 input 1e+06 rows Subquery SELECT(supplier_no, total_revenue) After code: 0: max besttotal_revenue 5: BReturn 0 } After code: 0: aggregate := := artm best 4: BReturn 0 time 3.9e-06% fanout 0 input 1 rows Subquery SELECT(aggregate) } 8: BReturn 0 time 2.8e-06% fanout 1 input 1 rows { fork time 3.7e-06% fanout 1 input 1 rows { fork time 14% fanout 2.26865e+07 input 1 rows LINEITEM 2.4e+07 rows(t1.L_SUPPKEY, t1.L_EXTENDEDPRICE, t1.L_DISCOUNT) L_SHIPDATE >= < time 6.1% fanout 1 input 2.26865e+07 rows Precode: 0: temp := artm 1 - t1.L_DISCOUNT 4: temp := artm t1.L_EXTENDEDPRICE * temp 8: BReturn 0 Stage 2 time 6% fanout 0 input 2.26865e+07 rows Sort (q_t1.L_SUPPKEY) -> (temp, scalar) } time 0.14% fanout 1e+06 input 1 rows group by read node (t1.L_SUPPKEY, aggregate, scalar)in each partition slice END Node After test: 0: if (aggregate = scalar) then 4 else 5 unkn 5 4: BReturn 1 5: BReturn 0 After code: 0: supplier_no := := artm t1.L_SUPPKEY 4: total_revenue := := artm aggregate 8: BReturn 0 time 2.4e-06% fanout 1 input 1 rows Subquery SELECT(supplier_no, total_revenue) time 9.6e-05% fanout 1 input 1 rows SUPPLIER unq 1 rows (.S_SUPPKEY, .S_NAME, .S_ADDRESS, .S_PHONE) inlined S_SUPPKEY = supplier_no time 0.00014% fanout 0 input 1 rows Sort (.S_SUPPKEY) -> (.S_NAME, .S_ADDRESS, .S_PHONE, total_revenue) } time 3.7e-05% fanout 1 input 1 rows Key from temp (.S_SUPPKEY, .S_NAME, .S_ADDRESS, .S_PHONE, total_revenue) time 1.3e-05% fanout 0 input 1 rows SELECT (.S_SUPPKEY, .S_NAME, .S_ADDRESS, .S_PHONE, total_revenue) } 28280.06 ms CPU, 1294 ms 2185.5% CPU drop view revenue0 { Node } 3.76062 ms CPU, 4 ms 94.016% CPU #line 1 "tq/16.sql" SELECT p_brand, p_type, p_size, COUNT(DISTINCT ps_suppkey) AS supplier_cnt FROM partsupp, part WHERE p_partkey = ps_partkey AND p_brand <> 'Brand#45' AND p_type NOT LIKE 'MEDIUM POLISHED%' AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9) AND ps_suppkey NOT IN ( SELECT s_suppkey FROM supplier WHERE s_comment LIKE '%Customer%Complaints%' ) GROUP BY p_brand, p_type, p_size ORDER BY supplier_cnt DESC, p_brand, p_type, p_size { time 4e-06% fanout 1 input 1 rows time 0.00046% fanout 1 input 1 rows Precode: 0: $27 "chash_in_init" := Call chash_in_init ( 189 , $29 "chash_in_tree", 0 , 0 , 49 , 14 , 23 , 45 , 19 , 3 , 36 , 9 ) 5: BReturn 0 { hash filler time 0.2% fanout 479 input 1 rows SUPPLIER 4.5e+02 rows(.S_SUPPKEY) S_COMMENT LIKE LIKE time 5.7e-05% fanout 0 input 479 rows Sort hf 37 (.S_SUPPKEY) } time 7.8e-06% fanout 1 input 1 rows { fork time 7.9e-06% fanout 1 input 1 rows { fork time 8% fanout 3.09523e+06 input 1 rows PART 3e+06 rows(.P_BRAND, .P_PARTKEY, .P_TYPE, .P_SIZE) P_TYPE LIKE LIKE hash partition+bloom by 0 () time 0.56% fanout 0.960107 input 3.09523e+06 rows END Node After test: 0: if (.P_BRAND = ) then 5 else 4 unkn 5 4: BReturn 1 5: BReturn 0 time 3.1% fanout 4 input 2.97175e+06 rows PARTSUPP 4 rows(.PS_SUPPKEY) inlined PS_PARTKEY = k_.P_PARTKEY hash partition by 41 () time 2.6% fanout 0.999525 input 1.1887e+07 rows END Node After test: 0: if ({ time 0.61% fanout 1 input 1.1887e+07 rows time 1.8% fanout 0.000475478 input 1.1887e+07 rows Hash source 37 0.00045 rows(k_.PS_SUPPKEY) -> () time 0.0048% fanout 0 input 5652 rows Subquery SELECT( ) } ) then 5 else 4 unkn 5 4: BReturn 1 5: BReturn 0 time 14% fanout 1 input 1.18813e+07 rows Stage 2 time 20% fanout 0 input 1.18813e+07 rows Sort (.P_BRAND, q_.P_TYPE, .P_SIZE) -> (dist) } time 0.33% fanout 27840 input 1 rows group by read node (.P_BRAND, .P_TYPE, .P_SIZE, supplier_cnt)in each partition slice time 0.97% fanout 0 input 27840 rows Sort (supplier_cnt, .P_BRAND, .P_TYPE, .P_SIZE) } time 0.013% fanout 27840 input 1 rows Key from temp (.P_BRAND, .P_TYPE, .P_SIZE, supplier_cnt) time 0.41% fanout 0 input 27840 rows SELECT (.P_BRAND, .P_TYPE, .P_SIZE, supplier_cnt) } 14821.78 ms CPU, 1316 ms 1126.3% CPU #line 3 "tq/17.sql" SELECT SUM(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem, part WHERE p_partkey = l_partkey AND p_brand = 'Brand#23' AND p_container = 'MED BOX' AND l_quantity < ( SELECT 2e-1 * AVG(l_quantity) FROM lineitem WHERE l_partkey = p_partkey ) { time 7e-06% fanout 1 input 1 rows time 0.0028% fanout 1 input 1 rows { hash filler time 0.33% fanout 20031 input 1 rows PART 3.3e+04 rows(.P_PARTKEY) P_BRAND = , P_CONTAINER = time 0.00043% fanout 0 input 20031 rows Sort hf 34 (.P_PARTKEY) } time 0.096% fanout 1 input 1 rows { hash filler Subquery 40 { time 44% fanout 600982 input 1 rows LINEITEM 6e+08 rows(t4.L_PARTKEY, t4.L_QUANTITY) hash partition+bloom by 38 (tmp)hash join merged always card 0.0016 -> () time 0.0052% fanout 1 input 600982 rows Hash source 34 merged into ts not partitionable 0.0016 rows(t4.L_PARTKEY) -> () After code: 0: t4.L_PARTKEY := := artm t4.L_PARTKEY 4: t4.L_QUANTITY := := artm t4.L_QUANTITY 8: BReturn 0 time 0.053% fanout 0 input 600982 rows Sort hf 62 (t4.L_PARTKEY) -> (t4.L_QUANTITY) } } time 8e-05% fanout 1 input 1 rows { fork time 47% fanout 600982 input 1 rows LINEITEM 6e+08 rows(.L_PARTKEY, .L_QUANTITY, .L_EXTENDEDPRICE) hash partition+bloom by 38 (tmp)hash join merged always card 0.00053 -> () time 0.00037% fanout 1 input 600982 rows Hash source 34 merged into ts 0.00053 rows(.L_PARTKEY) -> () Precode: 0: .P_PARTKEY := := artm .L_PARTKEY 4: BReturn 0 END Node After test: 0: { time 0.051% fanout 1 input 600982 rows time 0.19% fanout 1 input 600982 rows { fork time 8.3% fanout 0 input 600982 rows Hash source 62 not partitionable 0.049 rows(k_.P_PARTKEY) -> (.L_QUANTITY) After code: 0: sum sum.L_QUANTITYset no set_ctr 5: sum count 1 set no set_ctr 10: BReturn 0 } After code: 0: temp := artm sum / count 4: temp := artm 0.2 * temp 8: aggregate := := artm temp 12: BReturn 0 time 0.048% fanout 0 input 600982 rows Subquery SELECT(aggregate) } 8: if (.L_QUANTITY < scalar) then 12 else 13 unkn 13 12: BReturn 1 13: BReturn 0 After code: 0: sum sum.L_EXTENDEDPRICE 5: BReturn 0 } After code: 0: avg_yearly := artm sum / 7 4: BReturn 0 time 1.8e-05% fanout 0 input 1 rows SELECT (avg_yearly) } 44752.88 ms CPU, 2633 ms 1699.7% CPU #line 3 "tq/18.sql" SELECT TOP 100 c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, SUM(l_quantity) FROM customer TABLE OPTION (LOOP), orders, lineitem WHERE o_orderkey IN ( SELECT l_orderkey FROM lineitem GROUP BY l_orderkey HAVING SUM(l_quantity) > 312 ) AND c_custkey = o_custkey AND o_orderkey = l_orderkey GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice ORDER BY o_totalprice DESC, o_orderdate { time 9.2e-06% fanout 1 input 1 rows time 1.6e-05% fanout 1 input 1 rows { fork time 41% fanout 9.38797e+06 input 1 rows ORDERS 6.3e+04 rows(.O_TOTALPRICE, .O_ORDERKEY, .O_CUSTKEY, .O_ORDERDATE) top k on O_TOTALPRICE time 28% fanout 9.87434e-05 input 9.38797e+06 rows END Node After test: 0: if ({ time 0.57% fanout 1 input 9.38797e+06 rows time 13% fanout 4.02796 input 9.38797e+06 rows LINEITEM 4.6 rows(.L_ORDERKEY, .L_QUANTITY) inlined L_ORDERKEY = k_.O_ORDERKEY time 14% fanout 2.52285e-05 input 3.78144e+07 rows Sort streaming with duplicates (set_ctr, .L_ORDERKEY) -> (.L_QUANTITY) time 2.4% fanout 9840.63 input 954 rows group by read node (gb_set_no, .L_ORDERKEY, aggregate) END Node After test: 0: if (aggregate > 312 ) then 4 else 5 unkn 5 4: BReturn 1 5: BReturn 0 time 0.0007% fanout 0 input 927 rows Subquery SELECT( ) } ) then 4 else 5 unkn 5 4: BReturn 1 5: BReturn 0 time 0.021% fanout 1 input 927 rows CUSTOMER unq 0.96 rows (.C_CUSTKEY) inlined C_CUSTKEY = k_.O_CUSTKEY time 0.025% fanout 7 input 927 rows LINEITEM 4.6 rows(.L_QUANTITY) inlined L_ORDERKEY = k_.O_ORDERKEY time 0.0093% fanout 0.0174141 input 6489 rows Sort streaming with duplicates (.C_CUSTKEY, .O_ORDERKEY) -> (.L_QUANTITY, .O_TOTALPRICE, .O_ORDERDATE) time 0.0091% fanout 8.20354 input 113 rows group by read node (.C_CUSTKEY, .O_ORDERKEY, aggregate, .O_TOTALPRICE, .O_ORDERDATE) time 0.011% fanout 0 input 927 rows Sort (.O_TOTALPRICE, .O_ORDERDATE) -> (.O_ORDERKEY, aggregate, .C_CUSTKEY) } time 8.4e-05% fanout 100 input 1 rows top order by read (.O_ORDERKEY, .O_ORDERDATE, .O_TOTALPRICE, aggregate, .C_CUSTKEY) time 0.0016% fanout 1 input 100 rows CUSTOMER unq 0.96 rows (.C_NAME) inlined C_CUSTKEY = .C_CUSTKEY time 0.00022% fanout 0 input 100 rows SELECT (.C_NAME, .C_CUSTKEY, .O_ORDERKEY, .O_ORDERDATE, .O_TOTALPRICE, aggregate) } 18463.25 ms CPU, 855 ms 2159.4% CPU #line 1 "tq/19.sql" SELECT SUM(l_extendedprice* (1 - l_discount)) AS revenue FROM lineitem, part WHERE ( p_partkey = l_partkey AND p_brand = 'Brand#12' AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 1 + 10 AND p_size BETWEEN 1 AND 5 AND l_shipmode IN ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) OR ( p_partkey = l_partkey AND p_brand = 'Brand#23' AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 10 + 10 AND p_size BETWEEN 1 AND 10 AND l_shipmode IN ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) OR ( p_partkey = l_partkey AND p_brand = 'Brand#34' AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 20 + 10 AND p_size BETWEEN 1 AND 15 AND l_shipmode IN ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) { time 1e-05% fanout 1 input 1 rows time 0.013% fanout 1 input 1 rows Precode: 0: chash_in_init := Call chash_in_init ( 182 , $29 "chash_in_tree", 0 , 0 , , ) 5: temp := artm 1 + 10 9: temp := artm 10 + 10 13: temp := artm 20 + 10 17: BReturn 0 { hash filler time 4.4% fanout 2e+07 input 1 rows PART 7.2e+04 rows(.P_BRAND, .P_CONTAINER, .P_SIZE, .P_PARTKEY) P_SIZE >= 1 time 17% fanout 0.00240925 input 2e+07 rows END Node After test: 0: if (.P_BRAND = ) then 4 else 17 unkn 17 4: if (.P_SIZE <= 5 ) then 8 else 17 unkn 17 8: one_of_these := Call one_of_these (.P_CONTAINER, , , , ) 13: if ( 0 < one_of_these) then 51 else 17 unkn 17 17: if (.P_BRAND = ) then 21 else 34 unkn 34 21: if (.P_SIZE <= 10 ) then 25 else 34 unkn 34 25: one_of_these := Call one_of_these (.P_CONTAINER, , , , ) 30: if ( 0 < one_of_these) then 51 else 34 unkn 34 34: if (.P_BRAND = ) then 38 else 52 unkn 52 38: if (.P_SIZE <= 15 ) then 42 else 52 unkn 52 42: one_of_these := Call one_of_these (.P_CONTAINER, , , , ) 47: if ( 0 < one_of_these) then 51 else 52 unkn 52 51: BReturn 1 52: BReturn 0 time 0.056% fanout 0 input 48185 rows Sort hf 52 (.P_PARTKEY) -> (.P_SIZE, .P_CONTAINER, .P_BRAND) } time 3.1e-05% fanout 1 input 1 rows { fork time 79% fanout 51584 input 1 rows LINEITEM 1.1e+07 rows(.L_QUANTITY, .L_PARTKEY, .L_EXTENDEDPRICE, .L_DISCOUNT, .L_SHIPMODE) L_SHIPINSTRUCT = hash partition+bloom by 0 () hash partition+bloom by 59 (tmp)hash join merged always card 0.00032 -> (.P_SIZE, .P_CONTAINER, .P_BRAND) time 0.031% fanout 0.598112 input 51584 rows END Node After test: 0: if (.L_QUANTITY <= temp) then 4 else 8 unkn 8 4: if (.L_QUANTITY >= 1 ) then 24 else 8 unkn 8 8: if (.L_QUANTITY <= temp) then 12 else 16 unkn 16 12: if ( 10 <= .L_QUANTITY) then 24 else 16 unkn 16 16: if (temp >= .L_QUANTITY) then 20 else 25 unkn 25 20: if (.L_QUANTITY >= 20 ) then 24 else 25 unkn 25 24: BReturn 1 25: BReturn 0 time 0.0018% fanout 1 input 30853 rows Precode: 0: temp := artm 1 - .L_DISCOUNT 4: temp := artm .L_EXTENDEDPRICE * temp 8: BReturn 0 Hash source 52 merged into ts 0.00032 rows(k_.L_PARTKEY) -> (.P_SIZE, .P_CONTAINER, .P_BRAND) time 0.063% fanout 0 input 30853 rows END Node After test: 0: if (.P_BRAND = ) then 4 else 25 unkn 25 4: if (.L_QUANTITY >= 1 ) then 8 else 25 unkn 25 8: if (.L_QUANTITY <= temp) then 12 else 25 unkn 25 12: if (.P_SIZE <= 5 ) then 16 else 25 unkn 25 16: one_of_these := Call one_of_these (.P_CONTAINER, , , , ) 21: if ( 0 < one_of_these) then 75 else 25 unkn 25 25: if (.P_BRAND = ) then 29 else 50 unkn 50 29: if ( 10 <= .L_QUANTITY) then 33 else 50 unkn 50 33: if (.L_QUANTITY <= temp) then 37 else 50 unkn 50 37: if (.P_SIZE <= 10 ) then 41 else 50 unkn 50 41: one_of_these := Call one_of_these (.P_CONTAINER, , , , ) 46: if ( 0 < one_of_these) then 75 else 50 unkn 50 50: if (.P_BRAND = ) then 54 else 76 unkn 76 54: if (.L_QUANTITY >= 20 ) then 58 else 76 unkn 76 58: if (temp >= .L_QUANTITY) then 62 else 76 unkn 76 62: if (.P_SIZE <= 15 ) then 66 else 76 unkn 76 66: one_of_these := Call one_of_these (.P_CONTAINER, , , , ) 71: if ( 0 < one_of_these) then 75 else 76 unkn 76 75: BReturn 1 76: BReturn 0 After code: 0: sum revenuetemp 5: BReturn 0 } time 2.4e-05% fanout 0 input 1 rows SELECT (revenue) } 25398.35 ms CPU, 1154 ms 2200.9% CPU #line 3 "tq/20.sql" SELECT s_name, s_address FROM supplier, nation WHERE s_suppkey IN ( SELECT ps_suppkey FROM partsupp WHERE ps_partkey IN ( SELECT p_partkey FROM part WHERE p_name LIKE 'forest%' ) AND ps_availqty > ( SELECT 0.5 * SUM(l_quantity) FROM lineitem WHERE l_partkey = ps_partkey AND l_suppkey = ps_suppkey AND l_shipdate >= CAST ('1994-01-01' AS DATE) AND l_shipdate < DATEADD ('year', 1, CAST ('1994-01-01' AS DATE)) ) ) AND s_nationkey = n_nationkey AND n_name = 'CANADA' ORDER BY s_name { time 7.7e-06% fanout 1 input 1 rows time 0.053% fanout 1 input 1 rows { hash filler time 5.3% fanout 217222 input 1 rows PART 2.1e+05 rows(t61.P_PARTKEY) P_NAME LIKE LIKE time 0.0083% fanout 0 input 217222 rows Sort hf 34 (t61.P_PARTKEY) } time 0.57% fanout 1 input 1 rows { hash filler Subquery 43 { time 44% fanout 989835 input 1 rows LINEITEM 9.3e+07 rows(t63.L_PARTKEY, t63.L_SUPPKEY, t63.L_QUANTITY) L_SHIPDATE >= < hash partition+bloom by 41 (tmp)hash join merged always card 0.011 -> () time 0.024% fanout 1 input 989835 rows Hash source 34 merged into ts not partitionable 0.011 rows(t63.L_PARTKEY) -> () After code: 0: t63.L_PARTKEY := := artm t63.L_PARTKEY 4: t63.L_SUPPKEY := := artm t63.L_SUPPKEY 8: t63.L_QUANTITY := := artm t63.L_QUANTITY 12: BReturn 0 time 0.28% fanout 0 input 989835 rows Sort hf 67 (t63.L_PARTKEY, t63.L_SUPPKEY) -> (t63.L_QUANTITY) } } time 3.2e-05% fanout 1 input 1 rows { fork time 0.0003% fanout 1 input 1 rows NATION 1 rows(.N_NATIONKEY) N_NAME = time 1.4e-05% fanout 1 input 1 rows time 7.6% fanout 217222 input 1 rows PART 2.1e+05 rows(t6.P_PARTKEY) P_NAME LIKE LIKE time 2.3% fanout 4 input 217222 rows PARTSUPP 1.2 rows(t4.PS_AVAILQTY, t4.PS_PARTKEY, t4.PS_SUPPKEY) inlined PS_PARTKEY = t6.P_PARTKEY time 15% fanout 0.681339 input 868888 rows END Node After test: 0: { time 0.11% fanout 1 input 868888 rows time 6.1% fanout 1 input 868888 rows { fork time 4.4% fanout 0 input 868888 rows Hash source 67 0.013 rows(k_t4.PS_PARTKEY, k_t4.PS_SUPPKEY) -> (t8.L_QUANTITY) After code: 0: sum sumt8.L_QUANTITYset no set_ctr 5: BReturn 0 } After code: 0: temp := artm 0.5 * sum 4: aggregate := := artm temp 8: BReturn 0 time 1.1% fanout 0 input 868888 rows Subquery SELECT(aggregate) } 8: if (t4.PS_AVAILQTY > scalar) then 12 else 13 unkn 13 12: BReturn 1 13: BReturn 0 time 0.28% fanout 1 input 592007 rows Stage 2 time 0.39% fanout 0.755917 input 592007 rows Distinct (q_set_no, t4.PS_SUPPKEY) After code: 0: PS_SUPPKEY := := artm t4.PS_SUPPKEY 4: BReturn 0 time 0.0042% fanout 1 input 447508 rows Subquery SELECT(PS_SUPPKEY) time 0.9% fanout 0.0401579 input 447508 rows SUPPLIER unq 0.04 rows (.S_NAME, .S_ADDRESS) inlined S_SUPPKEY = PS_SUPPKEY S_NATIONKEY = k_q_.N_NATIONKEY time 0.2% fanout 0 input 17971 rows Sort (.S_NAME) -> (.S_ADDRESS) } time 0.0099% fanout 17971 input 1 rows Key from temp (.S_NAME, .S_ADDRESS) time 0.17% fanout 0 input 17971 rows SELECT (.S_NAME, .S_ADDRESS) } 18834.4 ms CPU, 1760 ms 1070.1% CPU #line 3 "tq/21.sql" SELECT TOP 100 s_name, COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.l_suppkey AND o_orderkey = l1.l_orderkey AND o_orderstatus = 'F' AND l1.l_receiptdate > l1.l_commitdate AND EXISTS ( SELECT * FROM lineitem l2 WHERE l2.l_orderkey = l1.l_orderkey AND l2.l_suppkey <> l1.l_suppkey ) AND NOT EXISTS ( SELECT * FROM lineitem l3 WHERE l3.l_orderkey = l1.l_orderkey AND l3.l_suppkey <> l1.l_suppkey AND l3.l_receiptdate > l3.l_commitdate ) AND s_nationkey = n_nationkey AND n_name = 'SAUDI ARABIA' GROUP BY s_name ORDER BY numwait DESC, s_name { time 2.7e-06% fanout 1 input 1 rows time 0.0026% fanout 1 input 1 rows { hash filler Subquery 27 { time 3.1e-05% fanout 1 input 1 rows NATION 1 rows(t4.N_NATIONKEY) N_NAME = time 0.01% fanout 39953 input 1 rows SUPPLIER 4e+04 rows(t1.S_SUPPKEY, t1.S_NAME) S_NATIONKEY = t4.N_NATIONKEY After code: 0: t1.S_SUPPKEY := := artm t1.S_SUPPKEY 4: t1.S_NAME := := artm t1.S_NAME 8: BReturn 0 time 0.0016% fanout 0 input 39953 rows Sort hf 48 (t1.S_SUPPKEY) -> (t1.S_NAME) } } time 2.9e-06% fanout 1 input 1 rows { fork time 2.8e-06% fanout 1 input 1 rows { fork time 6.1% fanout 7.30725e+07 input 1 rows ORDERS 7.3e+07 rows(.O_ORDERKEY) O_ORDERSTATUS = time 29% fanout 0.158797 input 7.30725e+07 rows LINEITEM 0.0042 rows(l1.L_RECEIPTDATE, l1.L_COMMITDATE, l1.L_ORDERKEY, l1.L_SUPPKEY) inlined L_ORDERKEY = .O_ORDERKEY hash partition+bloom by 58 (tmp)hash join merged always card 0.04 -> (.S_NAME) time 13% fanout 0.0341356 input 1.16037e+07 rows END Node After test: 0: if (l1.L_RECEIPTDATE > l1.L_COMMITDATE) then 4 else 13 unkn 13 4: if ({ time 0.22% fanout 0.630287 input 1.16037e+07 rows time 3.6% fanout 4.99115 input 7.31367e+06 rows LINEITEM 2.3 rows(l2.L_SUPPKEY) inlined L_ORDERKEY = k_l1.L_ORDERKEY time 0.6% fanout 0.799644 input 3.65036e+07 rows END Node After test: 0: if (l2.L_SUPPKEY = l1.L_SUPPKEY) then 5 else 4 unkn 5 4: BReturn 1 5: BReturn 0 time 0.25% fanout 0 input 2.91899e+07 rows Subquery SELECT( ) } ) then 8 else 13 unkn 13 8: if ({ time 0.22% fanout 0.607394 input 1.16037e+07 rows time 6.7% fanout 5.14157 input 7.04803e+06 rows LINEITEM 0.2 rows(l3.L_SUPPKEY, l3.L_RECEIPTDATE, l3.L_COMMITDATE) inlined L_ORDERKEY = k_l1.L_ORDERKEY time 1.3% fanout 0.508618 input 3.62379e+07 rows END Node After test: 0: if (l3.L_RECEIPTDATE > l3.L_COMMITDATE) then 4 else 9 unkn 9 4: if (l3.L_SUPPKEY = l1.L_SUPPKEY) then 9 else 8 unkn 9 8: BReturn 1 9: BReturn 0 time 0.17% fanout 0 input 1.84313e+07 rows Subquery SELECT( ) } ) then 13 else 12 unkn 13 12: BReturn 1 13: BReturn 0 time 0.0097% fanout 1 input 396100 rows Hash source 48 merged into ts 0.04 rows(k_l1.L_SUPPKEY) -> (.S_NAME) time 1.4% fanout 1 input 396100 rows Stage 2 time 0.16% fanout 0 input 396100 rows Sort (q_.S_NAME) -> (inc) } time 0.0097% fanout 39950 input 1 rows group by read node (.S_NAME, numwait)in each partition slice time 0.034% fanout 0 input 39950 rows Sort (numwait, .S_NAME) } time 1.4e-05% fanout 100 input 1 rows top order by read (.S_NAME, numwait) time 2.4e-05% fanout 0 input 100 rows SELECT (.S_NAME, numwait) } 48814.7 ms CPU, 2145 ms 2275.7% CPU #line 4 "tq/22.sql" SELECT cntrycode, COUNT(*) AS numcust, SUM(c_acctbal) AS totacctbal FROM ( SELECT SUBSTRING(c_phone, 1, 2) AS cntrycode, c_acctbal FROM customer WHERE SUBSTRING(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17') AND c_acctbal > ( SELECT AVG(c_acctbal) FROM customer WHERE c_acctbal > 0.00 AND SUBSTRING(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17') ) AND NOT EXISTS ( SELECT * FROM orders WHERE o_custkey = c_custkey ) ) AS custsale GROUP BY cntrycode ORDER BY cntrycode { time 1.4e-05% fanout 1 input 1 rows time 4.8% fanout 1 input 1 rows Precode: 0: $27 "chash_in_init" := Call chash_in_init ( 182 , $29 "chash_in_tree", 0 , 0 , , , , , , , ) 5: { time 1.2e-05% fanout 1 input 1 rows time 7.7e-05% fanout 1 input 1 rows { fork time 20% fanout 0 input 1 rows CUSTOMER 1.4e+07 rows(t4.C_ACCTBAL) C_ACCTBAL > 0 hash partition+bloom by 0 () After code: 0: sum sumt4.C_ACCTBAL 5: sum count 1 10: BReturn 0 } After code: 0: temp := artm sum / count 4: aggregate := := artm temp 8: BReturn 0 time 3.7e-05% fanout 0 input 1 rows Subquery SELECT(aggregate) } 13: BReturn 0 { fork time 2.7e-05% fanout 1 input 1 rows { fork time 25% fanout 1.90967e+06 input 1 rows CUSTOMER 8.6e+05 rows(t2.C_CUSTKEY, t2.C_PHONE, t2.C_ACCTBAL) C_ACCTBAL > k_scalar hash partition+bloom by 0 () time 6.5% fanout 0.333434 input 1.90967e+06 rows END Node After test: 0: if ({ time 0.27% fanout 1 input 1.90967e+06 rows time 4.7% fanout 9.9955 input 1.90967e+06 rows O_CK 2.8 rows() inlined O_CUSTKEY = k_t2.C_CUSTKEY time 1.3% fanout 0 input 1.90881e+07 rows Subquery SELECT( ) } ) then 5 else 4 unkn 5 4: BReturn 1 5: BReturn 0 time 5.4% fanout 1 input 636749 rows Precode: 0: cntrycode := Call substring (t2.C_PHONE, 1 , 2 ) 5: BReturn 0 Stage 2 time 0.43% fanout 0 input 636749 rows Sort (q_cntrycode) -> (inc, t2.C_ACCTBAL) } time 0.013% fanout 7 input 1 rows group by read node (cntrycode, numcust, totacctbal)in each partition slice time 0.0016% fanout 0 input 7 rows Sort (cntrycode) -> (numcust, totacctbal) } time 0.00016% fanout 7 input 1 rows Key from temp (cntrycode, numcust, totacctbal) time 6.8e-05% fanout 0 input 7 rows SELECT (cntrycode, numcust, totacctbal) } 5916.906 ms CPU, 742 ms 797.43% CPU