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
DataFusion	Estimation Error Est Err Row Operations Ops 6,201,215 6.2M 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 511 511 Rank	Estimation Error Est Err Row Operations Ops 137 137 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
Trino	Estimation Error Est Err Row Operations Ops 6,201,215 6.2M Rank		Estimation Error Est Err Row Operations Ops 200,000 200K 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 128 128 Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
Trino	Estimate Actual Operator 1 1 PROJECT sum AS revenue 1 1 AGGREGATE SUM(sum_1) AS sum 2255 1 DISTRIBUTE GATHER 2255 1 AGGREGATE SUM(expr) AS sum_1 2255 127 PROJECT l_extendedprice * (1.0 - l_discount) AS expr 2255 127 INNER JOIN HASH ON (l_partkey = p_partkey) AND (((p_brand = 'Brand#11') AND (p_container IN('SM CASE','SM BOX','SM PACK','SM PKG')) AND (l_quantity >= 5.0) AND (l_quantity <= 15.0) AND (p_size BETWEEN 1 AND 5)) OR ((p_brand = 'Brand#22') AND (p_container IN('MED BAG','MED BOX','MED PKG','MED PACK')) AND (l_quantity >= 15.0) AND (l_quantity <= 25.0) AND (p_size BETWEEN 1 AND 10)) OR ((p_brand = 'Brand#33') AND (p_container IN('LG CASE','LG BOX','LG PACK','LG PKG')) AND (l_q... 6001215 214377 │└FILTER (l_shipinstruct = 'DELIVER IN PERSON') AND (l_shipmode IN('AIR','AIR REG')) 6001215 6001215 │ TABLE SCAN lineitem 200000 200000 DISTRIBUTE GATHER 200000 200000 TABLE SCAN part
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)
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'
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)))
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...
DataFusion	Estimate Actual Operator 1 1 PROJECT revenue 1 1 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) 240048 10 DISTRIBUTE GATHER 240048 10 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) 240048 127 INNER JOIN HASH ON p_partkey = l_partkey AND (((((((p_brand = 'Brand#11') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG')) AND (l_quantity >= 5.00)) AND (l_quantity <= 15.00)) AND (p_size <= 5)) OR (((((p_brand = 'Brand#22') AND p_container IN('MED BAG','MED BOX','MED PKG','MED PACK')) AND (l_quantity >= 15.00)) AND (l_quantity <= 25.00)) AND (p_size <= 10))) OR (((((p_brand = 'Brand#33') AND p_container IN('LG CASE','LG BOX','LG PACK','LG PKG')) AND (l_quantity ... 40000 511 │└DISTRIBUTE GATHER 40000 511 │ FILTER (((((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))) AND (p_size >= 1) 200000 200000 │ DISTRIBUTE ROUND ROBIN 200000 200000 │ TABLE SCAN part WHERE (((((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))) AND (p_size >= 1) 1200243 132509 FILTER (((((l_quantity >= 5.00) AND (l_quantity <= 15.00)) OR ((l_quantity >= 15.00) AND (l_quantity <= 25.00))) OR ((l_quantity >= 25.00) AND (l_quantity <= 35.00))) AND ((l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG'))) AND (l_shipinstruct = 'DELIVER IN PERSON') 6001215 6001215 TABLE SCAN lineitem WHERE ((((((l_quantity >= 5.00) AND (l_quantity <= 15.00)) OR ((l_quantity >= 15.00) AND (l_quantity <= 25.00))) OR ((l_quantity >= 25.00) AND (l_quantity <= 35.00))) AND ((l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG'))) AND (l_shipinstruct = 'DELIVER IN PERSON')) AND (((l_partkey >= 109) AND (l_partkey <= 199761)) AND TRUE)
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_...