Planner — TPC-H — TPCH-Q20

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 4,339 4.3K Rank	Estimation Error Est Err Row Operations Ops 4,338 4.3K Rank	Estimation Error Est Err Row Operations Ops 180 180 Rank	Estimation Error Est Err Row Operations Ops 180 180 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 1,656,797 1.7M Rank	Estimation Error Est Err Row Operations Ops 1,677,128 1.7M Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 35,128 35K Rank	Estimation Error Est Err Row Operations Ops 9,062 9.1K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,859,523 1.9M Rank	Estimation Error Est Err Row Operations Ops 534,608 535K Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 534,936 535K Rank	Estimation Error Est Err Row Operations Ops 800,000 800K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 812,468 812K Rank	Estimation Error Est Err Row Operations Ops 10,699 11K Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 802,889 803K Rank	Estimation Error Est Err Row Operations Ops 360 360 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	`Estimate Actual Operator - 180 SORT s_name - 180 INNER JOIN HASH ON s_nationkey = n_nationkey - 1 │└TABLE SCAN nation WHERE n_name = 'KENYA' - 4338 TABLE SCAN supplier WHERE s_suppkey IN (SELECT ps_suppkey FROM tpch.partsupp WHERE ps_partkey IN (SELECT p_partkey FROM tpch.part WHERE p_name LIKE 'almond%') AND ps_availqty > (SELECT 0.5 * SUM(l_quantity) FROM tpch.lineitem WHERE l_partkey = ps_partkey AND l_suppkey = ps_suppkey AND l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01'))`
DuckDB	Estimate Actual Operator - 175 SORT s_name 76 175 RIGHT SEMI INNER JOIN HASH ON #0 = s_suppkey 384 391 │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation WHERE n_name = 'KENYA' 10000 391 │ TABLE SCAN supplier 160000 5583 FILTER CAST(ps_availqty AS DECIMAL(38,4)) > SUBQUERY 0 0 RIGHT SEMI INNER JOIN HASH ON ps_partkey IS NOT DISTINCT FROM ps_partkeyps_suppkey IS NOT DISTINCT FROM ps_suppkey 159980 8428 │└GROUP BY HASH #0, #2 AGGREGATE 0 8428 │ RIGHT OUTER JOIN HASH ON ps_partkey IS NOT DISTINCT FROM ps_partkeyps_suppkey IS NOT DISTINCT FROM ps_suppkey - 0 │ │└SCAN EMPTY 251258 5589 │ PROJECT 0.5 * SUM(l_quantity), ps_partkey, ps_suppkey 251258 5589 │ GROUP BY HASH #0, #1 AGGREGATE SUM(#2) 502516 9062 │ PROJECT ps_partkey, ps_suppkey, l_quantity 502516 9062 │ INNER JOIN HASH ON l_partkey = ps_partkeyl_suppkey = ps_suppkey 159980 0 │ │└SCAN MATERIALISED 1199764 854950 │ TABLE SCAN lineitem WHERE l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01' 160000 8428 LEFT SEMI JOIN HASH ON ps_partkey = #0 40000 2107 │└TABLE SCAN part WHERE p_name >= 'almond' AND p_name < 'almone' 800000 799348 TABLE SCAN partsupp
PostgreSQL	Estimate Actual Operator 108 175 SORT s_name 108 175 INNER JOIN LOOP ON n_nationkey = s_nationkey AND (n_nationkey = s_nationkey) 1 1 │└TABLE SCAN nation WHERE n_name = 'KENYA' 2693 4264 LEFT SEMI JOIN LOOP ON s_suppkey = ps_suppkey AND (s_suppkey = ps_suppkey) 10000 10000 │└TABLE SCAN supplier 2693 5583 INNER JOIN LOOP ON p_partkey = ps_partkey 266667 524761 │└TABLE SCAN partsupp WHERE ps_availqty > (SubPlan 1) 800000 800000 │ GROUP BY SIMPLE AGGREGATE 0.5 * SUM(l_quantity) 800000 800000 │ TABLE SEEK lineitem WHERE (l_shipdate >= '1993-01-01') AND (l_shipdate < '1994-01-01') AND (l_suppkey = ps_suppkey) 524761 524761 TABLE SEEK part WHERE p_name LIKE 'almond%'
SQL Server	Estimate Actual Operator 47 175 SORT s_name 47 175 INNER JOIN HASH ON 400 391 │└INNER JOIN HASH ON n_nationkey = s_nationkey 1 1 │ │└TABLE SEEK nation WHERE n_name = 'KENYA' 10000 10000 │ TABLE SEEK supplier 126 233 INNER JOIN HASH ON 2255 2107 │└TABLE SEEK part WHERE p_name LIKE 'almond%' 112 233 INNER JOIN HASH ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey AND ps_availqty > 0.5 * Expr1012 800000 800000 │└TABLE SCAN partsupp 84 233 PROJECT CASE WHEN Expr1228 = 0 THEN NULL ELSE Expr1229 END AS Expr1012 84 233 GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1228, SUM(l_quantity) AS Expr1229 84 360 TABLE SEEK lineitem WHERE l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01' AND BLOOM(l_partkey) AND BLOOM(l_suppkey)