Planner — TPC-H — TPCH-Q12

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 1,530,837 1.5M Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 1,500,000 1.5M Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 1,526,820 1.5M Rank	Estimation Error Est Err Row Operations Ops 1,495,983 1.5M Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 30,839 31K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 920,591 921K Rank	Estimation Error Est Err Row Operations Ops 34,818 35K Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,530,837 1.5M Rank	Estimation Error Est Err Row Operations Ops 1,500,000 1.5M Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 30,843 31K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 59,799 60K Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 30,837 31K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 2 PROJECT l_shipmode, a1 AS high_line_count, a2 AS low_line_count - 2 SORT l_shipmode - 2 PROJECT l_shipmode, a1, a2 - 2 AGGREGATE countIf a13 AS a1, countIf a16 AS a2 GROUP BY HASH l_shipmode - 30837 PROJECT l_shipmode, (o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') AS a13, (o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH') AS a16 - 30837 PROJECT l_shipmode, o_orderpriority - 30837 INNER JOIN HASH ON PROJECTION_317.l_orderkey = PROJECTION_314.o_orderkey - 1500000 │└PROJECT o_orderkey, o_orderpriority - 1500000 │ PROJECT o_orderpriority, o_orderkey - 1500000 │ TABLE SCAN orders - 30837 PROJECT l_orderkey, l_shipmode - 30837 PROJECT l_orderkey, l_shipmode - 30837 TABLE SCAN lineitem WHERE (l_commitdate < '1995-01-01') AND (l_shipdate < '1995-01-01') AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01') AND l_shipmode IN('AIR','TRUCK') AND (l_shipdate < l_commitdate) AND (l_commitdate < l_receiptdate)
DuckDB	Estimate Actual Operator - 2 SORT l_shipmode 7 2 AGGREGATE SUM(#1), SUM(#2) GROUP BY HASH #0 242051 30837 PROJECT l_shipmode, CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END, CASE WHEN ((o_orderpriority != '1-URGENT') AND (o_orderpriority != '2-HIGH')) THEN 1 ELSE 0 END 242051 30837 INNER JOIN HASH ON o_orderkey = l_orderkey 240048 30837 │└FILTER (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND ((l_shipmode = 'AIR') OR (l_shipmode = 'TRUCK')) 1200243 885773 │ TABLE SCAN lineitem WHERE l_shipmode IN('AIR','TRUCK') AND l_commitdate < '1995-01-01' AND l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01' AND l_shipdate < '1995-01-01' 1500000 34818 TABLE SCAN orders
Databricks	Estimate Actual Operator 1 2 SORT l_shipmode ASC NULLS FIRST 1 2 AGGREGATE SUM(casewhen(in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY),1,0)), SUM(casewhen(( NOT in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY)),1,0)) GROUP BY HASH l_shipmode 1 2 DISTRIBUTE HASH ON l_shipmode 1 2 AGGREGATE SUM(casewhen(in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY),1,0)), SUM(casewhen(( NOT in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY)),1,0)) GROUP BY HASH l_shipmode 51600 30837 INNER JOIN HASH ON l_orderkey = o_orderkey 51600 30837 │└DISTRIBUTE GATHER 1500000 30837 │ TABLE SCAN lineitem WHERE (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= DATE'1994-01-01') AND (l_receiptdate < DATE'1995-01-01') AND in(l_shipmode,'AIR'collate UTF8_BINARY,'TRUCK'collate UTF8_BINARY) 6000000 1495983 TABLE SCAN orders
PostgreSQL	Estimate Actual Operator 7 2 AGGREGATE SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY SORT l_shipmode 21 6 AGGREGATE PARTIAL SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), PARTIAL SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY SORT l_shipmode 36711 30837 SORT l_shipmode 12237 10279 INNER JOIN HASH ON o_orderkey = l_orderkey 36711 30837 │└TABLE SCAN lineitem AS lineitem WHERE (l_shipmode IN('AIR','TRUCK')) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01') 1875000 1500000 TABLE SCAN orders AS orders
SQL Server	Estimate Actual Operator 2 2 SORT l_shipmode 2 2 AGGREGATE SUM(Expr1008) AS Expr1006, SUM(Expr1009) AS Expr1007 GROUP BY HASH l_shipmode 311180 30837 INNER JOIN HASH ON o_orderkey = l_orderkey 311173 30837 │└TABLE SCAN lineitem WHERE ((l_shipmode = 'AIR' OR l_shipmode = 'TRUCK') AND l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01') AND (l_shipdate < l_commitdate AND l_commitdate < l_receiptdate) 150000 28962 PROJECT CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END AS Expr1008, CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' THEN 1 ELSE 0 END AS Expr1009 150000 28962 TABLE SCAN orders WHERE BLOOM(o_orderkey)