Planner — TPC-H — TPCH-Q10

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 1,686,369 1.7M Rank		Estimation Error Est Err Row Operations Ops 1,708,970 1.7M Rank	Estimation Error Est Err Row Operations Ops 38,182 38K Rank	Estimation Error Est Err Row Operations Ops 380,100 380K Rank	Estimation Error Est Err Row Operations Ops 115,050 115K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 1,681,513 1.7M Rank		Estimation Error Est Err Row Operations Ops 1,681,488 1.7M Rank	Estimation Error Est Err Row Operations Ops 26,475 26K Rank	Estimation Error Est Err Row Operations Ops 229,998 230K Rank	Estimation Error Est Err Row Operations Ops 157,817 158K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DataFusion	Estimation Error Est Err Row Operations Ops 7,651,240 7.7M Rank		Estimation Error Est Err Row Operations Ops 1,708,970 1.7M Rank	Estimation Error Est Err Row Operations Ops 76,364 76K Rank	Estimation Error Est Err Row Operations Ops 380,100 380K Rank	Estimation Error Est Err Row Operations Ops 115,050 115K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 220,628 221K Rank		Estimation Error Est Err Row Operations Ops 220,603 221K Rank	Estimation Error Est Err Row Operations Ops 38,182 38K Rank	Estimation Error Est Err Row Operations Ops 215,372 215K Rank	Estimation Error Est Err Row Operations Ops 115,050 115K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 322,447 322K Rank	Estimation Error Est Err Row Operations Ops 114,948 115K Rank	Estimation Error Est Err Row Operations Ops 284,208 284K Rank	Estimation Error Est Err Row Operations Ops 153,232 153K Rank	Estimation Error Est Err Row Operations Ops 191,682 192K Rank	Estimation Error Est Err Row Operations Ops 115,050 115K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 210,731 211K Rank		Estimation Error Est Err Row Operations Ops 345,150 345K Rank	Estimation Error Est Err Row Operations Ops 38,182 38K Rank	Estimation Error Est Err Row Operations Ops 345,150 345K Rank	Estimation Error Est Err Row Operations Ops 115,050 115K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Trino	Estimation Error Est Err Row Operations Ops 7,651,240 7.7M Rank		Estimation Error Est Err Row Operations Ops 345,150 345K Rank	Estimation Error Est Err Row Operations Ops 38,182 38K Rank	Estimation Error Est Err Row Operations Ops 1,743,920 1.7M Rank	Estimation Error Est Err Row Operations Ops 153,232 153K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
Trino	Estimate Actual Operator 76522 38182 PROJECT c_custkey, c_name, sum AS revenue, c_acctbal, n_name, c_address, c_phone, c_comment 76522 38182 SORT sum 76522 38182 AGGREGATE SUM(sum_3) AS sum GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 76522 38182 DISTRIBUTE HASH ON c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 76522 38182 AGGREGATE SUM(expr) AS sum_3 GROUP BY PARTIAL c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 76522 115050 PROJECT l_extendedprice * (1.0 - l_discount) AS expr, c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 76522 115050 INNER JOIN HASH ON c_nationkey = n_nationkey 76522 115050 │└INNER JOIN HASH ON c_custkey = o_custkey 150000 150000 │ │└TABLE SCAN customer 76522 115050 │ DISTRIBUTE HASH ON o_custkey 76522 115050 │ INNER JOIN HASH ON l_orderkey = o_orderkey 6001215 1478870 │ │└FILTER l_returnflag = 'R' 6001215 6001215 │ │ TABLE SCAN lineitem 57380 57474 │ DISTRIBUTE HASH ON o_orderkey 1500000 57474 │ FILTER (o_orderdate >= '1994-06-01') AND (o_orderdate < '1994-09-01') 1500000 1500000 │ TABLE SCAN orders 25 25 DISTRIBUTE GATHER 25 25 TABLE SCAN nation
SQL Server	Estimate Actual Operator 34460 38182 SORT Expr1012 34460 38182 AGGREGATE SUM(Expr1013) AS Expr1012, c_name, c_acctbal, c_phone, c_address, c_comment GROUP BY HASH c_custkey, n_name 66460 115050 INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 66460 115050 INNER JOIN HASH ON c_custkey = o_custkey 66460 115050 │└INNER JOIN HASH ON l_orderkey = o_orderkey 57385 57474 │ │└TABLE SCAN orders WHERE o_orderdate >= '1994-06-01' AND o_orderdate < '1994-09-01' 148032 115050 │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1013 148032 115050 │ TABLE SCAN lineitem WHERE l_returnflag = 'R' AND BLOOM(l_orderkey) 15000 38182 TABLE SCAN customer WHERE BLOOM(c_custkey)
DuckDB	Estimate Actual Operator - 38182 SORT SUM(l_extendedprice * (1 - l_discount)) 409397 38182 PROJECT c_custkey, c_name, revenue, c_acctbal, n_name, c_address, c_phone, c_comment 409397 38182 AGGREGATE SUM(#7) GROUP BY HASH #0, #1, #2, #3, #4, #5, #6 409397 115050 PROJECT c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment, l_extendedprice * (1.00 - l_discount) 409397 115050 INNER JOIN HASH ON l_orderkey = o_orderkey 304444 57474 │└INNER JOIN HASH ON c_custkey = o_custkey 300000 57474 │ │└TABLE SCAN orders WHERE o_orderdate >= '1994-06-01' AND o_orderdate < '1994-09-01' 144230 42848 │ INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │ │└TABLE SCAN nation 150000 42848 │ TABLE SCAN customer WHERE c_custkey <= 149999 2000405 120281 TABLE SCAN lineitem WHERE l_returnflag = 'R'
ClickHouse	Estimate Actual Operator - 38182 PROJECT c_custkey, c_name, a1 AS revenue, c_acctbal, n_name, c_address, c_phone, c_comment - 38182 SORT a1 - 38182 PROJECT a1, c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 38182 AGGREGATE SUM(a5) AS a1 GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment - 115050 PROJECT c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment, l_extendedprice * (1 - l_discount) AS a5 - 115050 PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, n_name - 115050 INNER JOIN HASH ON PROJECTION_272.c_nationkey = PROJECTION_269.n_nationkey - 25 │└PROJECT n_nationkey, n_name - 25 │ PROJECT n_name, n_nationkey - 25 │ TABLE SCAN nation - 115050 PROJECT c_nationkey, l_extendedprice, l_discount, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment - 115050 PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, c_nationkey - 115050 INNER JOIN HASH ON PROJECTION_278.o_custkey = PROJECTION_275.c_custkey - 150000 │└PROJECT c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, c_nationkey - 150000 │ PROJECT c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, c_nationkey - 150000 │ TABLE SCAN customer - 115050 PROJECT o_custkey, l_extendedprice, l_discount - 115050 PROJECT l_extendedprice, l_discount, o_custkey - 115050 INNER JOIN HASH ON PROJECTION_284.l_orderkey = PROJECTION_281.o_orderkey - 57474 │└PROJECT o_orderkey, o_custkey - 57474 │ PROJECT o_orderkey, o_custkey - 57474 │ TABLE SCAN orders WHERE (o_orderdate >= '1994-06-01') AND (o_orderdate < '1994-09-01') - 1478870 PROJECT l_orderkey, l_extendedprice, l_discount - 1478870 PROJECT l_orderkey, l_extendedprice, l_discount - 1478870 TABLE SCAN lineitem WHERE l_returnflag = 'R'
PostgreSQL	Estimate Actual Operator 55777 38182 SORT SUM(l_extendedprice * ('1' - l_discount)) 55777 38182 AGGREGATE c_name, SUM(l_extendedprice * ('1' - l_discount)), c_acctbal, c_address, c_phone, c_comment GROUP BY SORT c_custkey, n_name 69720 115050 SORT c_custkey, n_name 69720 115050 INNER JOIN LOOP ON l_orderkey = o_orderkey 23211 19158 │└INNER JOIN HASH ON c_nationkey = n_nationkey 25 25 │ │└TABLE SCAN nation AS nation 23211 19158 │ INNER JOIN HASH ON c_custkey = o_custkey 69633 57474 │ │└TABLE SCAN orders AS orders WHERE (o_orderdate >= '1994-06-01') AND (o_orderdate < '1994-09-01') 187500 150000 │ TABLE SCAN customer AS customer 172422 114948 TABLE SEEK lineitem AS lineitem WHERE l_returnflag = 'R'
DataFusion	Estimate Actual Operator 300000 38182 SORT revenue 300000 38182 SORT revenue 300000 38182 PROJECT c_custkey, c_name, revenue, c_acctbal, n_name, c_address, c_phone, c_comment 300000 38182 AGGREGATE SUM(l_extendedprice * (1 - l_discount)) GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 300000 115050 PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_address, c_phone, c_acctbal, c_comment, n_name 300000 115050 INNER JOIN HASH ON n_nationkey = c_nationkey 25 25 │└TABLE SCAN nation 300000 115050 PROJECT l_extendedprice, l_discount, c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_comment 300000 115050 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) 300000 115050 DISTRIBUTE HASH ON o_custkey 300000 115050 PROJECT l_extendedprice, l_discount, o_custkey 300000 115050 INNER JOIN HASH ON o_orderkey = l_orderkey 300000 57474 │└DISTRIBUTE HASH ON o_orderkey 300000 57474 │ FILTER (o_orderdate >= DATE'1994-06-01') AND (o_orderdate < DATE'1994-09-01') 1500000 1500000 │ TABLE SCAN orders WHERE ((o_orderdate >= DATE'1994-06-01') AND (o_orderdate < DATE'1994-09-01')) 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)) ... 1200243 1478870 DISTRIBUTE HASH ON l_orderkey 1200243 1478870 FILTER l_returnflag = 'R' 6001215 6001215 TABLE SCAN lineitem WHERE (l_returnflag = 'R') AND CASE MOD(HASH_REPARTITION l_orderkey,10) WHEN 0 THEN (((l_orderkey >= 5) AND (l_orderkey <= 5999588)) AND TRUE) WHEN 1 THEN (((l_orderkey >= 64) AND (l_orderkey <= 5999394)) AND TRUE) WHEN 2 THEN (((l_orderkey >= 69) AND (l_orderkey <= 5998789)) AND TRUE) WHEN 3 THEN (((l_orderkey >= 161) AND (l_orderkey <= 5999812)) AND TRUE) WHEN 4 THEN (((l_orderkey >= 131) AND (l_orderkey <= 5999045)) AND TRUE) WHEN 5 THEN (((l_orderke...
Databricks	Estimate Actual Operator 237000 26475 SORT revenue DESC NULLS LAST 237000 26475 DISTRIBUTE HASH ON revenue DESC NULLS LAST 237000 38182 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 237000 42767 DISTRIBUTE HASH ON c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 237000 42767 AGGREGATE SUM(l_extendedprice * (1BD - l_discount)) GROUP BY HASH c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 237000 115050 INNER JOIN HASH ON o_orderkey = l_orderkey 237000 57474 │└DISTRIBUTE GATHER 58500 57474 │ INNER JOIN HASH ON c_nationkey = n_nationkey 58500 57474 │ │└INNER JOIN HASH ON o_custkey = c_custkey 58500 57474 │ │ │└DISTRIBUTE GATHER 6000000 57474 │ │ │ TABLE SCAN orders WHERE (o_orderdate >= DATE'1994-06-01') AND (o_orderdate < DATE'1994-09-01') 25 147059 │ │ TABLE SCAN customer 58500 25 │ DISTRIBUTE GATHER 150000 25 │ TABLE SCAN nation 1500000 1476955 TABLE SCAN lineitem WHERE l_returnflag = 'R'collate UTF8_BINARY

Commentary

Histograms and Estimation

Correct estimation of the filter on o_orderdate is crucial for picking the optimal join order. Without histograms in the statistics, optimizers may mis-estimate the selectivity, which can lead to picking a "bad bushy" join that actually increases the total number of operations and memory usage.

Functional Dependency

By identifying functional dependencies between columns, an optimizer can realize that grouping by c_custkey is sufficient to satisfy the GROUP BY clause. This allows for aggregating the data earlier in the plan, before joining with the customer and nation tables.

Bloom Filters

Query 10 is an ideal candidate for Bloom filters. By building a Bloom filter on the filtered orders table, the engine can significantly reduce the number of rows scanned from the large lineitem table (often by 6x or more), effectively transferring the filter from one table to another during the scan.