Planner — TPC-H — TPCH-Q16

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 829,779 830K Rank		Estimation Error Est Err Row Operations Ops 800,000 800K Rank	Estimation Error Est Err Row Operations Ops 18,282 18K Rank	Estimation Error Est Err Row Operations Ops 119,100 119K Rank	Estimation Error Est Err Row Operations Ops 119,100 119K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 826,251 826K Rank		Estimation Error Est Err Row Operations Ops 238,143 238K Rank	Estimation Error Est Err Row Operations Ops 18,282 18K Rank	Estimation Error Est Err Row Operations Ops 915,572 916K Rank	Estimation Error Est Err Row Operations Ops 392,301 392K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 985,560 986K Rank		Estimation Error Est Err Row Operations Ops 1,104,648 1.1M Rank	Estimation Error Est Err Row Operations Ops 18,282 18K Rank	Estimation Error Est Err Row Operations Ops 423,748 424K Rank	Estimation Error Est Err Row Operations Ops 119,043 119K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 829,467 829K Rank	Estimation Error Est Err Row Operations Ops 799,680 800K Rank	Estimation Error Est Err Row Operations Ops 1,599,360 1.6M Rank	Estimation Error Est Err Row Operations Ops 137,325 137K Rank	Estimation Error Est Err Row Operations Ops 839,361 839K Rank	Estimation Error Est Err Row Operations Ops 119,043 119K Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 148,879 149K Rank		Estimation Error Est Err Row Operations Ops 238,143 238K Rank	Estimation Error Est Err Row Operations Ops 18,282 18K Rank	Estimation Error Est Err Row Operations Ops 238,086 238K Rank	Estimation Error Est Err Row Operations Ops 119,043 119K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 4 SEQUENCE - 18282 ├─PROJECT p_brand, p_type, p_size, a1 AS supplier_cnt - 18282 │ SORT a1, p_brand, p_type, p_size - 18282 │ PROJECT a1, p_brand, p_type, p_size - 18282 │ AGGREGATE COUNT(DISTINCT ps_suppkey) AS a1 GROUP BY HASH p_brand, p_type, p_size - 119100 │ PROJECT p_brand, p_type, p_size, ps_suppkey - 119100 │ PROJECT ps_suppkey, p_brand, p_type, p_size - 119100 │ INNER JOIN HASH ON PROJECTION_370.ps_partkey = PROJECTION_367.p_partkey - 29775 │ │└PROJECT p_partkey, p_brand, p_type, p_size - 29775 │ │ PROJECT p_partkey, p_size, p_type, p_brand - 29775 │ │ TABLE SCAN part WHERE (p_brand <> 'Brand#42') AND p_size IN(3,7,11,29,31,37,41,49) AND NOT (startsWith(p_type,'STANDARD ANODIZED')) - 800000 │ PROJECT ps_partkey, ps_suppkey - 800000 │ PROJECT ps_partkey, ps_suppkey - 800000 │ TABLE SCAN partsupp WHERE TRUE - 4 └─PROJECT s_suppkey - 4 PROJECT s_suppkey - 4 TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%'
DuckDB	Estimate Actual Operator - 18282 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size 6718 18282 AGGREGATE COUNT(DISTINCT #3) GROUP BY HASH #0, #1, #2 6719 119043 PROJECT p_brand, p_type, p_size, ps_suppkey 6719 119043 FILTER NOT SUBQUERY 33597 119100 INNER JOIN HASH ON ps_suppkey = #0 2000 4 │└TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 33597 119100 INNER JOIN HASH ON ps_partkey = p_partkey 8000 29775 │└FILTER IN ... 40000 185548 │ INNER JOIN HASH ON p_size = #0 0 8 │ │└SCAN MATERIALISED 40000 185548 │ 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 800000 TABLE SCAN partsupp
Databricks	Estimate Actual Operator 768 18282 SORT supplier_cnt DESC NULLS LAST, p_brand ASC NULLS FIRST, p_type ASC NULLS FIRST, p_size ASC NULLS FIRST 768 18282 AGGREGATE COUNT(DISTINCT ps_suppkey) GROUP BY HASH p_brand, p_type, p_size 768 35224 DISTRIBUTE HASH ON p_brand, p_type, p_size 768 35224 AGGREGATE COUNT(DISTINCT ps_suppkey) GROUP BY HASH p_brand, p_type, p_size 768 119013 GROUP BY HASH p_brand, p_type, p_size, ps_suppkey 768 119021 DISTRIBUTE HASH ON p_brand, p_type, p_size, ps_suppkey 768 119021 GROUP BY HASH p_brand, p_type, p_size, ps_suppkey 125000 119043 LEFT ANTI JOIN HASH ON ps_suppkey = s_suppkey 125000 119100 │└INNER JOIN HASH ON ps_partkey = p_partkey 800000 796472 │ │└TABLE SCAN partsupp 125000 29775 │ DISTRIBUTE GATHER 10000 29775 │ TABLE SCAN part WHERE ( NOT (p_brand = 'Brand#42'collate UTF8_BINARY)) AND ( NOT startswith(p_type,'STANDARD ANODIZED'collate UTF8_BINARY)) AND in(p_size,3L,7L,11L,29L,31L,37L,41L,49L) 125000 4 DISTRIBUTE GATHER 200000 4 TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%'collate UTF8_BINARY
PostgreSQL	Estimate Actual Operator 16215 18282 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size 16215 18282 AGGREGATE COUNT(DISTINCT ps_suppkey) GROUP BY SORT p_brand, p_type, p_size 76677 119043 SORT p_brand, p_type, p_size, ps_suppkey 25559 39681 INNER JOIN HASH ON ps_partkey = p_partkey 38340 29775 │└TABLE SCAN part AS 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')) 500001 799680 LEFT ANTI JOIN LOOP ON ps_suppkey = s_suppkey 500001 799680 │└TABLE SEEK partsupp AS partsupp WHERE NOT (IN(ps_suppkey = (hashed SubPlan 1) .col1)) 1 12 TABLE SCAN supplier AS supplier WHERE s_comment LIKE '%Customer%Complaints%'
SQL Server	Estimate Actual Operator 19495 18282 SORT Expr1009, p_brand, p_type, p_size 19495 18282 PROJECT CONVERT_IMPLICIT(int,Expr1014,0) AS Expr1009 19495 18282 AGGREGATE COUNT(DISTINCT ps_suppkey) AS Expr1014 GROUP BY HASH p_brand, p_type, p_size 126118 119043 INNER JOIN HASH ON ps_partkey = p_partkey 31200 29775 │└TABLE SCAN part WHERE NOT p_type LIKE 'STANDARD ANODIZED%' AND p_brand <> 'Brand#42' 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) 79984 119043 INNER JOIN HASH ON ps_suppkey = s_suppkey 1 4 │└TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 80000 119100 TABLE SCAN partsupp WHERE BLOOM(ps_partkey)