Planner — TPC-H — TPCH-Q17

/* TPC-H Q17 */
SELECT SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM tpch_sf1.lineitem
INNER JOIN tpch_sf1.part
    ON l_partkey = p_partkey
WHERE p_brand = 'Brand#13'
  AND p_container = 'MED CAN'
  AND l_quantity < (SELECT 0.2 * AVG(l_quantity) AS l_avg
                    FROM tpch_sf1.lineitem li
                    WHERE l_partkey = p_partkey)

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,007,442 6M Rank		Estimation Error Est Err 6,001,413 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 735 735 Rank	Estimation Error Est Err 6,566 6.6K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 12,002,628 12M Rank		Estimation Error Est Err 206,029 206K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,001,413 6M Rank	Estimation Error Est Err 6,001,752 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 11,994,446 12M Rank		Estimation Error Est Err 5,999,062 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 206,029 206K Rank	Estimation Error Est Err 6,401,748 6.4M Rank	Estimation Error Est Err 600,194 600K Rank
Estimation Error Est Err 12,202,430 12M Rank		Estimation Error Est Err 12,058 12K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,201,215 6.2M Rank	Estimation Error Est Err 7,899,459 7.9M Rank	Estimation Error Est Err 2,103,934 2.1M Rank
Estimation Error Est Err 11,996,132 12M Rank		Estimation Error Est Err 12,007,992 12M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 18,285 18K Rank	Estimation Error Est Err 6,566 6.6K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,191,386 6.2M Rank	Estimation Error Est Err 189,973 190K Rank	Estimation Error Est Err 6,001,215 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 537 537 Rank	Estimation Error Est Err 190,510 191K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 18,285 18K Rank		Estimation Error Est Err 6,566 6.6K Rank	Estimation Error Est Err 6,029 6K Rank	Estimation Error Est Err 12,058 12K Rank	Estimation Error Est Err 12,595 13K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,007,442 6M Rank		Estimation Error Est Err 18,087 18K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 212,058 212K Rank	Estimation Error Est Err 800,518 801K Rank	Estimation Error Est Err 600,191 600K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 1 1 AGGREGATE sum 2987 537 INNER JOIN HASH ON l_partkey35 = p_partkey AND l_quantity38 < 2 * AVG(l_quantity) 190 198 │└INNER JOIN HASH ON p_partkey = l_partkey32 195 198 │ │└TABLE SCAN part WHERE p_container = MED CAN AND p_brand = Brand#13 4894 198 │ AGGREGATE avg GROUP BY HASH l_partkey 5438 6029 │ TABLE SCAN lineitem 6001215 6001215 TABLE SCAN lineitem`
	Estimate Actual Operator - 1 PROJECT a2 / 7. AS avg_yearly - 1 AGGREGATE SUM(l_extendedprice) AS a2 - 537 PROJECT l_extendedprice - 537 FILTER l_quantity < a8 - 6029 PROJECT a8, l_extendedprice, l_quantity - 6029 INNER JOIN HASH ON PROJECTION_386.p_partkey = PROJECTION_383.l_partkey - 6001215 │└PROJECT l_partkey, l_extendedprice, l_quantity - 6001215 │ PROJECT l_extendedprice, l_partkey, l_quantity - 6001215 │ TABLE SCAN lineitem - 198 PROJECT p_partkey, a8 - 198 PROJECT p_partkey, a8 - 198 INNER JOIN HASH ON PROJECTION_392.l_partkey = PROJECTION_389.p_partkey - 198 │└PROJECT p_partkey AS p_partkey_right - 198 │ PROJECT p_partkey - 198 │ TABLE SCAN part WHERE (p_brand = 'Brand#13') AND (p_container = 'MED CAN') - 200000 PROJECT p_partkey, a8 - 200000 PROJECT 0.2 * a9 AS a8, l_partkey AS p_partkey - 200000 AGGREGATE AVG(l_quantity) AS a9 GROUP BY HASH l_partkey - 6001215 PROJECT l_quantity, l_partkey, l_partkey - 6001215 PROJECT l_quantity, l_partkey, l_partkey - 6001215 TABLE SCAN lineitem WHERE l_partkey = l_partkey
	Estimate Actual Operator 1 1 PROJECT avg_yearly - 1 AGGREGATE SUM(#0) 170092 537 PROJECT l_extendedprice 170092 537 FILTER CAST(l_quantity AS DOUBLE) < SUBQUERY 0 0 RIGHT SEMI INNER JOIN HASH ON p_partkey IS NOT DISTINCT FROM p_partkey 112547 198 │└AGGREGATE GROUP BY HASH #0 0 6029 │ RIGHT OUTER JOIN HASH ON p_partkey IS NOT DISTINCT FROM p_partkey - 0 │ │└SCAN EMPTY 1770568 198 │ PROJECT l_avg, p_partkey 1770568 198 │ AGGREGATE AVG(#1) GROUP BY HASH #0 3541136 6029 │ PROJECT p_partkey, l_quantity 3541136 6029 │ INNER JOIN HASH ON l_partkey = p_partkey 112547 0 │ │└SCAN MATERIALISED 6001215 5997583 │ TABLE SCAN lineitem 170092 6029 INNER JOIN HASH ON l_partkey = p_partkey 5406 198 │└TABLE SCAN part WHERE p_brand = 'Brand#13' AND p_container = 'MED CAN' 6001215 5998351 TABLE SCAN lineitem
	Estimate Actual Operator 1 1 PROJECT avg_yearly 1 1 AGGREGATE SUM(l_extendedprice) 36014581 10 DISTRIBUTE GATHER 36014581 10 AGGREGATE SUM(l_extendedprice) 36014581 537 INNER JOIN HASH ON p_partkey = l_partkey AND (CAST(l_quantity AS DECIMAL(30,15)) < l_avg) 6001215 200000 │└PROJECT l_avg, l_partkey 6001215 200000 │ AGGREGATE AVG(l_quantity) GROUP BY HASH l_partkey 6001215 1897697 │ DISTRIBUTE HASH ON l_partkey 6001215 1897697 │ AGGREGATE AVG(l_quantity) GROUP BY HASH l_partkey 6001215 6001215 │ TABLE SCAN lineitem WHERE CASE MOD(HASH_REPARTITION l_partkey,10) WHEN 0 THEN (((l_partkey >= 509) AND (l_partkey <= 175468)) AND l_partkey IN(21593,3559,14899,64424,126054,134525,80173,137549,134525,64424,42323,509,80173,104543,134525,104543,64424,86869,171687,169197,175468,115064,6637,69973,175468,175468,86869,509,107131,3559,115064,42323,134525,6637,107131,3559,69973,175468,107131,169197,64424,21593,115064,103316,175468,86869,69973,135656,97168,107131,80173,21593,50... 1200243 6029 DISTRIBUTE HASH ON p_partkey 1200243 6029 PROJECT l_quantity, l_extendedprice, p_partkey 1200243 6029 INNER JOIN HASH ON p_partkey = l_partkey 6001215 6001215 │└TABLE SCAN lineitem WHERE ((l_partkey >= 509) AND (l_partkey <= 199323)) AND TRUE 40000 198 DISTRIBUTE GATHER 40000 198 FILTER (p_brand = 'Brand#13') AND (p_container = 'MED CAN') 200000 200000 DISTRIBUTE ROUND ROBIN 200000 200000 TABLE SCAN part WHERE (p_brand = 'Brand#13') AND (p_container = 'MED CAN')
	Estimate Actual Operator 1 1 AGGREGATE SUM(l_extendedprice) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE SUM(l_extendedprice) ∞ 537 INNER JOIN HASH ON p_partkey = li.l_partkey ∞ 200000 │└DISTRIBUTE GATHER 200000 200000 │ FILTER 6000000 200000 │ AGGREGATE AVG(li.l_quantity) GROUP BY HASH li.l_partkey 6000000 399994 │ DISTRIBUTE HASH ON li.l_partkey 6000000 399994 │ AGGREGATE AVG(li.l_quantity) GROUP BY HASH li.l_partkey 6000000 6001215 │ TABLE SCAN lineitem 1T 6029 INNER JOIN HASH ON l_partkey = p_partkey 1T 198 │└DISTRIBUTE GATHER 200000 198 │ TABLE SCAN part WHERE (p_brand = 'Brand#13') AND (p_container = 'MED CAN') 6000000 5993033 TABLE SCAN lineitem
	Estimate Actual Operator 1 1 SEQUENCE 30532 6029 ├─MATERIALISE AS m5 30532 6029 │ GROUP BY SORT l_partkey 30532 6029 │ SORT l_partkey 30532 6029 │ INNER JOIN HASH ON l_partkey = p_partkey 1017 198 │ │└TABLE SCAN part WHERE p_container = 'MED CAN' AND p_brand = 'Brand#13' 60012 6029 │ TABLE SCAN lineitem WHERE BLOOM(l_partkey) 1 1 └─PROJECT Expr1010 / 7.0 AS Expr1011 1 1 PROJECT CASE WHEN Expr1056 = 0 THEN NULL ELSE Expr1057 END AS Expr1010 1 1 AGGREGATE COUNT() AS Expr1056, SUM(l_extendedprice) AS Expr1057 30 537 INNER JOIN LOOP ON l_quantity < 0.2 Expr1008 30 6029 │└SCAN MATERIALISED m5 1 198 PROJECT CASE WHEN Expr1054 = 0 THEN NULL ELSE Expr1055 / CONVERT_IMPLICIT(decimal(19,0),Expr1054,0) END AS Expr1008 1 198 AGGREGATE COUNT(*) AS Expr1054, SUM(l_quantity) AS Expr1055 30 6029 SCAN MATERIALISED m5
	Estimate Actual Operator 1 ∞ PROJECT expr_22 AS avg_yearly 1 1 PROJECT sum / 7.0 AS expr_22 1 1 DISTRIBUTE ROUND ROBIN 1 1 AGGREGATE SUM(sum_26) AS sum - 2 DISTRIBUTE GATHER - 2 AGGREGATE SUM(l_extendedprice) AS sum_26 - 537 FILTER CAST(l_quantity AS decimal(16,3)) < (COALESCE(avg,avg_25) * 0.2) - 6029 CROSS JOIN LOOP - 6029 │└LEFT OUTER JOIN HASH ON l_partkey = l_partkey_4 - 200000 │ │└DISTRIBUTE HASH ON l_partkey_4 - 200000 │ │ AGGREGATE AVG(avg_27) AS avg GROUP BY HASH l_partkey_4 6001215 399989 │ │ DISTRIBUTE HASH ON l_partkey_4 6001215 399989 │ │ AGGREGATE AVG(l_quantity_7) AS avg_27 GROUP BY PARTIAL l_partkey_4 6001215 399989 │ │ FILTER non_null 6001215 6001215 │ │ PROJECT l_quantity AS l_quantity_7, l_partkey AS l_partkey_4, true AS non_null 6001215 6001215 │ │ TABLE SCAN lineitem - 6029 │ INNER JOIN HASH ON l_partkey = p_partkey 200000 198 │ │└DISTRIBUTE GATHER 200000 198 │ │ FILTER (p_brand = 'Brand#13') AND (p_container = 'MED CAN') 200000 198 │ │ TABLE SCAN part 6001215 6029 │ TABLE SCAN lineitem 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE AVG(null_value) AS avg_25 1 1 FILTER null_24 1 1 PROJECT 0.0 AS null_value, false AS null_24 1 1 SELECT
	`Estimate Actual Operator 1 1 SEQUENCE 6029 6029 ├─AGGREGATE 0.2 * AVG(l_quantity) 180870 189973 │ TABLE SEEK lineitem AS li 1 1 └─AGGREGATE SUM(l_extendedprice) / 7.0 2001 537 INNER JOIN HASH ON l_partkey = p_partkey AND (l_quantity < (SubPlan 1)) 249 198 │└TABLE SCAN part AS part WHERE (p_brand = 'Brand#13') AND (p_container = 'MED CAN') 6001622 6001215 TABLE SCAN lineitem AS lineitem`