Planner — TPC-H — TPCH-Q22

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
ClickHouse	Estimation Error Est Err Row Operations Ops 1,518,248 1.5M Rank	Estimation Error Est Err Row Operations Ops 18,248 18K Rank	Estimation Error Est Err Row Operations Ops 7 7 Rank	Estimation Error Est Err Row Operations Ops 1,500,000 1.5M Rank	Estimation Error Est Err Row Operations Ops 18,248 18K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 1,576,781 1.6M Rank	Estimation Error Est Err Row Operations Ops 1,745,710 1.7M Rank	Estimation Error Est Err Row Operations Ops 7 7 Rank	Estimation Error Est Err Row Operations Ops 236,163 236K Rank	Estimation Error Est Err Row Operations Ops 48,823 49K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,518,412 1.5M Rank	Estimation Error Est Err Row Operations Ops 1,500,000 1.5M Rank	Estimation Error Est Err Row Operations Ops 12,180 12K Rank	Estimation Error Est Err Row Operations Ops 18,412 18K Rank	Estimation Error Est Err Row Operations Ops 12,180 12K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 1,576,976 1.6M Rank	Estimation Error Est Err Row Operations Ops 1,518,407 1.5M Rank	Estimation Error Est Err Row Operations Ops 7 7 Rank	Estimation Error Est Err Row Operations Ops 58,743 59K Rank	Estimation Error Est Err Row Operations Ops 48,817 49K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	`Estimate Actual Operator - 7 PROJECT COUNT(), SUM(c_acctbal) - 7 SORT cntrycode - 7 GROUP BY HASH cntrycode AGGREGATE COUNT(), SUM(c_acctbal) - 18248 PROJECT left(c_phone,2), left(c_phone,2) - 18248 LEFT SEMI JOIN HASH ON TRUE - 1500000 │└PROJECT - 1500000 │ PROJECT o_custkey - 1500000 │ TABLE SCAN orders WHERE 1 = 1 - 18248 TABLE SCAN customer WHERE (c_acctbal > (4981.241227349866::Float64)) AND left(c_phone,2) IN ('10', '17', '19', '23', '22', '31', '27')`
DuckDB	Estimate Actual Operator - 7 SORT cntrycode 0 7 GROUP BY HASH #0 AGGREGATE count_star(), SUM(#1) 2 12180 PROJECT cntrycode, c_acctbal 2 12180 PROJECT cntrycode, c_acctbal 2 0 RIGHT SEMI INNER JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey 9 18412 │└GROUP BY HASH #2 AGGREGATE 2 12180 │ RIGHT ANTI JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey - 0 │ │└SCAN EMPTY 90 187159 │ PROJECT c_custkey 90 187159 │ INNER JOIN HASH ON o_custkey = c_custkey 9 0 │ │└SCAN MATERIALISED 1500000 1499803 │ TABLE SCAN orders 10 18412 INNER JOIN LOOP ON CAST(c_acctbal AS DOUBLE) > SUBQUERY - 1 │└GROUP BY SIMPLE AGGREGATE "first" #0, count_star() - 1 │ GROUP BY SIMPLE AGGREGATE AVG(#0) 30000 36642 │ PROJECT c_acctbal 30000 36642 │ TABLE SCAN customer WHERE c_acctbal > 0.000 AND ("left"(c_phone,2) IN('10','17','19','23','22','31','27')) 30000 40336 TABLE SCAN customer WHERE "left"(c_phone,2) IN('10','17','19','23','22','31','27')
SQL Server	Estimate Actual Operator 434 7 SORT Expr1009 434 7 PROJECT CONVERT_IMPLICIT(int,Expr1022,0) AS Expr1010 434 7 GROUP BY HASH AGGREGATE COUNT() AS Expr1022, SUM(c_acctbal) AS Expr1011 3122 12177 INNER JOIN HASH ON o_custkey = c_custkey 4696 18407 │└INNER JOIN LOOP ON substring(c_phone,1,2) = '27' OR substring(c_phone,1,2) = '31' OR substring(c_phone,1,2) = '22' OR substring(c_phone,1,2) = '23' OR substring(c_phone,1,2) = '19' OR substring(c_phone,1,2) = '17' OR substring(c_phone,1,2) = '10' 10278 40336 │ │└PROJECT substring(c_phone,1,2) AS Expr1009 10278 40336 │ │ TABLE SEEK customer WHERE substring(c_phone,1,2) = '27' OR substring(c_phone,1,2) = '31' OR substring(c_phone,1,2) = '22' OR substring(c_phone,1,2) = '23' OR substring(c_phone,1,2) = '19' OR substring(c_phone,1,2) = '17' OR substring(c_phone,1,2) = '10' 1 1 │ PROJECT CASE WHEN Expr1020 = 0 THEN NULL ELSE Expr1021 / CONVERT_IMPLICIT(decimal(19,0),Expr1020,0) END AS Expr1004 1 1 │ GROUP BY HASH AGGREGATE COUNT() AS Expr1020, SUM(c_acctbal) AS Expr1021 9799 36640 │ TABLE SEEK customer WHERE c_acctbal > 0.00 AND (substring(c_phone,1,2) = '27' OR substring(c_phone,1,2) = '31' OR substring(c_phone,1,2) = '22' OR substring(c_phone,1,2) = '23' OR substring(c_phone,1,2) = '19' OR substring(c_phone,1,2) = '17' OR substring(c_phone,1,2) = '10') 1500000 1500000 TABLE SEEK orders
PostgreSQL	`Estimate Actual Operator 1186 7 GROUP BY SORT "left"(c_phone,2) AGGREGATE COUNT(*), SUM(c_acctbal) 1532 12180 SORT "left"(c_phone,2) 1532 12180 RIGHT ANTI JOIN HASH ON o_custkey = c_custkey 1458 18412 │└TABLE SCAN customer WHERE (c_acctbal > (InitPlan 1) .col1) AND ("left"(c_phone,2) IN('10','17','19','23','22','31','27')) 1935484 1500000 TABLE SCAN orders`