Planner — TPC-H — TPCH-Q16

/* TPC-H Q16 */
SELECT p_brand,
       p_type,
       p_size,
       COUNT(DISTINCT ps_suppkey) AS supplier_cnt
FROM tpch_sf1.partsupp
INNER JOIN tpch_sf1.part
    ON ps_partkey = p_partkey

WHERE p_brand <> 'Brand#42'
  AND p_type NOT LIKE 'STANDARD ANODIZED%'
  AND p_size IN (3, 7, 11, 29, 31, 37, 41, 49)
  AND ps_suppkey NOT IN (SELECT s_suppkey
                         FROM tpch_sf1.supplier
                         WHERE s_comment LIKE '%Customer%Complaints%')
GROUP BY p_brand,
         p_type,
         p_size
ORDER BY supplier_cnt DESC,
         p_brand,
         p_type,
         p_size

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Seek Seek	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 829,779 830K Rank		Estimation Error Est Err 919,100 919K Rank	Estimation Error Est Err 18,282 18K Rank	Estimation Error Est Err 238,143 238K Rank	Estimation Error Est Err 238,056 238K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 829,779 830K Rank		Estimation Error Est Err 800,000 800K Rank	Estimation Error Est Err 18,282 18K Rank	Estimation Error Est Err 119,100 119K Rank	Estimation Error Est Err 119,100 119K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 826,251 826K Rank		Estimation Error Est Err 1,592,626 1.6M Rank	Estimation Error Est Err 18,282 18K Rank	Estimation Error Est Err 915,197 915K Rank	Estimation Error Est Err 392,293 392K Rank	Estimation Error Est Err 184,016 184K Rank
Estimation Error Est Err 1,010,000 1M Rank		Estimation Error Est Err 238,200 238K Rank	Estimation Error Est Err 36,564 37K Rank	Estimation Error Est Err 919,043 919K Rank	Estimation Error Est Err 442,640 443K Rank	Estimation Error Est Err 563,459 563K Rank
Estimation Error Est Err 985,560 986K Rank		Estimation Error Est Err 1,104,648 1.1M Rank	Estimation Error Est Err 18,282 18K Rank	Estimation Error Est Err 423,748 424K Rank	Estimation Error Est Err 119,043 119K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 829,471 829K Rank		Estimation Error Est Err 1,599,360 1.6M Rank	Estimation Error Est Err 137,325 137K Rank	Estimation Error Est Err 829,455 829K Rank	Estimation Error Est Err 119,043 119K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 829,772 830K Rank	Estimation Error Est Err 799,993 800K Rank	Estimation Error Est Err 919,093 919K Rank	Estimation Error Est Err 167,070 167K Rank	Estimation Error Est Err 238,143 238K Rank	Estimation Error Est Err 119,013 119K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 148,879 149K Rank		Estimation Error Est Err 238,200 238K Rank	Estimation Error Est Err 18,282 18K Rank	Estimation Error Est Err 238,200 238K Rank	Estimation Error Est Err 453,336 453K Rank	Estimation Error Est Err 245,002 245K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 2728 18282 SORT COUNT(ps_suppkey), p_brand35, p_type36, p_size37 2728 18282 AGGREGATE count GROUP BY HASH p_brand31, p_type32, p_size33 106556 119013 GROUP BY HASH p_brand, p_type, p_size, ps_suppkey 118396 119043 LEFT ANTI JOIN HASH ON ps_suppkey = s_suppkey 9 4 │└TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 118552 119100 INNER JOIN HASH ON ps_partkey = p_partkey 31054 29775 │└TABLE SCAN part WHERE p_size BETWEEN 3 AND 49 AND p_brand != Brand#42 AND p_size IN(3,7,11,29,31,37,41,49) 800000 800000 TABLE SCAN partsupp
	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%'
	Estimate Actual Operator 6718 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
	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 2000 4 │└FILTER s_comment LIKE '%Customer%Complaints%' 10000 10000 │ DISTRIBUTE ROUND ROBIN 10000 10000 │ TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 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 800000 800000 │└TABLE SCAN partsupp WHERE ((ps_partkey >= 1) AND (ps_partkey <= 199998)) AND TRUE 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)
	Estimate Actual Operator 48B 18282 SORT supplier_cnt DESC NULLS LAST, p_brand ASC NULLS FIRST, p_type ASC NULLS FIRST, p_size ASC NULLS FIRST 48B 18282 AGGREGATE COUNT(DISTINCT ps_suppkey) GROUP BY HASH p_brand, p_type, p_size 48B 35216 DISTRIBUTE HASH ON p_brand, p_type, p_size 800000 35216 AGGREGATE COUNT(DISTINCT ps_suppkey) GROUP BY HASH p_brand, p_type, p_size 800000 119013 GROUP BY HASH p_brand, p_type, p_size, ps_suppkey 800000 119021 DISTRIBUTE HASH ON p_brand, p_type, p_size, ps_suppkey 800000 119021 GROUP BY HASH p_brand, p_type, p_size, ps_suppkey 48B 119043 INNER JOIN HASH ON ps_partkey = p_partkey 48B 29775 │└DISTRIBUTE GATHER 800000 29775 │ TABLE SCAN part WHERE ( NOT (p_brand = 'Brand#42')) AND (p_size IN(3,7,11,29,31,37,41,49)) AND ( NOT startswith(p_type,'STANDARD ANODIZED')) 800000 796154 LEFT ANTI JOIN HASH ON ps_suppkey = s_suppkey 800000 4 │└DISTRIBUTE GATHER 200000 4 │ TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 10000 796472 TABLE SCAN partsupp
	Estimate Actual Operator 19495 18282 SORT Expr1009, p_brand, p_type, p_size 19495 18282 PROJECT CONVERT_IMPLICIT(int,Expr1016,0) AS Expr1009 19495 18282 AGGREGATE COUNT(*) AS Expr1016 GROUP BY SORT p_brand, p_type, p_size 125272 119013 SORT p_brand, p_type, p_size, ps_suppkey 125304 119043 RIGHT ANTI JOIN HASH ON ps_suppkey = s_suppkey 1 4 │└TABLE SCAN supplier WHERE s_comment LIKE '%Customer%Complaints%' 125328 119100 INNER JOIN MERGE ON p_partkey = ps_partkey 31197 29775 │└SORT p_partkey 31197 29775 │ TABLE SCAN part WHERE (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) AND (p_brand <> 'Brand#42' AND NOT p_type LIKE 'STANDARD ANODIZED%') 800000 799993 TABLE SEEK partsupp
	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_9) AS count GROUP BY HASH p_brand, p_type, p_size - 96210 DISTRIBUTE HASH ON p_brand, p_type, p_size - 96210 AGGREGATE COUNT(ps_suppkey) AS count_9 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 - 119100 LEFT ANTI JOIN HASH ON ps_suppkey = s_suppkey 10000 4 │└DISTRIBUTE GATHER 10000 4 │ FILTER s_comment LIKE '%Customer%Complaints%' 10000 4 │ TABLE SCAN supplier - 119100 INNER JOIN HASH ON ps_partkey = p_partkey 90000 29775 │└DISTRIBUTE GATHER 90000 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%') 90000 29775 │ TABLE SCAN part 800000 119100 TABLE SCAN partsupp
	Estimate Actual Operator 15946 18282 SORT COUNT(DISTINCT ps_suppkey), p_brand, p_type, p_size 15946 18282 AGGREGATE COUNT(DISTINCT ps_suppkey) GROUP BY SORT p_brand, p_type, p_size 76136 119043 SORT p_brand, p_type, p_size, ps_suppkey 19034 29760 INNER JOIN HASH ON ps_partkey = p_partkey 49168 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')) 516128 799680 LEFT ANTI JOIN LOOP ON ps_suppkey = s_suppkey 516128 799680 │└TABLE SCAN partsupp AS partsupp WHERE NOT (IN(ps_suppkey = (hashed SubPlan 1) .col1)) 1 16 TABLE SCAN supplier AS supplier WHERE s_comment LIKE '%Customer%Complaints%'