Planner — TPC-H — TPCH-Q21

/* TPC-H Q21 */
SELECT s_name, COUNT(*) AS numwait
FROM tpch_sf1.lineitem l1
INNER JOIN tpch_sf1.orders
    ON l1.l_orderkey = o_orderkey
INNER JOIN tpch_sf1.supplier
    ON l1.l_suppkey = s_suppkey
INNER JOIN tpch_sf1.nation
    ON s_nationkey = n_nationkey
WHERE o_orderstatus = 'F'
  AND l1.l_receiptdate > l1.l_commitdate
  AND EXISTS (SELECT 1
              FROM tpch_sf1.lineitem l2
              WHERE l2.l_orderkey = l1.l_orderkey
                AND l2.l_suppkey <> l1.l_suppkey)
  AND NOT EXISTS (SELECT 1
                  FROM tpch_sf1.lineitem l3
                  WHERE l3.l_orderkey = l1.l_orderkey
                    AND l3.l_suppkey <> l1.l_suppkey
                    AND l3.l_receiptdate
                      > l3.l_commitdate)
  AND n_name = 'GERMANY'
GROUP BY s_name
ORDER BY numwait DESC,
         s_name

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 9,937,134 9.9M Rank		Estimation Error Est Err 9,937,133 9.9M Rank	Estimation Error Est Err 396 396 Rank	Estimation Error Est Err 322,016 322K Rank	Estimation Error Est Err 3,935 3.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 14,614,119 15M Rank		Estimation Error Est Err 14,146,834 14M Rank	Estimation Error Est Err 396 396 Rank	Estimation Error Est Err 4,421,148 4.4M Rank	Estimation Error Est Err 3,935 3.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 16,535,140 17M Rank		Estimation Error Est Err 7,850,762 7.9M Rank	Estimation Error Est Err 396 396 Rank	Estimation Error Est Err 12,834,611 13M Rank	Estimation Error Est Err 7,021 7K Rank	Estimation Error Est Err 22,539,441 23M Rank
Estimation Error Est Err 19,513,670 20M Rank		Estimation Error Est Err 3,874,319 3.9M Rank	Estimation Error Est Err 792 792 Rank	Estimation Error Est Err 17,245,629 17M Rank	Estimation Error Est Err 4,331 4.3K Rank	Estimation Error Est Err 4,666,580 4.7M Rank
Estimation Error Est Err 18,145,384 18M Rank		Estimation Error Est Err 14,095,388 14M Rank	Estimation Error Est Err 396 396 Rank	Estimation Error Est Err 1,175,886 1.2M Rank	Estimation Error Est Err 3,935 3.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,413,799 1.4M Rank	Estimation Error Est Err 684,385 684K Rank	Estimation Error Est Err 1,413,803 1.4M Rank	Estimation Error Est Err 4,331 4.3K Rank	Estimation Error Est Err 1,232,780 1.2M Rank	Estimation Error Est Err 3,935 3.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 804,667 805K Rank		Estimation Error Est Err 664,863 665K Rank	Estimation Error Est Err 6,689 6.7K Rank	Estimation Error Est Err 302,113 302K Rank	Estimation Error Est Err 6,293 6.3K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 10,675,115 11M Rank		Estimation Error Est Err 558,044 558K Rank	Estimation Error Est Err 396 396 Rank	Estimation Error Est Err 10,670,020 11M Rank	Estimation Error Est Err 263,708 264K Rank	Estimation Error Est Err 10,525,587 11M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 402 396 SORT count_star *, s_name87 402 396 AGGREGATE countstar GROUP BY HASH s_name 3298 3935 RIGHT SEMI INNER JOIN HASH ON l_orderkey68 = l_orderkey AND l_suppkey70 <> l_suppkey 3317 6582 │└INNER JOIN HASH ON l_orderkey = o_orderkey 6125 13450 │ │└RIGHT ANTI JOIN HASH ON l_orderkey37 = l_orderkey AND l_suppkey39 <> l_suppkey 150674 150794 │ │ │└INNER JOIN HASH ON l_suppkey = s_suppkey 402 396 │ │ │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │ │ │└TABLE SCAN nation WHERE n_name = GERMANY 10000 10000 │ │ │ │ TABLE SCAN supplier 3709735 2028506 │ │ │ TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate 3709735 1890830 │ │ TABLE SCAN lineitem WHERE l_receiptdate49 > l_commitdate48 744140 6582 │ TABLE SCAN orders WHERE o_orderstatus = F 6001215 6001215 TABLE SCAN lineitem
	Estimate Actual Operator - 396 SEQUENCE - 73048 ├─MATERIALISE AS m_SaveSubqueryResultToBuffer_82 - 73048 │ PROJECT l_orderkey, l_suppkey, l_receiptdate, l_commitdate, o_orderstatus, s_name, n_name - 73048 │ PROJECT l_orderkey, l_suppkey, l_receiptdate, l_commitdate, o_orderstatus, s_name, n_name - 73048 │ INNER JOIN HASH ON PROJECTION_492.s_suppkey = PROJECTION_483.l_suppkey - 1828911 │ │└PROJECT l_suppkey, l_orderkey, l_receiptdate, l_commitdate, o_orderstatus - 1828911 │ │ PROJECT l_orderkey, l_suppkey, l_receiptdate, l_commitdate, o_orderstatus - 1828911 │ │ INNER JOIN HASH ON PROJECTION_489.l_orderkey = PROJECTION_486.o_orderkey - 729413 │ │ │└PROJECT o_orderkey, o_orderstatus - 729413 │ │ │ PROJECT o_orderkey, o_orderstatus - 729413 │ │ │ TABLE SCAN orders WHERE o_orderstatus = 'F' - 3793296 │ │ PROJECT l_orderkey, l_suppkey, l_receiptdate, l_commitdate - 3793296 │ │ PROJECT l_orderkey, l_commitdate, l_receiptdate, l_suppkey - 3793296 │ │ TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate - 396 │ PROJECT s_suppkey, s_name, n_name - 396 │ PROJECT s_name, s_suppkey, n_name - 396 │ INNER JOIN HASH ON PROJECTION_498.s_nationkey = PROJECTION_495.n_nationkey - 1 │ │└PROJECT n_nationkey, n_name - 1 │ │ PROJECT n_nationkey, n_name - 1 │ │ TABLE SCAN nation WHERE n_name = 'GERMANY' - 10000 │ PROJECT s_nationkey, s_name, s_suppkey - 10000 │ PROJECT s_name, s_suppkey, s_nationkey - 10000 │ TABLE SCAN supplier - 70401 ├─MATERIALISE AS m_SaveSubqueryResultToBuffer_75 - 70401 │ PROJECT l_orderkey, l_suppkey, l_receiptdate, l_commitdate, o_orderstatus, s_name, n_name - 70401 │ PROJECT l_orderkey, l_suppkey, l_receiptdate, l_commitdate, o_orderstatus, s_name, n_name - 70401 │ RIGHT SEMI INNER JOIN HASH ON PROJECTION_476.l_orderkey = FILTER_501.l_orderkey - 73048 │ │└PROJECT l_orderkey AS l_orderkey_right, l_suppkey AS l_suppkey_right, l_receiptdate, l_commitdate, o_orderstatus, s_name, n_name - 73048 │ │ PROJECT l_orderkey, l_suppkey, n_name, l_commitdate, l_receiptdate, o_orderstatus, s_name - 73048 │ │ SCAN MATERIALISED SaveSubqueryResultToBuffer_82 - 291808 │ FILTER l_suppkey_left <> l_suppkey_right - 364891 │ PROJECT l_suppkey, l_orderkey, l_suppkey - 364891 │ INNER JOIN HASH ON PROJECTION_506.l_orderkey = PROJECTION_504.l_orderkey - 73048 │ │└PROJECT l_orderkey AS l_orderkey_right, l_suppkey AS l_suppkey_right - 73048 │ │ SCAN MATERIALISED SaveSubqueryResultToBuffer_82 - 6001215 │ PROJECT l_orderkey AS l_orderkey_left, l_suppkey AS l_suppkey_left - 6001215 │ PROJECT l_orderkey, l_suppkey - 6001215 │ TABLE SCAN lineitem - 396 └─PROJECT s_name, a1 AS numwait - 396 SORT a1, s_name - 396 PROJECT a1, s_name - 396 AGGREGATE COUNT() AS a1 GROUP BY HASH s_name - 3935 PROJECT s_name - 3935 PROJECT s_name - 3935 RIGHT ANTI JOIN HASH ON PROJECTION_469.l_orderkey = FILTER_509.l_orderkey - 70401 │└PROJECT l_orderkey_right, l_suppkey_right, s_name - 70401 │ PROJECT l_orderkey_right, l_suppkey_right, s_name - 70401 │ SCAN MATERIALISED SaveSubqueryResultToBuffer_75 - 184171 FILTER l_suppkey_left <> l_suppkey_right - 254590 PROJECT l_suppkey, l_orderkey, l_suppkey - 254590 INNER JOIN HASH ON PROJECTION_514.l_orderkey = PROJECTION_512.l_orderkey - 70401 │└PROJECT l_orderkey AS l_orderkey_right, l_suppkey AS l_suppkey_right - 70401 │ SCAN MATERIALISED SaveSubqueryResultToBuffer_75 - 3793296 PROJECT l_orderkey AS l_orderkey_left, l_suppkey AS l_suppkey_left, l_commitdate, l_receiptdate - 3793296 PROJECT l_commitdate, l_receiptdate, l_orderkey, l_suppkey - 3793296 TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate
	Estimate Actual Operator 776 396 SORT count_star(), s_name 776 396 AGGREGATE count_star() GROUP BY HASH #0 930 3935 PROJECT s_name 930 0 LEFT SEMI JOIN HASH ON l_suppkey IS NOT DISTINCT FROM l_suppkey AND l_orderkey IS NOT DISTINCT FROM l_orderkey 4653 70392 │└AGGREGATE GROUP BY HASH #1, #0 930 3935 │ LEFT ANTI JOIN HASH ON l_suppkey IS NOT DISTINCT FROM l_suppkey AND l_orderkey IS NOT DISTINCT FROM l_orderkey 5865 184147 │ │└PROJECT l_suppkey, l_orderkey 5865 184147 │ │ INNER JOIN HASH ON l_orderkey = l_orderkey AND l_suppkey != l_suppkey 4653 0 │ │ │└SCAN MATERIALISED 1200243 3793128 │ │ FILTER l_receiptdate > l_commitdate 6001215 6000945 │ │ TABLE SCAN lineitem 930 70401 │ SCAN MATERIALISED 4654 0 RIGHT SEMI INNER JOIN HASH ON l_suppkey IS NOT DISTINCT FROM l_suppkey AND l_orderkey IS NOT DISTINCT FROM l_orderkey 23273 73039 │└AGGREGATE GROUP BY HASH #2, #1 4654 70401 │ RIGHT SEMI INNER JOIN HASH ON l_suppkey IS NOT DISTINCT FROM l_suppkey AND l_orderkey IS NOT DISTINCT FROM l_orderkey - 0 │ │└SCAN EMPTY 146690 291776 │ PROJECT l_suppkey, l_orderkey 146690 291776 │ INNER JOIN HASH ON l_orderkey = l_orderkey AND l_suppkey != l_suppkey 23273 0 │ │└SCAN MATERIALISED 6001215 6000945 │ TABLE SCAN lineitem 23274 73048 INNER JOIN HASH ON o_orderkey = l_orderkey 46163 150794 │└INNER JOIN HASH ON l_suppkey = s_suppkey 384 396 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY' 10000 396 │ │ TABLE SCAN supplier 1200243 3791665 │ FILTER l_receiptdate > l_commitdate 6001215 5998667 │ TABLE SCAN lineitem 750000 74029 TABLE SCAN orders WHERE o_orderstatus = 'F'
	Estimate Actual Operator 60000 396 SORT numwait, s_name 60000 396 SORT numwait, s_name 60000 396 PROJECT s_name, numwait 60000 396 AGGREGATE COUNT(1) GROUP BY HASH s_name 60000 396 DISTRIBUTE HASH ON s_name 60000 396 AGGREGATE COUNT(1) GROUP BY HASH s_name 60000 3935 LEFT ANTI JOIN HASH ON l_orderkey = l_orderkey AND (l_suppkey <> l_suppkey) 1200243 3793296 │└FILTER l_receiptdate > l_commitdate 6001215 6001215 │ TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate 60000 70401 DISTRIBUTE GATHER 60000 70401 LEFT SEMI JOIN HASH ON l_orderkey = l_orderkey AND (l_suppkey <> l_suppkey) 6001215 6001215 │└TABLE SCAN lineitem 60000 73048 DISTRIBUTE GATHER 60000 73048 INNER JOIN HASH ON n_nationkey = s_nationkey 300000 1828911 │└PROJECT l_orderkey, l_suppkey, s_name, s_nationkey 300000 1828911 │ INNER JOIN HASH ON s_suppkey = l_suppkey 300000 1828911 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 1200243 3793296 │ │ │└DISTRIBUTE HASH ON l_orderkey 1200243 3793296 │ │ │ FILTER l_receiptdate > l_commitdate 6001215 6001215 │ │ │ TABLE SCAN lineitem WHERE ((l_receiptdate > l_commitdate) 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) WHE... 300000 729413 │ │ DISTRIBUTE HASH ON o_orderkey 300000 729413 │ │ FILTER o_orderstatus = 'F' 1500000 1500000 │ │ TABLE SCAN orders WHERE o_orderstatus = 'F' 10000 10000 │ TABLE SCAN supplier WHERE ((s_nationkey >= 7) AND (s_nationkey <= 7)) AND s_nationkey IN 7 5 1 DISTRIBUTE GATHER 5 1 FILTER n_name = 'GERMANY' 25 25 DISTRIBUTE ROUND ROBIN 25 25 TABLE SCAN nation WHERE n_name = 'GERMANY'
	Estimate Actual Operator 385 396 SORT numwait DESC NULLS LAST, s_name ASC NULLS FIRST 385 396 AGGREGATE COUNT(1) GROUP BY HASH s_name 385 3086 DISTRIBUTE HASH ON s_name 385 3086 AGGREGATE COUNT(1) GROUP BY HASH s_name ∞ 3935 INNER JOIN HASH ON s_nationkey = n_nationkey ∞ 1 │└DISTRIBUTE GATHER 6000000 1 │ TABLE SCAN nation WHERE n_name = 'GERMANY' ∞ 98833 INNER JOIN HASH ON l1.l_suppkey = s_suppkey ∞ 10000 │└DISTRIBUTE GATHER 6000000 10000 │ TABLE SCAN supplier 9T 98833 INNER JOIN HASH ON l1.l_orderkey = o_orderkey 9T 729413 │└DISTRIBUTE HASH ON o_orderkey 6000000 729413 │ TABLE SCAN orders WHERE o_orderstatus = 'F' 6000000 202092 LEFT ANTI JOIN HASH ON l1.l_orderkey = l3.l_orderkey 6000000 6001215 │└DISTRIBUTE HASH 1500000 6001215 │ DISTRIBUTE HASH 1500000 6001215 │ TABLE SCAN lineitem 6000000 3657708 LEFT SEMI JOIN HASH ON l1.l_orderkey = l2.l_orderkey 6000000 6001215 │└DISTRIBUTE HASH ON l2.l_orderkey 10000 6001215 │ TABLE SCAN lineitem 6000000 3793296 DISTRIBUTE HASH ON l1.l_orderkey 25 3793296 TABLE SCAN lineitem WHERE l1.l_receiptdate > l1.l_commitdate
	Estimate Actual Operator 399 396 SORT Expr1017, s_name 399 396 PROJECT CONVERT_IMPLICIT(int,globalagg1019,0) AS Expr1017 399 396 AGGREGATE SUM(partialagg1018) AS globalagg1019 GROUP BY SORT s_name 1990 2358 SORT s_name 1990 2358 AGGREGATE COUNT(*) AS partialagg1018 GROUP BY SORT s_name 9642 3935 SORT s_name 9642 3935 INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │└TABLE SCAN nation WHERE n_name = 'GERMANY' 24105 3935 INNER JOIN HASH ON l1.l_suppkey = s_suppkey 1000 396 │└TABLE SCAN supplier WHERE BLOOM(s_nationkey) 24105 3935 LEFT ANTI JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 24105 70401 │└LEFT SEMI JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 24105 73048 │ │└INNER JOIN HASH ON o_orderkey = l1.l_orderkey 180036 150794 │ │ │└TABLE SCAN lineitem AS l1 WHERE (BLOOM(l_suppkey as l_suppkey)) AND (l_receiptdate as l_receiptdate > l_commitdate as l_commitdate) 72967 69531 │ │ TABLE SCAN orders WHERE (BLOOM(o_orderkey)) AND (o_orderstatus = 'F') 60012 344690 │ TABLE SCAN lineitem AS l2 WHERE BLOOM(l_orderkey as l_orderkey) 18003 239255 TABLE SCAN lineitem AS l3 WHERE (BLOOM(l_orderkey as l_orderkey)) AND (l_receiptdate as l_receiptdate > l_commitdate as l_commitdate)
	Estimate Actual Operator - 396 PROJECT s_name, count AS numwait - 396 SORT count, s_name - 396 AGGREGATE COUNT(count_54) AS count GROUP BY HASH s_name - 1266 DISTRIBUTE HASH ON s_name - 1266 AGGREGATE COUNT(*) AS count_54 GROUP BY PARTIAL s_name - 3935 FILTER NOT (COALESCE(aggrbool,false)) - 70401 AGGREGATE BOOL_OR(subquerytrue_49) AS aggrbool GROUP BY HASH l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, exists_value, unique_value - 188106 LEFT OUTER JOIN HASH ON (l_orderkey = l_orderkey_29) AND (l_suppkey_31 <> l_suppkey) 3040569 3793296 │└DISTRIBUTE HASH ON l_orderkey_29 3040569 3793296 │ PROJECT true AS subquerytrue_49, l_orderkey_29, l_suppkey_31 3040569 3793296 │ PROJECT l_orderkey AS l_orderkey_29, l_suppkey AS l_suppkey_31 3040569 3793296 │ FILTER l_receiptdate > l_commitdate 3040569 3793296 │ TABLE SCAN lineitem - 70401 PROJECT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, exists_value, row_number() OVER() AS unique_value - 70401 PROJECT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, true AS exists_value - 70401 GROUP BY HASH l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, unique_53 - 70401 INNER JOIN HASH ON (l_orderkey = l_orderkey_9) AND (l_suppkey_11 <> l_suppkey) 6001215 6001215 │└DISTRIBUTE HASH ON l_orderkey_9 6001215 6001215 │ PROJECT l_orderkey AS l_orderkey_9, l_suppkey AS l_suppkey_11 6001215 6001215 │ TABLE SCAN lineitem - 73048 PROJECT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, row_number() OVER() AS unique_53 - 73048 INNER JOIN HASH ON s_nationkey = n_nationkey 25 1 │└DISTRIBUTE GATHER 25 1 │ FILTER n_name = 'GERMANY' 25 1 │ TABLE SCAN nation - 73048 INNER JOIN HASH ON l_suppkey = s_suppkey 10000 396 │└DISTRIBUTE GATHER 10000 396 │ TABLE SCAN supplier - 73048 INNER JOIN HASH ON l_orderkey = o_orderkey 1350000 729413 │└DISTRIBUTE HASH ON o_orderkey 1350000 729413 │ FILTER o_orderstatus = 'F' 1350000 729413 │ TABLE SCAN orders 3040569 150794 FILTER l_receiptdate > l_commitdate 3040569 150794 TABLE SCAN lineitem
	Estimate Actual Operator 1 396 SORT COUNT(), s_name 1 396 AGGREGATE COUNT() GROUP BY SORT s_name 1 3935 SORT s_name 1 3935 LEFT SEMI JOIN LOOP ON l_orderkey = l_orderkey 4 6582 │└LEFT ANTI JOIN LOOP ON l_orderkey = l_orderkey 12585 18262 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 320088 603176 │ │ │└INNER JOIN LOOP ON l_suppkey = s_suppkey 1600 1584 │ │ │ │└INNER JOIN LOOP ON s_nationkey = n_nationkey 1 1 │ │ │ │ │└TABLE SCAN nation AS nation WHERE n_name = 'GERMANY' 1600 1584 │ │ │ │ TABLE SEEK supplier AS supplier 316800 603171 │ │ │ TABLE SEEK lineitem AS l1 WHERE l1.l_receiptdate > l1.l_commitdate 943612 729413 │ │ TABLE SCAN orders AS orders WHERE o_orderstatus = 'F' 292192 73048 │ TABLE SEEK lineitem AS l3 WHERE (l3.l_receiptdate > l3.l_commitdate) AND (l3.l_suppkey <> l1.l_suppkey) 85566 6582 TABLE SEEK lineitem AS l2 WHERE l2.l_suppkey <> l1.l_suppkey