Planner — TPC-H — TPCH-Q08

/* TPC-H Q08 */
SELECT o_year,
       SUM(CASE
               WHEN nation = 'FRANCE'
                   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 tpch_sf1.lineitem
      INNER JOIN tpch_sf1.orders
          ON o_orderkey = l_orderkey
      INNER JOIN tpch_sf1.part
          ON l_partkey = p_partkey
      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 c_nationkey = n1.n_nationkey
      INNER JOIN tpch_sf1.region
          ON n1.n_regionkey = r_regionkey
      INNER JOIN tpch_sf1.nation n2
          ON s_nationkey = n2.n_nationkey
      WHERE r_name = 'EUROPE'
        AND o_orderdate BETWEEN '1995-01-01' AND '1996-12-31'
        AND p_type = 'SMALL POLISHED NICKEL') AS all_nations
GROUP BY o_year
ORDER BY o_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 6,384,770 6.4M Rank		Estimation Error Est Err 6,398,893 6.4M Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 104,959 105K Rank	Estimation Error Est Err 2,647 2.6K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,619,932 6.6M Rank		Estimation Error Est Err 6,623,797 6.6M Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 868,417 868K Rank	Estimation Error Est Err 2,647 2.6K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,611,758 6.6M Rank		Estimation Error Est Err 6,089,325 6.1M Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 680,384 680K Rank	Estimation Error Est Err 2,651 2.7K Rank	Estimation Error Est Err 618,721 619K Rank
Estimation Error Est Err 7,861,270 7.9M Rank		Estimation Error Est Err 2,023,427 2M Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 7,884,900 7.9M Rank	Estimation Error Est Err 2,667 2.7K Rank	Estimation Error Est Err 6,822,812 6.8M Rank
Estimation Error Est Err 6,031,294 6M Rank		Estimation Error Est Err 6,044,573 6M Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 104,959 105K Rank	Estimation Error Est Err 2,647 2.6K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 178,843 179K Rank	Estimation Error Est Err 177,414 177K Rank	Estimation Error Est Err 192,913 193K Rank	Estimation Error Est Err 2,646 2.6K Rank	Estimation Error Est Err 268,894 269K Rank	Estimation Error Est Err 2,646 2.6K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 60,525 61K Rank		Estimation Error Est Err 68,194 68K Rank	Estimation Error Est Err 2,647 2.6K Rank	Estimation Error Est Err 107,601 108K Rank	Estimation Error Est Err 2,647 2.6K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 136,993 137K Rank		Estimation Error Est Err 18,529 19K Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 143,500 144K Rank	Estimation Error Est Err 2,651 2.7K Rank	Estimation Error Est Err 134,350 134K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 2 2 SORT o_year 2 2 AGGREGATE sum, sum GROUP BY HASH EXTRACT(YEAR FROM o_orderdate) 3014 2647 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 3042 2647 INNER JOIN HASH ON l_suppkey = s_suppkey 3007 2647 │└INNER JOIN HASH ON c_nationkey = n_nationkey14 5 5 │ │└INNER JOIN HASH ON n_regionkey16 = r_regionkey 1 1 │ │ │└TABLE SCAN region WHERE r_name = EUROPE 25 25 │ │ TABLE SCAN nation 15134 12905 │ INNER JOIN HASH ON o_custkey = c_custkey 15134 12905 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 44732 42054 │ │ │└INNER JOIN HASH ON l_partkey = p_partkey 1562 1403 │ │ │ │└TABLE SCAN part WHERE p_type = SMALL POLISHED NICKEL 6001215 6001215 │ │ │ TABLE SCAN lineitem 498046 222101 │ │ TABLE SCAN orders WHERE o_orderdate BETWEEN 2449719 AND 2450449 150000 150000 │ TABLE SCAN customer 10000 10000 TABLE SCAN supplier
	Estimate Actual Operator - 2 PROJECT o_year, a2 / a3 AS mkt_share - 2 SORT o_year - 2 PROJECT o_year - 2 AGGREGATE sumIf(volume,a9) AS a2, SUM(volume) AS a3 GROUP BY HASH o_year - 2647 PROJECT EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n_name = 'FRANCE' AS a9 - 2647 PROJECT l_extendedprice, l_discount, o_orderdate, n_name - 2647 INNER JOIN HASH ON PROJECTION_222.n_nationkey = PROJECTION_183.s_nationkey - 2647 │└PROJECT s_nationkey, l_extendedprice, l_discount, o_orderdate - 2647 │ PROJECT l_extendedprice, l_discount, o_orderdate, s_nationkey - 2647 │ INNER JOIN HASH ON PROJECTION_219.s_suppkey = PROJECTION_186.l_suppkey - 2647 │ │└PROJECT l_suppkey, l_extendedprice, l_discount, o_orderdate - 2647 │ │ PROJECT l_extendedprice, l_discount, l_suppkey, o_orderdate - 2647 │ │ INNER JOIN HASH ON PROJECTION_216.p_partkey = PROJECTION_189.l_partkey - 370103 │ │ │└PROJECT l_partkey, l_extendedprice, l_discount, l_suppkey, o_orderdate - 370103 │ │ │ PROJECT l_extendedprice, l_discount, l_partkey, l_suppkey, o_orderdate - 370103 │ │ │ INNER JOIN HASH ON PROJECTION_213.l_orderkey = PROJECTION_192.o_orderkey - 92715 │ │ │ │└PROJECT o_orderkey, o_orderdate - 92715 │ │ │ │ PROJECT o_orderdate, o_orderkey - 92715 │ │ │ │ INNER JOIN HASH ON PROJECTION_210.o_custkey = PROJECTION_195.c_custkey - 30197 │ │ │ │ │└PROJECT c_custkey - 30197 │ │ │ │ │ PROJECT c_custkey - 30197 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_207.c_nationkey = PROJECTION_198.n_nationkey - 5 │ │ │ │ │ │└PROJECT n_nationkey - 5 │ │ │ │ │ │ PROJECT n_nationkey - 5 │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_204.n_regionkey = PROJECTION_201.r_regionkey - 1 │ │ │ │ │ │ │└PROJECT r_regionkey - 1 │ │ │ │ │ │ │ PROJECT r_regionkey - 1 │ │ │ │ │ │ │ TABLE SCAN region WHERE r_name = 'EUROPE' - 25 │ │ │ │ │ │ PROJECT n_regionkey, n_nationkey - 25 │ │ │ │ │ │ PROJECT n_nationkey, n_regionkey - 25 │ │ │ │ │ │ TABLE SCAN nation - 150000 │ │ │ │ │ PROJECT c_nationkey, c_custkey - 150000 │ │ │ │ │ PROJECT c_custkey, c_nationkey - 150000 │ │ │ │ │ TABLE SCAN customer - 457263 │ │ │ │ PROJECT o_custkey, o_orderdate, o_orderkey - 457263 │ │ │ │ PROJECT o_orderkey, o_orderdate, o_custkey - 457263 │ │ │ │ TABLE SCAN orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate <= '1996-12-31') - 6001215 │ │ │ PROJECT l_orderkey, l_extendedprice, l_discount, l_partkey, l_suppkey - 6001215 │ │ │ PROJECT l_extendedprice, l_discount, l_orderkey, l_partkey, l_suppkey - 6001215 │ │ │ TABLE SCAN lineitem - 1403 │ │ PROJECT p_partkey - 1403 │ │ PROJECT p_partkey - 1403 │ │ TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' - 10000 │ PROJECT s_suppkey, s_nationkey - 10000 │ PROJECT s_suppkey, s_nationkey - 10000 │ TABLE SCAN supplier - 25 PROJECT n_nationkey, n_name - 25 PROJECT n_name, n_nationkey - 25 TABLE SCAN nation
	Estimate Actual Operator 1588 2 SORT all_nations.o_year 1588 2 PROJECT o_year, mkt_share - 2 AGGREGATE SUM(#1), SUM(#2) GROUP BY HASH #0 1757 2647 PROJECT o_year, CASE WHEN (nation = 'FRANCE') THEN volume ELSE 0.0000 END, volume 1757 2647 PROJECT o_year, volume, nation 1757 2647 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 1827 2647 INNER JOIN HASH ON s_suppkey = l_suppkey 1827 2647 │└INNER JOIN HASH ON c_nationkey = n_nationkey 5 5 │ │└INNER JOIN HASH ON n_regionkey = r_regionkey 1 1 │ │ │└TABLE SCAN region WHERE r_name = 'EUROPE' 25 5 │ │ TABLE SCAN nation 9502 10715 │ INNER JOIN HASH ON c_custkey = o_custkey 9003 12905 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 44646 42054 │ │ │└INNER JOIN HASH ON l_partkey = p_partkey 1419 1403 │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' 6001215 6000567 │ │ │ TABLE SCAN lineitem 300000 17277 │ │ TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31' 150000 10088 │ TABLE SCAN customer WHERE c_custkey <= 149999 10000 1928 TABLE SCAN supplier
	Estimate Actual Operator 12002 2 SORT o_year 12002 2 SORT o_year 12002 2 PROJECT o_year, mkt_share 12002 2 AGGREGATE SUM(CASE WHEN (nation = 'FRANCE') THEN volume ELSE 0.0000 END), SUM(volume) GROUP BY HASH o_year 12002 20 DISTRIBUTE HASH ON o_year 12002 20 AGGREGATE SUM(CASE WHEN (nation = 'FRANCE') THEN volume ELSE 0.0000 END), SUM(volume) GROUP BY HASH o_year 12002 2647 PROJECT o_year, volume, nation 12002 2647 INNER JOIN HASH ON n_nationkey = s_nationkey 12002 2647 │└INNER JOIN HASH ON r_regionkey = n_regionkey 60012 12905 │ │└PROJECT l_extendedprice, l_discount, o_orderdate, s_nationkey, n_regionkey 60012 12905 │ │ INNER JOIN HASH ON n_nationkey = c_nationkey 60012 12905 │ │ │└PROJECT l_extendedprice, l_discount, o_orderdate, s_nationkey, c_nationkey 60012 12905 │ │ │ INNER JOIN HASH ON c_custkey = o_custkey 60012 12905 │ │ │ │└DISTRIBUTE HASH ON o_custkey 60012 12905 │ │ │ │ PROJECT l_extendedprice, l_discount, o_custkey, o_orderdate, s_nationkey 60012 12905 │ │ │ │ INNER JOIN HASH ON s_suppkey = l_suppkey 60012 12905 │ │ │ │ │└INNER JOIN HASH ON p_partkey = l_partkey 300060 1829418 │ │ │ │ │ │└PROJECT l_partkey, l_suppkey, l_extendedprice, l_discount, o_custkey, o_orderdate 300060 1829418 │ │ │ │ │ │ INNER JOIN HASH ON o_orderkey = l_orderkey 6001215 6001215 │ │ │ │ │ │ │└DISTRIBUTE HASH ON l_orderkey 6001215 6001215 │ │ │ │ │ │ │ TABLE SCAN lineitem WHERE (CASE MOD(HASH_REPARTITION l_orderkey,10) WHEN 0 THEN (((l_orderkey >= 7) AND (l_orderkey <= 5999847)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 36) AND (l_orderkey <= 5999970)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 39) AND (l_orderkey <= 5999972)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 1) AND (l_orderkey <= 5999971)) AND TRUE) WHEN 4 THEN (((l_orderkey >= 160) AND (l_orderkey <= 5999969)) AND TRUE) WHEN 5 THEN (((l_order... 300000 457263 │ │ │ │ │ │ DISTRIBUTE HASH ON o_orderkey 300000 457263 │ │ │ │ │ │ FILTER (o_orderdate >= DATE'1995-01-01') AND (o_orderdate <= DATE'1996-12-31') 1500000 1500000 │ │ │ │ │ │ TABLE SCAN orders WHERE ((o_orderdate >= DATE'1995-01-01') AND (o_orderdate <= DATE'1996-12-31')) AND 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... 40000 1403 │ │ │ │ │ DISTRIBUTE GATHER 40000 1403 │ │ │ │ │ FILTER p_type = 'SMALL POLISHED NICKEL' 200000 200000 │ │ │ │ │ DISTRIBUTE ROUND ROBIN 200000 200000 │ │ │ │ │ TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' 10000 10000 │ │ │ │ TABLE SCAN supplier WHERE ((s_nationkey >= 0) AND (s_nationkey <= 24)) AND s_nationkey IN(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) 150000 150000 │ │ │ DISTRIBUTE HASH ON c_custkey 150000 150000 │ │ │ TABLE SCAN customer WHERE ((c_nationkey >= 0) AND (c_nationkey <= 24)) AND c_nationkey IN(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) 25 25 │ │ TABLE SCAN nation WHERE ((n_regionkey >= 3) AND (n_regionkey <= 3)) AND n_regionkey IN 3 1 1 │ DISTRIBUTE GATHER 1 1 │ FILTER r_name = 'EUROPE' 5 5 │ DISTRIBUTE ROUND ROBIN 5 5 │ TABLE SCAN region WHERE r_name = 'EUROPE' 25 25 TABLE SCAN nation
	Estimate Actual Operator ∞ 2 SORT all_nations.o_year ASC NULLS FIRST ∞ 2 AGGREGATE SUM(CASE WHEN (all_nations.nation = 'FRANCE') THEN all_nations.volume ELSE 0.0000BD END), SUM(all_nations.volume) GROUP BY HASH all_nations.o_year ∞ 4 DISTRIBUTE HASH ON all_nations.o_year ∞ 4 AGGREGATE SUM(CASE WHEN (all_nations.nation = 'FRANCE') THEN all_nations.volume ELSE 0.0000BD END), SUM(all_nations.volume) GROUP BY HASH all_nations.o_year ∞ 2647 INNER JOIN HASH ON s_nationkey = n2.n_nationkey ∞ 25 │└DISTRIBUTE GATHER 6000000 25 │ TABLE SCAN nation ∞ 2647 INNER JOIN HASH ON n1.n_regionkey = r_regionkey ∞ 1 │└DISTRIBUTE GATHER 200000 1 │ TABLE SCAN region WHERE r_name = 'EUROPE' ∞ 12905 INNER JOIN HASH ON c_nationkey = n1.n_nationkey ∞ 25 │└DISTRIBUTE GATHER 1500000 25 │ TABLE SCAN nation ∞ 12905 INNER JOIN HASH ON o_custkey = c_custkey ∞ 150000 │└DISTRIBUTE GATHER 10000 150000 │ TABLE SCAN customer ∞ 12905 INNER JOIN HASH ON l_suppkey = s_suppkey ∞ 10000 │└DISTRIBUTE GATHER 150000 10000 │ TABLE SCAN supplier ∞ 12905 INNER JOIN HASH ON l_orderkey = o_orderkey ∞ 457263 │└DISTRIBUTE HASH ON o_orderkey 25 457263 │ TABLE SCAN orders WHERE (o_orderdate >= DATE'1995-01-01') AND (o_orderdate <= DATE'1996-12-31') 1T 42017 INNER JOIN HASH ON l_partkey = p_partkey 1T 1403 │└DISTRIBUTE GATHER 25 1403 │ TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' 5 5993041 TABLE SCAN lineitem
	Estimate Actual Operator 2 2 PROJECT Expr1024 / Expr1025 AS Expr1026 2 2 AGGREGATE SUM(CASE WHEN n_name as n_name = 'FRANCE' THEN l_extendedprice * (1. - l_discount) ELSE 0.0000 END) AS Expr1024, SUM(Expr1027) AS Expr1025 GROUP BY SORT Expr1022 703 2647 SORT Expr1022 703 2647 INNER JOIN HASH ON n1.n_regionkey = r_regionkey 1 1 │└TABLE SCAN region WHERE r_name = 'EUROPE' 351 2647 INNER JOIN HASH ON s_nationkey = n2.n_nationkey 25 25 │└TABLE SCAN nation AS n2 351 2647 INNER JOIN HASH ON c_nationkey = n1.n_nationkey 2 5 │└TABLE SCAN nation AS n1 WHERE BLOOM(n_regionkey as n_regionkey) 351 2647 INNER JOIN HASH ON s_suppkey = l_suppkey 351 2647 │└INNER JOIN HASH ON c_custkey = o_custkey 3512 12905 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 42100 42054 │ │ │└INNER JOIN HASH ON l_partkey = p_partkey 1403 1403 │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' 60012 42054 │ │ │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1027 60012 42054 │ │ │ TABLE SCAN lineitem WHERE BLOOM(l_partkey) 4546 12714 │ │ PROJECT datepart(year,o_orderdate) AS Expr1022 4546 12714 │ │ TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31' AND BLOOM(o_orderkey) 150 2432 │ TABLE SCAN customer WHERE BLOOM(c_custkey) AND BLOOM(c_nationkey) 100 1891 TABLE SCAN supplier WHERE BLOOM(s_suppkey)
	Estimate Actual Operator - 2 PROJECT expr_gid AS o_year, expr_28 AS mkt_share - 2 SORT expr_gid - 2 PROJECT expr_gid, sum / sum_27 AS expr_28 - 2 AGGREGATE SUM(sum_29) AS sum, SUM(sum_30) AS sum_27 GROUP BY HASH expr_gid - 4 DISTRIBUTE HASH ON expr_gid - 4 AGGREGATE SUM(expr_26) AS sum_29, SUM(expr_25) AS sum_30 GROUP BY PARTIAL expr_gid - 2647 PROJECT expr_25, CASE WHEN (n_name_19 = 'FRANCE') THEN expr_25 ELSE 0.0000 END AS expr_26, EXTRACT(YEAR FROM o_orderdate) AS expr_gid - 2647 PROJECT n_name_19, l_extendedprice * (1 - l_discount) AS expr_25, o_orderdate - 2647 INNER JOIN HASH ON s_nationkey = n_nationkey_18 25 25 │└DISTRIBUTE GATHER 25 25 │ PROJECT n_nationkey AS n_nationkey_18, n_name AS n_name_19 25 25 │ TABLE SCAN nation - 2647 INNER JOIN HASH ON n_regionkey = r_regionkey 5 1 │└DISTRIBUTE GATHER 5 1 │ FILTER r_name = 'EUROPE' 5 1 │ TABLE SCAN region - 2647 INNER JOIN HASH ON c_nationkey = n_nationkey 25 5 │└DISTRIBUTE GATHER 25 5 │ TABLE SCAN nation - 2647 INNER JOIN HASH ON o_custkey = c_custkey 150000 30197 │└DISTRIBUTE GATHER 150000 30197 │ TABLE SCAN customer - 2647 INNER JOIN HASH ON l_suppkey = s_suppkey 10000 10000 │└DISTRIBUTE GATHER 10000 10000 │ TABLE SCAN supplier - 2647 INNER JOIN HASH ON l_partkey = p_partkey 200000 1403 │└DISTRIBUTE GATHER 200000 1403 │ FILTER p_type = 'SMALL POLISHED NICKEL' 200000 1403 │ TABLE SCAN part - 2647 INNER JOIN HASH ON l_orderkey = o_orderkey 455301 92715 │└DISTRIBUTE HASH ON o_orderkey 455301 92715 │ FILTER o_orderdate BETWEEN '1995-01-01' AND '1996-12-31' 455301 92715 │ TABLE SCAN orders 6001215 2647 TABLE SCAN lineitem
	Estimate Actual Operator 2406 2 AGGREGATE SUM(CASE WHEN (n_name = 'FRANCE') THEN (l_extendedprice * ('1' - l_discount)) ELSE '0' END) / SUM(l_extendedprice * ('1' - l_discount)) GROUP BY SORT EXTRACT(year FROM o_orderdate) 3021 2646 SORT EXTRACT(year FROM o_orderdate) 1007 882 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation AS n2 3021 2646 INNER JOIN LOOP ON s_suppkey = l_suppkey 1007 882 │└INNER JOIN HASH ON o_custkey = c_custkey 90000 90591 │ │└INNER JOIN LOOP ON c_nationkey = n_nationkey 15 15 │ │ │└INNER JOIN LOOP ON n_regionkey = r_regionkey AND (n_regionkey = r_regionkey) 75 75 │ │ │ │└TABLE SEEK nation AS n1 1 1 │ │ │ TABLE SCAN region AS region WHERE r_name = 'EUROPE' 90000 90591 │ │ TABLE SEEK customer AS customer 15105 12905 │ INNER JOIN LOOP ON o_orderkey = l_orderkey 49740 42054 │ │└INNER JOIN LOOP ON l_partkey = p_partkey 1656 1403 │ │ │└TABLE SCAN part AS part WHERE p_type = 'SMALL POLISHED NICKEL' 42090 42047 │ │ TABLE SEEK lineitem AS lineitem 42054 42054 │ TABLE SEEK orders AS orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate <= '1996-12-31') 2647 2647 TABLE SEEK supplier AS supplier