Planner — TPC-H — TPCH-Q03

/* TPC-H Q03 */
SELECT l_orderkey,
       SUM(l_extendedprice * (1 - l_discount)) AS revenue,
       o_orderdate,
       o_shippriority
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 c_mktsegment = 'MACHINERY'
  AND o_orderdate < '1995-03-15'
  AND l_shipdate > '1995-03-15'
GROUP BY l_orderkey,
         o_orderdate,
         o_shippriority
ORDER BY revenue DESC,
         o_orderdate
LIMIT 10

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,782,373 1.8M Rank		Estimation Error Est Err 1,752,424 1.8M Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 290,432 290K Rank	Estimation Error Est Err 29,857 30K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,999,030 4M Rank		Estimation Error Est Err 3,393,107 3.4M Rank	Estimation Error Est Err 11,281 11K Rank	Estimation Error Est Err 757,254 757K Rank	Estimation Error Est Err 29,857 30K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,997,498 4M Rank		Estimation Error Est Err 1,555,731 1.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 290,432 290K Rank	Estimation Error Est Err 3,253,057 3.3M Rank	Estimation Error Est Err 1,016,404 1M Rank
Estimation Error Est Err 7,651,215 7.7M Rank		Estimation Error Est Err 757,254 757K Rank	Estimation Error Est Err 82 82 Rank	Estimation Error Est Err 3,393,107 3.4M Rank	Estimation Error Est Err 29,857 30K Rank	Estimation Error Est Err 3,999,030 4M Rank
Estimation Error Est Err 809,889 810K Rank		Estimation Error Est Err 779,940 780K Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 290,432 290K Rank	Estimation Error Est Err 29,857 30K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 320,417 320K Rank	Estimation Error Est Err 290,468 290K Rank	Estimation Error Est Err 290,468 290K Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 290,431 290K Rank	Estimation Error Est Err 29,856 30K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 205,022 205K Rank		Estimation Error Est Err 22,562 23K Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 175,165 175K Rank	Estimation Error Est Err 29,857 30K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 205,022 205K Rank		Estimation Error Est Err 59,714 60K Rank	Estimation Error Est Err 260 260 Rank	Estimation Error Est Err 175,165 175K Rank	Estimation Error Est Err 41,138 41K Rank	Estimation Error Est Err 186,696 187K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 10 10 SORT SUM(), o_orderdate44 321411 11281 AGGREGATE sum GROUP BY HASH l_orderkey, o_orderdate, o_shippriority 357123 29857 INNER JOIN HASH ON l_orderkey = o_orderkey 151150 145216 │└INNER JOIN HASH ON o_custkey = c_custkey 30468 29949 │ │└TABLE SCAN customer WHERE c_mktsegment = MACHINERY 744140 359581 │ TABLE SCAN orders WHERE o_orderdate <= 2449791 3246751 1392843 TABLE SCAN lineitem WHERE l_shipdate >= 2449793`
	Estimate Actual Operator - 10 PROJECT l_orderkey, a1 AS revenue, o_orderdate, o_shippriority - 10 LIMIT 10 - 11281 SORT a1, o_orderdate - 11281 PROJECT a1, o_orderdate, l_orderkey, o_shippriority - 11281 AGGREGATE SUM(a5) AS a1 GROUP BY HASH l_orderkey, o_orderdate, o_shippriority - 29857 PROJECT l_orderkey, o_orderdate, o_shippriority, l_extendedprice * (1 - l_discount) AS a5 - 29857 PROJECT l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority - 29857 INNER JOIN HASH ON PROJECTION_75.o_custkey = PROJECTION_72.c_custkey - 29949 │└PROJECT c_custkey - 29949 │ PROJECT c_custkey - 29949 │ TABLE SCAN customer WHERE c_mktsegment = 'MACHINERY' - 151331 PROJECT o_custkey, l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority - 151331 PROJECT l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority, o_custkey - 151331 INNER JOIN HASH ON PROJECTION_81.l_orderkey = PROJECTION_78.o_orderkey - 727305 │└PROJECT o_orderkey, o_orderdate, o_shippriority, o_custkey - 727305 │ PROJECT o_orderkey, o_orderdate, o_shippriority, o_custkey - 727305 │ TABLE SCAN orders WHERE o_orderdate < '1995-03-15' - 3241776 PROJECT l_orderkey, l_extendedprice, l_discount - 3241776 PROJECT l_orderkey, l_extendedprice, l_discount - 3241776 TABLE SCAN lineitem WHERE l_shipdate > '1995-03-15'
	Estimate Actual Operator 10 10 LIMIT 10 10 ∞ SORT SUM(l_extendedprice * (1 - l_discount)), o_orderdate 63865 11281 PROJECT l_orderkey, revenue, o_orderdate, o_shippriority 63865 11281 AGGREGATE SUM(#3) GROUP BY HASH #0, #1, #2 63866 29857 PROJECT l_orderkey, o_orderdate, o_shippriority, l_extendedprice * (1.00 - l_discount) 63866 29857 INNER JOIN HASH ON l_orderkey = o_orderkey 79155 145216 │└INNER JOIN HASH ON o_custkey = c_custkey 37500 29949 │ │└FILTER c_custkey <= 149999 37500 29949 │ │ TABLE SCAN customer WHERE c_mktsegment = 'MACHINERY' 300000 727292 │ TABLE SCAN orders WHERE o_orderdate < '1995-03-15' 1200243 52648 TABLE SCAN lineitem WHERE l_shipdate > '1995-03-15'
	Estimate Actual Operator 60000 10 LIMIT 10 60000 10 SORT revenue, o_orderdate 60000 72 LIMIT 10 60000 72 SORT revenue, o_orderdate 60000 11281 PROJECT l_orderkey, revenue, o_orderdate, o_shippriority 60000 11281 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH l_orderkey, o_orderdate, o_shippriority 60000 29857 INNER JOIN HASH ON c_custkey = o_custkey 300000 151331 │└PROJECT l_orderkey, l_extendedprice, l_discount, o_custkey, o_orderdate, o_shippriority 300000 151331 │ INNER JOIN HASH ON o_orderkey = l_orderkey 1200243 3241776 │ │└DISTRIBUTE HASH ON l_orderkey 1200243 3241776 │ │ FILTER l_shipdate > DATE'1995-03-15' 6001215 6001215 │ │ TABLE SCAN lineitem WHERE (l_shipdate > DATE'1995-03-15') AND CASE MOD(HASH_REPARTITION l_orderkey,10) WHEN 0 THEN (((l_orderkey >= 5) AND (l_orderkey <= 5999813)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 6) AND (l_orderkey <= 5999843)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 37) AND (l_orderkey <= 5999975)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 99) AND (l_orderkey <= 5999942)) AND TRUE) WHEN 4 THEN (((l_orderkey >= 131) AND (l_orderkey <= 5999685)) AND TRUE) WHEN 5 ... 300000 727305 │ DISTRIBUTE HASH ON o_orderkey 300000 727305 │ FILTER o_orderdate < DATE'1995-03-15' 1500000 1500000 │ TABLE SCAN orders WHERE (o_orderdate < DATE'1995-03-15') AND (((o_custkey >= 4) AND (o_custkey <= 149997)) AND TRUE) 30000 29949 DISTRIBUTE GATHER 30000 29949 FILTER c_mktsegment = 'MACHINERY' 150000 150000 TABLE SCAN customer WHERE c_mktsegment = 'MACHINERY'
	Estimate Actual Operator ∞ 11281 DISTRIBUTE GATHER ∞ 11281 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_orderkey, o_orderdate, o_shippriority ∞ 11281 INNER JOIN HASH ON l_orderkey = o_orderkey ∞ 145216 │└DISTRIBUTE HASH ON o_orderkey 45B 145216 │ INNER JOIN HASH ON o_custkey = c_custkey 45B 29949 │ │└DISTRIBUTE GATHER 150000 29949 │ │ TABLE SCAN customer WHERE c_mktsegment = 'MACHINERY' 6000000 725773 │ TABLE SCAN orders WHERE o_orderdate < DATE'1995-03-15' ∞ 829958 DISTRIBUTE HASH ON l_orderkey ∞ 829958 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH l_orderkey 1500000 3241776 TABLE SCAN lineitem WHERE l_shipdate > DATE'1995-03-15'
	Estimate Actual Operator 10 10 SORT Expr1009, o_orderdate 129702 11281 INNER JOIN HASH ON o_custkey = c_custkey 29910 29949 │└TABLE SCAN customer WHERE c_mktsegment = 'MACHINERY' 41806 11281 INNER JOIN HASH ON l_orderkey = o_orderkey 72796 145216 │└TABLE SCAN orders WHERE o_orderdate < '1995-03-15' AND BLOOM(o_custkey) 31296 11281 AGGREGATE SUM(Expr1010) AS Expr1009 GROUP BY HASH l_orderkey 32396 29857 PROJECT l_extendedprice * (1. - l_discount) AS Expr1010 32396 29857 TABLE SCAN lineitem WHERE l_shipdate > '1995-03-15' AND BLOOM(l_orderkey)
	Estimate Actual Operator 10 ∞ PROJECT l_orderkey, sum AS revenue, o_orderdate, o_shippriority 10 10 LIMIT 10 10 10 SORT sum, o_orderdate - 10 DISTRIBUTE GATHER - 10 SORT sum, o_orderdate - 80 DISTRIBUTE GATHER - 80 SORT sum, o_orderdate - 160 DISTRIBUTE ROUND ROBIN - 160 SORT sum, o_orderdate - 11281 AGGREGATE SUM(sum_6) AS sum GROUP BY HASH l_orderkey, o_orderdate, o_shippriority - 11281 DISTRIBUTE HASH ON l_orderkey, o_orderdate, o_shippriority - 11281 AGGREGATE SUM(expr) AS sum_6 GROUP BY PARTIAL l_orderkey, o_orderdate, o_shippriority - 29857 PROJECT l_extendedprice * (1 - l_discount) AS expr, l_orderkey, o_orderdate, o_shippriority - 29857 INNER JOIN HASH ON o_custkey = c_custkey 150000 29949 │└DISTRIBUTE GATHER 150000 29949 │ FILTER c_mktsegment = 'MACHINERY' 150000 29949 │ TABLE SCAN customer - 29857 INNER JOIN HASH ON l_orderkey = o_orderkey 729106 145216 │└DISTRIBUTE HASH ON o_orderkey 729106 145216 │ FILTER o_orderdate < '1995-03-15' 729106 145216 │ TABLE SCAN orders 3225207 29857 FILTER l_shipdate > '1995-03-15' 3225207 29857 TABLE SCAN lineitem
	Estimate Actual Operator 10 10 LIMIT 10 312767 10 SORT SUM(l_extendedprice * ('1' - l_discount)), o_orderdate 312767 11281 AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) GROUP BY HASH l_orderkey, o_orderdate, o_shippriority 390960 29856 INNER JOIN LOOP ON l_orderkey = o_orderkey 180735 145215 │└INNER JOIN LOOP ON o_custkey = c_custkey 37131 29949 │ │└TABLE SCAN customer AS customer WHERE c_mktsegment = 'MACHINERY' 269541 145252 │ TABLE SEEK orders AS orders WHERE o_orderdate < '1995-03-15' 1016512 145216 TABLE SEEK lineitem AS lineitem WHERE l_shipdate > '1995-03-15'