Planner — TPC-H — TPCH-Q14

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 268,122 268K Rank	Estimation Error Est Err Row Operations Ops 68,122 68K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 200,000 200K Rank	Estimation Error Est Err Row Operations Ops 68,122 68K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 268,122 268K Rank	Estimation Error Est Err Row Operations Ops 68,122 68K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 200,000 200K Rank	Estimation Error Est Err Row Operations Ops 68,122 68K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 268,120 268K Rank	Estimation Error Est Err Row Operations Ops 68,120 68K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 200,000 200K Rank	Estimation Error Est Err Row Operations Ops 68,125 68K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 268,122 268K Rank	Estimation Error Est Err Row Operations Ops 200,000 200K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 68,122 68K Rank	Estimation Error Est Err Row Operations Ops 68,122 68K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 1 PROJECT 100. * sumIf((l_extendedprice * (1 - l_discount)),(p_type LIKE 'PROMO%')), (100. * sumIf((l_extendedprice * (1 - l_discount)),(p_type LIKE 'PROMO%'))) / SUM(l_extendedprice * (1 - l_discount)), (100. * sumIf((l_extendedprice * (1 - l_discount)),(p_type LIKE 'PROMO%'))) / SUM(l_extendedprice * (1 - l_discount)) - 1 GROUP BY HASH AGGREGATE sumIf((l_extendedprice * (1 - l_discount)),(p_type LIKE 'PROMO%')), SUM(l_extendedprice * (1 - l_discount)) - 68122 PROJECT 1 - l_discount, p_type LIKE 'PROMO%', l_extendedprice * (1 - l_discount) - 68122 INNER JOIN HASH ON l_partkey = p_partkey - 200000 │└TABLE SCAN part - 68122 TABLE SCAN lineitem WHERE (l_shipdate >= '1996-02-01') AND (l_shipdate < '1996-03-01')
DuckDB	`Estimate Actual Operator 1 1 PROJECT promo_revenue - 1 GROUP BY SIMPLE AGGREGATE SUM(#0), SUM(#1) 1201548 68122 PROJECT CASE WHEN(prefix(p_type,'PROMO')) THEN(l_extendedprice * (1.000 - l_discount)) ELSE 0.000000 END, l_extendedprice * (1.000 - l_discount) 1201548 68122 INNER JOIN HASH ON l_partkey = p_partkey 200000 200000 │└TABLE SCAN part 1199764 68122 TABLE SCAN lineitem WHERE l_shipdate >= '1996-02-01' AND l_shipdate < '1996-03-01'`
SQL Server	Estimate Actual Operator 1 ∞ PROJECT 100.00 * Expr1005 / Expr1006 AS Expr1007 1 1 PROJECT CASE WHEN Expr1017 = 0 THEN NULL ELSE Expr1018 END AS Expr1005, CASE WHEN Expr1019 = 0 THEN NULL ELSE Expr1020 END AS Expr1006 1 1 GROUP BY HASH AGGREGATE COUNT(CASE WHEN p_type LIKE 'PROMO%'THEN l_extendedprice * (1. - l_discount) ELSE 0.0000 END) AS Expr1017, SUM(CASE WHEN p_type LIKE 'PROMO%'THEN l_extendedprice * (1. - l_discount) ELSE 0.0000 END) AS Expr1018, COUNT(Expr1008) AS Expr1019, SUM(Expr1008) AS Expr1020 67989 68122 INNER JOIN HASH ON p_partkey = l_partkey 67989 68122 │└PROJECT l_extendedprice * (1. - l_discount) AS Expr1008 67989 68122 │ TABLE SCAN lineitem WHERE l_shipdate >= '1996-02-01' AND l_shipdate < '1996-03-01' 200000 200000 TABLE SEEK part
PostgreSQL	Estimate Actual Operator 1 1 GROUP BY SIMPLE AGGREGATE (100.00 * SUM(CASE WHEN(p_type LIKE 'PROMO%') THEN(l_extendedprice * ('1' - l_discount)) ELSE'0'END)) / SUM(l_extendedprice * ('1' - l_discount)) 5 5 GROUP BY SIMPLE AGGREGATE PARTIALSUM(CASE WHEN(p_type LIKE 'PROMO%') THEN(l_extendedprice * ('1' - l_discount)) ELSE'0'END), PARTIALSUM(l_extendedprice * ('1' - l_discount)) 82655 68120 INNER JOIN HASH ON l_partkey = p_partkey 416665 200000 │└TABLE SCAN part 82655 68120 TABLE SCAN lineitem WHERE (l_shipdate >= '1996-02-01') AND (l_shipdate < '1996-03-01')