Planner — TPC-H — TPCH-Q12

/* TPC-H Q12 */
SELECT l_shipmode,
       SUM(CASE
               WHEN o_orderpriority = '1-URGENT'
                   OR o_orderpriority = '2-HIGH'
                   THEN 1
               ELSE 0
           END) AS high_line_count,
       SUM(CASE
               WHEN o_orderpriority <> '1-URGENT'
                   AND o_orderpriority <> '2-HIGH'
                   THEN 1
               ELSE 0
           END) AS low_line_count
FROM tpch_sf1.lineitem
INNER JOIN tpch_sf1.orders
    ON o_orderkey = l_orderkey
WHERE l_shipmode IN ('AIR', 'TRUCK')
  AND l_commitdate < l_receiptdate
  AND l_shipdate < l_commitdate
  AND l_receiptdate >= '1994-01-01'
  AND l_receiptdate < '1995-01-01'
GROUP BY l_shipmode
ORDER BY l_shipmode

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,530,837 1.5M Rank		Estimation Error Est Err 1,500,000 1.5M Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,530,837 1.5M Rank		Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 1,500,000 1.5M Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,530,837 1.5M Rank		Estimation Error Est Err 1,500,000 1.5M Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,861 31K Rank	Estimation Error Est Err 1,530,861 1.5M Rank
Estimation Error Est Err 7,501,215 7.5M Rank		Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 1,500,000 1.5M Rank	Estimation Error Est Err 30,857 31K Rank	Estimation Error Est Err 1,530,857 1.5M Rank
Estimation Error Est Err 920,591 921K Rank		Estimation Error Est Err 34,818 35K Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 61,674 62K Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,847 31K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 59,799 60K Rank		Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,853 31K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 59,799 60K Rank		Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 30,837 31K Rank	Estimation Error Est Err 30,839 31K Rank	Estimation Error Est Err 30,839 31K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 2 2 SORT l_shipmode32 2 2 AGGREGATE sum, sum GROUP BY HASH l_shipmode 23887 30837 INNER JOIN HASH ON o_orderkey = l_orderkey 23442 30837 │└TABLE SCAN lineitem WHERE l_receiptdate BETWEEN 2449354 AND 2449718 AND l_shipmode IN(AIR,TRUCK) AND l_shipdate < l_commitdate AND l_commitdate < l_receiptdate 1500000 1500000 TABLE SCAN orders`
	Estimate Actual Operator - 2 PROJECT l_shipmode, a1 AS high_line_count, a2 AS low_line_count - 2 SORT l_shipmode - 2 PROJECT l_shipmode, a1, a2 - 2 AGGREGATE countIf a13 AS a1, countIf a16 AS a2 GROUP BY HASH l_shipmode - 30837 PROJECT l_shipmode, (o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') AS a13, (o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH') AS a16 - 30837 PROJECT l_shipmode, o_orderpriority - 30837 INNER JOIN HASH ON PROJECTION_317.l_orderkey = PROJECTION_314.o_orderkey - 1500000 │└PROJECT o_orderkey, o_orderpriority - 1500000 │ PROJECT o_orderpriority, o_orderkey - 1500000 │ TABLE SCAN orders - 30837 PROJECT l_orderkey, l_shipmode - 30837 PROJECT l_orderkey, l_shipmode - 30837 TABLE SCAN lineitem WHERE (l_commitdate < '1995-01-01') AND (l_shipdate < '1995-01-01') AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01') AND l_shipmode IN('AIR','TRUCK') AND (l_shipdate < l_commitdate) AND (l_commitdate < l_receiptdate)
	Estimate Actual Operator 7 2 SORT l_shipmode 7 2 AGGREGATE SUM(#1), SUM(#2) GROUP BY HASH #0 242051 30837 PROJECT l_shipmode, CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END, CASE WHEN ((o_orderpriority != '1-URGENT') AND (o_orderpriority != '2-HIGH')) THEN 1 ELSE 0 END 242051 30837 INNER JOIN HASH ON o_orderkey = l_orderkey 240048 30837 │└FILTER (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND ((l_shipmode = 'AIR') OR (l_shipmode = 'TRUCK')) 1200243 885773 │ TABLE SCAN lineitem WHERE l_shipmode IN('AIR','TRUCK') AND l_commitdate < '1995-01-01' AND l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01' AND l_shipdate < '1995-01-01' 1500000 34818 TABLE SCAN orders
	Estimate Actual Operator 1200243 2 SORT l_shipmode 1200243 2 SORT l_shipmode 1200243 2 PROJECT l_shipmode, high_line_count, low_line_count 1200243 2 AGGREGATE SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY HASH l_shipmode 1200243 20 DISTRIBUTE HASH ON l_shipmode 1200243 20 AGGREGATE SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY HASH l_shipmode 1200243 30837 INNER JOIN HASH ON l_orderkey = o_orderkey 1500000 1500000 │└DISTRIBUTE HASH ON o_orderkey 1500000 1500000 │ TABLE SCAN orders WHERE CASE MOD(HASH_REPARTITION o_orderkey,10) WHEN 0 THEN (((o_orderkey >= 833) AND (o_orderkey <= 5999072)) AND TRUE) WHEN 1 THEN (((o_orderkey >= 1316) AND (o_orderkey <= 5999394)) AND TRUE) WHEN 2 THEN (((o_orderkey >= 3073) AND (o_orderkey <= 5999202)) AND TRUE) WHEN 3 THEN (((o_orderkey >= 1031) AND (o_orderkey <= 5994951)) AND TRUE) WHEN 4 THEN (((o_orderkey >= 1985) AND (o_orderkey <= 5999493)) AND TRUE) WHEN 5 THEN (((o_orderkey >= 66) AND (o... 1200243 30837 DISTRIBUTE HASH ON l_orderkey 1200243 30837 FILTER (((((l_shipmode = 'AIR') OR (l_shipmode = 'TRUCK')) AND (l_receiptdate > l_commitdate)) AND (l_shipdate < l_commitdate)) AND (l_receiptdate >= DATE'1994-01-01')) AND (l_receiptdate < DATE'1995-01-01') 6001215 6001215 TABLE SCAN lineitem WHERE (((((l_shipmode = 'AIR') OR (l_shipmode = 'TRUCK')) AND (l_receiptdate > l_commitdate)) AND (l_shipdate < l_commitdate)) AND (l_receiptdate >= DATE'1994-01-01')) AND (l_receiptdate < DATE'1995-01-01')
	Estimate Actual Operator 9T 2 SORT l_shipmode ASC NULLS FIRST 9T 2 AGGREGATE SUM(CASE WHEN (o_orderpriority IN('1-URGENT','2-HIGH')) THEN 1 ELSE 0 END), SUM(CASE WHEN ( NOT (o_orderpriority IN('1-URGENT','2-HIGH'))) THEN 1 ELSE 0 END) GROUP BY HASH l_shipmode 9T 24 DISTRIBUTE HASH ON l_shipmode 9T 24 AGGREGATE SUM(CASE WHEN (o_orderpriority IN('1-URGENT','2-HIGH')) THEN 1 ELSE 0 END), SUM(CASE WHEN ( NOT (o_orderpriority IN('1-URGENT','2-HIGH'))) THEN 1 ELSE 0 END) GROUP BY HASH l_shipmode 9T 30837 INNER JOIN HASH ON l_orderkey = o_orderkey 9T 30837 │└DISTRIBUTE HASH ON l_orderkey 1500000 30837 │ TABLE SCAN lineitem WHERE (l_shipmode IN('AIR','TRUCK')) AND (l_receiptdate >= DATE'1994-01-01') AND (l_receiptdate < DATE'1995-01-01') AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) 9T 1500000 DISTRIBUTE HASH ON o_orderkey 6000000 1500000 TABLE SCAN orders
	Estimate Actual Operator 2 2 AGGREGATE SUM(partialagg1010) AS Expr1006, SUM(partialagg1011) AS Expr1007 GROUP BY SORT l_shipmode 10 16 SORT l_shipmode 10 16 AGGREGATE SUM(Expr1008) AS partialagg1010, SUM(Expr1009) AS partialagg1011 GROUP BY HASH l_shipmode 85456 30837 INNER JOIN HASH ON o_orderkey = l_orderkey 310472 30837 │└TABLE SCAN lineitem WHERE (l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01') AND (l_shipdate < l_commitdate AND (l_shipmode = 'AIR' OR l_shipmode = 'TRUCK') AND l_commitdate < l_receiptdate) 150000 28962 PROJECT CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END AS Expr1008, CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' THEN 1 ELSE 0 END AS Expr1009 150000 28962 TABLE SCAN orders WHERE BLOOM(o_orderkey)
	Estimate Actual Operator - 2 PROJECT l_shipmode, sum AS high_line_count, sum_6 AS low_line_count - 2 SORT l_shipmode - 2 AGGREGATE SUM(sum_7) AS sum, SUM(sum_8) AS sum_6 GROUP BY HASH l_shipmode - 2 DISTRIBUTE HASH ON l_shipmode - 2 AGGREGATE SUM(expr_4) AS sum_7, SUM(expr_5) AS sum_8 GROUP BY PARTIAL l_shipmode - 30837 PROJECT CAST((CASE WHEN (o_orderpriority IN('1-URGENT','2-HIGH')) THEN 1 ELSE 0 END) AS bigint) AS expr_4, CAST((CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) AS bigint) AS expr_5, l_shipmode - 30837 INNER JOIN HASH ON o_orderkey = l_orderkey 61601 30837 │└DISTRIBUTE HASH ON l_orderkey 61601 30837 │ FILTER (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01') AND (l_shipmode IN('AIR','TRUCK')) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) 61601 30837 │ TABLE SCAN lineitem 1500000 28962 TABLE SCAN orders
	Estimate Actual Operator 7 2 AGGREGATE SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY SORT l_shipmode 35 10 AGGREGATE PARTIAL SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), PARTIAL SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY SORT l_shipmode 36850 30837 SORT l_shipmode 36850 30837 INNER JOIN LOOP ON o_orderkey = l_orderkey 36850 30837 │└TABLE SCAN lineitem AS lineitem WHERE (l_shipmode IN('AIR','TRUCK')) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01') 30837 30837 TABLE SEEK orders AS orders