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
DataFusion	Estimation Error Est Err Row Operations Ops 1,010,000 1M Rank		Estimation Error Est Err Row Operations Ops 919,043 919K Rank	Estimation Error Est Err Row Operations Ops 36,564 37K Rank	Estimation Error Est Err Row Operations Ops 238,200 238K Rank	Estimation Error Est Err Row Operations Ops 442,640 443K 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
Trino	Estimation Error Est Err Row Operations Ops 1,010,000 1M Rank		Estimation Error Est Err Row Operations Ops 238,200 238K Rank	Estimation Error Est Err Row Operations Ops 18,282 18K Rank	Estimation Error Est Err Row Operations Ops 919,043 919K Rank	Estimation Error Est Err Row Operations Ops 375,351 375K Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
Trino	Estimate Actual Operator - 18282 PROJECT p_brand, p_type, p_size, count AS supplier_cnt - 18282 SORT count, p_brand, p_type, p_size - 18282 AGGREGATE COUNT(count_5) AS count GROUP BY HASH p_brand, p_type, p_size - 18282 DISTRIBUTE HASH ON p_brand, p_type, p_size - 18282 AGGREGATE COUNT(ps_suppkey) AS count_5 GROUP BY PARTIAL p_brand, p_type, p_size - 119013 GROUP BY HASH p_brand, p_type, p_size, ps_suppkey - 119013 DISTRIBUTE HASH ON p_brand, p_type, p_size, ps_suppkey - 119013 GROUP BY PARTIAL p_brand, p_type, p_size, ps_suppkey 111726 119043 LEFT ANTI JOIN HASH ON ps_suppkey = s_suppkey - 4 │└DISTRIBUTE GATHER 10000 4 │ FILTER s_comment LIKE '%Customer%Complaints%' 10000 10000 │ TABLE SCAN supplier 111726 119100 INNER JOIN HASH ON ps_partkey = p_partkey 800000 800000 │└TABLE SCAN partsupp 27931 29775 DISTRIBUTE GATHER 200000 29775 FILTER (p_brand <> 'Brand#42') AND (p_size IN(3,7,11,29,31,37,41,49)) AND NOT (p_type LIKE 'STANDARD ANODIZED%') 200000 200000 TABLE SCAN part
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)
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
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%'
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%'
DataFusion	Estimate Actual Operator 160000 18282 SORT supplier_cnt, p_brand, p_type, p_size 160000 18282 SORT supplier_cnt, p_brand, p_type, p_size 160000 18282 PROJECT p_brand, p_type, p_size, supplier_cnt 160000 18282 AGGREGATE COUNT(alias1) GROUP BY HASH p_brand, p_type, p_size 160000 85571 DISTRIBUTE HASH ON p_brand, p_type, p_size 160000 85571 AGGREGATE COUNT(alias1) GROUP BY HASH p_brand, p_type, p_size 160000 119013 GROUP BY HASH p_brand, p_type, p_size, alias1 160000 119013 DISTRIBUTE HASH ON p_brand, p_type, p_size, alias1 160000 119013 GROUP BY HASH p_brand, p_type, p_size, alias1 160000 119043 LEFT ANTI JOIN HASH ON ps_suppkey = s_suppkey 160000 119100 │└DISTRIBUTE GATHER 160000 119100 │ PROJECT ps_suppkey, p_brand, p_type, p_size 160000 119100 │ INNER JOIN HASH ON p_partkey = ps_partkey 40000 29775 │ │└DISTRIBUTE GATHER 40000 29775 │ │ FILTER ((p_brand <> 'Brand#42') AND p_type NOT LIKE 'STANDARD ANODIZED%') AND p_size IN(3,7,11,29,31,37,41,49) 200000 200000 │ │ DISTRIBUTE ROUND ROBIN 200000 200000 │ │ 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) 800000 800000 │ TABLE SCAN partsupp WHERE ((ps_partkey >= 1) AND (ps_partkey <= 199998)) AND TRUE 2000 4 FILTER s_comment LIKE '%Customer%Complaints%' 10000 10000 DISTRIBUTE ROUND ROBIN 10000 10000 TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%'
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