Planner — TPC-H — TPCH-Q07

/* TPC-H Q07 */
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 tpch_sf1.lineitem
      INNER JOIN tpch_sf1.orders
          ON l_orderkey = o_orderkey
      INNER JOIN tpch_sf1.supplier
          ON l_suppkey = s_suppkey
      INNER JOIN tpch_sf1.customer
          ON o_custkey = c_custkey
      INNER JOIN tpch_sf1.nation n1
          ON s_nationkey = n1.n_nationkey
      INNER JOIN tpch_sf1.nation n2
          ON c_nationkey = n2.n_nationkey
      WHERE ((n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
          OR (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY'))
        AND l_shipdate BETWEEN '1995-01-01' AND '1996-12-31') AS shipping
GROUP BY supp_nation,
         cust_nation,
         l_year
ORDER BY supp_nation,
         cust_nation,
         l_year

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Seek Seek	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 2,089,575 2.1M Rank		Estimation Error Est Err 2,151,250 2.2M Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 317,133 317K Rank	Estimation Error Est Err 5,924 5.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,488,454 3.5M Rank		Estimation Error Est Err 5,507,073 5.5M Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 3,954,438 4M Rank	Estimation Error Est Err 5,924 5.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,488,454 3.5M Rank		Estimation Error Est Err 7,459,503 7.5M Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 5,636,977 5.6M Rank	Estimation Error Est Err 5,992 6K Rank	Estimation Error Est Err 3,488,524 3.5M Rank
Estimation Error Est Err 7,661,265 7.7M Rank		Estimation Error Est Err 5,636,977 5.6M Rank	Estimation Error Est Err 8 8 Rank	Estimation Error Est Err 7,459,503 7.5M Rank	Estimation Error Est Err 5,964 6K Rank	Estimation Error Est Err 5,306,994 5.3M Rank
Estimation Error Est Err 1,973,053 2M Rank		Estimation Error Est Err 2,131,885 2.1M Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 316,220 316K Rank	Estimation Error Est Err 5,924 5.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 710,799 711K Rank	Estimation Error Est Err 560,795 561K Rank	Estimation Error Est Err 832,162 832K Rank	Estimation Error Est Err 5,924 5.9K Rank	Estimation Error Est Err 1,001,938 1M Rank	Estimation Error Est Err 5,924 5.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 145,857 146K Rank		Estimation Error Est Err 58,615 59K Rank	Estimation Error Est Err 5,924 5.9K Rank	Estimation Error Est Err 162,702 163K Rank	Estimation Error Est Err 5,924 5.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 145,857 146K Rank		Estimation Error Est Err 58,615 59K Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 162,702 163K Rank	Estimation Error Est Err 5,932 5.9K Rank	Estimation Error Est Err 134,142 134K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 2 4 SORT n_name67, n_name68, l_year 2 4 AGGREGATE sum GROUP BY HASH n_name32, n_name, EXTRACT(YEAR FROM l_shipdate) 82963 5924 INNER JOIN HASH ON l_orderkey = o_orderkey AND n_name32 IN(GERMANY,FRANCE,FRANCE,GERMANY) 118913 121324 │└INNER JOIN HASH ON o_custkey = c_custkey 11891 12008 │ │└INNER JOIN HASH ON c_nationkey = n_nationkey 2 2 │ │ │└TABLE SCAN nation WHERE n_name IN(FRANCE,GERMANY) 150000 150000 │ │ TABLE SCAN customer 1500000 1500000 │ TABLE SCAN orders 149075 61679 INNER JOIN HASH ON l_suppkey = s_suppkey 804 798 │└INNER JOIN HASH ON s_nationkey = n_nationkey31 2 2 │ │└TABLE SCAN nation WHERE n_name32 IN(GERMANY,FRANCE) 10000 10000 │ TABLE SCAN supplier 1834355 429571 TABLE SCAN lineitem WHERE l_shipdate BETWEEN 2449719 AND 2450449
	Estimate Actual Operator - 4 PROJECT supp_nation, cust_nation, l_year, a1 AS revenue - 4 SORT supp_nation, cust_nation, l_year - 4 PROJECT supp_nation, cust_nation, l_year, a1 - 4 AGGREGATE SUM(volume) AS a1 GROUP BY HASH supp_nation, cust_nation, l_year - 5924 PROJECT n_name AS supp_nation, n_name AS cust_nation, EXTRACT(YEAR FROM l_shipdate) AS l_year, l_extendedprice * (1 - l_discount) AS volume - 5924 FILTER ((n_name_left = 'GERMANY') AND (n_name_right = 'FRANCE')) OR ((n_name_left = 'FRANCE') AND (n_name_right = 'GERMANY')) - 11723 PROJECT l_shipdate, l_extendedprice, l_discount, n_name, n_name - 11723 INNER JOIN HASH ON PROJECTION_167.s_suppkey = PROJECTION_146.l_suppkey - 148370 │└PROJECT l_suppkey, l_shipdate, l_extendedprice, l_discount, n_name AS n_name_right - 148370 │ PROJECT l_shipdate, l_extendedprice, l_discount, l_suppkey, n_name - 148370 │ INNER JOIN HASH ON PROJECTION_152.c_nationkey = PROJECTION_149.n_nationkey - 2 │ │└PROJECT n_nationkey, n_name - 2 │ │ PROJECT n_name, n_nationkey - 2 │ │ TABLE SCAN nation WHERE (n_name = 'FRANCE') OR (n_name = 'GERMANY') - 1828450 │ PROJECT c_nationkey, l_shipdate, l_extendedprice, l_discount, l_suppkey - 1828450 │ PROJECT l_shipdate, l_extendedprice, l_discount, l_suppkey, c_nationkey - 1828450 │ INNER JOIN HASH ON PROJECTION_158.o_custkey = PROJECTION_155.c_custkey - 150000 │ │└PROJECT c_custkey, c_nationkey - 150000 │ │ PROJECT c_custkey, c_nationkey - 150000 │ │ TABLE SCAN customer - 1828450 │ PROJECT o_custkey, l_shipdate, l_extendedprice, l_discount, l_suppkey - 1828450 │ PROJECT l_shipdate, l_extendedprice, l_discount, l_suppkey, o_custkey - 1828450 │ INNER JOIN HASH ON PROJECTION_164.l_orderkey = PROJECTION_161.o_orderkey - 1500000 │ │└PROJECT o_orderkey, o_custkey - 1500000 │ │ PROJECT o_orderkey, o_custkey - 1500000 │ │ TABLE SCAN orders - 1828450 │ PROJECT l_orderkey, l_shipdate, l_extendedprice, l_discount, l_suppkey - 1828450 │ PROJECT l_orderkey, l_shipdate, l_extendedprice, l_discount, l_suppkey - 1828450 │ TABLE SCAN lineitem WHERE (l_shipdate >= '1995-01-01') AND (l_shipdate <= '1996-12-31') - 798 PROJECT s_suppkey, n_name AS n_name_left - 798 PROJECT s_suppkey, n_name - 798 INNER JOIN HASH ON PROJECTION_173.s_nationkey = PROJECTION_170.n_nationkey - 2 │└PROJECT n_nationkey, n_name - 2 │ PROJECT n_nationkey, n_name - 2 │ TABLE SCAN nation WHERE (n_name = 'GERMANY') OR (n_name = 'FRANCE') - 10000 PROJECT s_nationkey, s_suppkey - 10000 PROJECT s_suppkey, s_nationkey - 10000 TABLE SCAN supplier
	Estimate Actual Operator 47237 4 SORT shipping.supp_nation, shipping.cust_nation, shipping.l_year 47237 4 AGGREGATE SUM(#3) GROUP BY HASH #0, #1, #2 47238 5924 PROJECT supp_nation, cust_nation, l_year, volume 47238 5924 PROJECT supp_nation, cust_nation, l_year, volume 47238 5924 FILTER ((n_name = 'GERMANY') AND (n_name = 'FRANCE')) OR ((n_name = 'FRANCE') AND (n_name = 'GERMANY')) 47238 11723 INNER JOIN HASH ON o_custkey = c_custkey 28846 12008 │└INNER JOIN HASH ON c_nationkey = n_nationkey 25 2 │ │└FILTER (n_name = 'FRANCE') OR (n_name = 'GERMANY') 25 25 │ │ TABLE SCAN nation WHERE n_name = 'FRANCE' OR n_name = 'GERMANY' 150000 12008 │ TABLE SCAN customer WHERE c_custkey <= 149999 232742 145696 INNER JOIN HASH ON o_orderkey = l_orderkey 230815 145703 │└INNER JOIN HASH ON l_suppkey = s_suppkey 1923 798 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 5 2 │ │ │└FILTER (n_name = 'GERMANY') OR (n_name = 'FRANCE') 25 25 │ │ │ TABLE SCAN nation WHERE n_name = 'GERMANY' OR n_name = 'FRANCE' 10000 798 │ │ TABLE SCAN supplier 1200243 1827687 │ TABLE SCAN lineitem WHERE l_shipdate >= '1995-01-01' AND l_shipdate <= '1996-12-31' 1500000 132510 TABLE SCAN orders
	Estimate Actual Operator 48009 4 SORT supp_nation, cust_nation, l_year 48009 4 SORT supp_nation, cust_nation, l_year 48009 4 PROJECT supp_nation, cust_nation, l_year, revenue 48009 4 AGGREGATE SUM(volume) GROUP BY HASH supp_nation, cust_nation, l_year 48009 40 DISTRIBUTE HASH ON supp_nation, cust_nation, l_year 48009 40 AGGREGATE SUM(volume) GROUP BY HASH supp_nation, cust_nation, l_year 48009 5924 PROJECT supp_nation, cust_nation, l_year, volume 48009 5924 INNER JOIN HASH ON n_nationkey = c_nationkey AND (((n_name = 'GERMANY') AND (n_name = 'FRANCE')) OR ((n_name = 'FRANCE') AND (n_name = 'GERMANY'))) 240048 145703 │└PROJECT l_extendedprice, l_discount, l_shipdate, c_nationkey, n_name 240048 145703 │ INNER JOIN HASH ON n_nationkey = s_nationkey 1200243 1828450 │ │└PROJECT l_extendedprice, l_discount, l_shipdate, s_nationkey, c_nationkey 1200243 1828450 │ │ INNER JOIN HASH ON c_custkey = o_custkey 1200243 1828450 │ │ │└DISTRIBUTE HASH ON o_custkey 1200243 1828450 │ │ │ PROJECT l_extendedprice, l_discount, l_shipdate, o_custkey, s_nationkey 1200243 1828450 │ │ │ INNER JOIN HASH ON s_suppkey = l_suppkey 1200243 1828450 │ │ │ │└PROJECT l_suppkey, l_extendedprice, l_discount, l_shipdate, o_custkey 1200243 1828450 │ │ │ │ INNER JOIN HASH ON o_orderkey = l_orderkey 1200243 1828450 │ │ │ │ │└DISTRIBUTE HASH ON l_orderkey 1200243 1828450 │ │ │ │ │ FILTER (l_shipdate >= DATE'1995-01-01') AND (l_shipdate <= DATE'1996-12-31') 6001215 6001215 │ │ │ │ │ TABLE SCAN lineitem WHERE (((l_shipdate >= DATE'1995-01-01') AND (l_shipdate <= DATE'1996-12-31')) AND CASE MOD(HASH_REPARTITION l_orderkey,10) WHEN 0 THEN (((l_orderkey >= 5) AND (l_orderkey <= 5999847)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 6) AND (l_orderkey <= 5999970)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 37) AND (l_orderkey <= 5999975)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 1) AND (l_orderkey <= 5999971)) AND TRUE) WHEN 4 THEN (((l_orderkey >=... 1500000 1500000 │ │ │ │ DISTRIBUTE HASH ON o_orderkey 1500000 1500000 │ │ │ │ TABLE SCAN orders WHERE CASE MOD(HASH_REPARTITION o_custkey,10) WHEN 0 THEN (((o_custkey >= 5) AND (o_custkey <= 149992)) AND TRUE) WHEN 1 THEN (((o_custkey >= 6) AND (o_custkey <= 149997)) AND TRUE) WHEN 2 THEN (((o_custkey >= 10) AND (o_custkey <= 149995)) AND TRUE) WHEN 3 THEN (((o_custkey >= 1) AND (o_custkey <= 150000)) AND TRUE) WHEN 4 THEN (((o_custkey >= 12) AND (o_custkey <= 149998)) AND TRUE) WHEN 5 THEN (((o_custkey >= 8) AND (o_custkey <= 149988... 10000 10000 │ │ │ TABLE SCAN supplier WHERE ((s_nationkey >= 6) AND (s_nationkey <= 7)) AND s_nationkey IN(6,7) 150000 150000 │ │ DISTRIBUTE HASH ON c_custkey 150000 150000 │ │ TABLE SCAN customer WHERE ((c_nationkey >= 6) AND (c_nationkey <= 7)) AND c_nationkey IN(6,7) 5 2 │ DISTRIBUTE GATHER 5 2 │ FILTER (n_name = 'GERMANY') OR (n_name = 'FRANCE') 25 25 │ DISTRIBUTE ROUND ROBIN 25 25 │ TABLE SCAN nation WHERE (n_name = 'GERMANY') OR (n_name = 'FRANCE') 5 2 DISTRIBUTE GATHER 5 2 FILTER (n_name = 'FRANCE') OR (n_name = 'GERMANY') 25 25 DISTRIBUTE ROUND ROBIN 25 25 TABLE SCAN nation WHERE (n_name = 'FRANCE') OR (n_name = 'GERMANY')
	Estimate Actual Operator ∞ 4 SORT shipping.supp_nation ASC NULLS FIRST, shipping.cust_nation ASC NULLS FIRST, shipping.l_year ASC NULLS FIRST ∞ 4 AGGREGATE SUM(shipping.volume) GROUP BY HASH shipping.supp_nation, shipping.cust_nation, shipping.l_year ∞ 68 DISTRIBUTE HASH ON shipping.supp_nation, shipping.cust_nation, shipping.l_year ∞ 68 AGGREGATE SUM(shipping.volume) GROUP BY HASH shipping.supp_nation, shipping.cust_nation, shipping.l_year ∞ 5924 INNER JOIN HASH ON c_nationkey = n2.n_nationkey ∞ 2 │└DISTRIBUTE HASH 6000000 2 │ DISTRIBUTE HASH 6000000 2 │ TABLE SCAN nation WHERE (n1.n_name = 'GERMANY') OR (n1.n_name = 'FRANCE') ∞ 145703 INNER JOIN HASH ON s_nationkey = n1.n_nationkey ∞ 2 │└DISTRIBUTE GATHER 1500000 2 │ TABLE SCAN nation WHERE (n1.n_name = 'GERMANY') OR (n1.n_name = 'FRANCE') ∞ 1828450 INNER JOIN HASH ON o_custkey = c_custkey ∞ 150000 │└DISTRIBUTE GATHER 10000 150000 │ TABLE SCAN customer ∞ 1828450 INNER JOIN HASH ON l_suppkey = s_suppkey ∞ 10000 │└DISTRIBUTE GATHER 150000 10000 │ TABLE SCAN supplier 9T 1828450 INNER JOIN HASH ON l_orderkey = o_orderkey 9T 1500000 │└DISTRIBUTE HASH ON o_orderkey 25 1500000 │ TABLE SCAN orders 9T 1828450 DISTRIBUTE HASH ON l_orderkey 25 1828450 TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1995-01-01') AND (l_shipdate <= DATE'1996-12-31')
	Estimate Actual Operator 2 4 AGGREGATE SUM(Expr1019) AS Expr1018 GROUP BY SORT n_name, n_name, Expr1016 1254 5924 SORT n_name, n_name, Expr1016 1254 5924 INNER JOIN HASH ON n_nationkey as n_nationkey = s_nationkey AND (n_name as n_name = 'GERMANY' AND n_name as n_name = 'FRANCE' OR n_name as n_name = 'FRANCE' AND n_name as n_name = 'GERMANY') 2 2 │└TABLE SCAN nation AS n1 WHERE n_name as n_name = 'FRANCE' OR n_name as n_name = 'GERMANY' 6269 11723 INNER JOIN HASH ON l_suppkey = s_suppkey 1000 798 │└TABLE SCAN supplier WHERE BLOOM(s_nationkey) 6269 11723 INNER JOIN HASH ON c_nationkey = n2.n_nationkey 2 2 │└TABLE SCAN nation AS n2 WHERE n_name as n_name = 'FRANCE' OR n_name as n_name = 'GERMANY' 5044 11723 INNER JOIN HASH ON o_custkey = c_custkey 15000 12008 │└TABLE SCAN customer WHERE BLOOM(c_nationkey) 5037 11723 INNER JOIN HASH ON l_orderkey = o_orderkey 150000 121324 │└TABLE SCAN orders WHERE BLOOM(o_custkey) 1830 11723 PROJECT datepart(year,l_shipdate) AS Expr1016, l_extendedprice * (1. - l_discount) AS Expr1019 1830 11723 TABLE SCAN lineitem WHERE l_shipdate >= '1995-01-01' AND l_shipdate <= '1996-12-31' AND BLOOM(l_orderkey) AND BLOOM(l_suppkey)
	Estimate Actual Operator - 4 PROJECT n_name AS supp_nation, n_name_13 AS cust_nation, expr_gid AS l_year, sum AS revenue - 4 SORT n_name, n_name_13, expr_gid - 4 AGGREGATE SUM(sum_20) AS sum GROUP BY HASH n_name, n_name_13, expr_gid - 8 DISTRIBUTE HASH ON n_name, n_name_13, expr_gid - 8 AGGREGATE SUM(expr_19) AS sum_20 GROUP BY PARTIAL n_name, n_name_13, expr_gid - 5924 PROJECT l_extendedprice * (1 - l_discount) AS expr_19, n_name, n_name_13, EXTRACT(YEAR FROM l_shipdate) AS expr_gid - 5924 INNER JOIN HASH ON (c_nationkey = n_nationkey_12) AND (((n_name = 'GERMANY') OR (n_name_13 = 'GERMANY')) AND ((n_name_13 = 'FRANCE') OR (n_name = 'FRANCE'))) 25 2 │└DISTRIBUTE GATHER 25 2 │ PROJECT n_nationkey AS n_nationkey_12, n_name AS n_name_13 25 2 │ FILTER n_name IN('FRANCE','GERMANY') 25 2 │ TABLE SCAN nation - 11723 INNER JOIN HASH ON s_nationkey = n_nationkey 25 2 │└DISTRIBUTE GATHER 25 2 │ FILTER n_name IN('FRANCE','GERMANY') 25 2 │ TABLE SCAN nation - 11723 INNER JOIN HASH ON o_custkey = c_custkey 150000 12008 │└DISTRIBUTE GATHER 150000 12008 │ TABLE SCAN customer - 11723 INNER JOIN HASH ON l_suppkey = s_suppkey 10000 798 │└DISTRIBUTE GATHER 10000 798 │ TABLE SCAN supplier - 11723 INNER JOIN HASH ON l_orderkey = o_orderkey 1500000 121324 │└DISTRIBUTE HASH ON o_orderkey 1500000 121324 │ TABLE SCAN orders 1735005 11723 FILTER l_shipdate BETWEEN '1995-01-01' AND '1996-12-31' 1735005 11723 TABLE SCAN lineitem
	Estimate Actual Operator 6063 4 AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) GROUP BY SORT n_name, n_name, EXTRACT(year FROM l_shipdate) 7578 5924 SORT n_name, n_name, EXTRACT(year FROM l_shipdate) 2526 1974 INNER JOIN HASH ON o_orderkey = l_orderkey AND (((n_name = 'GERMANY') AND (n_name = 'FRANCE')) OR ((n_name = 'FRANCE') AND (n_name = 'GERMANY'))) 437079 437109 │└INNER JOIN LOOP ON l_suppkey = s_suppkey 2400 2394 │ │└INNER JOIN LOOP ON s_nationkey = n_nationkey 2 2 │ │ │└TABLE SCAN nation AS n1 WHERE (n1.n_name = 'GERMANY') OR (n1.n_name = 'FRANCE') 2400 2394 │ │ TABLE SEEK supplier AS supplier 435708 437120 │ TABLE SEEK lineitem AS lineitem WHERE (l_shipdate >= '1995-01-01') AND (l_shipdate <= '1996-12-31') 150000 121323 INNER JOIN LOOP ON o_custkey = c_custkey 5000 4002 │└INNER JOIN HASH ON c_nationkey = n_nationkey 2 2 │ │└TABLE SCAN nation AS n2 WHERE (n2.n_name = 'FRANCE') OR (n2.n_name = 'GERMANY') 187500 150000 │ TABLE SCAN customer AS customer 216144 121280 TABLE SEEK orders AS orders