Planner — TPC-H — TPCH-Q08

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 6,619,932 6.6M Rank		Estimation Error Est Err Row Operations Ops 6,623,797 6.6M Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 868,417 868K Rank	Estimation Error Est Err Row Operations Ops 2,647 2.6K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 6,607,232 6.6M Rank		Estimation Error Est Err Row Operations Ops 694,976 695K Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 6,090,855 6.1M Rank	Estimation Error Est Err Row Operations Ops 2,649 2.6K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DataFusion	Estimation Error Est Err Row Operations Ops 7,861,270 7.9M Rank		Estimation Error Est Err Row Operations Ops 7,884,900 7.9M Rank	Estimation Error Est Err Row Operations Ops 4 4 Rank	Estimation Error Est Err Row Operations Ops 2,023,427 2M Rank	Estimation Error Est Err Row Operations Ops 2,667 2.7K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 6,041,438 6M Rank		Estimation Error Est Err Row Operations Ops 6,056,314 6.1M Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 104,959 105K Rank	Estimation Error Est Err Row Operations Ops 2,647 2.6K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 101,107 101K Rank	Estimation Error Est Err Row Operations Ops 99,653 100K Rank	Estimation Error Est Err Row Operations Ops 115,237 115K Rank	Estimation Error Est Err Row Operations Ops 2,646 2.6K Rank	Estimation Error Est Err Row Operations Ops 101,429 101K Rank	Estimation Error Est Err Row Operations Ops 2,646 2.6K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 60,525 61K Rank		Estimation Error Est Err Row Operations Ops 68,194 68K Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 107,601 108K Rank	Estimation Error Est Err Row Operations Ops 2,647 2.6K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Trino	Estimation Error Est Err Row Operations Ops 7,861,270 7.9M Rank		Estimation Error Est Err Row Operations Ops 1,243,226 1.2M Rank	Estimation Error Est Err Row Operations Ops 2 2 Rank	Estimation Error Est Err Row Operations Ops 7,358,709 7.4M Rank	Estimation Error Est Err Row Operations Ops 370,105 370K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
Trino	Estimate Actual Operator - 2 PROJECT expr_gid AS o_year, expr_14 AS mkt_share - 2 SORT expr_gid - 2 PROJECT expr_gid, sum / sum_13 AS expr_14 - 2 AGGREGATE SUM(sum_15) AS sum, SUM(sum_16) AS sum_13 GROUP BY HASH expr_gid 546494 2 DISTRIBUTE HASH ON expr_gid 546494 2 AGGREGATE SUM(expr_12) AS sum_15, SUM(expr_11) AS sum_16 GROUP BY PARTIAL expr_gid 546494 370103 PROJECT expr_11, CASE WHEN (n_name_8 = 'FRANCE') THEN expr_11 ELSE 0.0 END AS expr_12, EXTRACT(YEAR FROM o_orderdate) AS expr_gid 546494 370103 PROJECT n_name_8, l_extendedprice * (1.0 - l_discount) AS expr_11, o_orderdate 546494 370103 INNER JOIN HASH ON l_suppkey = s_suppkey 546494 370103 │└INNER JOIN HASH ON l_partkey = p_partkey 546494 370103 │ │└INNER JOIN HASH ON l_orderkey = o_orderkey 6001215 6001215 │ │ │└TABLE SCAN lineitem 136595 92715 │ │ DISTRIBUTE HASH ON o_orderkey 136595 92715 │ │ INNER JOIN HASH ON o_custkey = c_custkey 1500000 457263 │ │ │└FILTER o_orderdate BETWEEN '1995-01-01' AND '1996-12-31' 1500000 1500000 │ │ │ TABLE SCAN orders 30000 30197 │ │ DISTRIBUTE HASH ON c_custkey 30000 30197 │ │ INNER JOIN HASH ON c_nationkey = n_nationkey 150000 150000 │ │ │└TABLE SCAN customer 5 5 │ │ DISTRIBUTE HASH ON n_nationkey 5 5 │ │ INNER JOIN HASH ON n_regionkey = r_regionkey 25 25 │ │ │└TABLE SCAN nation 1 1 │ │ DISTRIBUTE GATHER 5 1 │ │ FILTER r_name = 'EUROPE' 5 5 │ │ TABLE SCAN region 200000 200000 │ DISTRIBUTE GATHER 200000 200000 │ TABLE SCAN part 10000 10000 DISTRIBUTE HASH ON s_suppkey 10000 10000 INNER JOIN HASH ON s_nationkey = n_nationkey_7 10000 10000 │└TABLE SCAN supplier 25 25 DISTRIBUTE GATHER 25 25 PROJECT n_nationkey AS n_nationkey_7, n_name AS n_name_8 25 25 TABLE SCAN nation
SQL Server	Estimate Actual Operator 2 2 SORT Expr1022 2 2 PROJECT Expr1024 / Expr1025 AS Expr1026 2 2 AGGREGATE SUM(Expr1051) AS Expr1024, SUM(Expr1027) AS Expr1025 GROUP BY HASH Expr1022 3012 2647 PROJECT CASE WHEN n_name as n_name = 'FRANCE' THEN l_extendedprice * (1. - l_discount) ELSE 0.0000 END AS Expr1051 3012 2647 INNER JOIN HASH ON n1.n_regionkey = r_regionkey 1 1 │└TABLE SCAN region WHERE r_name = 'EUROPE' 1506 2647 INNER JOIN HASH ON s_nationkey = n2.n_nationkey 25 25 │└TABLE SCAN nation AS n2 1506 2647 INNER JOIN HASH ON c_nationkey = n1.n_nationkey 2 5 │└TABLE SCAN nation AS n1 WHERE BLOOM(n_regionkey as n_regionkey) 1506 2647 INNER JOIN HASH ON s_suppkey = l_suppkey 1506 2647 │└INNER JOIN HASH ON c_custkey = o_custkey 15060 12905 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 42117 42054 │ │ │└INNER JOIN HASH ON l_partkey = p_partkey 1403 1403 │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' 60012 42054 │ │ │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1027 60012 42054 │ │ │ TABLE SCAN lineitem WHERE BLOOM(l_partkey) 45706 12714 │ │ PROJECT datepart(year,o_orderdate) AS Expr1022 45706 12714 │ │ TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31' AND BLOOM(o_orderkey) 1500 2432 │ TABLE SCAN customer WHERE BLOOM(c_custkey) AND BLOOM(c_nationkey) 1000 1891 TABLE SCAN supplier WHERE BLOOM(s_suppkey)
DuckDB	Estimate Actual Operator - 2 SORT all_nations.o_year 1588 2 PROJECT o_year, mkt_share - 2 AGGREGATE SUM(#1), SUM(#2) GROUP BY HASH #0 1757 2647 PROJECT o_year, CASE WHEN (nation = 'FRANCE') THEN volume ELSE 0.0000 END, volume 1757 2647 PROJECT o_year, volume, nation 1757 2647 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 1827 2647 INNER JOIN HASH ON s_suppkey = l_suppkey 1827 2647 │└INNER JOIN HASH ON c_nationkey = n_nationkey 5 5 │ │└INNER JOIN HASH ON n_regionkey = r_regionkey 1 1 │ │ │└TABLE SCAN region WHERE r_name = 'EUROPE' 25 25 │ │ TABLE SCAN nation 9502 12905 │ INNER JOIN HASH ON c_custkey = o_custkey 9003 12905 │ │└INNER JOIN HASH ON o_orderkey = l_orderkey 44646 42054 │ │ │└INNER JOIN HASH ON l_partkey = p_partkey 1419 1403 │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' 6001215 6000555 │ │ │ TABLE SCAN lineitem 300000 17277 │ │ TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31' 150000 12152 │ TABLE SCAN customer WHERE c_custkey <= 149999 10000 10000 TABLE SCAN supplier
ClickHouse	Estimate Actual Operator - 2 PROJECT o_year, a2 / a3 AS mkt_share - 2 SORT o_year - 2 PROJECT o_year - 2 AGGREGATE sumIf(volume,a9) AS a2, SUM(volume) AS a3 GROUP BY HASH o_year - 2647 PROJECT EXTRACT(YEAR FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n_name = 'FRANCE' AS a9 - 2647 PROJECT l_extendedprice, l_discount, o_orderdate, n_name - 2647 INNER JOIN HASH ON PROJECTION_222.n_nationkey = PROJECTION_183.s_nationkey - 2647 │└PROJECT s_nationkey, l_extendedprice, l_discount, o_orderdate - 2647 │ PROJECT l_extendedprice, l_discount, o_orderdate, s_nationkey - 2647 │ INNER JOIN HASH ON PROJECTION_219.s_suppkey = PROJECTION_186.l_suppkey - 2647 │ │└PROJECT l_suppkey, l_extendedprice, l_discount, o_orderdate - 2647 │ │ PROJECT l_extendedprice, l_discount, l_suppkey, o_orderdate - 2647 │ │ INNER JOIN HASH ON PROJECTION_216.p_partkey = PROJECTION_189.l_partkey - 370103 │ │ │└PROJECT l_partkey, l_extendedprice, l_discount, l_suppkey, o_orderdate - 370103 │ │ │ PROJECT l_extendedprice, l_discount, l_partkey, l_suppkey, o_orderdate - 370103 │ │ │ INNER JOIN HASH ON PROJECTION_213.l_orderkey = PROJECTION_192.o_orderkey - 92715 │ │ │ │└PROJECT o_orderkey, o_orderdate - 92715 │ │ │ │ PROJECT o_orderdate, o_orderkey - 92715 │ │ │ │ INNER JOIN HASH ON PROJECTION_210.o_custkey = PROJECTION_195.c_custkey - 30197 │ │ │ │ │└PROJECT c_custkey - 30197 │ │ │ │ │ PROJECT c_custkey - 30197 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_207.c_nationkey = PROJECTION_198.n_nationkey - 5 │ │ │ │ │ │└PROJECT n_nationkey - 5 │ │ │ │ │ │ PROJECT n_nationkey - 5 │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_204.n_regionkey = PROJECTION_201.r_regionkey - 1 │ │ │ │ │ │ │└PROJECT r_regionkey - 1 │ │ │ │ │ │ │ PROJECT r_regionkey - 1 │ │ │ │ │ │ │ TABLE SCAN region WHERE r_name = 'EUROPE' - 25 │ │ │ │ │ │ PROJECT n_regionkey, n_nationkey - 25 │ │ │ │ │ │ PROJECT n_nationkey, n_regionkey - 25 │ │ │ │ │ │ TABLE SCAN nation - 150000 │ │ │ │ │ PROJECT c_nationkey, c_custkey - 150000 │ │ │ │ │ PROJECT c_custkey, c_nationkey - 150000 │ │ │ │ │ TABLE SCAN customer - 457263 │ │ │ │ PROJECT o_custkey, o_orderdate, o_orderkey - 457263 │ │ │ │ PROJECT o_orderkey, o_orderdate, o_custkey - 457263 │ │ │ │ TABLE SCAN orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate <= '1996-12-31') - 6001215 │ │ │ PROJECT l_orderkey, l_extendedprice, l_discount, l_partkey, l_suppkey - 6001215 │ │ │ PROJECT l_extendedprice, l_discount, l_orderkey, l_partkey, l_suppkey - 6001215 │ │ │ TABLE SCAN lineitem - 1403 │ │ PROJECT p_partkey - 1403 │ │ PROJECT p_partkey - 1403 │ │ TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' - 10000 │ PROJECT s_suppkey, s_nationkey - 10000 │ PROJECT s_suppkey, s_nationkey - 10000 │ TABLE SCAN supplier - 25 PROJECT n_nationkey, n_name - 25 PROJECT n_name, n_nationkey - 25 TABLE SCAN nation
PostgreSQL	Estimate Actual Operator 2406 2 AGGREGATE SUM(CASE WHEN (n_name = 'FRANCE') THEN (l_extendedprice * ('1' - l_discount)) ELSE '0' END) / SUM(l_extendedprice * ('1' - l_discount)) GROUP BY SORT EXTRACT(year FROM o_orderdate) 3015 2646 SORT EXTRACT(year FROM o_orderdate) 1005 882 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation AS n2 3015 2646 INNER JOIN LOOP ON s_suppkey = l_suppkey 1005 882 │└INNER JOIN HASH ON c_nationkey = n_nationkey 5 5 │ │└INNER JOIN HASH ON n_regionkey = r_regionkey 1 1 │ │ │└TABLE SCAN region AS region WHERE r_name = 'EUROPE' 25 25 │ │ TABLE SCAN nation AS n1 15066 12905 │ INNER JOIN LOOP ON c_custkey = o_custkey 15066 12905 │ │└INNER JOIN LOOP ON o_orderkey = l_orderkey 49548 42054 │ │ │└INNER JOIN LOOP ON l_partkey = p_partkey 1650 1403 │ │ │ │└TABLE SCAN part AS part WHERE p_type = 'SMALL POLISHED NICKEL' 43493 42047 │ │ │ TABLE SEEK lineitem AS lineitem 42054 42054 │ │ TABLE SEEK orders AS orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate <= '1996-12-31') 12905 12905 │ TABLE SEEK customer AS customer 2647 2647 TABLE SEEK supplier AS supplier
DataFusion	Estimate Actual Operator 12002 2 SORT o_year 12002 2 SORT o_year 12002 2 PROJECT o_year, mkt_share 12002 2 AGGREGATE SUM(CASE WHEN (nation = 'FRANCE') THEN volume ELSE 0.0000 END), SUM(volume) GROUP BY HASH o_year 12002 20 DISTRIBUTE HASH ON o_year 12002 20 AGGREGATE SUM(CASE WHEN (nation = 'FRANCE') THEN volume ELSE 0.0000 END), SUM(volume) GROUP BY HASH o_year 12002 2647 PROJECT o_year, volume, nation 12002 2647 INNER JOIN HASH ON n_nationkey = s_nationkey 25 25 │└TABLE SCAN nation 12002 2647 INNER JOIN HASH ON r_regionkey = n_regionkey 1 1 │└DISTRIBUTE GATHER 1 1 │ FILTER r_name = 'EUROPE' 5 5 │ DISTRIBUTE ROUND ROBIN 5 5 │ TABLE SCAN region WHERE r_name = 'EUROPE' 60012 12905 PROJECT l_extendedprice, l_discount, o_orderdate, s_nationkey, n_regionkey 60012 12905 INNER JOIN HASH ON n_nationkey = c_nationkey 25 25 │└TABLE SCAN nation WHERE ((n_regionkey >= 3) AND (n_regionkey <= 3)) AND n_regionkey IN 3 60012 12905 PROJECT l_extendedprice, l_discount, o_orderdate, s_nationkey, c_nationkey 60012 12905 INNER JOIN HASH ON c_custkey = o_custkey 150000 150000 │└DISTRIBUTE HASH ON c_custkey 150000 150000 │ TABLE SCAN customer WHERE ((c_nationkey >= 0) AND (c_nationkey <= 24)) AND c_nationkey IN(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) 60012 12905 DISTRIBUTE HASH ON o_custkey 60012 12905 PROJECT l_extendedprice, l_discount, o_custkey, o_orderdate, s_nationkey 60012 12905 INNER JOIN HASH ON s_suppkey = l_suppkey 10000 10000 │└TABLE SCAN supplier WHERE ((s_nationkey >= 0) AND (s_nationkey <= 24)) AND s_nationkey IN(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) 60012 12905 INNER JOIN HASH ON p_partkey = l_partkey 40000 1403 │└DISTRIBUTE GATHER 40000 1403 │ FILTER p_type = 'SMALL POLISHED NICKEL' 200000 200000 │ DISTRIBUTE ROUND ROBIN 200000 200000 │ TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL' 300060 1829418 PROJECT l_partkey, l_suppkey, l_extendedprice, l_discount, o_custkey, o_orderdate 300060 1829418 INNER JOIN HASH ON o_orderkey = l_orderkey 300000 457263 │└DISTRIBUTE HASH ON o_orderkey 300000 457263 │ FILTER (o_orderdate >= DATE'1995-01-01') AND (o_orderdate <= DATE'1996-12-31') 1500000 1500000 │ TABLE SCAN orders WHERE ((o_orderdate >= DATE'1995-01-01') AND (o_orderdate <= DATE'1996-12-31')) AND CASE MOD(HASH_REPARTITION o_custkey,10) WHEN 0 THEN (((o_custkey >= 5) AND (o_custkey <= 149992)) AND TRUE) WHEN 1 THEN (((o_custkey >= 6) AND (o_custkey <= 149997)) AND TRUE) WHEN 2 THEN (((o_custkey >= 10) AND (o_custkey <= 149995)) AND TRUE) WHEN 3 THEN (((o_custkey >= 1) AND (o_custkey <= 150000)) AND TRUE) WHEN 4 THEN (((o_custkey >= 12) AND (o_custkey <= 149998))... 6001215 6001215 DISTRIBUTE HASH ON l_orderkey 6001215 6001215 TABLE SCAN lineitem WHERE (CASE MOD(HASH_REPARTITION l_orderkey,10) WHEN 0 THEN (((l_orderkey >= 7) AND (l_orderkey <= 5999847)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 36) AND (l_orderkey <= 5999970)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 39) AND (l_orderkey <= 5999972)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 1) AND (l_orderkey <= 5999971)) AND TRUE) WHEN 4 THEN (((l_orderkey >= 160) AND (l_orderkey <= 5999969)) AND TRUE) WHEN 5 THEN (((l_orderkey >= 775) AND (l_orderkey ...
Databricks	Estimate Actual Operator 41200 2 SORT all_nations.o_year ASC NULLS FIRST 41200 2 AGGREGATE SUM(casewhen((all_nations.nation = 'FRANCE'collate UTF8_BINARY),all_nations.volume,0.0000BD)), SUM(all_nations.volume) GROUP BY HASH all_nations.o_year 41200 2 DISTRIBUTE HASH ON all_nations.o_year 41200 2 AGGREGATE SUM(casewhen((all_nations.nation = 'FRANCE'collate UTF8_BINARY),all_nations.volume,0.0000BD)), SUM(all_nations.volume) GROUP BY HASH all_nations.o_year 41200 2647 INNER JOIN HASH ON l_suppkey = s_suppkey 41200 2647 │└INNER JOIN HASH ON o_custkey = c_custkey 40700 12874 │ │└INNER JOIN HASH ON l_orderkey = o_orderkey 40700 42054 │ │ │└DISTRIBUTE GATHER 5 42054 │ │ │ INNER JOIN HASH ON l_partkey = p_partkey 5 5993078 │ │ │ │└TABLE SCAN lineitem 5 1403 │ │ │ DISTRIBUTE GATHER 10000 1403 │ │ │ TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL'collate UTF8_BINARY 200000 456028 │ │ TABLE SCAN orders WHERE (o_orderdate >= DATE'1995-01-01') AND (o_orderdate <= DATE'1996-12-31') 41200 30197 │ DISTRIBUTE GATHER 30000 30197 │ INNER JOIN HASH ON n1.n_nationkey = c_nationkey 30000 5 │ │└DISTRIBUTE GATHER 10000 5 │ │ INNER JOIN HASH ON n1.n_regionkey = r_regionkey 150000 5 │ │ │└TABLE SCAN nation 10000 1 │ │ DISTRIBUTE GATHER 25 1 │ │ TABLE SCAN region WHERE r_name = 'EUROPE'collate UTF8_BINARY 25 146692 │ TABLE SCAN customer 41200 10000 DISTRIBUTE GATHER 41200 10000 INNER JOIN HASH ON s_nationkey = n2.n_nationkey 6000000 10000 │└TABLE SCAN supplier 41200 25 DISTRIBUTE GATHER 1500000 25 TABLE SCAN nation

Commentary

Optimal Join Order

The most important optimization for Query 8 is taking the highly selective filter on part ( p_type = 'MEDIUM PLATED BRASS') as early as possible. This reduces the lineitem stream to less than 1% before joining with larger tables like orders.

Bushy Joins

Query 8 presents an opportunity for a bushy join. Instead of joining the main stream directly to customer and then through nation and region, an optimizer can pre-join nation and region (applying the r_name = 'EUROPE' filter) and then join the result. This reduces the total number of join operations.

Join Algorithms

The choice of join algorithm (Hash, Merge, or Loop) affects both performance and memory usage. While Hash joins are typically faster for analytical workloads, some optimizers like SQL Server may choose Merge joins to minimize memory consumption when the runtime is comparable.