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 271,636 272K Rank	Estimation Error Est Err Row Operations Ops 71,636 72K 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 71,636 72K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 268,713 269K Rank	Estimation Error Est Err Row Operations Ops 197,077 197K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 71,636 72K Rank	Estimation Error Est Err Row Operations Ops 71,637 72K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 271,636 272K Rank	Estimation Error Est Err Row Operations Ops 71,636 72K 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 71,636 72K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 271,636 272K Rank	Estimation Error Est Err Row Operations Ops 71,636 72K 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 23,881 24K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 131,846 132K Rank	Estimation Error Est Err Row Operations Ops 71,636 72K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 71,636 72K Rank	Estimation Error Est Err Row Operations Ops 71,636 72K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 1 PROJECT (100. * a4) / a5 AS promo_revenue - 1 AGGREGATE sumIf(a10,a12) AS a4, SUM(a10) AS a5 - 71636 PROJECT l_extendedprice * (1 - l_discount) AS a10, startsWith(p_type,'PROMO') AS a12 - 71636 PROJECT l_extendedprice, l_discount, p_type - 71636 INNER JOIN HASH ON PROJECTION_343.l_partkey = PROJECTION_340.p_partkey - 200000 │└PROJECT p_partkey, p_type - 200000 │ PROJECT p_type, p_partkey - 200000 │ TABLE SCAN part - 71636 PROJECT l_partkey, l_extendedprice, l_discount - 71636 PROJECT l_partkey, l_extendedprice, l_discount - 71636 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 AGGREGATE SUM(#0), SUM(#1) 1258538 71636 PROJECT CASE WHEN (prefix(p_type,'PROMO')) THEN (l_extendedprice * (1.00 - l_discount)) ELSE 0.0000 END, l_extendedprice * (1.00 - l_discount) 1258538 71636 INNER JOIN HASH ON l_partkey = p_partkey 200000 200000 │└TABLE SCAN part 1200243 71636 TABLE SCAN lineitem WHERE l_shipdate >= '1996-02-01' AND l_shipdate < '1996-03-01'`
Databricks	Estimate Actual Operator 1 1 AGGREGATE SUM(casewhen(startswith(p_type,'PROMO'collate UTF8_BINARY),(l_extendedprice * (1BD - l_discount)),0.0000BD)), SUM(l_extendedprice * (1BD - l_discount)) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE SUM(casewhen(startswith(p_type,'PROMO'collate UTF8_BINARY),(l_extendedprice * (1BD - l_discount)),0.0000BD)), SUM(l_extendedprice * (1BD - l_discount)) 71500 71636 INNER JOIN HASH ON l_partkey = p_partkey 71500 71636 │└DISTRIBUTE GATHER 200000 71636 │ TABLE SCAN lineitem WHERE (l_shipdate >= DATE'1996-02-01') AND (l_shipdate < DATE'1996-03-01') 6000000 197077 TABLE SCAN part
PostgreSQL	Estimate Actual Operator 1 1 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)) 3 3 AGGREGATE PARTIAL SUM(CASE WHEN (p_type LIKE 'PROMO%') THEN (l_extendedprice * ('1' - l_discount)) ELSE '0' END), PARTIAL SUM(l_extendedprice * ('1' - l_discount)) 29675 23878 INNER JOIN HASH ON l_partkey = p_partkey 249999 200000 │└TABLE SCAN part AS part 89025 71636 TABLE SCAN lineitem AS lineitem WHERE (l_shipdate >= '1996-02-01') AND (l_shipdate < '1996-03-01')
SQL Server	Estimate Actual Operator 1 1 PROJECT 100.00 * Expr1006 / Expr1007 AS Expr1008 1 1 PROJECT CASE WHEN Expr1021 = 0 THEN NULL ELSE Expr1022 END AS Expr1006, CASE WHEN Expr1023 = 0 THEN NULL ELSE Expr1024 END AS Expr1007 1 1 AGGREGATE COUNT() AS Expr1021, SUM(Expr1020) AS Expr1022, COUNT(Expr1009) AS Expr1023, SUM(Expr1009) AS Expr1024 71586 71636 PROJECT CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice (1. - l_discount) ELSE 0.0000 END AS Expr1020 71586 71636 INNER JOIN HASH ON p_partkey = l_partkey 71586 71636 │└PROJECT l_extendedprice * (1. - l_discount) AS Expr1009 71586 71636 │ TABLE SCAN lineitem WHERE l_shipdate >= '1996-02-01' AND l_shipdate < '1996-03-01' 20000 60210 TABLE SCAN part WHERE BLOOM(p_partkey)