Planner — TPC-H — TPCH-Q13

/* TPC-H Q13 */
SELECT
  c_count,
  COUNT(*) AS custdist
FROM
  (
    SELECT
      c_custkey,
      COUNT(o_orderkey) AS c_count
    FROM
      tpch_sf1.customer
      LEFT OUTER JOIN tpch_sf1.orders ON c_custkey = o_custkey
      AND o_comment NOT LIKE ' % special % requests % '
    GROUP BY
      c_custkey
  ) AS c_orders
GROUP BY
  c_count
ORDER BY
  custdist DESC,
  c_count DESC
;

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 1,649,959 1.6M Rank	Estimation Error Est Err 1,499,959 1.5M Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 150,000 150K Rank	Estimation Error Est Err 150,000 150K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,650,000 1.7M Rank	Estimation Error Est Err 1,550,004 1.6M Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 1,550,004 1.6M Rank	Estimation Error Est Err 1,700,004 1.7M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,649,959 1.6M Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 1,700,423 1.7M Rank	Estimation Error Est Err 1,650,419 1.7M Rank
Estimation Error Est Err 1,650,000 1.7M Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 84 84 Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 1,700,347 1.7M Rank	Estimation Error Est Err 1,650,343 1.7M Rank
Estimation Error Est Err 1,649,959 1.6M Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 1,699,963 1.7M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,649,959 1.6M Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 1,699,963 1.7M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,649,959 1.6M Rank	Estimation Error Est Err 150,000 150K Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 150,000 150K Rank	Estimation Error Est Err 1,649,959 1.6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,649,959 1.6M Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 1,549,963 1.5M Rank	Estimation Error Est Err 1,850,565 1.9M Rank	Estimation Error Est Err 1,650,561 1.7M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 13 42 SORT count_star *, COUNT(o_orderkey) 27 13 42 AGGREGATE countstar GROUP BY HASH COUNT(o_orderkey) 150000 150000 RIGHT OUTER JOIN HASH ON c_custkey = o_custkey 150000 150000 │└TABLE SCAN customer 1500000 1499959 TABLE SCAN orders WHERE NOT (o_comment LIKE ' % special % requests % ')`
	Estimate Actual Operator - 42 PROJECT c_count, a1 AS custdist - 42 SORT a1, c_count - 42 PROJECT a1, c_count - 42 AGGREGATE COUNT() AS a1 GROUP BY HASH c_count - 150000 PROJECT a2 AS c_count - 150000 AGGREGATE COUNT(a5) AS a2 GROUP BY HASH c_custkey - 1550004 PROJECT c_custkey, o_orderkey - 1550004 PROJECT c_custkey, a5 - 1550004 RIGHT OUTER JOIN HASH ON PROJECTION_332.o_custkey = PROJECTION_329.c_custkey - 150000 │└PROJECT c_custkey - 150000 │ PROJECT c_custkey - 150000 │ TABLE SCAN customer - 1500000 PROJECT o_custkey, o_orderkey AS a5, o_orderkey - 1500000 PROJECT o_orderkey, o_custkey - 1500000 TABLE SCAN orders WHERE notLike(o_comment,' % special % requests % ')
	`Estimate Actual Operator 83106 42 SORT count_star(), c_orders.c_count 83106 42 AGGREGATE count_star() GROUP BY HASH #0 124908 150000 PROJECT c_count 124908 150000 PROJECT COUNT(o_orderkey) 124908 150000 AGGREGATE COUNT(#1) GROUP BY HASH #0 300000 1549963 PROJECT c_custkey, o_orderkey 300000 1549963 RIGHT OUTER JOIN HASH ON o_custkey = c_custkey 150000 150000 │└TABLE SCAN customer 300000 1499959 TABLE SCAN orders WHERE o_comment NOT LIKE ' % special % requests % '`
	Estimate Actual Operator 300000 42 SORT custdist, c_count 300000 42 SORT custdist, c_count 300000 42 PROJECT c_count, custdist 300000 42 AGGREGATE COUNT(1) GROUP BY HASH c_count 300000 384 DISTRIBUTE HASH ON c_count 300000 384 AGGREGATE COUNT(1) GROUP BY HASH c_count 300000 150000 PROJECT c_count 300000 150000 AGGREGATE COUNT(o_orderkey) GROUP BY HASH c_custkey 300000 1549963 LEFT OUTER JOIN HASH ON c_custkey = o_custkey 300000 1499959 │└DISTRIBUTE HASH ON o_custkey 300000 1499959 │ FILTER o_comment NOT LIKE ' % special % requests % ' 1500000 1500000 │ TABLE SCAN orders WHERE o_comment NOT LIKE ' % special % requests % ' 150000 150000 DISTRIBUTE HASH ON c_custkey 150000 150000 TABLE SCAN customer
	Estimate Actual Operator 147000 42 SORT custdist DESC NULLS LAST, c_orders.c_count DESC NULLS LAST 147000 42 AGGREGATE COUNT(1) GROUP BY HASH c_orders.c_count 147000 460 DISTRIBUTE HASH ON c_orders.c_count 147000 460 AGGREGATE COUNT(1) GROUP BY HASH c_orders.c_count 147000 150000 AGGREGATE COUNT(o_orderkey) GROUP BY HASH c_custkey 67B 1549963 LEFT OUTER JOIN HASH ON c_custkey = o_custkey 67B 1499959 │└DISTRIBUTE HASH ON o_custkey 1500000 1499959 │ TABLE SCAN orders WHERE NOT o_comment LIKE ' % special % requests % ' 67B 150000 DISTRIBUTE HASH ON c_custkey 150000 150000 TABLE SCAN customer
	Estimate Actual Operator 24 42 SORT Expr1007, Expr1006 24 42 PROJECT CONVERT_IMPLICIT(int,Expr1015,0) AS Expr1007 24 42 AGGREGATE COUNT() AS Expr1015 GROUP BY HASH Expr1006 150000 150000 PROJECT CASE WHEN Expr1006 IS NULL THEN 0 ELSE Expr1006 END AS Expr1006 150000 150000 LEFT OUTER JOIN HASH ON c_custkey = o_custkey 96548 99996 │└PROJECT CONVERT_IMPLICIT(int,Expr1014,0) AS Expr1006 96548 99996 │ AGGREGATE COUNT() AS Expr1014 GROUP BY HASH o_custkey 1500000 1499959 │ TABLE SCAN orders WHERE NOT o_comment LIKE ' % special % requests % ' 150000 150000 TABLE SCAN customer
	Estimate Actual Operator - 42 PROJECT count AS c_count, count_3 AS custdist - 42 SORT count_3, count - 42 AGGREGATE COUNT(count_4) AS count_3 GROUP BY HASH count - 602 DISTRIBUTE HASH ON count - 602 AGGREGATE COUNT(*) AS count_4 GROUP BY PARTIAL count - 150000 AGGREGATE COUNT(count_5) AS count GROUP BY HASH c_custkey - 150000 DISTRIBUTE HASH ON c_custkey - 150000 AGGREGATE COUNT(o_orderkey) AS count_5 GROUP BY PARTIAL c_custkey - 1549963 LEFT OUTER JOIN HASH ON c_custkey = o_custkey 1500000 1499959 │└DISTRIBUTE HASH ON o_custkey 1500000 1499959 │ FILTER NOT (o_comment LIKE ' % special % requests % ') 1500000 1499959 │ TABLE SCAN orders 150000 150000 TABLE SCAN customer
	`Estimate Actual Operator 200 42 SORT COUNT(), COUNT(o_orderkey) 200 42 AGGREGATE COUNT() GROUP BY HASH COUNT(o_orderkey) 150000 150000 AGGREGATE COUNT(o_orderkey) GROUP BY HASH c_custkey 1499850 1549963 RIGHT OUTER JOIN HASH ON o_custkey = c_custkey 150000 150000 │└TABLE SCAN customer AS customer 1499850 1499959 TABLE SCAN orders AS orders WHERE o_comment NOT LIKE ' % special % requests % '`