Planner — TPC-H — TPCH-Q21

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Seek Seek	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
ClickHouse	Estimation Error Est Err Row Operations Ops 14,614,119 15M Rank		Estimation Error Est Err Row Operations Ops 14,146,834 14M Rank	Estimation Error Est Err Row Operations Ops 396 396 Rank	Estimation Error Est Err Row Operations Ops 4,421,148 4.4M Rank	Estimation Error Est Err Row Operations Ops 3,935 3.9K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 14,318,377 14M Rank		Estimation Error Est Err Row Operations Ops 14,312,690 14M Rank	Estimation Error Est Err Row Operations Ops 396 396 Rank	Estimation Error Est Err Row Operations Ops 329,155 329K Rank	Estimation Error Est Err Row Operations Ops 6,263 6.3K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DataFusion	Estimation Error Est Err Row Operations Ops 19,513,670 20M Rank		Estimation Error Est Err Row Operations Ops 17,245,629 17M Rank	Estimation Error Est Err Row Operations Ops 792 792 Rank	Estimation Error Est Err Row Operations Ops 3,874,319 3.9M Rank	Estimation Error Est Err Row Operations Ops 4,331 4.3K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 18,157,536 18M Rank		Estimation Error Est Err Row Operations Ops 14,106,623 14M Rank	Estimation Error Est Err Row Operations Ops 396 396 Rank	Estimation Error Est Err Row Operations Ops 1,175,886 1.2M Rank	Estimation Error Est Err Row Operations Ops 3,935 3.9K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,262,610 1.3M Rank	Estimation Error Est Err Row Operations Ops 533,196 533K Rank	Estimation Error Est Err Row Operations Ops 1,262,613 1.3M Rank	Estimation Error Est Err Row Operations Ops 4,331 4.3K Rank	Estimation Error Est Err Row Operations Ops 936,883 937K Rank	Estimation Error Est Err Row Operations Ops 3,935 3.9K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 1,362,532 1.4M Rank		Estimation Error Est Err Row Operations Ops 1,357,116 1.4M Rank	Estimation Error Est Err Row Operations Ops 396 396 Rank	Estimation Error Est Err Row Operations Ops 449,983 450K Rank	Estimation Error Est Err Row Operations Ops 3,935 3.9K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Trino	Estimation Error Est Err Row Operations Ops 19,513,670 20M Rank		Estimation Error Est Err Row Operations Ops 14,127,560 14M Rank	Estimation Error Est Err Row Operations Ops 396 396 Rank	Estimation Error Est Err Row Operations Ops 15,775,907 16M Rank	Estimation Error Est Err Row Operations Ops 1,000,113 1M Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
Trino	Estimate Actual Operator - 396 PROJECT s_name, count AS numwait - 396 SORT count, s_name - 396 AGGREGATE COUNT(count_44) AS count GROUP BY HASH s_name - 396 DISTRIBUTE HASH ON s_name - 396 AGGREGATE COUNT(*) AS count_44 GROUP BY PARTIAL s_name - 8035 FILTER NOT (COALESCE(aggrbool,false)) - 145379 AGGREGATE BOOL_OR(subquerytrue_39) AS aggrbool GROUP BY SORT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, exists_value, unique_value - 388959 LEFT OUTER JOIN HASH ON (l_orderkey = l_orderkey_22) AND (l_suppkey_24 <> l_suppkey) 3040568 3793296 │└DISTRIBUTE HASH ON l_orderkey_22 3040568 3793296 │ PROJECT true AS subquerytrue_39, l_orderkey_22, l_suppkey_24 6001215 3793296 │ PROJECT l_orderkey AS l_orderkey_22, l_suppkey AS l_suppkey_24 6001215 3793296 │ FILTER l_receiptdate > l_commitdate 6001215 6001215 │ TABLE SCAN lineitem - 145379 PROJECT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, exists_value, row_number() OVER() AS unique_value - 145379 PROJECT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, true AS exists_value - 145379 GROUP BY SORT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, unique_43 - 602723 INNER JOIN HASH ON (l_orderkey = l_orderkey_4) AND (l_suppkey_6 <> l_suppkey) - 150794 │└PROJECT l_orderkey, l_suppkey, l_commitdate, l_receiptdate, o_orderstatus, s_name, n_name, row_number() OVER() AS unique_43 - 150794 │ INNER JOIN HASH ON s_nationkey = n_nationkey - 3793296 │ │└INNER JOIN HASH ON l_suppkey = s_suppkey - 3793296 │ │ │└INNER JOIN HASH ON l_orderkey = o_orderkey 6001215 3793296 │ │ │ │└FILTER l_receiptdate > l_commitdate 6001215 6001215 │ │ │ │ TABLE SCAN lineitem 729413 1500000 │ │ │ DISTRIBUTE GATHER 729413 1500000 │ │ │ TABLE SCAN orders 10000 10000 │ │ DISTRIBUTE GATHER 10000 10000 │ │ TABLE SCAN supplier 1 1 │ DISTRIBUTE GATHER 25 1 │ FILTER n_name = 'GERMANY' 25 25 │ TABLE SCAN nation 6001215 6001215 DISTRIBUTE HASH ON l_orderkey_4 6001215 6001215 PROJECT l_orderkey AS l_orderkey_4, l_suppkey AS l_suppkey_6 6001215 6001215 TABLE SCAN lineitem
SQL Server	Estimate Actual Operator 399 396 SORT Expr1017, s_name 399 396 PROJECT CONVERT_IMPLICIT(int,Expr1086,0) AS Expr1017 399 396 AGGREGATE COUNT(*) AS Expr1086 GROUP BY HASH s_name 41084 3935 INNER JOIN HASH ON o_orderkey = l1.l_orderkey 72025 8035 │└INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 72025 145379 │ │└INNER JOIN HASH ON l1.l_suppkey = s_suppkey 400 396 │ │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY' 1000 396 │ │ │ TABLE SCAN supplier WHERE BLOOM(s_nationkey) 180036 145379 │ │ INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 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) 600122 712425 │ │ TABLE SCAN lineitem AS l2 WHERE BLOOM(l_orderkey as l_orderkey) 180036 494981 │ TABLE SCAN lineitem AS l3 WHERE (BLOOM(l_orderkey as l_orderkey)) AND (l_receiptdate as l_receiptdate > l_commitdate as l_commitdate) 72921 3935 TABLE SCAN orders WHERE o_orderstatus = 'F' AND BLOOM(o_orderkey)
DuckDB	Estimate Actual Operator - 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 #7, #3 930 3935 │ LEFT ANTI JOIN HASH ON l_suppkey IS NOT DISTINCT FROM l_suppkey AND l_orderkey IS NOT DISTINCT FROM l_orderkey 5771 184147 │ │└PROJECT l_suppkey, l_orderkey 5771 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 #7, #3 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 144336 291776 │ PROJECT l_suppkey, l_orderkey 144336 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 10000 │ │ TABLE SCAN supplier 1200243 3793296 │ FILTER l_receiptdate > l_commitdate 6001215 6001215 │ TABLE SCAN lineitem 750000 74029 TABLE SCAN orders WHERE o_orderstatus = 'F'
ClickHouse	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
PostgreSQL	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 3 6582 │└LEFT ANTI JOIN LOOP ON l_orderkey = l_orderkey 16248 24349 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 240048 452382 │ │ │└INNER JOIN LOOP ON l_suppkey = s_suppkey 1200 1188 │ │ │ │└INNER JOIN LOOP ON s_nationkey = n_nationkey 1 1 │ │ │ │ │└TABLE SCAN nation AS nation WHERE n_name = 'GERMANY' 1200 1188 │ │ │ │ TABLE SEEK supplier AS supplier 238788 452378 │ │ │ TABLE SEEK lineitem AS l1 WHERE l1.l_receiptdate > l1.l_commitdate 913749 729413 │ │ TABLE SCAN orders AS orders WHERE o_orderstatus = 'F' 365240 73048 │ TABLE SEEK lineitem AS l3 WHERE (l3.l_receiptdate > l3.l_commitdate) AND (l3.l_suppkey <> l1.l_suppkey) 92148 6582 TABLE SEEK lineitem AS l2 WHERE l2.l_suppkey <> l1.l_suppkey
DataFusion	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) 60000 70401 │└DISTRIBUTE GATHER 60000 70401 │ LEFT SEMI JOIN HASH ON l_orderkey = l_orderkey AND (l_suppkey <> l_suppkey) 60000 73048 │ │└DISTRIBUTE GATHER 60000 73048 │ │ INNER JOIN HASH ON n_nationkey = s_nationkey 5 1 │ │ │└DISTRIBUTE GATHER 5 1 │ │ │ FILTER n_name = 'GERMANY' 25 25 │ │ │ DISTRIBUTE ROUND ROBIN 25 25 │ │ │ TABLE SCAN nation WHERE n_name = 'GERMANY' 300000 1828911 │ │ PROJECT l_orderkey, l_suppkey, s_name, s_nationkey 300000 1828911 │ │ INNER JOIN HASH ON s_suppkey = l_suppkey 10000 10000 │ │ │└TABLE SCAN supplier WHERE ((s_nationkey >= 7) AND (s_nationkey <= 7)) AND s_nationkey IN 7 300000 1828911 │ │ INNER JOIN HASH ON o_orderkey = l_orderkey 300000 729413 │ │ │└DISTRIBUTE HASH ON o_orderkey 300000 729413 │ │ │ FILTER o_orderstatus = 'F' 1500000 1500000 │ │ │ TABLE SCAN orders WHERE o_orderstatus = 'F' 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) WHEN 5 ... 6001215 6001215 │ TABLE SCAN lineitem 1200243 3793296 FILTER l_receiptdate > l_commitdate 6001215 6001215 TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate
Databricks	Estimate Actual Operator 400 396 SORT numwait DESC NULLS LAST, s_name ASC NULLS FIRST 400 396 AGGREGATE COUNT(1) GROUP BY HASH s_name 400 2328 DISTRIBUTE HASH ON s_name 400 2328 AGGREGATE COUNT(1) GROUP BY HASH s_name 254000 3935 INNER JOIN HASH ON l1.l_orderkey = o_orderkey 251000 8035 │└LEFT SEMI JOIN HASH ON l1.l_orderkey = l2.l_orderkey 251000 13450 │ │└LEFT ANTI JOIN HASH ON l1.l_orderkey = l3.l_orderkey 251000 150794 │ │ │└DISTRIBUTE HASH ON l1.l_orderkey 251000 150794 │ │ │ INNER JOIN HASH ON s_suppkey = l1.l_suppkey 251000 396 │ │ │ │└DISTRIBUTE GATHER 400 396 │ │ │ │ INNER JOIN HASH ON s_nationkey = n_nationkey 6000000 6082 │ │ │ │ │└TABLE SCAN supplier 400 1 │ │ │ │ DISTRIBUTE GATHER 1500000 1 │ │ │ │ TABLE SCAN nation WHERE n_name = 'GERMANY'collate UTF8_BINARY 6000000 3788370 │ │ │ TABLE SCAN lineitem WHERE l1.l_receiptdate > l1.l_commitdate 251000 3793296 │ │ DISTRIBUTE HASH ON l3.l_orderkey 6000000 3793296 │ │ TABLE SCAN lineitem WHERE l3.l_receiptdate > l3.l_commitdate 251000 6001215 │ DISTRIBUTE HASH ON l2.l_orderkey 25 6001215 │ TABLE SCAN lineitem 254000 729413 DISTRIBUTE GATHER 10000 729413 TABLE SCAN orders WHERE o_orderstatus = 'F'collate UTF8_BINARY