Planner — TPC-H — TPCH-Q19

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Seek Seek	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
ClickHouse	Estimation Error Est Err Row Operations Ops 132,985 133K Rank		Estimation Error Est Err Row Operations Ops 132,509 133K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 476 476 Rank	Estimation Error Est Err Row Operations Ops 120 120 Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 132,136 132K Rank		Estimation Error Est Err Row Operations Ops 511 511 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 131,625 132K Rank	Estimation Error Est Err Row Operations Ops 129 129 Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 1,700,048 1.7M Rank		Estimation Error Est Err Row Operations Ops 214,377 214K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 214,377 214K Rank	Estimation Error Est Err Row Operations Ops 127 127 Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,021 1K Rank	Estimation Error Est Err Row Operations Ops 511 511 Rank	Estimation Error Est Err Row Operations Ops 511 511 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 510 510 Rank	Estimation Error Est Err Row Operations Ops 129 129 Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 829 829 Rank		Estimation Error Est Err Row Operations Ops 318 318 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank	Estimation Error Est Err Row Operations Ops 511 511 Rank	Estimation Error Est Err Row Operations Ops 127 127 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 a1 AS revenue - 1 AGGREGATE SUM(a5) AS a1 - 120 PROJECT l_extendedprice * (1 - l_discount) AS a5 - 120 FILTER ((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) AND (p_size <= 15) AND (l_quantity >= 1)) OR ((p_brand = 'Brand#22') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (l_quantity >= 15) AND (l_quantity <= 25) AND (p_size <= 10)) OR ((p_brand = 'Brand#33') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (l_quantity >= 25) AND (l_quantity <= 35) AND (p_size <= 15)) - 312 PROJECT l_extendedprice, l_discount, l_quantity, p_size, p_brand, p_container - 312 INNER JOIN HASH ON PROJECTION_432.l_partkey = PROJECTION_429.p_partkey - 476 │└PROJECT p_partkey, p_size, p_brand, p_container - 476 │ PROJECT p_partkey, p_size, p_brand, p_container - 476 │ 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) AND (p_size <= 15)) OR ((p_brand = 'Brand#22') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (p_size <= 10)) OR ((p_brand = 'Brand#33') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (p_size <= 15))) - 132509 PROJECT l_partkey, l_extendedprice, l_discount, l_quantity - 132509 PROJECT l_partkey, l_extendedprice, l_discount, l_quantity - 132509 TABLE SCAN lineitem WHERE l_shipmode IN('AIR','AIR REG') AND (l_shipinstruct = 'DELIVER IN PERSON') AND (((l_quantity >= 5) AND (l_quantity <= 15) AND (l_quantity >= 1)) OR ((l_quantity >= 15) AND (l_quantity <= 25)) OR ((l_quantity >= 25) AND (l_quantity <= 35)))
DuckDB	Estimate Actual Operator - 1 AGGREGATE SUM(#0) 314633 127 PROJECT l_extendedprice * (1.00 - l_discount) 314633 127 FILTER ((l_quantity >= 5.00) AND (l_quantity <= 15.00) 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.00) AND (l_quantity <= 25.00) 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.00) AND (l_quantity <= 35.00) 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'))) 314633 214377 INNER JOIN HASH ON l_partkey = p_partkey 200000 200000 │└TABLE SCAN part 300060 214377 FILTER (l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG') 1500304 1500048 TABLE SCAN lineitem WHERE l_shipmode IN('AIR','AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON'
Databricks	Estimate Actual Operator 1 1 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) 12900 127 INNER JOIN HASH ON l_partkey = p_partkey 6000000 131625 │└TABLE SCAN lineitem WHERE (l_quantity >= 5.00BD) AND (l_quantity <= 35.00BD) AND (l_shipinstruct = 'DELIVER IN PERSON'collate UTF8_BINARY) AND in(l_shipmode,'AIR'collate UTF8_BINARY,'AIR REG'collate UTF8_BINARY) 12900 511 DISTRIBUTE GATHER 200000 511 TABLE SCAN part WHERE (p_size >= 1L) AND (((((p_size <= 5L) AND (p_brand = 'Brand#11'collate UTF8_BINARY)) AND in(p_container,'SM CASE'collate UTF8_BINARY,'SM BOX'collate UTF8_BINARY,'SM PACK'collate UTF8_BINARY,'SM PKG'collate UTF8_BINARY)) OR (((p_size <= 10L) AND (p_brand = 'Brand#22'collate UTF8_BINARY)) AND in(p_container,'MED BAG'collate UTF8_BINARY,'MED BOX'collate UTF8_BINARY,'MED PKG'collate UTF8_BINARY,'MED PACK'collate UTF8_BINARY))) OR (((p_size <= 15L) AND (p_...
PostgreSQL	Estimate Actual Operator 1 1 AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) 3 3 AGGREGATE PARTIAL SUM(l_extendedprice * ('1' - l_discount)) 138 126 INNER JOIN LOOP ON l_partkey = p_partkey 594 510 │└TABLE SCAN part AS 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))) 511 511 TABLE SEEK lineitem AS 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...
SQL Server	Estimate Actual Operator 1 1 PROJECT CASE WHEN Expr1013 = 0 THEN NULL ELSE Expr1014 END AS Expr1006 1 1 AGGREGATE COUNT(Expr1007) AS Expr1013, SUM(Expr1007) AS Expr1014 5900 127 INNER JOIN HASH ON l_partkey = p_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... 3099 511 │└TABLE SCAN 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'... 3807 318 PROJECT l_extendedprice * (1. - l_discount) AS Expr1007 3807 318 TABLE SCAN lineitem WHERE (l_shipmode = 'AIR' OR l_shipmode = 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' AND l_quantity >= 5.00 AND l_quantity <= 35.00 AND BLOOM(l_partkey)