Planner — TPC-H — TPCH-Q09

/* TPC-H Q09 */
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 tpch_sf1.lineitem
      INNER JOIN tpch_sf1.orders
          ON l_orderkey = o_orderkey
      INNER JOIN tpch_sf1.partsupp
          ON l_suppkey = ps_suppkey
          AND l_partkey = ps_partkey
      INNER JOIN tpch_sf1.part
          ON l_partkey = p_partkey
      INNER JOIN tpch_sf1.supplier
          ON l_suppkey = s_suppkey
      INNER JOIN tpch_sf1.nation
          ON s_nationkey = n_nationkey
      WHERE p_name LIKE '%lace%') AS profit
GROUP BY nation,
         o_year
ORDER BY nation,
         o_year DESC

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 7,933,823 7.9M Rank		Estimation Error Est Err 8,250,039 8.3M Rank	Estimation Error Est Err 175 175 Rank	Estimation Error Est Err 1,035,072 1M Rank	Estimation Error Est Err 327,148 327K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 8,322,147 8.3M Rank		Estimation Error Est Err 8,638,363 8.6M Rank	Estimation Error Est Err 175 175 Rank	Estimation Error Est Err 12,666,726 13M Rank	Estimation Error Est Err 327,148 327K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 8,314,398 8.3M Rank		Estimation Error Est Err 7,302,058 7.3M Rank	Estimation Error Est Err 175 175 Rank	Estimation Error Est Err 3,281,444 3.3M Rank	Estimation Error Est Err 327,498 327K Rank	Estimation Error Est Err 2,321,282 2.3M Rank
Estimation Error Est Err 8,511,240 8.5M Rank		Estimation Error Est Err 12,983,874 13M Rank	Estimation Error Est Err 350 350 Rank	Estimation Error Est Err 18,657,941 19M Rank	Estimation Error Est Err 328,898 329K Rank	Estimation Error Est Err 14,515,087 15M Rank
Estimation Error Est Err 8,322,111 8.3M Rank		Estimation Error Est Err 7,465,475 7.5M Rank	Estimation Error Est Err 175 175 Rank	Estimation Error Est Err 3,437,148 3.4M Rank	Estimation Error Est Err 327,148 327K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,891,770 1.9M Rank	Estimation Error Est Err 370,838 371K Rank	Estimation Error Est Err 1,925,373 1.9M Rank	Estimation Error Est Err 175 175 Rank	Estimation Error Est Err 719,738 720K Rank	Estimation Error Est Err 82,487 82K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 685,198 685K Rank		Estimation Error Est Err 1,025,247 1M Rank	Estimation Error Est Err 327,323 327K Rank	Estimation Error Est Err 1,025,247 1M Rank	Estimation Error Est Err 327,323 327K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,891,708 1.9M Rank		Estimation Error Est Err 1,635,740 1.6M Rank	Estimation Error Est Err 175 175 Rank	Estimation Error Est Err 2,808,592 2.8M Rank	Estimation Error Est Err 327,498 327K Rank	Estimation Error Est Err 1,564,910 1.6M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 84 175 SORT n_name69, o_year 84 175 AGGREGATE sum GROUP BY HASH n_name, EXTRACT(YEAR FROM o_orderdate) 19225 327148 INNER JOIN HASH ON l_suppkey = s_suppkey 9909 10000 │└INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │ │└TABLE SCAN nation 10000 10000 │ TABLE SCAN supplier 250075 327148 INNER JOIN HASH ON l_orderkey = o_orderkey 229095 327148 │└INNER JOIN HASH ON l_suppkey = ps_suppkey AND l_partkey = ps_partkey 23424 43628 │ │└INNER JOIN HASH ON p_partkey = ps_partkey 8984 10907 │ │ │└TABLE SCAN part WHERE p_name LIKE '%lace%' 546373 411676 │ │ TABLE SCAN partsupp 4098631 6001215 │ TABLE SCAN lineitem 1500000 1500000 TABLE SCAN orders
	Estimate Actual Operator - 175 PROJECT nation, o_year, a1 AS sum_profit - 175 SORT nation, o_year - 175 PROJECT nation, o_year, a1 - 175 AGGREGATE SUM(amount) AS a1 GROUP BY HASH nation, o_year - 327148 PROJECT n_name AS nation, EXTRACT(YEAR FROM o_orderdate) AS o_year, (l_extendedprice * (1 - l_discount)) - (ps_supplycost * l_quantity) AS amount - 327148 PROJECT l_extendedprice, l_discount, l_quantity, o_orderdate, ps_supplycost, n_name - 327148 INNER JOIN HASH ON PROJECTION_259.o_orderkey = PROJECTION_232.l_orderkey - 327148 │└PROJECT l_orderkey, l_extendedprice, l_discount, l_quantity, ps_supplycost, n_name - 327148 │ PROJECT l_extendedprice, l_discount, l_quantity, l_orderkey, ps_supplycost, n_name - 327148 │ INNER JOIN HASH ON tuple(PROJECTION_256.ps_suppkey,PROJECTION_256.ps_partkey) = tuple(PROJECTION_235.l_suppkey,PROJECTION_235.l_partkey) - 327148 │ │└PROJECT l_suppkey, l_partkey, l_extendedprice, l_discount, l_quantity, l_orderkey, n_name - 327148 │ │ PROJECT l_extendedprice, l_discount, l_quantity, l_orderkey, l_suppkey, l_partkey, n_name - 327148 │ │ INNER JOIN HASH ON PROJECTION_253.p_partkey = PROJECTION_238.l_partkey - 6001215 │ │ │└PROJECT l_partkey, l_extendedprice, l_discount, l_quantity, l_orderkey, l_suppkey, n_name - 6001215 │ │ │ PROJECT l_extendedprice, l_discount, l_quantity, l_orderkey, l_suppkey, l_partkey, n_name - 6001215 │ │ │ INNER JOIN HASH ON PROJECTION_250.l_suppkey = PROJECTION_241.s_suppkey - 10000 │ │ │ │└PROJECT s_suppkey, n_name - 10000 │ │ │ │ PROJECT s_suppkey, n_name - 10000 │ │ │ │ INNER JOIN HASH ON PROJECTION_247.s_nationkey = PROJECTION_244.n_nationkey - 25 │ │ │ │ │└PROJECT n_nationkey, n_name - 25 │ │ │ │ │ PROJECT n_name, n_nationkey - 25 │ │ │ │ │ TABLE SCAN nation - 10000 │ │ │ │ PROJECT s_nationkey, s_suppkey - 10000 │ │ │ │ PROJECT s_suppkey, s_nationkey - 10000 │ │ │ │ TABLE SCAN supplier - 6001215 │ │ │ PROJECT l_suppkey, l_extendedprice, l_discount, l_quantity, l_orderkey, l_partkey - 6001215 │ │ │ PROJECT l_extendedprice, l_discount, l_quantity, l_orderkey, l_suppkey, l_partkey - 6001215 │ │ │ TABLE SCAN lineitem - 10907 │ │ PROJECT p_partkey - 10907 │ │ PROJECT p_partkey - 10907 │ │ TABLE SCAN part WHERE p_name LIKE '%lace%' - 800000 │ PROJECT ps_suppkey, ps_partkey, ps_supplycost - 800000 │ PROJECT ps_supplycost, ps_suppkey, ps_partkey - 800000 │ TABLE SCAN partsupp - 1500000 PROJECT o_orderkey, o_orderdate - 1500000 PROJECT o_orderdate, o_orderkey - 1500000 TABLE SCAN orders
	Estimate Actual Operator 4934640 175 SORT profit.nation, profit.o_year 4934640 175 AGGREGATE SUM(#2) GROUP BY HASH #0, #1 5118056 327148 PROJECT nation, o_year, amount 5118056 327148 PROJECT nation, o_year, amount 5118056 327148 INNER JOIN HASH ON l_suppkey = s_suppkey AND p_partkey = ps_partkey 769230 800000 │└INNER JOIN HASH ON ps_suppkey = s_suppkey 9615 10000 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │ │ │└TABLE SCAN nation 10000 10000 │ │ TABLE SCAN supplier 800000 800000 │ TABLE SCAN partsupp 1269050 327148 INNER JOIN HASH ON l_orderkey = o_orderkey 1500000 1500000 │└TABLE SCAN orders 1258545 327148 INNER JOIN HASH ON l_partkey = p_partkey 40000 10907 │└TABLE SCAN part WHERE contains(p_name,'lace') 6001215 6001179 TABLE SCAN lineitem
	Estimate Actual Operator 1200242 175 SORT nation, o_year 1200242 175 SORT nation, o_year 1200242 175 PROJECT nation, o_year, sum_profit 1200242 175 AGGREGATE SUM(amount) GROUP BY HASH nation, o_year 1200242 1750 DISTRIBUTE HASH ON nation, o_year 1200242 1750 AGGREGATE SUM(amount) GROUP BY HASH nation, o_year 1200242 327148 PROJECT nation, o_year, amount 1200242 327148 INNER JOIN HASH ON n_nationkey = s_nationkey 1200242 327148 │└PROJECT l_quantity, l_extendedprice, l_discount, o_orderdate, ps_supplycost, s_nationkey 1200242 327148 │ INNER JOIN HASH ON s_suppkey = l_suppkey 1200242 327148 │ │└INNER JOIN HASH ON p_partkey = l_partkey 6001212 6001215 │ │ │└PROJECT l_partkey, l_suppkey, l_quantity, l_extendedprice, l_discount, o_orderdate, ps_supplycost 6001212 6001215 │ │ │ INNER JOIN HASH ON ps_suppkey = l_suppkey AND ps_partkey = l_partkey 1500303 6001215 │ │ │ │└DISTRIBUTE HASH ON l_suppkey, l_partkey 1500303 6001215 │ │ │ │ PROJECT l_partkey, l_suppkey, l_quantity, l_extendedprice, l_discount, o_orderdate 1500303 6001215 │ │ │ │ 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 >= 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 >= 131) AND (l_orderkey <= 5999969)) AND TRUE) WHEN 5 THEN (((l_orderkey >= 6... 1500000 1500000 │ │ │ │ DISTRIBUTE HASH ON o_orderkey 1500000 1500000 │ │ │ │ TABLE SCAN orders 800000 800000 │ │ │ DISTRIBUTE HASH ON ps_suppkey, ps_partkey 800000 800000 │ │ │ TABLE SCAN partsupp 40000 10907 │ │ DISTRIBUTE GATHER 40000 10907 │ │ FILTER p_name LIKE '%lace%' 200000 200000 │ │ DISTRIBUTE ROUND ROBIN 200000 200000 │ │ TABLE SCAN part WHERE p_name LIKE '%lace%' 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) 25 25 TABLE SCAN nation
	Estimate Actual Operator ∞ 175 SORT profit.nation ASC NULLS FIRST, profit.o_year DESC NULLS LAST ∞ 175 AGGREGATE SUM(profit.amount) GROUP BY HASH profit.nation, profit.o_year ∞ 350 DISTRIBUTE HASH ON profit.nation, profit.o_year ∞ 350 AGGREGATE SUM(profit.amount) GROUP BY HASH profit.nation, profit.o_year ∞ 327148 INNER JOIN HASH ON s_nationkey = n_nationkey ∞ 25 │└DISTRIBUTE GATHER 6000000 25 │ TABLE SCAN nation ∞ 327148 INNER JOIN HASH ON l_suppkey = s_suppkey ∞ 10000 │└DISTRIBUTE GATHER 200000 10000 │ TABLE SCAN supplier ∞ 327148 INNER JOIN HASH ON l_suppkey = ps_suppkey ∞ 800000 │└DISTRIBUTE HASH ON ps_suppkey, ps_partkey 1500000 800000 │ TABLE SCAN partsupp ∞ 327148 INNER JOIN HASH ON l_orderkey = o_orderkey ∞ 1500000 │└DISTRIBUTE HASH ON o_orderkey 800000 1500000 │ TABLE SCAN orders 840B 327148 INNER JOIN HASH ON l_partkey = p_partkey 840B 10907 │└DISTRIBUTE GATHER 25 10907 │ TABLE SCAN part WHERE contains(p_name,'lace') 10000 5993466 TABLE SCAN lineitem
	Estimate Actual Operator 175 175 AGGREGATE SUM(partialagg1030) AS Expr1020 GROUP BY SORT n_name, Expr1018 175 175 SORT n_name, Expr1018 175 175 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 175 175 AGGREGATE SUM(Expr1021 - ps_supplycost * l_quantity) AS partialagg1030 GROUP BY SORT Expr1018, s_nationkey 11713 327148 SORT Expr1018, s_nationkey 11713 327148 INNER JOIN HASH ON ps_suppkey = s_suppkey 10000 10000 │└TABLE SCAN supplier 11713 327148 INNER JOIN HASH ON o_orderkey = l_orderkey 42556 327148 │└INNER JOIN HASH ON l_partkey = p_partkey AND l_suppkey = ps_suppkey 42675 43628 │ │└INNER JOIN HASH ON ps_partkey = p_partkey 10623 10907 │ │ │└TABLE SCAN part WHERE p_name LIKE '%lace%' 80000 43628 │ │ TABLE SCAN partsupp WHERE BLOOM(ps_partkey) 60012 327148 │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1021 60012 327148 │ TABLE SCAN lineitem WHERE BLOOM(l_partkey) AND BLOOM(l_suppkey) 15000 293490 PROJECT datepart(year,o_orderdate) AS Expr1018 15000 293490 TABLE SCAN orders WHERE BLOOM(o_orderkey)
	Estimate Actual Operator - 175 PROJECT n_name AS nation, expr_gid AS o_year, sum AS sum_profit - 175 SORT n_name, expr_gid - 175 AGGREGATE SUM(sum_16) AS sum GROUP BY HASH n_name, expr_gid - 350 DISTRIBUTE HASH ON n_name, expr_gid - 350 AGGREGATE SUM(expr_15) AS sum_16 GROUP BY PARTIAL n_name, expr_gid - 327148 PROJECT (l_extendedprice * (1 - l_discount)) - (ps_supplycost * l_quantity) AS expr_15, n_name, EXTRACT(YEAR FROM o_orderdate) AS expr_gid - 327148 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└DISTRIBUTE GATHER 25 25 │ TABLE SCAN nation - 327148 INNER JOIN HASH ON l_suppkey = s_suppkey 10000 10000 │└DISTRIBUTE GATHER 10000 10000 │ TABLE SCAN supplier - 327148 INNER JOIN HASH ON l_partkey = p_partkey 200000 10907 │└DISTRIBUTE GATHER 200000 10907 │ FILTER p_name LIKE '%lace%' 200000 10907 │ TABLE SCAN part - 327148 INNER JOIN HASH ON (l_suppkey = ps_suppkey) AND (l_partkey = ps_partkey) 800000 43628 │└DISTRIBUTE GATHER 800000 43628 │ TABLE SCAN partsupp - 327148 INNER JOIN HASH ON l_orderkey = o_orderkey 1500000 1500000 │└DISTRIBUTE HASH ON o_orderkey 1500000 1500000 │ TABLE SCAN orders 6001215 327148 TABLE SCAN lineitem
	Estimate Actual Operator 60150 175 SORT n_name, EXTRACT(year FROM o_orderdate) 60150 175 AGGREGATE SUM((l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)) GROUP BY HASH n_name, EXTRACT(year FROM o_orderdate) 240600 700 AGGREGATE PARTIAL SUM((l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)) GROUP BY HASH n_name, EXTRACT(year FROM o_orderdate) 156449 81787 INNER JOIN HASH ON o_orderkey = l_orderkey 808320 327148 │└INNER JOIN LOOP ON (l_partkey = ps_partkey) AND (l_suppkey = ps_suppkey) 26937 10907 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │ │ │└TABLE SCAN nation AS nation 26937 10907 │ │ INNER JOIN HASH ON ps_suppkey = s_suppkey 10000 10000 │ │ │└TABLE SCAN supplier AS supplier 107748 43628 │ │ INNER JOIN LOOP ON ps_partkey = p_partkey 26936 10907 │ │ │└TABLE SCAN part AS part WHERE p_name LIKE '%lace%' 43628 43628 │ │ TABLE SEEK partsupp AS partsupp 43628 327210 │ TABLE SEEK lineitem AS lineitem 1935484 1500000 TABLE SCAN orders AS orders