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 272,228 272K Rank	Estimation Error Est Err Row Operations Ops 242,358 242K Rank	Estimation Error Est Err Row Operations Ops 15,560 16K Rank	Estimation Error Est Err Row Operations Ops 36,132 36K Rank	Estimation Error Est Err Row Operations Ops 36,132 36K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 988,701 989K Rank	Estimation Error Est Err Row Operations Ops 1,105,162 1.1M Rank	Estimation Error Est Err Row Operations Ops 18,149 18K Rank	Estimation Error Est Err Row Operations Ops 424,818 425K Rank	Estimation Error Est Err Row Operations Ops 81,838 82K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 580,359 580K Rank	Estimation Error Est Err Row Operations Ops 547,296 547K Rank	Estimation Error Est Err Row Operations Ops 99,989 100K Rank	Estimation Error Est Err Row Operations Ops 81,840 82K Rank	Estimation Error Est Err Row Operations Ops 81,840 82K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 952,684 953K Rank	Estimation Error Est Err Row Operations Ops 1,039,240 1M Rank	Estimation Error Est Err Row Operations Ops 18,149 18K Rank	Estimation Error Est Err Row Operations Ops 321,078 321K Rank	Estimation Error Est Err Row Operations Ops 163,655 164K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 15560 PROJECT COUNT(DISTINCT ps_suppkey) - 15560 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size - 15560 GROUP BY HASH p_brand, p_type, p_size AGGREGATE COUNT(DISTINCT ps_suppkey) - 36132 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%') - 242358 TABLE SCAN partsupp WHERE ps_suppkey IN (SELECT s_suppkey FROM tpch.supplier WHERE s_comment LIKE '%Customer%Complaints%')
DuckDB	Estimate Actual Operator - 18149 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size 6408 18149 GROUP BY HASH #0, #1, #2 AGGREGATE COUNT(DISTINCT #3) 6409 81838 PROJECT p_brand, p_type, p_size, ps_suppkey 6409 81838 FILTER NOT SUBQUERY 32047 119620 INNER JOIN HASH ON ps_suppkey = #0 2000 3159 │└TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 32047 119620 INNER JOIN HASH ON ps_partkey = p_partkey 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) 800000 799964 TABLE SCAN partsupp
PostgreSQL	Estimate Actual Operator 16214 18149 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size 16214 18149 GROUP BY SORT p_brand, p_type, p_size AGGREGATE COUNT(DISTINCT ps_suppkey) 79140 81840 SORT p_brand, p_type, p_size, ps_suppkey 79140 81840 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')) 516128 547296 TABLE SCAN partsupp WHERE NOT (IN(ps_suppkey = (hashed SubPlan 1) .col1)) 3838 3159 TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%'
SQL Server	Estimate Actual Operator 19532 18149 SORT Expr1007, p_brand, p_type, p_size 19532 18149 PROJECT CONVERT_IMPLICIT(int,Expr1010,0) AS Expr1007 19532 18149 GROUP BY HASH AGGREGATE COUNT(ps_suppkey) AS Expr1010 92536 81817 GROUP BY HASH AGGREGATE 92552 81838 INNER JOIN HASH ON ps_suppkey = s_suppkey 2647 3159 │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%' 125866 119620 LEFT ANTI JOIN LOOP ON s_comment LIKE '%Customer%Complaints%' 1 119620 │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%' 125866 119620 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) 800000 800000 TABLE SCAN partsupp