Planner — TPC-H — TPCH-Q14

/* TPC-H Q14 */
SELECT 100.00 * SUM(CASE
                        WHEN p_type LIKE 'PROMO%'
                            THEN l_extendedprice * (1 - l_discount)
                        ELSE 0
    END) / SUM(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM tpch_sf1.lineitem
INNER JOIN tpch_sf1.part
    ON l_partkey = p_partkey
WHERE l_shipdate >= '1996-02-01'
  AND l_shipdate < '1996-03-01'

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 271,636 272K Rank	Estimation Error Est Err 200,000 200K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 271,636 272K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 200,000 200K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 271,636 272K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 200,000 200K Rank	Estimation Error Est Err 71,638 72K Rank	Estimation Error Est Err 200,002 200K Rank
Estimation Error Est Err 6,201,215 6.2M Rank	Estimation Error Est Err 200,000 200K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 71,646 72K Rank	Estimation Error Est Err 271,646 272K Rank
Estimation Error Est Err 271,636 272K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 200,000 200K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 271,636 272K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 200,000 200K Rank	Estimation Error Est Err 14,332 14K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 131,846 132K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 131,846 132K Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 71,636 72K Rank	Estimation Error Est Err 71,637 72K Rank	Estimation Error Est Err 71,638 72K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 1 1 AGGREGATE sum, sum 50340 71636 INNER JOIN HASH ON l_partkey = p_partkey 52745 71636 │└TABLE SCAN lineitem WHERE l_shipdate BETWEEN 2450115 AND 2450143 200000 200000 TABLE SCAN part`
	Estimate Actual Operator - 1 PROJECT (100. * a4) / a5 AS promo_revenue - 1 AGGREGATE sumIf(a10,a12) AS a4, SUM(a10) AS a5 - 71636 PROJECT l_extendedprice * (1 - l_discount) AS a10, startsWith(p_type,'PROMO') AS a12 - 71636 PROJECT l_extendedprice, l_discount, p_type - 71636 INNER JOIN HASH ON PROJECTION_343.l_partkey = PROJECTION_340.p_partkey - 200000 │└PROJECT p_partkey, p_type - 200000 │ PROJECT p_type, p_partkey - 200000 │ TABLE SCAN part - 71636 PROJECT l_partkey, l_extendedprice, l_discount - 71636 PROJECT l_partkey, l_extendedprice, l_discount - 71636 TABLE SCAN lineitem WHERE (l_shipdate >= '1996-02-01') AND (l_shipdate < '1996-03-01')
	`Estimate Actual Operator 1 1 PROJECT promo_revenue - 1 AGGREGATE SUM(#0), SUM(#1) 1258545 71636 PROJECT CASE WHEN (prefix(p_type,'PROMO')) THEN (l_extendedprice * (1.00 - l_discount)) ELSE 0.0000 END, l_extendedprice * (1.00 - l_discount) 1258545 71636 INNER JOIN HASH ON l_partkey = p_partkey 200000 200000 │└TABLE SCAN part 1200243 71636 TABLE SCAN lineitem WHERE l_shipdate >= '1996-02-01' AND l_shipdate < '1996-03-01'`
	Estimate Actual Operator 1 1 PROJECT promo_revenue 1 1 AGGREGATE SUM(CASE WHEN p_type LIKE 'PROMO%' THEN __common_expr_1 ELSE 0.0000 END), SUM(__common_expr_1) 1200243 10 DISTRIBUTE GATHER 1200243 10 AGGREGATE SUM(CASE WHEN p_type LIKE 'PROMO%' THEN __common_expr_1 ELSE 0.0000 END), SUM(__common_expr_1) 1200243 71636 PROJECT __common_expr_1, p_type 1200243 71636 INNER JOIN HASH ON p_partkey = l_partkey 1200243 71636 │└DISTRIBUTE HASH ON l_partkey 1200243 71636 │ FILTER (l_shipdate >= DATE'1996-02-01') AND (l_shipdate < DATE'1996-03-01') 6001215 6001215 │ TABLE SCAN lineitem WHERE ((l_shipdate >= DATE'1996-02-01') AND (l_shipdate < DATE'1996-03-01')) AND CASE MOD(HASH_REPARTITION l_partkey,10) WHEN 0 THEN (((l_partkey >= 5) AND (l_partkey <= 200000)) AND TRUE) WHEN 1 THEN (((l_partkey >= 6) AND (l_partkey <= 199996)) AND TRUE) WHEN 2 THEN (((l_partkey >= 10) AND (l_partkey <= 199997)) AND TRUE) WHEN 3 THEN (((l_partkey >= 1) AND (l_partkey <= 199998)) AND TRUE) WHEN 4 THEN (((l_partkey >= 12) AND (l_partkey <= 199994)) ... 200000 200000 DISTRIBUTE HASH ON p_partkey 200000 200000 TABLE SCAN part
	Estimate Actual Operator 1 1 AGGREGATE SUM(CASE WHEN startswith(p_type,'PROMO') THEN (l_extendedprice * (1BD - l_discount)) ELSE 0.0000BD END), SUM(l_extendedprice * (1BD - l_discount)) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE SUM(CASE WHEN startswith(p_type,'PROMO') THEN (l_extendedprice * (1BD - l_discount)) ELSE 0.0000BD END), SUM(l_extendedprice * (1BD - l_discount)) 1T 71636 INNER JOIN HASH ON l_partkey = p_partkey 1T 200000 │└DISTRIBUTE GATHER 6000000 200000 │ TABLE SCAN part 200000 71636 TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1996-02-01') AND (l_shipdate < DATE'1996-03-01')
	Estimate Actual Operator 1 1 PROJECT 100.00 * Expr1006 / Expr1007 AS Expr1008 1 1 PROJECT CASE WHEN Expr1021 = 0 THEN NULL ELSE Expr1022 END AS Expr1006, CASE WHEN Expr1023 = 0 THEN NULL ELSE Expr1024 END AS Expr1007 1 1 AGGREGATE COUNT() AS Expr1021, SUM(Expr1020) AS Expr1022, COUNT(Expr1009) AS Expr1023, SUM(Expr1009) AS Expr1024 72219 71636 PROJECT CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice (1. - l_discount) ELSE 0.0000 END AS Expr1020 72219 71636 INNER JOIN HASH ON p_partkey = l_partkey 72219 71636 │└PROJECT l_extendedprice * (1. - l_discount) AS Expr1009 72219 71636 │ TABLE SCAN lineitem WHERE l_shipdate >= '1996-02-01' AND l_shipdate < '1996-03-01' 20000 60210 TABLE SCAN part WHERE BLOOM(p_partkey)
	Estimate Actual Operator 1 ∞ PROJECT expr_5 AS promo_revenue 1 1 PROJECT (sum * 100.00) / sum_4 AS expr_5 1 1 DISTRIBUTE ROUND ROBIN 1 1 AGGREGATE SUM(sum_6) AS sum, SUM(sum_7) AS sum_4 - 1 DISTRIBUTE GATHER - 1 AGGREGATE SUM(expr) AS sum_6, SUM(expr_3) AS sum_7 - 71636 PROJECT CASE WHEN (p_type LIKE 'PROMO%') THEN (l_extendedprice * (1 - l_discount)) ELSE 0.0000 END AS expr, l_extendedprice * (1 - l_discount) AS expr_3 - 71636 INNER JOIN HASH ON p_partkey = l_partkey 68925 71636 │└DISTRIBUTE HASH ON l_partkey 68925 71636 │ FILTER (l_shipdate >= '1996-02-01') AND (l_shipdate < '1996-03-01') 68925 71636 │ TABLE SCAN lineitem 200000 60210 TABLE SCAN part
	Estimate Actual Operator 1 1 AGGREGATE (100.00 * SUM(CASE WHEN (p_type LIKE 'PROMO%') THEN (l_extendedprice * ('1' - l_discount)) ELSE '0' END)) / SUM(l_extendedprice * ('1' - l_discount)) 5 5 AGGREGATE PARTIAL SUM(CASE WHEN (p_type LIKE 'PROMO%') THEN (l_extendedprice * ('1' - l_discount)) ELSE '0' END), PARTIAL SUM(l_extendedprice * ('1' - l_discount)) 18519 14327 INNER JOIN HASH ON l_partkey = p_partkey 416665 200000 │└TABLE SCAN part AS part 92595 71636 TABLE SCAN lineitem AS lineitem WHERE (l_shipdate >= '1996-02-01') AND (l_shipdate < '1996-03-01')