Planner — TPC-H — TPCH-Q18

/* TPC-H Q18 */
SELECT c_name,
       c_custkey,
       o_orderkey,
       o_orderdate,
       o_totalprice,
       SUM(l_quantity)
FROM tpch_sf1.lineitem
INNER JOIN tpch_sf1.orders
    ON l_orderkey = o_orderkey
INNER JOIN tpch_sf1.customer
    ON o_custkey = c_custkey
WHERE o_orderkey IN (SELECT l_orderkey
                     FROM tpch_sf1.lineitem
                     GROUP BY l_orderkey
                     HAVING SUM(l_quantity) > 314)
GROUP BY c_name,
         c_custkey,
         o_orderkey,
         o_orderdate,
         o_totalprice
ORDER BY o_totalprice DESC,
         o_orderdate

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 13,652,430 14M Rank		Estimation Error Est Err 7,651,215 7.7M Rank	Estimation Error Est Err 9 9 Rank	Estimation Error Est Err 81 81 Rank	Estimation Error Est Err 6,001,278 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 13,652,430 14M Rank		Estimation Error Est Err 12,002,430 12M Rank	Estimation Error Est Err 1,500,000 1.5M Rank	Estimation Error Est Err 12,002,430 12M Rank	Estimation Error Est Err 12,002,430 12M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 13,652,430 14M Rank		Estimation Error Est Err 1,500,036 1.5M Rank	Estimation Error Est Err 9 9 Rank	Estimation Error Est Err 1,650,019 1.7M Rank	Estimation Error Est Err 13,502,467 14M Rank	Estimation Error Est Err 4,650,029 4.7M Rank
Estimation Error Est Err 13,652,430 14M Rank		Estimation Error Est Err 12,002,493 12M Rank	Estimation Error Est Err 18 18 Rank	Estimation Error Est Err 18,003,645 18M Rank	Estimation Error Est Err 7,501,285 7.5M Rank	Estimation Error Est Err 21,153,652 21M Rank
Estimation Error Est Err 6,151,569 6.2M Rank		Estimation Error Est Err 364 364 Rank	Estimation Error Est Err 9 9 Rank	Estimation Error Est Err 150,071 150K Rank	Estimation Error Est Err 6,001,278 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 7,651,278 7.7M Rank	Estimation Error Est Err 63 63 Rank	Estimation Error Est Err 1,500,065 1.5M Rank	Estimation Error Est Err 18 18 Rank	Estimation Error Est Err 150,099 150K Rank	Estimation Error Est Err 6,001,278 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,001,377 6M Rank	Estimation Error Est Err 81 81 Rank	Estimation Error Est Err 162 162 Rank	Estimation Error Est Err 18 18 Rank	Estimation Error Est Err 162 162 Rank	Estimation Error Est Err 6,001,278 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,151,287 6.2M Rank		Estimation Error Est Err 189 189 Rank	Estimation Error Est Err 9 9 Rank	Estimation Error Est Err 150,126 150K Rank	Estimation Error Est Err 7,501,287 7.5M Rank	Estimation Error Est Err 1,650,027 1.7M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 29943 9 SORT o_totalprice68, o_orderdate67 29943 9 AGGREGATE sum GROUP BY HASH c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice 33270 63 INNER JOIN HASH ON o_orderkey = l_orderkey45 6892 9 │└INNER JOIN HASH ON o_custkey = c_custkey 6892 9 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey20 28189 9 │ │ │└FILTER 1472074 1500000 │ │ │ AGGREGATE sum GROUP BY HASH l_orderkey 6001215 6001215 │ │ │ TABLE SCAN lineitem 1500000 1500000 │ │ TABLE SCAN orders 150000 150000 │ TABLE SCAN customer 6001215 6001215 TABLE SCAN lineitem
	Estimate Actual Operator - 9 SEQUENCE - 1500000 ├─PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, a1 - 1500000 │ SORT o_totalprice, o_orderdate - 1500000 │ PROJECT o_totalprice, o_orderdate, a1, c_name, c_custkey, o_orderkey - 1500000 │ AGGREGATE SUM(l_quantity) AS a1 GROUP BY HASH c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice - 6001215 │ PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, l_quantity - 6001215 │ PROJECT l_quantity, o_orderkey, o_orderdate, o_totalprice, c_name, c_custkey - 6001215 │ INNER JOIN HASH ON PROJECTION_408.o_custkey = PROJECTION_405.c_custkey - 150000 │ │└PROJECT c_custkey, c_name - 150000 │ │ PROJECT c_name, c_custkey - 150000 │ │ TABLE SCAN customer - 6001215 │ PROJECT o_custkey, l_quantity, o_orderkey, o_orderdate, o_totalprice - 6001215 │ PROJECT l_quantity, o_orderkey, o_orderdate, o_totalprice, o_custkey - 6001215 │ INNER JOIN HASH ON PROJECTION_414.l_orderkey = PROJECTION_411.o_orderkey - 1500000 │ │└PROJECT o_orderkey, o_orderdate, o_totalprice, o_custkey - 1500000 │ │ PROJECT o_orderkey, o_orderdate, o_totalprice, o_custkey - 1500000 │ │ TABLE SCAN orders WHERE TRUE - 6001215 │ PROJECT l_orderkey, l_quantity - 6001215 │ PROJECT l_orderkey, l_quantity - 6001215 │ TABLE SCAN lineitem WHERE TRUE - 9 └─PROJECT l_orderkey - 9 FILTER (a9 > 314) AS a7 - 1500000 AGGREGATE SUM(l_quantity) AS a9 GROUP BY HASH l_orderkey - 6001215 PROJECT l_orderkey, l_quantity - 6001215 TABLE SCAN lineitem
	Estimate Actual Operator 1277320 9 SORT o_totalprice, o_orderdate 1277320 9 AGGREGATE SUM(#5) GROUP BY HASH #0, #1, #2, #3, #4 1277320 63 PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, l_quantity 1277320 63 INNER JOIN HASH ON l_orderkey = o_orderkey 316622 9 │└INNER JOIN HASH ON o_custkey = c_custkey 150000 149999 │ │└TABLE SCAN customer WHERE c_custkey <= 149999 300000 9 │ LEFT SEMI JOIN HASH ON o_orderkey = #0 95211 9 │ │└FILTER SUM(l_quantity) > 314.00 476056 1500000 │ │ AGGREGATE sum_no_overflow #1 GROUP BY HASH #0 6001215 6001215 │ │ PROJECT l_orderkey, l_quantity 6001215 6001215 │ │ TABLE SCAN lineitem 1500000 116 │ TABLE SCAN orders 6001215 239 TABLE SCAN lineitem
	Estimate Actual Operator 1500303 9 SORT o_totalprice, o_orderdate 1500303 9 SORT o_totalprice, o_orderdate 1500303 9 AGGREGATE SUM(l_quantity) GROUP BY HASH c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice 1500303 63 RIGHT SEMI INNER JOIN HASH ON l_orderkey = o_orderkey 1500303 6001215 │└DISTRIBUTE HASH ON o_orderkey 1500303 6001215 │ PROJECT l_quantity, o_orderkey, o_totalprice, o_orderdate, c_custkey, c_name 1500303 6001215 │ INNER JOIN HASH ON c_custkey = o_custkey 1500303 6001215 │ │└DISTRIBUTE HASH ON o_custkey 1500303 6001215 │ │ PROJECT l_quantity, o_orderkey, o_custkey, o_totalprice, o_orderdate 1500303 6001215 │ │ INNER JOIN HASH ON o_orderkey = l_orderkey 6001215 6001215 │ │ │└DISTRIBUTE HASH ON l_orderkey 6001215 6001215 │ │ │ TABLE SCAN lineitem WHERE CASE MOD(HASH_REPARTITION l_orderkey,10) WHEN 0 THEN (((l_orderkey >= 5) AND (l_orderkey <= 5999847)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 6) AND (l_orderkey <= 5999970)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 37) AND (l_orderkey <= 5999975)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 1) AND (l_orderkey <= 5999971)) AND TRUE) WHEN 4 THEN (((l_orderkey >= 131) AND (l_orderkey <= 5999969)) AND TRUE) WHEN 5 THEN (((l_orderkey >= 66) AND (l_... 1500000 1500000 │ │ DISTRIBUTE HASH ON o_orderkey 1500000 1500000 │ │ TABLE SCAN orders WHERE CASE MOD(HASH_REPARTITION o_custkey,10) WHEN 0 THEN (((o_custkey >= 5) AND (o_custkey <= 149992)) AND TRUE) WHEN 1 THEN (((o_custkey >= 6) AND (o_custkey <= 149997)) AND TRUE) WHEN 2 THEN (((o_custkey >= 10) AND (o_custkey <= 149995)) AND TRUE) WHEN 3 THEN (((o_custkey >= 1) AND (o_custkey <= 150000)) AND TRUE) WHEN 4 THEN (((o_custkey >= 12) AND (o_custkey <= 149998)) AND TRUE) WHEN 5 THEN (((o_custkey >= 8) AND (o_custkey <= 149988)) AND T... 150000 150000 │ DISTRIBUTE HASH ON c_custkey 150000 150000 │ TABLE SCAN customer 1200243 9 FILTER SUM(l_quantity) > 314.00 6001215 1500000 AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 6001215 1500007 DISTRIBUTE HASH ON l_orderkey 6001215 1500007 AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 6001215 6001215 TABLE SCAN lineitem
	Estimate Actual Operator ∞ 9 SORT o_totalprice DESC NULLS LAST, o_orderdate ASC NULLS FIRST ∞ 18 DISTRIBUTE HASH ON o_totalprice, o_orderdate ∞ 18 AGGREGATE SUM(l_quantity) GROUP BY HASH c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice ∞ 18 INNER JOIN HASH ON o_custkey = c_custkey ∞ 150000 │└DISTRIBUTE GATHER 6000000 150000 │ TABLE SCAN customer ∞ 18 AGGREGATE SUM(l_quantity) GROUP BY HASH o_custkey, o_orderkey, o_orderdate, o_totalprice 9T 18 INNER JOIN HASH ON l_orderkey = o_orderkey 9T 1500001 │└DISTRIBUTE HASH ON l_orderkey 9T 1500001 │ AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 1500000 6001215 │ TABLE SCAN lineitem 1500000 18 LEFT SEMI JOIN HASH ON o_orderkey = l_orderkey 1500000 9 │└DISTRIBUTE GATHER 6000000 9 │ FILTER 6000000 1500000 │ AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 6000000 1500001 │ DISTRIBUTE HASH ON l_orderkey 6000000 1500001 │ AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 150000 6001215 │ TABLE SCAN lineitem 1500000 1500000 DISTRIBUTE HASH ON o_orderkey 6000000 1500000 TABLE SCAN orders
	Estimate Actual Operator 1 9 SORT o_totalprice, o_orderdate 1 9 AGGREGATE SUM(l_quantity) AS Expr1013, c_custkey, o_orderdate, o_totalprice GROUP BY SORT o_orderkey, c_name 1 63 INNER JOIN LOOP ON ColStoreLoc1000 = ColStoreLoc1000 1 63 │└TABLE SCAN lineitem 1 63 INNER JOIN LOOP ON o_orderkey = l_orderkey 1 63 │└TABLE SEEK lineitem 1 9 INNER JOIN LOOP ON ColStoreLoc1006 = ColStoreLoc1006 1 9 │└TABLE SCAN customer 1 9 INNER JOIN LOOP ON o_custkey = c_custkey 1 9 │└TABLE SEEK customer 1 9 INNER JOIN LOOP ON ColStoreLoc1003 = ColStoreLoc1003 1 9 │└TABLE SCAN orders 1 9 INNER JOIN LOOP ON l_orderkey = o_orderkey 1 9 │└TABLE SEEK orders 1 9 SORT l_orderkey 1 9 FILTER Expr1012 > 314.00 5449630 1500000 AGGREGATE SUM(l_quantity) AS Expr1012 GROUP BY HASH l_orderkey 6001220 6001215 TABLE SCAN lineitem
	Estimate Actual Operator - 9 PROJECT c_name, o_custkey AS c_custkey, l_orderkey AS o_orderkey, o_orderdate, o_totalprice, sum_27 AS _col5 - 9 SORT o_totalprice, o_orderdate - 9 AGGREGATE SUM(sum_29) AS sum_27 GROUP BY HASH c_name, o_custkey, l_orderkey, o_orderdate, o_totalprice - 9 DISTRIBUTE HASH ON c_name, o_custkey, l_orderkey, o_orderdate, o_totalprice - 9 AGGREGATE SUM(l_quantity) AS sum_29 GROUP BY PARTIAL c_name, o_custkey, l_orderkey, o_orderdate, o_totalprice - 63 INNER JOIN HASH ON l_orderkey = l_orderkey_6 - 9 │└DISTRIBUTE HASH ON l_orderkey_6 - 9 │ FILTER sum > 314.00 - 1500000 │ AGGREGATE SUM(sum_30) AS sum GROUP BY HASH l_orderkey_6 6001215 1500000 │ DISTRIBUTE HASH ON l_orderkey_6 6001215 1500000 │ AGGREGATE SUM(l_quantity_10) AS sum_30 GROUP BY PARTIAL l_orderkey_6 6001215 6001215 │ PROJECT l_orderkey AS l_orderkey_6, l_quantity AS l_quantity_10 6001215 6001215 │ TABLE SCAN lineitem - 63 INNER JOIN HASH ON o_custkey = c_custkey 150000 150000 │└DISTRIBUTE GATHER 150000 150000 │ TABLE SCAN customer - 63 INNER JOIN HASH ON l_orderkey = o_orderkey 1500000 9 │└DISTRIBUTE HASH ON o_orderkey 1500000 9 │ TABLE SCAN orders 6001215 63 TABLE SCAN lineitem
	Estimate Actual Operator 2000536 9 SORT o_totalprice, o_orderdate 2000536 9 AGGREGATE c_name, o_orderdate, o_totalprice, SUM(l_quantity) GROUP BY SORT c_custkey, o_orderkey 2000536 63 INNER JOIN LOOP ON l_orderkey = o_orderkey 195564 9 │└SORT c_custkey, o_orderkey 48891 2 │ INNER JOIN HASH ON o_custkey = c_custkey 250000 150000 │ │└TABLE SCAN customer AS customer 48891 2 │ INNER JOIN HASH ON o_orderkey = l_orderkey 606248 36 │ │└GROUP BY HASH l_orderkey 6001622 6001215 │ │ TABLE SCAN lineitem AS lineitem_1 1935484 1500000 │ TABLE SCAN orders AS orders 117 63 TABLE SEEK lineitem AS lineitem