Planner — TPC-H — TPCH-Q06

/* TPC-H Q06 */
SELECT SUM(l_extendedprice * l_discount) AS revenue
FROM tpch_sf1.lineitem
WHERE l_shipdate >= '1994-01-01'
  AND l_shipdate < '1995-01-01'
  AND l_discount BETWEEN 0.04 AND 0.06
  AND l_quantity < 24

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,116 114K Rank	Estimation Error Est Err 2 2 Rank
Estimation Error Est Err 6,001,215 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,124 114K Rank	Estimation Error Est Err 10 10 Rank
Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,119 114K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 114,114 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 114,116 114K Rank	Estimation Error Est Err 2 2 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 1 1 AGGREGATE sum 152374 114114 TABLE SCAN lineitem WHERE l_shipdate BETWEEN 2449354 AND 2449718 AND l_discount BETWEEN 4 AND 6 AND l_quantity <= 2399`
	`Estimate Actual Operator - 1 PROJECT a1 AS revenue - 1 AGGREGATE SUM(a3) AS a1 - 114114 PROJECT l_extendedprice * l_discount AS a3 - 114114 PROJECT l_extendedprice, l_discount - 114114 TABLE SCAN lineitem WHERE (l_shipdate >= '1994-01-01') AND (l_shipdate < '1995-01-01') AND (l_discount >= 0.04) AND (l_discount <= 0.06) AND (l_quantity < 24)`
	`Estimate Actual Operator - 1 AGGREGATE sum_no_overflow #0 1200243 114114 PROJECT l_extendedprice * l_discount 1200243 114114 TABLE SCAN lineitem WHERE l_shipdate >= '1994-01-01' AND l_shipdate < '1995-01-01' AND l_discount >= 0.04 AND l_discount <= 0.06 AND l_quantity < 24.00`
	Estimate Actual Operator 1 1 PROJECT revenue 1 1 AGGREGATE SUM(l_extendedprice * l_discount) 1200243 10 DISTRIBUTE GATHER 1200243 10 AGGREGATE SUM(l_extendedprice * l_discount) 1200243 114114 FILTER ((((l_shipdate >= DATE'1994-01-01') AND (l_shipdate < DATE'1995-01-01')) AND (l_discount >= 0.04)) AND (l_discount <= 0.06)) AND (l_quantity < 24.00) 6001215 6001215 TABLE SCAN lineitem WHERE ((((l_shipdate >= DATE'1994-01-01') AND (l_shipdate < DATE'1995-01-01')) AND (l_discount >= 0.04)) AND (l_discount <= 0.06)) AND (l_quantity < 24.00)
	`Estimate Actual Operator 1 1 AGGREGATE SUM(l_extendedprice * l_discount) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE SUM(l_extendedprice * l_discount) 6000000 114114 TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1994-01-01') AND (l_shipdate < DATE'1995-01-01') AND (l_discount >= 0.04BD) AND (l_discount <= 0.06BD) AND (l_quantity < 24.00BD)`
	`Estimate Actual Operator 1 1 PROJECT CASE WHEN Expr1012 = 0 THEN NULL ELSE Expr1013 END AS Expr1003 1 1 AGGREGATE COUNT(Expr1007) AS Expr1012, SUM(Expr1007) AS Expr1013 392567 114114 PROJECT l_extendedprice * l_discount AS Expr1007 392567 114114 TABLE SCAN lineitem WHERE l_shipdate >= CONVERT_IMPLICIT(date,@1,0) AND l_shipdate < CONVERT_IMPLICIT(date,@2,0) AND l_discount >= @3 AND l_discount <= @4 AND l_quantity < CONVERT_IMPLICIT(decimal(15,2),@5,0)`
	`Estimate Actual Operator 1 ∞ PROJECT sum AS revenue 1 1 AGGREGATE SUM(sum_0) AS sum 169543 2 DISTRIBUTE GATHER 169543 2 AGGREGATE SUM(expr) AS sum_0 169543 114114 PROJECT l_extendedprice * l_discount AS expr 169543 114114 FILTER (l_discount BETWEEN 0.04 AND 0.06) AND (l_quantity < 24.00) AND (l_shipdate >= '1994-01-01') AND (l_shipdate < '1995-01-01') 169543 114114 TABLE SCAN lineitem`
	`Estimate Actual Operator 1 1 AGGREGATE SUM(l_extendedprice * l_discount) 5 5 AGGREGATE PARTIAL SUM(l_extendedprice * l_discount) 141460 114114 TABLE SCAN lineitem AS lineitem WHERE (l_shipdate >= '1994-01-01') AND (l_shipdate < '1995-01-01') AND (l_discount >= 0.04) AND (l_discount <= 0.06) AND (l_quantity < '24')`