Planner — TPC-H — TPCH-Q16

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
ClickHouse	Estimation Error Est Err Row Operations Ops 211,639 212K Rank	Estimation Error Est Err Row Operations Ops 181,769 182K Rank	Estimation Error Est Err Row Operations Ops 12,804 13K Rank	Estimation Error Est Err Row Operations Ops 29,870 30K Rank	Estimation Error Est Err Row Operations Ops 27,055 27K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 788,721 789K Rank	Estimation Error Est Err Row Operations Ops 874,962 875K Rank	Estimation Error Est Err Row Operations Ops 15,163 15K Rank	Estimation Error Est Err Row Operations Ops 364,378 364K Rank	Estimation Error Est Err Row Operations Ops 61,214 61K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 443,535 444K Rank	Estimation Error Est Err Row Operations Ops 410,472 410K Rank	Estimation Error Est Err Row Operations Ops 76,379 76K Rank	Estimation Error Est Err Row Operations Ops 61,216 61K Rank	Estimation Error Est Err Row Operations Ops 61,216 61K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 722,464 722K Rank	Estimation Error Est Err Row Operations Ops 778,800 779K Rank	Estimation Error Est Err Row Operations Ops 15,163 15K Rank	Estimation Error Est Err Row Operations Ops 240,014 240K Rank	Estimation Error Est Err Row Operations Ops 122,417 122K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 12804 PROJECT COUNT(DISTINCT ps_suppkey) - 12804 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size - 12804 GROUP BY HASH p_brand, p_type, p_size AGGREGATE COUNT(DISTINCT ps_suppkey) - 27055 INNER JOIN HASH ON ps_partkey = p_partkey - 29870 │└TABLE SCAN part WHERE (p_brand <> 'Brand#42') AND p_size IN (3, 7, 11, 29, 31, 37, 41, 49) AND notLike(p_type,'STANDARD ANODIZED%') - 181769 TABLE SCAN partsupp WHERE ps_suppkey IN (SELECT s_suppkey FROM tpch.supplier WHERE s_comment LIKE '%Customer%Complaints%')
DuckDB	Estimate Actual Operator - 15163 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size 4806 15163 GROUP BY HASH #0, #1, #2 AGGREGATE COUNT(DISTINCT #3) 4807 61214 PROJECT p_brand, p_type, p_size, ps_suppkey 4807 61214 FILTER NOT SUBQUERY 24035 89400 INNER JOIN HASH ON ps_suppkey = #0 2000 3159 │└TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 24035 89400 INNER JOIN HASH ON ps_partkey = p_partkey 8000 22350 │└FILTER p_partkey <= 150000 8000 29905 │ FILTER IN ... 40000 185578 │ INNER JOIN HASH ON p_size = #0 0 8 │ │└SCAN MATERIALISED 40000 185578 │ TABLE SCAN part WHERE p_brand != 'Brand#42' AND ( NOT prefix(p_type,'STANDARD ANODIZED')) AND p_size IN(3,7,11,29,31,37,41,49) 600000 599984 TABLE SCAN partsupp
SQL Server	Estimate Actual Operator 19546 15163 SORT Expr1007, p_brand, p_type, p_size 19546 15163 PROJECT CONVERT_IMPLICIT(int,Expr1010,0) AS Expr1007 19546 15163 GROUP BY HASH AGGREGATE COUNT(ps_suppkey) AS Expr1010 109004 61203 GROUP BY HASH AGGREGATE 109027 61214 INNER JOIN HASH ON ps_suppkey = s_suppkey 2647 3159 │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%' 148276 89400 LEFT ANTI JOIN LOOP ON s_comment LIKE '%Customer%Complaints%' 1 89400 │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%' 148276 89400 INNER JOIN HASH ON p_partkey = ps_partkey 31466 29905 │└TABLE SEEK part WHERE p_brand <> 'Brand#42' AND NOT p_type LIKE 'STANDARD ANODIZED%' AND (p_size = 3 OR p_size = 7 OR p_size = 11 OR p_size = 29 OR p_size = 31 OR p_size = 37 OR p_size = 41 OR p_size = 49) 600000 600000 TABLE SCAN partsupp
PostgreSQL	Estimate Actual Operator 16214 15163 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size 16214 15163 GROUP BY SORT p_brand, p_type, p_size AGGREGATE COUNT(DISTINCT ps_suppkey) 59352 61216 SORT p_brand, p_type, p_size, ps_suppkey 59352 61216 INNER JOIN HASH ON ps_partkey = p_partkey 51112 29904 │└TABLE SCAN part WHERE (p_brand <> 'Brand#42') AND (p_type NOT LIKE 'STANDARD ANODIZED%') AND (p_size IN('3','7','11','29','31','37','41','49')) 387096 410472 TABLE SCAN partsupp WHERE NOT (IN(ps_suppkey = (hashed SubPlan 1) .col1)) 3838 3159 TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%'