Planner — TPC-H — TPCH-Q10

/* TPC-H Q10 */
SELECT c_custkey,
       c_name,
       SUM(l_extendedprice * (1 - l_discount)) AS revenue,
       c_acctbal,
       n_name,
       c_address,
       c_phone,
       c_comment
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.nation
    ON c_nationkey = n_nationkey
WHERE o_orderdate >= '1994-06-01'
  AND o_orderdate < '1994-09-01'
  AND l_returnflag = 'R'
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
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 1,120,878 1.1M Rank		Estimation Error Est Err 1,120,853 1.1M Rank	Estimation Error Est Err 38,182 38K Rank	Estimation Error Est Err 229,998 230K Rank	Estimation Error Est Err 115,050 115K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,686,369 1.7M Rank		Estimation Error Est Err 1,708,970 1.7M Rank	Estimation Error Est Err 38,182 38K Rank	Estimation Error Est Err 380,100 380K Rank	Estimation Error Est Err 115,050 115K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,684,454 1.7M Rank		Estimation Error Est Err 1,937,155 1.9M Rank	Estimation Error Est Err 38,182 38K Rank	Estimation Error Est Err 575,250 575K Rank	Estimation Error Est Err 230,100 230K Rank	Estimation Error Est Err 398,913 399K Rank
Estimation Error Est Err 7,651,240 7.7M Rank		Estimation Error Est Err 380,100 380K Rank	Estimation Error Est Err 76,364 76K Rank	Estimation Error Est Err 1,708,970 1.7M Rank	Estimation Error Est Err 115,050 115K Rank	Estimation Error Est Err 1,801,394 1.8M Rank
Estimation Error Est Err 220,628 221K Rank		Estimation Error Est Err 220,603 221K Rank	Estimation Error Est Err 38,182 38K Rank	Estimation Error Est Err 215,372 215K Rank	Estimation Error Est Err 115,050 115K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 322,447 322K Rank	Estimation Error Est Err 114,948 115K Rank	Estimation Error Est Err 258,862 259K Rank	Estimation Error Est Err 38,182 38K Rank	Estimation Error Est Err 293,812 294K Rank	Estimation Error Est Err 28,762 29K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 210,731 211K Rank		Estimation Error Est Err 345,150 345K Rank	Estimation Error Est Err 153,232 153K Rank	Estimation Error Est Err 345,150 345K Rank	Estimation Error Est Err 115,050 115K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 322,549 323K Rank		Estimation Error Est Err 345,150 345K Rank	Estimation Error Est Err 38,182 38K Rank	Estimation Error Est Err 380,100 380K Rank	Estimation Error Est Err 157,693 158K Rank	Estimation Error Est Err 250,142 250K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 50701 38182 SORT SUM() 50701 38182 AGGREGATE sum GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 56335 115050 INNER JOIN HASH ON l_orderkey = o_orderkey 50805 57474 │└INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │ │└TABLE SCAN nation 51269 57474 │ INNER JOIN HASH ON o_custkey = c_custkey 51269 57474 │ │└TABLE SCAN orders WHERE o_orderdate BETWEEN 2449505 AND 2449596 150000 150000 │ TABLE SCAN customer 1523745 913379 TABLE SCAN lineitem WHERE l_returnflag = R
	Estimate Actual Operator - 38182 PROJECT c_custkey, c_name, a1 AS revenue, c_acctbal, n_name, c_address, c_phone, c_comment - 38182 SORT a1 - 38182 PROJECT a1, c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 38182 AGGREGATE SUM(a5) AS a1 GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 115050 PROJECT c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment, l_extendedprice * (1 - l_discount) AS a5 - 115050 PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, n_name - 115050 INNER JOIN HASH ON PROJECTION_272.c_nationkey = PROJECTION_269.n_nationkey - 25 │└PROJECT n_nationkey, n_name - 25 │ PROJECT n_name, n_nationkey - 25 │ TABLE SCAN nation - 115050 PROJECT c_nationkey, l_extendedprice, l_discount, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment - 115050 PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, c_nationkey - 115050 INNER JOIN HASH ON PROJECTION_278.o_custkey = PROJECTION_275.c_custkey - 150000 │└PROJECT c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, c_nationkey - 150000 │ PROJECT c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, c_nationkey - 150000 │ TABLE SCAN customer - 115050 PROJECT o_custkey, l_extendedprice, l_discount - 115050 PROJECT l_extendedprice, l_discount, o_custkey - 115050 INNER JOIN HASH ON PROJECTION_284.l_orderkey = PROJECTION_281.o_orderkey - 57474 │└PROJECT o_orderkey, o_custkey - 57474 │ PROJECT o_orderkey, o_custkey - 57474 │ TABLE SCAN orders WHERE (o_orderdate >= '1994-06-01') AND (o_orderdate < '1994-09-01') - 1478870 PROJECT l_orderkey, l_extendedprice, l_discount - 1478870 PROJECT l_orderkey, l_extendedprice, l_discount - 1478870 TABLE SCAN lineitem WHERE l_returnflag = 'R'
	Estimate Actual Operator 409397 38182 SORT SUM(l_extendedprice * (1 - l_discount)) 409397 38182 PROJECT c_custkey, c_name, revenue, c_acctbal, n_name, c_address, c_phone, c_comment 409397 38182 AGGREGATE SUM(#7) GROUP BY HASH #0, #1, #2, #3, #4, #5, #6 409397 115050 PROJECT c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment, l_extendedprice * (1.00 - l_discount) 409397 115050 INNER JOIN HASH ON l_orderkey = o_orderkey 304444 57474 │└INNER JOIN HASH ON c_custkey = o_custkey 300000 57474 │ │└TABLE SCAN orders WHERE o_orderdate >= '1994-06-01' AND o_orderdate < '1994-09-01' 144230 42848 │ INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │ │└TABLE SCAN nation 150000 42848 │ TABLE SCAN customer WHERE c_custkey <= 149999 2000405 120281 TABLE SCAN lineitem WHERE l_returnflag = 'R'
	Estimate Actual Operator 300000 38182 SORT revenue 300000 38182 SORT revenue 300000 38182 PROJECT c_custkey, c_name, revenue, c_acctbal, n_name, c_address, c_phone, c_comment 300000 38182 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 300000 115050 PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_address, c_phone, c_acctbal, c_comment, n_name 300000 115050 INNER JOIN HASH ON n_nationkey = c_nationkey 300000 115050 │└PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_comment 300000 115050 │ INNER JOIN HASH ON c_custkey = o_custkey 300000 115050 │ │└DISTRIBUTE HASH ON o_custkey 300000 115050 │ │ PROJECT l_extendedprice, l_discount, o_custkey 300000 115050 │ │ INNER JOIN HASH ON o_orderkey = l_orderkey 1200243 1478870 │ │ │└DISTRIBUTE HASH ON l_orderkey 1200243 1478870 │ │ │ FILTER l_returnflag = 'R' 6001215 6001215 │ │ │ TABLE SCAN lineitem WHERE (l_returnflag = 'R') AND CASE MOD(HASH_REPARTITION l_orderkey,10) WHEN 0 THEN (((l_orderkey >= 5) AND (l_orderkey <= 5999588)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 64) AND (l_orderkey <= 5999394)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 69) AND (l_orderkey <= 5998789)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 161) AND (l_orderkey <= 5999812)) AND TRUE) WHEN 4 THEN (((l_orderkey >= 131) AND (l_orderkey <= 5999045)) AND TRUE) WHEN 5 THEN ... 300000 57474 │ │ DISTRIBUTE HASH ON o_orderkey 300000 57474 │ │ FILTER (o_orderdate >= DATE'1994-06-01') AND (o_orderdate < DATE'1994-09-01') 1500000 1500000 │ │ TABLE SCAN orders WHERE ((o_orderdate >= DATE'1994-06-01') AND (o_orderdate < DATE'1994-09-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 <= 14999... 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
	Estimate Actual Operator ∞ 38182 SORT revenue DESC NULLS LAST ∞ 76364 DISTRIBUTE HASH ON revenue ∞ 76364 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment ∞ 230100 INNER JOIN HASH ON c_nationkey = n_nationkey ∞ 25 │└DISTRIBUTE GATHER 6000000 25 │ TABLE SCAN nation ∞ 230100 INNER JOIN HASH ON o_custkey = c_custkey ∞ 115050 │└DISTRIBUTE HASH ON o_custkey 9T 115050 │ INNER JOIN HASH ON l_orderkey = o_orderkey 9T 57474 │ │└DISTRIBUTE HASH ON o_orderkey 25 57474 │ │ TABLE SCAN orders WHERE (o_orderdate >= DATE'1994-06-01') AND (o_orderdate < DATE'1994-09-01') 150000 1476955 │ TABLE SCAN lineitem WHERE l_returnflag = 'R' ∞ 150000 DISTRIBUTE HASH ON c_custkey 1500000 150000 TABLE SCAN customer
	Estimate Actual Operator 14568 38182 SORT Expr1012 14568 38182 AGGREGATE SUM(Expr1013) AS Expr1012, c_name, c_acctbal, c_phone, c_address, c_comment GROUP BY SORT n_name, o_custkey 15935 115050 SORT n_name, o_custkey 15935 115050 INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 15935 115050 INNER JOIN HASH ON c_custkey = o_custkey 15912 115050 │└INNER JOIN HASH ON l_orderkey = o_orderkey 58598 57474 │ │└TABLE SCAN orders WHERE o_orderdate >= '1994-06-01' AND o_orderdate < '1994-09-01' 14798 115050 │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1013 14798 115050 │ TABLE SCAN lineitem WHERE (BLOOM(l_orderkey)) AND (l_returnflag = 'R') 15000 38182 TABLE SCAN customer WHERE BLOOM(c_custkey)
	Estimate Actual Operator - 38182 PROJECT o_custkey AS c_custkey, c_name, sum AS revenue, c_acctbal, n_name, c_address, c_phone, c_comment - 38182 SORT sum - 38182 AGGREGATE SUM(sum_9) AS sum GROUP BY HASH o_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 42643 DISTRIBUTE HASH ON o_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 42643 AGGREGATE SUM(expr) AS sum_9 GROUP BY PARTIAL o_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 115050 PROJECT l_extendedprice * (1 - l_discount) AS expr, o_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 115050 INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │└DISTRIBUTE GATHER 25 25 │ TABLE SCAN nation - 115050 INNER JOIN HASH ON o_custkey = c_custkey 150000 150000 │└DISTRIBUTE GATHER 150000 150000 │ TABLE SCAN customer - 115050 INNER JOIN HASH ON l_orderkey = o_orderkey 57380 57474 │└DISTRIBUTE HASH ON o_orderkey 57380 57474 │ FILTER (o_orderdate >= '1994-06-01') AND (o_orderdate < '1994-09-01') 57380 57474 │ TABLE SCAN orders 5401094 115050 FILTER l_returnflag = 'R' 5401094 115050 TABLE SCAN lineitem
	Estimate Actual Operator 56768 38182 SORT SUM(l_extendedprice * ('1' - l_discount)) 56768 38182 AGGREGATE c_name, SUM(l_extendedprice * ('1' - l_discount)), c_acctbal, c_address, c_phone, c_comment GROUP BY HASH c_custkey, n_name 18312 28762 INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │└TABLE SCAN nation AS nation 18312 28762 INNER JOIN HASH ON o_custkey = c_custkey 250000 150000 │└TABLE SCAN customer AS customer 73248 115050 INNER JOIN LOOP ON l_orderkey = o_orderkey 75080 57474 │└TABLE SCAN orders AS orders WHERE (o_orderdate >= '1994-06-01') AND (o_orderdate < '1994-09-01') 172422 114948 TABLE SEEK lineitem AS lineitem WHERE l_returnflag = 'R'