Planner — TPC-H — TPCH-Q15

/* TPC-H Q15 */
WITH revenue AS (SELECT l_suppkey                               AS supplier_no,
                        SUM(l_extendedprice * (1 - l_discount)) AS total_revenue
                 FROM tpch_sf1.lineitem
                 WHERE l_shipdate >= '1997-09-01'
                   AND l_shipdate < '1997-12-01'
                 GROUP BY l_suppkey)
SELECT s_suppkey,
       s_name,
       s_address,
       s_phone,
       total_revenue
FROM tpch_sf1.supplier
INNER JOIN revenue
    ON s_suppkey = supplier_no
WHERE total_revenue = (SELECT MAX(total_revenue)
                       FROM revenue)
ORDER BY s_suppkey

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 246,297 246K Rank		Estimation Error Est Err 20,000 20K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 226,297 226K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 236,297 236K Rank		Estimation Error Est Err 10,000 10K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 226,297 226K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 236,297 236K Rank		Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,000 10K Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 12,012,430 12M Rank		Estimation Error Est Err 10,001 10K Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 20,000 20K Rank	Estimation Error Est Err 641,582 642K Rank	Estimation Error Est Err 178,988 179K Rank
Estimation Error Est Err 246,298 246K Rank		Estimation Error Est Err 10,001 10K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 236,298 236K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 236,299 236K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 285,753 286K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 226,299 226K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 901 901 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 226,297 226K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 462,594 463K Rank		Estimation Error Est Err 20,000 20K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 10,001 10K Rank	Estimation Error Est Err 502,598 503K Rank	Estimation Error Est Err 50,005 50K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 1 1 SORT s_suppkey 1 1 INNER JOIN HASH ON s_suppkey = supplier_no 1 1 │└INNER JOIN LOOP ON total_revenue25 = MAX(total_revenue) 1 1 │ │└AGGREGATE max 205119 226297 │ │ TABLE SCAN lineitem WHERE l_shipdate BETWEEN 2450693 AND 2450783 9887 10000 │ TABLE SCAN < temp > 10000 10000 TABLE SCAN supplier`
	Estimate Actual Operator - 1 PROJECT s_suppkey, s_name, s_address, s_phone, total_revenue - 1 SORT s_suppkey - 1 PROJECT s_suppkey, s_name, s_address, s_phone, total_revenue - 1 PROJECT s_suppkey, s_name, s_address, s_phone, total_revenue - 1 INNER JOIN HASH ON PROJECTION_357.s_suppkey = PROJECTION_351.l_suppkey - 1 │└PROJECT l_suppkey AS supplier_no, a5 AS total_revenue - 1 │ FILTER (a5 = '1743799.3741') AS a3 - 10000 │ AGGREGATE SUM(a9) AS a5 GROUP BY HASH l_suppkey - 226297 │ PROJECT l_suppkey, l_extendedprice * (1 - l_discount) AS a9 - 226297 │ PROJECT l_suppkey, l_extendedprice, l_discount - 226297 │ TABLE SCAN lineitem WHERE (l_shipdate >= '1997-09-01') AND (l_shipdate < '1997-12-01') - 10000 PROJECT s_suppkey, s_name, s_address, s_phone - 10000 PROJECT s_suppkey, s_name, s_address, s_phone - 10000 TABLE SCAN supplier
	Estimate Actual Operator 3 1 SEQUENCE 8565 10000 ├─MATERIALISE AS m_revenue 8565 10000 │ AGGREGATE sum_no_overflow #1 GROUP BY HASH #0 1200243 226297 │ PROJECT l_suppkey, l_extendedprice * (1.00 - l_discount) 1200243 226297 │ TABLE SCAN lineitem WHERE l_shipdate >= '1997-09-01' AND l_shipdate < '1997-12-01' 3 1 └─SORT s_suppkey 3 1 INNER JOIN HASH ON s_suppkey = supplier_no 3 1 │└INNER JOIN HASH ON total_revenue = SUBQUERY 1 1 │ │└PROJECT CASE WHEN (#1 > 1) THEN ("error"('More than one row returned by a subquery used as an expression - scalar subqueries can only return a single row. Use "SET scalar_subquery_error_on_multiple_rows=false" to revert to previous behavior of returning a random row.')) ELSE #0 END - 1 │ │ AGGREGATE "first" #0, count_star() - 1 │ │ AGGREGATE MAX(#0) 8565 10000 │ │ PROJECT total_revenue 8565 10000 │ │ SCAN MATERIALISED revenue 8565 10000 │ SCAN MATERIALISED revenue 10000 1 TABLE SCAN supplier
	Estimate Actual Operator 1 1 SORT s_suppkey 1 1 SORT s_suppkey 1 1 INNER JOIN HASH ON MAX(revenue.total_revenue) = total_revenue 1200243 10000 │└INNER JOIN HASH ON s_suppkey = supplier_no 1200243 10000 │ │└PROJECT supplier_no, total_revenue 1200243 10000 │ │ AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey 1200243 89489 │ │ DISTRIBUTE HASH ON l_suppkey 1200243 89489 │ │ AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey 1200243 226297 │ │ FILTER (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01') 6001215 6001215 │ │ TABLE SCAN lineitem WHERE ((l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')) AND (((l_suppkey >= 1) AND (l_suppkey <= 10000)) AND TRUE) 10000 10000 │ TABLE SCAN supplier 1 1 AGGREGATE MAX(total_revenue) 1200243 10 DISTRIBUTE GATHER 1200243 10 AGGREGATE MAX(total_revenue) 1200243 10000 PROJECT total_revenue 1200243 10000 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey 1200243 89489 DISTRIBUTE HASH ON l_suppkey 1200243 89489 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_suppkey 1200243 226297 FILTER (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01') 6001215 6001215 TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01')
	Estimate Actual Operator 1 1 SEQUENCE 60B 2 ├─DISTRIBUTE HASH ON s_suppkey 60B 2 │ INNER JOIN HASH ON s_suppkey = revenue.supplier_no 60B 10000 │ │└DISTRIBUTE GATHER 10000 10000 │ │ TABLE SCAN supplier 6000000 2 │ FILTER 10000 20000 │ AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_suppkey 10000 19997 │ DISTRIBUTE HASH ON l_suppkey 10000 19997 │ AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_suppkey 6000000 226297 │ TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1997-09-01') AND (l_shipdate < DATE'1997-12-01') 1 1 └─AGGREGATE MAX(revenue.total_revenue) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MAX(revenue.total_revenue) 1 10000 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_suppkey - ∞ DISTRIBUTE HASH
	Estimate Actual Operator 1 1 INNER JOIN LOOP ON ColStoreLoc1000 = ColStoreLoc1000 1 1 │└TABLE SCAN supplier 1 1 INNER JOIN LOOP ON l_suppkey = s_suppkey 1 1 │└TABLE SEEK supplier 1 1 SORT l_suppkey 1 1 LIMIT 0 1 900 SORT Expr1006 8998 10000 FILTER Expr1006 IS NOT NULL 9998 10000 AGGREGATE SUM(Expr1021) AS Expr1006 GROUP BY HASH l_suppkey 226751 226297 PROJECT l_extendedprice * (1. - l_discount) AS Expr1021 226751 226297 TABLE SCAN lineitem WHERE l_shipdate >= '1997-09-01' AND l_shipdate < '1997-12-01'
	Estimate Actual Operator - 1 PROJECT l_suppkey AS s_suppkey, s_name, s_address, s_phone, sum AS total_revenue - 1 SORT l_suppkey - 1 INNER JOIN HASH ON sum = max 1 1 │└DISTRIBUTE HASH ON max 1 1 │ AGGREGATE MAX(max_25) AS max - 16 │ DISTRIBUTE GATHER - 16 │ AGGREGATE MAX(sum_23) AS max_25 - 10000 │ AGGREGATE SUM(sum_26) AS sum_23 GROUP BY HASH l_suppkey_5 216281 19994 │ DISTRIBUTE HASH ON l_suppkey_5 216281 19994 │ AGGREGATE SUM(expr_22) AS sum_26 GROUP BY PARTIAL l_suppkey_5 216281 226297 │ PROJECT l_extendedprice * (1 - l_discount) AS expr_22, l_suppkey AS l_suppkey_5 216281 226297 │ FILTER (l_shipdate >= '1997-09-01') AND (l_shipdate < '1997-12-01') 216281 226297 │ TABLE SCAN lineitem - 10000 INNER JOIN HASH ON l_suppkey = s_suppkey 10000 10000 │└DISTRIBUTE GATHER 10000 10000 │ TABLE SCAN supplier - 10000 AGGREGATE SUM(sum_24) AS sum GROUP BY HASH l_suppkey 216281 19994 DISTRIBUTE HASH ON l_suppkey 216281 19994 AGGREGATE SUM(expr) AS sum_24 GROUP BY PARTIAL l_suppkey 216281 226297 PROJECT l_extendedprice * (1 - l_discount) AS expr, l_suppkey 216281 226297 FILTER (l_shipdate >= '1997-09-01') AND (l_shipdate < '1997-12-01') 216281 226297 TABLE SCAN lineitem
	Estimate Actual Operator 50 1 SEQUENCE 10009 10000 ├─AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) GROUP BY HASH l_suppkey 50045 49456 │ AGGREGATE PARTIAL SUM(l_extendedprice * ('1' - l_discount)) GROUP BY HASH l_suppkey 288435 226297 │ TABLE SCAN lineitem AS lineitem WHERE (l_shipdate >= '1997-09-01') AND (l_shipdate < '1997-12-01') 1 1 ├─AGGREGATE MAX(total_revenue) 10009 10000 │ SCAN MATERIALISED revenue 50 1 └─SORT s_suppkey 50 1 INNER JOIN LOOP ON s_suppkey = supplier_no 50 1 │└SCAN MATERIALISED revenue 1 1 TABLE SEEK supplier AS supplier