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
Trino	Estimation Error Est Err Row Operations Ops 6,201,215 6.2M 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,637 72K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
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
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')
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')
Trino	Estimate Actual Operator 1 1 PROJECT expr_3 AS promo_revenue 1 1 PROJECT (sum * 100.0) / sum_2 AS expr_3 1 1 DISTRIBUTE ROUND ROBIN 1 1 AGGREGATE SUM(sum_4) AS sum, SUM(sum_5) AS sum_2 68924 1 DISTRIBUTE GATHER 68924 1 AGGREGATE SUM(expr) AS sum_4, SUM(expr_1) AS sum_5 68924 71636 PROJECT CASE WHEN (p_type LIKE 'PROMO%') THEN (l_extendedprice * (1.0 - l_discount)) ELSE 0.0 END AS expr, l_extendedprice * (1.0 - l_discount) AS expr_1 68924 71636 INNER JOIN HASH ON p_partkey = l_partkey 200000 200000 │└TABLE SCAN part 68924 71636 DISTRIBUTE HASH ON l_partkey 6001215 71636 FILTER (l_shipdate >= '1996-02-01') AND (l_shipdate < '1996-03-01') 6001215 6001215 TABLE SCAN lineitem
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)