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
DuckDB	788,721 rows 789K	874,962 rows 875K	15,163 rows 15K	364,378 rows 364K	61,214 rows 61K	0 rows 0
PostgreSQL	443,535 rows 444K	410,472 rows 410K	76,379 rows 76K	61,216 rows 61K	61,216 rows 61K	0 rows 0
SQL Server	722,464 rows 722K	778,800 rows 779K	15,163 rows 15K	240,014 rows 240K	122,417 rows 122K	0 rows 0

Actual Query Plans

Query Plan

Query Plan per Engine ?
	Query Plan
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%'
DuckDB	Estimate Actual Operator 4806 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 19500 15163 SORT Expr1007, p_brand, p_type, p_size 19500 15163 PROJECT CONVERT_IMPLICIT int,Expr1010,0 AS Expr1007 19500 15163 GROUP BY HASH AGGREGATE COUNT(ps_suppkey) AS Expr1010 92248 61203 GROUP BY HASH AGGREGATE 92264 61214 INNER JOIN HASH ON ps_suppkey = s_suppkey 2647 3159 │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%' 125475 89400 LEFT ANTI JOIN LOOP ON s_comment LIKE '%Customer%Complaints%' 1 89400 │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%' 125475 89400 INNER JOIN HASH ON p_partkey = ps_partkey 31368 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%'

DuckDB

Estimate    Actual  Operator
    4806     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
   19500     15163  SORT Expr1007, p_brand, p_type, p_size
   19500     15163  PROJECT CONVERT_IMPLICIT int,Expr1010,0 AS Expr1007
   19500     15163  GROUP BY HASH AGGREGATE COUNT(ps_suppkey) AS Expr1010
   92248     61203  GROUP BY HASH AGGREGATE 
   92264     61214  INNER JOIN HASH ON ps_suppkey = s_suppkey
    2647      3159  │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%'
  125475     89400  LEFT ANTI JOIN LOOP ON s_comment LIKE '%Customer%Complaints%'
       1     89400  │└TABLE SEEK supplier WHERE s_comment LIKE '%Customer%Complaints%'
  125475     89400  INNER JOIN HASH ON p_partkey = ps_partkey
   31368     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