Planner — TPC-H — TPCH-Q22

Engine Compare

Accuracy chart, rows processed ?
Scan
Scan
Join Probe
Join
Sort
Sort
Hash Build
Hash
Aggregate
Agg
Distribute
Dist
DuckDB
1,576,781 rows
1.6M
1,745,710 rows
1.7M
7 rows
7
236,163 rows
236K
48,823 rows
49K
0 rows
0
PostgreSQL
1,518,412 rows
1.5M
1,500,000 rows
1.5M
12,180 rows
12K
18,412 rows
18K
12,180 rows
12K
0 rows
0
SQL Server
1,576,976 rows
1.6M
1,518,407 rows
1.5M
7 rows
7
58,743 rows
59K
48,817 rows
49K
0 rows
0

Actual Query Plans

Query Plan per Engine ?
Query Plan
PostgreSQL
Estimate    Actual  Operator
    1186         7  GROUP BY SORT "left"(c_phone , 2) AGGREGATE COUNT(*), SUM(c_acctbal)
    1532     12180  SORT "left"(c_phone , 2)
    1532     12180  RIGHT ANTI JOIN HASH ON o_custkey = c_custkey
     729     18412  │└TABLE SCAN customer WHERE (c_acctbal > (InitPlan 1) .col1) AND ("left"(c_phone , 2) IN('10','17','19','23','22','31','27'))
 1935484   1500000  TABLE SCAN orders
DuckDB
Estimate    Actual  Operator
       0         7  SORT cntrycode
       0         7  GROUP BY HASH #0 AGGREGATE count_star(), SUM(#1)
       2     12180  PROJECT cntrycode, c_acctbal
       2     12180  PROJECT cntrycode, c_acctbal
       2         0  RIGHT SEMI INNER JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey
       9     18412  │└GROUP BY HASH #2 AGGREGATE 
       2     12180  │ RIGHT ANTI JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey
       9         0  │ │└SCAN EMPTY
      90    187159  │ PROJECT c_custkey
      90    187159  │ INNER JOIN HASH ON o_custkey = c_custkey
       9         0  │ │└SCAN MATERIALISED
 1500000   1499803  │ TABLE SCAN orders
      10     18412  INNER JOIN LOOP ON CAST(c_acctbal AS DOUBLE) > SUBQUERY
   30000         1  │└GROUP BY SIMPLE AGGREGATE "first" #0, count_star()
   30000         1  │ GROUP BY SIMPLE AGGREGATE avg #0
   30000     36642  │ PROJECT c_acctbal
   30000     36642  │ TABLE SCAN customer WHERE c_acctbal > 0.000 AND ("left"(c_phone, 2) IN('10','17','19','23','22','31','27'))
   30000     40336  TABLE SCAN customer WHERE "left"(c_phone, 2) IN('10','17','19','23','22','31','27')
SQL Server
Estimate    Actual  Operator
     422         7  SORT Expr1009
     422         7  PROJECT CONVERT_IMPLICIT int,Expr1022,0 AS Expr1010
     422         7  GROUP BY HASH AGGREGATE COUNT(*) AS Expr1022, SUM(c_acctbal) AS Expr1011
    3033     12177  INNER JOIN HASH ON o_custkey = c_custkey
    4557     18407  │└INNER JOIN LOOP ON substring(c_phone, 1 , 2) = '27' OR substring(c_phone, 1 , 2) = '31' OR substring(c_phone, 1 , 2) = '22' OR substring(c_phone, 1 , 2) = '23' OR substring(c_phone, 1 , 2) = '19' OR substring(c_phone, 1 , 2) = '17' OR substring(c_phone, 1 , 2) = '10'
   10026     40336  │ │└PROJECT substring(c_phone, 1 , 2) AS Expr1009
   10026     40336  │ │ TABLE SEEK customer WHERE substring(c_phone, 1 , 2) = '27' OR substring(c_phone, 1 , 2) = '31' OR substring(c_phone, 1 , 2) = '22' OR substring(c_phone, 1 , 2) = '23' OR substring(c_phone, 1 , 2) = '19' OR substring(c_phone, 1 , 2) = '17' OR substring(c_phone, 1 , 2) = '10'
       1         1  │ PROJECT CASE WHEN Expr1020 = 0 THEN NULL ELSE Expr1021 / CONVERT_IMPLICIT(decimal 19,0 ,Expr1020,0) END AS Expr1004
       1         1  │ GROUP BY HASH AGGREGATE COUNT(*) AS Expr1020, SUM(c_acctbal) AS Expr1021
    9556     36640  │ TABLE SEEK customer WHERE c_acctbal > 0.00 AND (substring(c_phone, 1 , 2) = '27' OR substring(c_phone, 1 , 2) = '31' OR substring(c_phone, 1 , 2) = '22' OR substring(c_phone, 1 , 2) = '23' OR substring(c_phone, 1 , 2) = '19' OR substring(c_phone, 1 , 2) = '17' OR substring(c_phone, 1 , 2) = '10')
 1500000   1500000  TABLE SEEK orders