Planner — TPC-H — TPCH-Q05

/* TPC-H Q05 */
SELECT n_name,
       SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM tpch_sf1.lineitem
INNER JOIN tpch_sf1.orders
    ON l_orderkey = o_orderkey
INNER JOIN tpch_sf1.customer
    ON o_custkey = c_custkey
INNER JOIN tpch_sf1.supplier
    ON l_suppkey = s_suppkey
INNER JOIN tpch_sf1.nation
    ON s_nationkey = n_nationkey
INNER JOIN tpch_sf1.region
    ON n_regionkey = r_regionkey
WHERE r_name = 'EUROPE'
  AND o_orderdate >= '1995-01-01'
  AND o_orderdate < '1996-01-01'
  AND s_nationkey = c_nationkey
GROUP BY n_name
ORDER BY revenue 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 6,275,163 6.3M Rank		Estimation Error Est Err 6,449,546 6.4M Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 270,783 271K Rank	Estimation Error Est Err 7,403 7.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,389,878 6.4M Rank		Estimation Error Est Err 6,389,877 6.4M Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 2,567,411 2.6M Rank	Estimation Error Est Err 7,403 7.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,382,886 6.4M Rank		Estimation Error Est Err 7,895,241 7.9M Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 1,908,421 1.9M Rank	Estimation Error Est Err 7,413 7.4K Rank	Estimation Error Est Err 388,673 389K Rank
Estimation Error Est Err 7,661,245 7.7M Rank		Estimation Error Est Err 1,908,421 1.9M Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 7,902,233 7.9M Rank	Estimation Error Est Err 7,453 7.5K Rank	Estimation Error Est Err 7,293,835 7.3M Rank
Estimation Error Est Err 560,727 561K Rank		Estimation Error Est Err 735,110 735K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 270,783 271K Rank	Estimation Error Est Err 7,403 7.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 390,553 391K Rank	Estimation Error Est Err 230,527 231K Rank	Estimation Error Est Err 564,994 565K Rank	Estimation Error Est Err 7,408 7.4K Rank	Estimation Error Est Err 250,651 251K Rank	Estimation Error Est Err 7,418 7.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 426,436 426K Rank		Estimation Error Est Err 392,517 393K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 607,253 607K Rank	Estimation Error Est Err 7,403 7.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 114,940 115K Rank		Estimation Error Est Err 124,465 124K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 104,959 105K Rank	Estimation Error Est Err 7,413 7.4K Rank	Estimation Error Est Err 78,397 78K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 5 5 SORT SUM() 5 5 AGGREGATE sum GROUP BY HASH n_name 383368 7403 INNER JOIN HASH ON l_suppkey = s_suppkey AND s_nationkey = n_nationkey 10000 10000 │└TABLE SCAN supplier 200856 184384 INNER JOIN HASH ON l_orderkey = o_orderkey 45992 46197 │└INNER JOIN HASH ON o_custkey = c_custkey 29808 30197 │ │└INNER JOIN HASH ON n_nationkey = c_nationkey 5 5 │ │ │└INNER JOIN HASH ON n_regionkey = r_regionkey 1 1 │ │ │ │└TABLE SCAN region WHERE r_name = EUROPE 25 25 │ │ │ TABLE SCAN nation 150000 150000 │ │ TABLE SCAN customer 231445 113922 │ TABLE SCAN orders WHERE o_orderdate BETWEEN 2449719 AND 2450083 6001215 6001215 TABLE SCAN lineitem
	Estimate Actual Operator - 5 PROJECT n_name, a1 AS revenue - 5 SORT a1 - 5 PROJECT a1, n_name - 5 AGGREGATE SUM(a5) AS a1 GROUP BY HASH n_name - 7403 PROJECT n_name, l_extendedprice * (1 - l_discount) AS a5 - 7403 PROJECT l_extendedprice, l_discount, n_name - 7403 INNER JOIN HASH ON tuple(PROJECTION_130.c_custkey,PROJECTION_130.c_nationkey) = tuple(PROJECTION_103.o_custkey,PROJECTION_103.s_nationkey) - 181905 │└PROJECT o_custkey, s_nationkey, l_extendedprice, l_discount, n_name - 181905 │ PROJECT l_extendedprice, l_discount, o_custkey, s_nationkey, n_name - 181905 │ INNER JOIN HASH ON PROJECTION_127.o_orderkey = PROJECTION_106.l_orderkey - 1191757 │ │└PROJECT l_orderkey, l_extendedprice, l_discount, s_nationkey, n_name - 1191757 │ │ PROJECT l_extendedprice, l_discount, l_orderkey, s_nationkey, n_name - 1191757 │ │ INNER JOIN HASH ON PROJECTION_124.l_suppkey = PROJECTION_109.s_suppkey - 1987 │ │ │└PROJECT s_suppkey, s_nationkey, n_name - 1987 │ │ │ PROJECT s_suppkey, s_nationkey, n_name - 1987 │ │ │ INNER JOIN HASH ON PROJECTION_121.s_nationkey = PROJECTION_112.n_nationkey - 5 │ │ │ │└PROJECT n_nationkey, n_name - 5 │ │ │ │ PROJECT n_name, n_nationkey - 5 │ │ │ │ INNER JOIN HASH ON PROJECTION_118.n_regionkey = PROJECTION_115.r_regionkey - 1 │ │ │ │ │└PROJECT r_regionkey - 1 │ │ │ │ │ PROJECT r_regionkey - 1 │ │ │ │ │ TABLE SCAN region WHERE r_name = 'EUROPE' - 25 │ │ │ │ PROJECT n_regionkey, n_name, n_nationkey - 25 │ │ │ │ PROJECT n_name, n_nationkey, n_regionkey - 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_orderkey - 6001215 │ │ PROJECT l_extendedprice, l_discount, l_orderkey, l_suppkey - 6001215 │ │ TABLE SCAN lineitem - 228637 │ PROJECT o_orderkey, o_custkey - 228637 │ PROJECT o_orderkey, o_custkey - 228637 │ TABLE SCAN orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate < '1996-01-01') - 150000 PROJECT c_custkey, c_nationkey - 150000 PROJECT c_custkey, c_nationkey - 150000 TABLE SCAN customer
	Estimate Actual Operator 239027 5 SORT SUM(l_extendedprice * (1 - l_discount)) 239027 5 AGGREGATE SUM(#1) GROUP BY HASH #0 245638 7403 PROJECT n_name, l_extendedprice * (1.00 - l_discount) 245638 7403 INNER JOIN HASH ON l_suppkey = s_suppkey AND n_nationkey = s_nationkey 10000 10000 │└TABLE SCAN supplier 245638 184384 INNER JOIN HASH ON l_orderkey = o_orderkey 60888 46197 │└INNER JOIN HASH ON o_custkey = c_custkey 28846 30197 │ │└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 150000 125387 │ │ TABLE SCAN customer WHERE c_custkey <= 149999 300000 228626 │ TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate < '1996-01-01' 6001215 196708 TABLE SCAN lineitem
	Estimate Actual Operator 24004800 5 SORT revenue 24004800 5 SORT revenue 24004800 5 PROJECT n_name, revenue 24004800 5 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH n_name 24004800 50 DISTRIBUTE HASH ON n_name 24004800 50 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH n_name 24004800 7403 INNER JOIN HASH ON r_regionkey = n_regionkey 120M 36582 │└PROJECT l_extendedprice, l_discount, n_name, n_regionkey 120M 36582 │ INNER JOIN HASH ON n_nationkey = s_nationkey 120M 36582 │ │└PROJECT l_extendedprice, l_discount, s_nationkey 120M 36582 │ │ INNER JOIN HASH ON s_suppkey = l_suppkey AND s_nationkey = c_nationkey 300060 913927 │ │ │└PROJECT l_suppkey, l_extendedprice, l_discount, c_nationkey 300060 913927 │ │ │ INNER JOIN HASH ON c_custkey = o_custkey 300060 913927 │ │ │ │└DISTRIBUTE HASH ON o_custkey 300060 913927 │ │ │ │ PROJECT l_suppkey, l_extendedprice, l_discount, o_custkey 300060 913927 │ │ │ │ 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 >= 32) AND (l_orderkey <= 5999847)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 36) AND (l_orderkey <= 5999970)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 805) AND (l_orderkey <= 5999906)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 166) AND (l_orderkey <= 5999524)) AND TRUE) WHEN 4 THEN (((l_orderkey >= 326) AND (l_orderkey <= 5999392)) AND TRUE) WHEN 5 THEN (((l_orderkey >... 300000 228637 │ │ │ │ DISTRIBUTE HASH ON o_orderkey 300000 228637 │ │ │ │ FILTER (o_orderdate >= DATE'1995-01-01') AND (o_orderdate < DATE'1996-01-01') 1500000 1500000 │ │ │ │ TABLE SCAN orders WHERE ((o_orderdate >= DATE'1995-01-01') AND (o_orderdate < DATE'1996-01-01')) 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_custkey ... 150000 150000 │ │ │ DISTRIBUTE HASH ON c_custkey 150000 150000 │ │ │ TABLE SCAN customer 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 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'
	Estimate Actual Operator 6 5 SORT revenue DESC NULLS LAST 6 5 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH n_name 6 10 DISTRIBUTE HASH ON n_name 6 10 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH n_name ∞ 7403 INNER JOIN HASH ON n_regionkey = r_regionkey ∞ 1 │└DISTRIBUTE GATHER 6000000 1 │ TABLE SCAN region WHERE r_name = 'EUROPE' ∞ 36582 INNER JOIN HASH ON s_nationkey = n_nationkey ∞ 25 │└DISTRIBUTE GATHER 1500000 25 │ TABLE SCAN nation ∞ 36582 INNER JOIN HASH ON c_nationkey = s_nationkey ∞ 10000 │└DISTRIBUTE GATHER 150000 10000 │ TABLE SCAN supplier ∞ 913927 INNER JOIN HASH ON o_custkey = c_custkey ∞ 150000 │└DISTRIBUTE GATHER 10000 150000 │ TABLE SCAN customer 9T 913927 INNER JOIN HASH ON l_orderkey = o_orderkey 9T 228637 │└DISTRIBUTE HASH ON o_orderkey 5 228637 │ TABLE SCAN orders WHERE (o_orderdate >= DATE'1995-01-01') AND (o_orderdate < DATE'1996-01-01') 25 5994223 TABLE SCAN lineitem
	Estimate Actual Operator 5 5 SORT Expr1018 5 5 AGGREGATE SUM(Expr1019) AS Expr1018 GROUP BY HASH n_name 2004 7403 INNER JOIN HASH ON n_regionkey = r_regionkey 1 1 │└TABLE SCAN region WHERE r_name = 'EUROPE' 1002 7403 INNER JOIN HASH ON s_nationkey = n_nationkey 2 5 │└TABLE SCAN nation WHERE BLOOM(n_regionkey) 1002 7403 INNER JOIN HASH ON c_custkey = o_custkey AND s_nationkey = c_nationkey 24986 181905 │└INNER JOIN HASH ON l_suppkey = s_suppkey 1000 1987 │ │└TABLE SCAN supplier WHERE BLOOM(s_nationkey) 24986 181905 │ INNER JOIN HASH ON l_orderkey = o_orderkey 226895 228637 │ │└TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate < '1996-01-01' 60012 181905 │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1019 60012 181905 │ TABLE SCAN lineitem WHERE BLOOM(l_orderkey) AND BLOOM(l_suppkey) 1500 13901 TABLE SCAN customer WHERE BLOOM(c_custkey) AND BLOOM(c_nationkey)
	Estimate Actual Operator - 5 PROJECT n_name, sum AS revenue - 5 SORT sum - 5 AGGREGATE SUM(sum_15) AS sum GROUP BY HASH n_name - 10 DISTRIBUTE HASH ON n_name - 10 AGGREGATE SUM(expr) AS sum_15 GROUP BY PARTIAL n_name - 7403 PROJECT l_extendedprice * (1 - l_discount) AS expr, n_name - 7403 INNER JOIN HASH ON n_regionkey = r_regionkey 5 1 │└DISTRIBUTE GATHER 5 1 │ FILTER r_name = 'EUROPE' 5 1 │ TABLE SCAN region - 7403 INNER JOIN HASH ON c_nationkey = n_nationkey 25 5 │└DISTRIBUTE GATHER 25 5 │ TABLE SCAN nation - 7403 INNER JOIN HASH ON (l_suppkey = s_suppkey) AND (c_nationkey = s_nationkey) 10000 1987 │└DISTRIBUTE GATHER 10000 1987 │ TABLE SCAN supplier - 36553 INNER JOIN HASH ON o_custkey = c_custkey 150000 30197 │└DISTRIBUTE GATHER 150000 30197 │ TABLE SCAN customer - 36553 INNER JOIN HASH ON l_orderkey = o_orderkey 227651 46197 │└DISTRIBUTE HASH ON o_orderkey 227651 46197 │ FILTER (o_orderdate >= '1995-01-01') AND (o_orderdate < '1996-01-01') 227651 46197 │ TABLE SCAN orders 6001215 36553 TABLE SCAN lineitem
	Estimate Actual Operator 25 5 SORT SUM(l_extendedprice * ('1' - l_discount)) 25 5 AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) GROUP BY SORT n_name 75 15 AGGREGATE PARTIAL SUM(l_extendedprice * ('1' - l_discount)) GROUP BY SORT n_name 9087 7403 SORT n_name 3029 2467 INNER JOIN HASH ON (l_suppkey = s_suppkey) AND (n_nationkey = s_nationkey) 10000 10000 │└TABLE SCAN supplier AS supplier 227133 184383 INNER JOIN LOOP ON l_orderkey = o_orderkey 56766 46197 │└INNER JOIN LOOP ON o_custkey = c_custkey 12500 10065 │ │└INNER JOIN HASH ON c_nationkey = n_nationkey 5 5 │ │ │└INNER JOIN HASH ON n_regionkey = r_regionkey 1 1 │ │ │ │└TABLE SCAN region AS region WHERE r_name = 'EUROPE' 25 25 │ │ │ TABLE SCAN nation AS nation 187500 150000 │ │ TABLE SCAN customer AS customer 90591 46201 │ TABLE SEEK orders AS orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate < '1996-01-01') 600561 184326 TABLE SEEK lineitem AS lineitem