Planner — TPC-H — TPCH-Q07

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 3,375,198 3.4M Rank	Estimation Error Est Err Row Operations Ops 7,916,027 7.9M Rank	Estimation Error Est Err Row Operations Ops 50 50 Rank	Estimation Error Est Err Row Operations Ops 7,738,029 7.7M Rank	Estimation Error Est Err Row Operations Ops 1,537,150 1.5M Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 3,224,816 3.2M Rank	Estimation Error Est Err Row Operations Ops 3,358,906 3.4M Rank	Estimation Error Est Err Row Operations Ops 4 4 Rank	Estimation Error Est Err Row Operations Ops 292,909 293K Rank	Estimation Error Est Err Row Operations Ops 5,457 5.5K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 2,009,312 2M Rank	Estimation Error Est Err Row Operations Ops 2,143,470 2.1M Rank	Estimation Error Est Err Row Operations Ops 5,455 5.5K Rank	Estimation Error Est Err Row Operations Ops 296,055 296K Rank	Estimation Error Est Err Row Operations Ops 5,455 5.5K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 3,375,139 3.4M Rank	Estimation Error Est Err Row Operations Ops 2,009,308 2M Rank	Estimation Error Est Err Row Operations Ops 134,162 134K Rank	Estimation Error Est Err Row Operations Ops 1,769,090 1.8M Rank	Estimation Error Est Err Row Operations Ops 5,457 5.5K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 50 PROJECT SUM(volume) - 50 SORT supp_nation, cust_nation, l_year - 50 GROUP BY HASH supp_nation, cust_nation, l_year AGGREGATE SUM(volume) - 1537150 PROJECT EXTRACT(YEAR FROM l_shipdate), n_name, n_name, 1 - l_discount, EXTRACT(YEAR FROM l_shipdate), l_extendedprice * (1 - l_discount), l_extendedprice * (1 - l_discount) - 1537150 FILTER (((n_name = 'GERMANY') AND (n_name = 'FRANCE')) OR ((n_name = 'FRANCE') AND (n_name = 'GERMANY'))) AND ((l_shipdate >= '1995-01-01') AND (l_shipdate <= '1996-12-31')) - 1537150 INNER JOIN HASH ON c_nationkey = n_nationkey - 25 │└TABLE SCAN nation - 1550168 INNER JOIN HASH ON s_nationkey = n_nationkey - 25 │└TABLE SCAN nation - 1550168 INNER JOIN HASH ON o_custkey = c_custkey - 150000 │└TABLE SCAN customer - 1550197 INNER JOIN HASH ON l_suppkey = s_suppkey - 10000 │└TABLE SCAN supplier - 1550346 INNER JOIN HASH ON l_orderkey = o_orderkey - 1500000 │└TABLE SCAN orders - 1715148 TABLE SCAN lineitem WHERE (l_shipdate >= '1995-01-01') AND (l_shipdate <= '1996-12-31')
DuckDB	Estimate Actual Operator - 4 SORT supp_nation, cust_nation, l_year 55216 4 GROUP BY HASH #0, #1, #2 AGGREGATE SUM(#3) 55217 5457 PROJECT supp_nation, cust_nation, l_year, volume 55217 5457 PROJECT supp_nation, cust_nation, l_year, volume 55217 5457 FILTER ((n_name = 'GERMANY') AND (n_name = 'FRANCE')) OR ((n_name = 'FRANCE') AND (n_name = 'GERMANY')) 55217 10959 INNER JOIN HASH ON o_custkey = c_custkey 28846 11904 │└INNER JOIN HASH ON c_nationkey = n_nationkey 25 2 │ │└FILTER (n_name = 'FRANCE') OR (n_name = 'GERMANY') 25 25 │ │ TABLE SCAN nation WHERE n_name = 'FRANCE' OR n_name = 'GERMANY' 150000 11904 │ TABLE SCAN customer WHERE c_custkey >= 3 271595 134140 INNER JOIN HASH ON o_orderkey = l_orderkey 269191 134158 │└INNER JOIN HASH ON l_suppkey = s_suppkey 1923 785 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 5 2 │ │ │└FILTER (n_name = 'GERMANY') OR (n_name = 'FRANCE') 25 25 │ │ │ TABLE SCAN nation WHERE n_name = 'GERMANY' OR n_name = 'FRANCE' 10000 785 │ │ TABLE SCAN supplier 1199764 1712292 │ FILTER l_suppkey >= 1 1199764 1712292 │ TABLE SCAN lineitem WHERE l_shipdate >= '1995-01-01' AND l_shipdate <= '1996-12-31' 1500000 1499785 TABLE SCAN orders
SQL Server	Estimate Actual Operator 2 4 SORT n_name, n_name, Expr1011 2 4 PROJECT CASE WHEN Expr1019 = 0 THEN NULL ELSE Expr1020 END AS Expr1013 2 4 GROUP BY HASH AGGREGATE COUNT(Expr1014) AS Expr1019, SUM(Expr1014) AS Expr1020 9207 5457 INNER JOIN HASH ON n_nationkey as n_nationkey = c_nationkey AND c_custkey = o_custkey 67988 134158 │└INNER JOIN MERGE ON o_orderkey = l_orderkey 1500000 1499985 │ │└TABLE SEEK orders 67988 134158 │ SORT l_orderkey 67988 134158 │ INNER JOIN HASH ON s_suppkey = l_suppkey 400 785 │ │└INNER JOIN HASH ON n_nationkey as n_nationkey = s_nationkey 1 2 │ │ │└INNER JOIN LOOP ON n_name as n_name = 'FRANCE' OR n_name as n_name = 'GERMANY' 2 4 │ │ │ │└TABLE SEEK nation WHERE n_name as n_name = 'FRANCE' OR n_name as n_name = 'GERMANY' 2 2 │ │ │ TABLE SEEK nation WHERE n_name as n_name = 'FRANCE' OR n_name as n_name = 'GERMANY' 10000 10000 │ │ TABLE SEEK supplier 1699860 1715148 │ PROJECT datepart(EXTRACT(YEAR FROM l_shipdate) AS Expr1011, l_extendedprice * (1. - l_discount) AS Expr1014 1699860 1715148 │ TABLE SCAN lineitem WHERE l_shipdate >= '1995-01-01' AND l_shipdate <= '1996-12-31' 150000 150000 TABLE SEEK customer
PostgreSQL	Estimate Actual Operator 5728 4 GROUP BY SORT n_name, n_name, EXTRACT(EXTRACT(YEAR FROM l_shipdate) AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) 7160 5455 SORT n_name, n_name, EXTRACT(EXTRACT(YEAR FROM l_shipdate) 7160 5455 INNER JOIN HASH ON o_custkey = c_custkey AND (((n_name = 'GERMANY') AND (n_name = 'FRANCE')) OR ((n_name = 'FRANCE') AND (n_name = 'GERMANY'))) 5000 11905 │└INNER JOIN HASH ON c_nationkey = n_nationkey 2 2 │ │└TABLE SCAN n2 WHERE (n_name = 'FRANCE') OR (n_name = 'GERMANY') 62500 150000 │ TABLE SCAN customer 172050 134160 INNER JOIN LOOP ON o_orderkey = l_orderkey 172050 134160 │└INNER JOIN HASH ON l_suppkey = s_suppkey 4000 3925 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 2 2 │ │ │└TABLE SCAN n1 WHERE (n_name = 'GERMANY') OR (n_name = 'FRANCE') 10000 10000 │ │ TABLE SCAN supplier 2156855 1715150 │ TABLE SCAN lineitem WHERE (l_shipdate >= '1995-01-01') AND (l_shipdate <= '1996-12-31') 134158 134158 TABLE SEEK orders