Planner — TPC-H — TPCH-Q20

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,720,946 1.7M Rank		Estimation Error Est Err Row Operations Ops 552,941 553K Rank	Estimation Error Est Err Row Operations Ops 376 376 Rank	Estimation Error Est Err Row Operations Ops 800,376 800K Rank	Estimation Error Est Err Row Operations Ops 908,721 909K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 1,716,911 1.7M Rank		Estimation Error Est Err Row Operations Ops 555,267 555K Rank	Estimation Error Est Err Row Operations Ops 158 158 Rank	Estimation Error Est Err Row Operations Ops 1,348,029 1.3M Rank	Estimation Error Est Err Row Operations Ops 1,587,744 1.6M Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 935,208 935K Rank		Estimation Error Est Err Row Operations Ops 956,803 957K Rank	Estimation Error Est Err Row Operations Ops 158 158 Rank	Estimation Error Est Err Row Operations Ops 37,470 37K Rank	Estimation Error Est Err Row Operations Ops 10,030 10K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 18,680 19K Rank	Estimation Error Est Err Row Operations Ops 16,455 16K Rank	Estimation Error Est Err Row Operations Ops 21,328 21K Rank	Estimation Error Est Err Row Operations Ops 158 158 Rank	Estimation Error Est Err Row Operations Ops 12,807 13K Rank	Estimation Error Est Err Row Operations Ops 10,052 10K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 3,239 3.2K Rank		Estimation Error Est Err Row Operations Ops 980 980 Rank	Estimation Error Est Err Row Operations Ops 158 158 Rank	Estimation Error Est Err Row Operations Ops 3,290 3.3K Rank	Estimation Error Est Err Row Operations Ops 324 324 Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 2224 SEQUENCE - 376 ├─PROJECT s_name, s_address - 376 │ SORT s_name - 376 │ PROJECT s_name, s_address - 376 │ PROJECT s_name, s_address - 376 │ INNER JOIN HASH ON PROJECTION_443.s_nationkey = PROJECTION_440.n_nationkey - 1 │ │└PROJECT n_nationkey - 1 │ │ PROJECT n_nationkey - 1 │ │ TABLE SCAN nation WHERE n_name = 'KENYA' - 10000 │ PROJECT s_nationkey, s_name, s_address - 10000 │ PROJECT s_nationkey, s_name, s_address - 10000 │ TABLE SCAN supplier WHERE TRUE - 541794 ├─PROJECT ps_suppkey - 541794 │ FILTER ps_availqty > a10 - 542941 │ PROJECT a10, ps_suppkey, ps_availqty - 542941 │ INNER JOIN HASH ON tuple(PROJECTION_453.l_partkey,PROJECTION_453.l_suppkey) = tuple(PROJECTION_450.ps_partkey,PROJECTION_450.ps_suppkey) - 800000 │ │└PROJECT ps_partkey AS ps_partkey_right, ps_suppkey AS ps_suppkey_right, ps_availqty - 800000 │ │ PROJECT ps_partkey, ps_suppkey, ps_availqty - 800000 │ │ TABLE SCAN partsupp WHERE TRUE - 542941 │ PROJECT ps_partkey, ps_suppkey, a10 - 542941 │ PROJECT l_partkey AS ps_partkey, l_suppkey AS ps_suppkey, 0.5 * a11 AS a10 - 542941 │ AGGREGATE SUM(l_quantity) AS a11 GROUP BY HASH l_partkey, l_suppkey - 908721 │ PROJECT l_quantity, l_partkey, l_suppkey, l_shipdate, l_partkey, l_suppkey - 908721 │ TABLE SCAN lineitem WHERE (l_shipdate >= '1993-01-01') AND (l_shipdate < '1994-01-01') AND (l_suppkey = l_suppkey) AND (l_partkey = l_partkey) AND TRUE - 2224 └─PROJECT p_partkey - 2224 PROJECT p_partkey - 2224 TABLE SCAN part WHERE startsWith(p_name,'almond')
DuckDB	Estimate Actual Operator - 158 SORT s_name 76 158 PROJECT s_name, s_address 76 158 RIGHT SEMI INNER JOIN HASH ON #3 = s_suppkey 384 376 │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation WHERE n_name = 'KENYA' 10000 10000 │ TABLE SCAN supplier 160000 6028 FILTER CAST(ps_availqty AS DECIMAL(38,3)) > SUBQUERY 0 0 RIGHT SEMI INNER JOIN HASH ON ps_suppkey IS NOT DISTINCT FROM ps_suppkey AND ps_partkey IS NOT DISTINCT FROM ps_partkey 159972 8896 │└AGGREGATE GROUP BY HASH #11, #3 0 8896 │ RIGHT OUTER JOIN HASH ON ps_suppkey IS NOT DISTINCT FROM ps_suppkey AND ps_partkey IS NOT DISTINCT FROM ps_partkey - 0 │ │└SCAN EMPTY 251663 6043 │ PROJECT qty, ps_suppkey, ps_partkey 251663 6043 │ AGGREGATE SUM(#2) GROUP BY HASH #0, #1 503327 10030 │ PROJECT ps_suppkey, ps_partkey, l_quantity 503327 10030 │ INNER JOIN HASH ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey 159972 0 │ │└SCAN MATERIALISED 1200243 908623 │ TABLE SCAN lineitem WHERE l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01' 160000 8896 LEFT SEMI JOIN HASH ON ps_partkey = #3 40000 2224 │└TABLE SCAN part WHERE p_name >= 'almond' AND p_name < 'almone' 800000 14360 TABLE SCAN partsupp
Databricks	Estimate Actual Operator 400 158 SORT s_name ASC NULLS FIRST 400 158 DISTRIBUTE HASH ON s_name ASC NULLS FIRST 400 158 LEFT SEMI JOIN HASH ON s_suppkey = ps_suppkey 400 270 │└INNER JOIN HASH ON s_nationkey = n_nationkey 10000 5965 │ │└TABLE SCAN supplier 400 1 │ DISTRIBUTE GATHER 25 1 │ TABLE SCAN nation WHERE n_name = 'KENYA'collate UTF8_BINARY 400 6028 DISTRIBUTE GATHER 116000 6028 LEFT SEMI JOIN HASH ON ps_partkey = p_inner.p_partkey 116000 541794 │└LEFT SEMI JOIN HASH ON ps_partkey = li_inner.l_partkey 116000 800000 │ │└DISTRIBUTE HASH ON ps_partkey, ps_suppkey 6000000 800000 │ │ TABLE SCAN partsupp 116000 542941 │ DISTRIBUTE GATHER 1 542941 │ FILTER qty IS NOT NULL 868000 542941 │ AGGREGATE SUM(li_inner.l_quantity) GROUP BY HASH li_inner.l_partkey, li_inner.l_suppkey 868000 679023 │ DISTRIBUTE HASH ON li_inner.l_partkey, li_inner.l_suppkey 800000 679023 │ AGGREGATE SUM(li_inner.l_quantity) GROUP BY HASH li_inner.l_partkey, li_inner.l_suppkey 200000 908721 │ TABLE SCAN lineitem WHERE (li_inner.l_shipdate >= DATE'1993-01-01') AND (li_inner.l_shipdate < DATE'1994-01-01') 116000 2224 DISTRIBUTE GATHER 800000 2224 TABLE SCAN part WHERE startswith(p_inner.p_name,'almond'collate UTF8_BINARY)
PostgreSQL	Estimate Actual Operator 108 158 SORT s_name 108 158 RIGHT SEMI INNER JOIN HASH ON ps_suppkey = s_suppkey 400 376 │└INNER JOIN LOOP ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation AS nation WHERE n_name = 'KENYA' 400 376 │ TABLE SEEK supplier AS supplier 2693 6028 INNER JOIN LOOP ON ps_partkey = p_partkey 2020 2224 │└TABLE SCAN part AS p_inner WHERE p_inner.p_name LIKE 'almond%' 2224 6027 LEFT SEMI JOIN LOOP ON (li_inner.l_partkey = ps_partkey) AND (li_inner.l_suppkey = ps_suppkey) 2224 6027 │└TABLE SEEK partsupp AS partsupp WHERE ps_availqty > (SubPlan 1) 8896 8896 AGGREGATE 0.5 * SUM(l_quantity) 8896 10052 TABLE SEEK lineitem AS li_inner WHERE (li_inner.l_shipdate >= '1993-01-01') AND (li_inner.l_shipdate < '1994-01-01')
SQL Server	Estimate Actual Operator 52 158 SORT s_name 52 158 INNER JOIN HASH ON ps_suppkey = s_suppkey 400 376 │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation WHERE n_name = 'KENYA' 1000 376 │ TABLE SCAN supplier WHERE BLOOM(s_nationkey) 139 201 INNER JOIN HASH ON ps_partkey = p_inner.p_partkey 2266 2224 │└TABLE SCAN part AS p_inner WHERE p_name as p_name LIKE 'almond%' 121 201 INNER JOIN HASH ON l_partkey as l_partkey = ps_partkey AND l_suppkey as l_suppkey = ps_suppkey AND ps_availqty > 0.5 * Expr1013 800 314 │└TABLE SCAN partsupp WHERE BLOOM(ps_partkey) AND BLOOM(ps_suppkey) 91 202 AGGREGATE SUM(l_quantity as l_quantity) AS Expr1013 GROUP BY HASH l_partkey, l_suppkey 91 324 TABLE SCAN lineitem AS li_inner WHERE l_shipdate as l_shipdate >= '1993-01-01' AND l_shipdate as l_shipdate < '1994-01-01' AND BLOOM(l_partkey as l_partkey) AND BLOOM(l_suppkey as l_suppkey)