Planner — TPC-H — TPCH-Q07

Engine Compare

Accuracy chart, rows processed ?
Scan
Scan
Join Probe
Join
Sort
Sort
Hash Build
Hash
Aggregate
Agg
Distribute
Dist
DuckDB
7,501,210 rows
7.5M
7,992,060 rows
8M
10 rows
10
665,087 rows
665K
12,302 rows
12K
0 rows
0
PostgreSQL
7,658,824 rows
7.7M
8,150,500 rows
8.2M
12,300 rows
12K
642,665 rows
643K
12,300 rows
12K
0 rows
0
SQL Server
7,658,824 rows
7.7M
8,450,318 rows
8.5M
10 rows
10
1,425,334 rows
1.4M
12,302 rows
12K
0 rows
0

Actual Query Plans

Query Plan per Engine ?
Query Plan
PostgreSQL
Estimate    Actual  Operator
   10652        10  GROUP BY SORT n_name, n_name, EXTRACT(year FROM l_shipdate) AGGREGATE SUM(l_extendedprice * ('1' - l_discount))
   16035     12300  SORT n_name, n_name, EXTRACT(year FROM l_shipdate)
   16035     12300  INNER JOIN HASH ON l_suppkey = s_suppkey
    4000      3925  │└INNER JOIN HASH ON s_nationkey = n_nationkey
       2         2  │ │└TABLE SCAN n1 WHERE (n_name = 'GERMANY') OR (n_name = 'FRANCE')
   10000     10000  │ TABLE SCAN supplier
  599880    491680  INNER JOIN HASH ON l_orderkey = o_orderkey
  193550    122855  │└INNER JOIN HASH ON o_custkey = c_custkey
    5000     11905  │ │└INNER JOIN HASH ON c_nationkey = n_nationkey
       2         2  │ │ │└TABLE SCAN n2 WHERE (n_name = 'FRANCE') OR (n_name = 'GERMANY')
   62500    150000  │ │ TABLE SCAN customer
 2419355   1500000  │ TABLE SCAN orders
 7498525   5998820  TABLE SCAN lineitem
DuckDB
Estimate    Actual  Operator
  276088        10  SORT supp_nation, cust_nation, l_year
  276088        10  GROUP BY HASH #0, #1, #2 AGGREGATE SUM(#3)
  276089     12302  PROJECT supp_nation, cust_nation, l_year, volume
  276089     12302  PROJECT supp_nation, cust_nation, l_year, volume
  276089     12302  FILTER (n_name = 'GERMANY') AND (n_name = 'FRANCE') OR (l_shipdate >= '1995-01-01') AND (l_shipdate <= '1996-12-31') AND (n_name = 'FRANCE') AND (n_name = 'GERMANY')
  276089     38642  INNER JOIN HASH ON l_suppkey = s_suppkey
    1923       785  │└INNER JOIN HASH ON s_nationkey = n_nationkey
       5         2  │ │└FILTER (n_name = 'GERMANY') OR (n_name = 'FRANCE')
      25        25  │ │ TABLE SCAN nation WHERE n_name = 'GERMANY' OR n_name = 'FRANCE'
   10000       785  │ TABLE SCAN supplier
 1230510    490900  INNER JOIN HASH ON l_orderkey = o_orderkey
  304964    122856  │└INNER JOIN HASH ON o_custkey = c_custkey
   28846     11904  │ │└INNER JOIN HASH ON c_nationkey = n_nationkey
      25         2  │ │ │└FILTER (n_name = 'FRANCE') OR (n_name = 'GERMANY')
      25        25  │ │ │ TABLE SCAN nation WHERE n_name = 'FRANCE' OR n_name = 'GERMANY'
  150000     11904  │ │ TABLE SCAN customer WHERE c_custkey >= 3
 1500000   1499831  │ TABLE SCAN orders
 5998820   5988640  TABLE SCAN lineitem WHERE l_suppkey >= 1
SQL Server
Estimate    Actual  Operator
       7        10  SORT n_name, n_name, Expr1011
       7        10  PROJECT CASE WHEN Expr1019 = 0 THEN NULL ELSE Expr1020 END AS Expr1013
       7        10  GROUP BY HASH AGGREGATE COUNT(Expr1014) AS Expr1019, SUM(Expr1014) AS Expr1020
  129307     12302  INNER JOIN HASH ON n_nationkey as n_nationkey = c_nationkey AND (n_name as n_name = 'GERMANY' AND n_name as n_name = 'FRANCE' OR n_name as n_name = 'FRANCE' AND n_name as n_name = 'GERMANY' AND l_shipdate >= '1995-01-01' AND l_shipdate <= '1996-12-31')
       2         2  │└TABLE SEEK nation WHERE n_name as n_name = 'FRANCE' OR n_name as n_name = 'GERMANY'
  478985    470749  INNER JOIN HASH ON c_custkey = o_custkey
  150000    150000  │└TABLE SEEK customer
  479428    470749  INNER JOIN HASH ON o_orderkey = l_orderkey
  479844    470749  │└INNER JOIN HASH ON s_suppkey = l_suppkey
     800       785  │ │└INNER JOIN HASH ON n_nationkey as n_nationkey = s_nationkey
       2         2  │ │ │└TABLE SEEK nation WHERE n_name as n_name = 'FRANCE' OR n_name as n_name = 'GERMANY'
   10000     10000  │ │ TABLE SEEK supplier
 5998820   5998820  │ PROJECT datepart year,l_shipdate AS Expr1011, l_extendedprice * (1. - l_discount) AS Expr1014
 5998820   5998820  │ TABLE SCAN lineitem
 1500000   1500000  TABLE SEEK orders