Planner — TPC-H — TPCH-Q22

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 1,568,282 1.6M Rank		Estimation Error Est Err Row Operations Ops 1,500,000 1.5M Rank	Estimation Error Est Err Row Operations Ops 25 25 Rank	Estimation Error Est Err Row Operations Ops 68,282 68K Rank	Estimation Error Est Err Row Operations Ops 22,836 23K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 1,557,100 1.6M Rank		Estimation Error Est Err Row Operations Ops 1,500,000 1.5M Rank	Estimation Error Est Err Row Operations Ops 7 7 Rank	Estimation Error Est Err Row Operations Ops 18,967 19K Rank	Estimation Error Est Err Row Operations Ops 44,458 44K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 1,580,045 1.6M Rank		Estimation Error Est Err Row Operations Ops 1,751,627 1.8M Rank	Estimation Error Est Err Row Operations Ops 7 7 Rank	Estimation Error Est Err Row Operations Ops 235,015 235K Rank	Estimation Error Est Err Row Operations Ops 44,459 44K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 76,086 76K Rank	Estimation Error Est Err Row Operations Ops 18,975 19K Rank	Estimation Error Est Err Row Operations Ops 18,975 19K Rank	Estimation Error Est Err Row Operations Ops 6,321 6.3K Rank	Estimation Error Est Err Row Operations Ops 18,975 19K Rank	Estimation Error Est Err Row Operations Ops 44,460 44K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 476,975 477K Rank		Estimation Error Est Err Row Operations Ops 209,641 210K Rank	Estimation Error Est Err Row Operations Ops 7 7 Rank	Estimation Error Est Err Row Operations Ops 60,925 61K Rank	Estimation Error Est Err Row Operations Ops 44,450 44K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 25 PROJECT cntrycode, a1 AS numcust, a2 AS totacctbal - 25 SORT cntrycode - 25 PROJECT cntrycode, a1, a2 - 25 AGGREGATE COUNT() AS a1, SUM(c_acctbal) AS a2 GROUP BY HASH cntrycode - 22836 PROJECT left(c_phone,2) AS cntrycode, c_acctbal - 22836 PROJECT c_phone, c_acctbal - 22836 RIGHT ANTI JOIN HASH ON PROJECTION_528.o_custkey = PROJECTION_525.c_custkey - 68282 │└PROJECT c_custkey AS c_custkey_right, c_phone, c_acctbal - 68282 │ PROJECT c_custkey, c_acctbal, c_phone - 68282 │ TABLE SCAN customer WHERE (c_acctbal > 4979.819810133222) AND TRUE - 1500000 PROJECT o_custkey - 1500000 TABLE SCAN orders WHERE o_custkey = o_custkey
DuckDB	Estimate Actual Operator - 7 SORT custsale.cntrycode 2 7 AGGREGATE count_star(), SUM(#1) GROUP BY HASH #0 3 6321 PROJECT cntrycode, c_acctbal 3 6321 PROJECT cntrycode, c_acctbal 3 0 RIGHT SEMI INNER JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey 18 18975 │└AGGREGATE GROUP BY HASH #11 3 6321 │ RIGHT ANTI JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey - 0 │ │└SCAN EMPTY 180 190744 │ PROJECT c_custkey 180 190744 │ INNER JOIN HASH ON o_custkey = c_custkey 18 0 │ │└SCAN MATERIALISED 1500000 1499950 │ TABLE SCAN orders 19 18975 INNER JOIN LOOP ON CAST(c_acctbal AS DOUBLE) > SUBQUERY 1 1 │└PROJECT CASE WHEN (#1 > 1) THEN ("error"('More than one row returned by a subquery used as an expression - scalar subqueries can only return a single row. Use "SET scalar_subquery_error_on_multiple_rows=false" to revert to previous behavior of returning a random row.')) ELSE #0 END - 1 │ AGGREGATE "first" #0, count_star() - 1 │ AGGREGATE AVG(#0) 30000 38137 │ PROJECT c_acctbal 30000 38137 │ TABLE SCAN customer WHERE c_acctbal > 0.000 AND ("left"(c_phone,2) IN('10','17','19','23','22','31','27')) 30000 41958 TABLE SCAN customer WHERE "left"(c_phone,2) IN('10','17','19','23','22','31','27')
Databricks	Estimate Actual Operator 150000 1 SEQUENCE 150000 7 ├─SORT custsale.cntrycode ASC NULLS FIRST 1 7 │ DISTRIBUTE HASH ON custsale.cntrycode ASC NULLS FIRST 1 7 │ AGGREGATE COUNT(1), SUM(custsale.c_acctbal) GROUP BY HASH custsale.cntrycode 1 7 │ DISTRIBUTE HASH ON custsale.cntrycode 1 7 │ AGGREGATE COUNT(1), SUM(custsale.c_acctbal) GROUP BY HASH custsale.cntrycode 150000 6317 │ LEFT ANTI JOIN HASH ON c_custkey = o_custkey 150000 18967 │ │└TABLE SCAN customer WHERE in(ephemeralsubstring(c_phone,1,2),'10'collate UTF8_BINARY,'17'collate UTF8_BINARY,'19'collate UTF8_BINARY,'23'collate UTF8_BINARY,'22'collate UTF8_BINARY,'31'collate UTF8_BINARY,'27'collate UTF8_BINARY) 150000 1500000 │ DISTRIBUTE HASH ON o_custkey 1500000 1500000 │ TABLE SCAN orders 150000 1 └─AGGREGATE AVG(unscaledvalue c_acctbal) 150000 1 DISTRIBUTE GATHER 150000 1 AGGREGATE AVG(unscaledvalue c_acctbal) 150000 38133 TABLE SCAN customer WHERE (c_acctbal > 0BD) AND in(ephemeralsubstring(c_phone,1,2),'10'collate UTF8_BINARY,'17'collate UTF8_BINARY,'19'collate UTF8_BINARY,'23'collate UTF8_BINARY,'22'collate UTF8_BINARY,'31'collate UTF8_BINARY,'27'collate UTF8_BINARY)
PostgreSQL	Estimate Actual Operator 756 7 SEQUENCE 1 1 ├─AGGREGATE AVG(c_acctbal) 3 3 │ AGGREGATE PARTIAL AVG(c_acctbal) 5958 38136 │ TABLE SCAN customer AS customer_1 WHERE (customer_1.c_acctbal > 0.00) AND (LEFT(customer_1.c_phone,2) IN('10','17','19','23','22','31','27')) 756 7 └─AGGREGATE COUNT(*), SUM(c_acctbal) GROUP BY SORT LEFT(c_phone,2) 945 6321 SORT LEFT(c_phone,2) 945 6321 LEFT ANTI JOIN LOOP ON o_custkey = c_custkey 2187 18975 │└TABLE SCAN customer AS customer WHERE (c_acctbal > (InitPlan 1) .col1) AND (LEFT(c_phone,2) IN('10','17','19','23','22','31','27')) 341550 18975 TABLE SEEK orders AS orders
SQL Server	Estimate Actual Operator 231 7 SORT Expr1012 231 7 PROJECT CONVERT_IMPLICIT(int,Expr1028,0) AS Expr1013 231 7 AGGREGATE COUNT() AS Expr1028, SUM(c_acctbal) AS Expr1014 GROUP BY HASH Expr1012 1721 6317 INNER JOIN HASH ON o_custkey = c_custkey 4709 18967 │└INNER JOIN LOOP ON Expr1012 = '27' OR Expr1012 = '31' OR Expr1012 = '22' OR Expr1012 = '23' OR Expr1012 = '19' OR Expr1012 = '17' OR Expr1012 = '10' 10375 41958 │ │└FILTER Expr1012 = '27' OR Expr1012 = '31' OR Expr1012 = '22' OR Expr1012 = '23' OR Expr1012 = '19' OR Expr1012 = '17' OR Expr1012 = '10' 150000 150000 │ │ PROJECT substring(c_phone,1,2) AS Expr1012 150000 150000 │ │ TABLE SCAN customer 1 1 │ PROJECT CASE WHEN Expr1026 = 0 THEN NULL ELSE Expr1027 / CONVERT_IMPLICIT(decimal(19,0),Expr1026,0) END AS Expr1006 1 1 │ AGGREGATE COUNT() AS Expr1026, SUM(c_acctbal) AS Expr1027 9894 38133 │ FILTER Expr1015 = '27' OR Expr1015 = '31' OR Expr1015 = '22' OR Expr1015 = '23' OR Expr1015 = '19' OR Expr1015 = '17' OR Expr1015 = '10' 9894 136301 │ PROJECT substring(c_phone,1,2) AS Expr1015 9894 136301 │ TABLE SCAN customer WHERE c_acctbal > 0.00 150000 190674 TABLE SCAN orders WHERE BLOOM(o_custkey)