Planner — TPC-H — TPCH-Q19

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 414,265 414K Rank	Estimation Error Est Err Row Operations Ops 214,265 214K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 214,264 214K Rank	Estimation Error Est Err Row Operations Ops 214,264 214K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 1,699,869 1.7M Rank	Estimation Error Est Err Row Operations Ops 214,265 214K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 214,265 214K Rank	Estimation Error Est Err Row Operations Ops 97 97 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,026 1K Rank	Estimation Error Est Err Row Operations Ops 513 513 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 513 513 Rank	Estimation Error Est Err Row Operations Ops 99 99 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 331,301 331K Rank	Estimation Error Est Err Row Operations Ops 131,301 131K 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 97 97 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 SUM(l_extendedprice * (1 - l_discount)) - 1 GROUP BY HASH AGGREGATE SUM(l_extendedprice * (1 - l_discount)) - 214264 PROJECT 1 - l_discount, l_extendedprice * (1 - l_discount) - 214264 FILTER (p_size >= 1) AND l_shipmode IN('AIR','AIR REG') AND (((p_brand = 'Brand#11') AND p_container IN('LG CASE','LG BOX','LG PACK','LG PKG') AND (l_quantity >= 5) AND (l_quantity <= 15_UInt16) AND (p_size <= 5) AND (p_size <= 15_UInt16) AND (l_quantity >= 1)) OR ((p_brand = 'Brand#22') AND p_container IN('LG CASE','LG BOX','LG PACK','LG PKG') AND (l_quantity >= 15) AND (l_quantity <= 25_UInt16) AND (p_size <= 10)) OR ((p_brand = 'Brand#33') AND p_container IN('LG CASE','LG BOX','LG PACK','LG PKG') AND (l_quantity >= 25) AND (l_quantity <= 35_UInt16) AND (p_size <= 15))) AND (l_shipinstruct = 'DELIVER IN PERSON') - 214264 INNER JOIN HASH ON l_partkey = p_partkey - 200000 │└TABLE SCAN part WHERE p_size >= 1 - 214265 TABLE SCAN lineitem WHERE l_shipmode IN ('AIR', 'AIR REG') AND (l_shipinstruct = 'DELIVER IN PERSON')
DuckDB	Estimate Actual Operator - 1 GROUP BY SIMPLE AGGREGATE SUM(#0) 300387 97 PROJECT l_extendedprice * (1.000 - l_discount) 300387 97 FILTER ((l_quantity >= 5.000) AND (l_quantity <= 15.000) AND (p_size <= 5) AND (p_brand = 'Brand#11') AND ((p_container = 'SM CASE') OR (p_container = 'SM BOX') OR (p_container = 'SM PACK') OR (p_container = 'SM PKG'))) OR ((l_quantity >= 15.000) AND (l_quantity <= 25.000) AND (p_size <= 10) AND (p_brand = 'Brand#22') AND ((p_container = 'MED BAG') OR (p_container = 'MED BOX') OR (p_container = 'MED PKG') OR (p_container = 'MED PACK'))) OR ((l_quantity >= 25.000) AND (l_quantity <= 35.000) AND (p_size <= 15) AND (p_brand = 'Brand#33') AND ((p_container = 'LG CASE') OR (p_container = 'LG BOX') OR (p_container = 'LG PACK') OR (p_container = 'LG PKG'))) 300387 214265 INNER JOIN HASH ON l_partkey = p_partkey 200000 200000 │└TABLE SCAN part 299941 214265 FILTER (l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG') 1499705 1499869 TABLE SCAN lineitem WHERE l_shipmode IN('AIR','AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON'
SQL Server	Estimate Actual Operator 1 1 PROJECT CASE WHEN Expr1011 = 0 THEN NULL ELSE Expr1012 END AS Expr1005 1 1 GROUP BY HASH AGGREGATE COUNT(Expr1006) AS Expr1011, SUM(Expr1006) AS Expr1012 5819 97 INNER JOIN HASH ON p_partkey = l_partkey AND (p_brand = 'Brand#11' AND (p_container = 'SM BOX' OR p_container = 'SM CASE' OR p_container = 'SM PACK' OR p_container = 'SM PKG') AND l_quantity >= 5.00 AND l_quantity <= 15.00 AND p_size <= 5 OR p_brand = 'Brand#22' AND (p_container = 'MED BAG' OR p_container = 'MED BOX' OR p_container = 'MED PACK' OR p_container = 'MED PKG') AND l_quantity >= 15.00 AND l_quantity <= 25.00 AND p_size <= 10 OR p_brand = 'Brand#33' AND (p_contai... 3084 200000 │└TABLE SEEK part WHERE (p_size >= 1) AND (p_brand = 'Brand#11' AND (p_container = 'SM BOX' OR p_container = 'SM CASE' OR p_container = 'SM PACK' OR p_container = 'SM PKG') AND p_size <= 5 OR p_brand = 'Brand#22' AND (p_container = 'MED BAG' OR p_container = 'MED BOX' OR p_container = 'MED PACK' OR p_container = 'MED PKG') AND p_size <= 10 OR p_brand = 'Brand#33' AND (p_container = 'LG BOX' OR p_container = 'LG CASE' OR p_container = 'LG PACK' OR p_container = 'LG PKG'... 377357 131301 PROJECT l_extendedprice * (1. - l_discount) AS Expr1006 377357 131301 TABLE SCAN lineitem WHERE l_quantity >= 5.00 AND l_quantity <= 35.00 AND l_shipinstruct = 'DELIVER IN PERSON' AND (l_shipmode = 'AIR' OR l_shipmode = 'AIR REG')
PostgreSQL	Estimate Actual Operator 1 1 GROUP BY SIMPLE AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) 3 3 GROUP BY SIMPLE AGGREGATE PARTIALSUM(l_extendedprice * ('1' - l_discount)) 129 96 INNER JOIN LOOP ON l_partkey = p_partkey 594 513 │└TABLE SCAN part WHERE (p_size >= 1) AND (((p_brand = 'Brand#11') AND (p_container IN('"SM CASE"','"SM BOX"','"SM PACK"','"SM PKG"')) AND (p_size <= 5)) OR ((p_brand = 'Brand#22') AND (p_container IN('"MED BAG"','"MED BOX"','"MED PKG"','"MED PACK"')) AND (p_size <= 10)) OR ((p_brand = 'Brand#33') AND (p_container IN('"LG CASE"','"LG BOX"','"LG PACK"','"LG PKG"')) AND (p_size <= 15))) 513 513 TABLE SEEK lineitem WHERE (l_shipmode IN('AIR','"AIR REG"')) AND (l_shipinstruct = 'DELIVER IN PERSON') AND (((l_quantity >= '5') AND (l_quantity <= '15')) OR ((l_quantity >= '15') AND (l_quantity <= '25')) OR ((l_quantity >= '25') AND (l_quantity <= '35'))) AND (((p_brand = 'Brand#11') AND (p_container IN('"SM CASE"','"SM BOX"','"SM PACK"','"SM PKG"')) AND (l_quantity >= '5') AND (l_quantity <= '15') AND (p_size <= 5)) OR ((p_brand = 'Brand#22') AND (p_container IN('"MED ...