Planner — TPC-H — TPCH-Q09

Engine Compare

Accuracy chart, rows processed ?
Scan
Scan
Join Probe
Join
Sort
Sort
Hash Build
Hash
Aggregate
Agg
Distribute
Dist
DuckDB
6,618,379 rows
6.6M
5,598,397 rows
5.6M
175 rows
175
2,033,770 rows
2M
245,491 rows
245K
0 rows
0
PostgreSQL
1,331,548 rows
1.3M
1,582,052 rows
1.6M
245,492 rows
245K
1,014,844 rows
1M
246,192 rows
246K
0 rows
0
SQL Server
8,119,783 rows
8.1M
7,090,006 rows
7.1M
245,841 rows
246K
2,023,977 rows
2M
245,666 rows
246K
0 rows
0

Actual Query Plans

Query Plan per Engine ?
Query Plan
PostgreSQL
Estimate    Actual  Operator
     114       175  GROUP BY SORT n_name, EXTRACT(year FROM o_orderdate) AGGREGATE SUM(l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)
     148       700  GROUP BY SORT n_name, EXTRACT(year FROM o_orderdate) AGGREGATE PARTIALSUM(l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)
     148    245492  SORT n_name, EXTRACT(year FROM o_orderdate)
     148    245492  INNER JOIN HASH ON s_nationkey = n_nationkey
      25        25  │└TABLE SCAN nation
     148    245492  INNER JOIN LOOP ON s_suppkey = l_suppkey
     148    245520  │└INNER JOIN LOOP ON o_orderkey = l_orderkey
     148    245520  │ │└INNER JOIN LOOP ON l_partkey = ps_partkey
   46920     32792  │ │ │└INNER JOIN HASH ON ps_partkey = p_partkey
    5050     10939  │ │ │ │└TABLE SCAN part WHERE p_name LIKE'%lace%'
  774192    600000  │ │ │ TABLE SCAN partsupp
   32792    229544  │ │ TABLE SEEK lineitem WHERE ps_suppkey = l_suppkey
  245520    245520  │ TABLE SEEK orders
  245520    245520  TABLE SEEK supplier
DuckDB
Estimate    Actual  Operator
 3949951       175  SORT nation, o_year
 3949951       175  GROUP BY HASH #0, #1 AGGREGATE SUM(#2)
 4066138    245491  PROJECT nation, o_year, amount
 4066138    245491  PROJECT nation, o_year, amount
 4066138    245491  INNER JOIN HASH ON l_suppkey = s_suppkey
    9615     10000  │└INNER JOIN HASH ON s_nationkey = n_nationkey
      25        25  │ │└TABLE SCAN nation
   10000     10000  │ TABLE SCAN supplier
 1214083    245491  INNER JOIN HASH ON l_orderkey = o_orderkey
 1500000   1500000  │└TABLE SCAN orders
 1203335    245491  INNER JOIN HASH ON l_suppkey = ps_suppkeyl_partkey = p_partkey
  120178     32788  │└INNER JOIN HASH ON ps_partkey = p_partkey
   40000      8198  │ │└FILTER p_partkey <= 150000
   40000     10939  │ │ TABLE SCAN part WHERE contains(p_name,'lace')
  600000    599897  │ TABLE SCAN partsupp WHERE ps_suppkey >= 1
 5998820   4497518  FILTER l_suppkey >= 1
 5998820   4497518  TABLE SCAN lineitem WHERE l_partkey <= 150000
SQL Server
Estimate    Actual  Operator
     175       175  PROJECT CASE WHEN globalagg1019 = 0 THEN NULL ELSE globalagg1021 END AS Expr1016
     175       175  GROUP BY HASH ,  AGGREGATE SUM(partialagg1018) AS globalagg1019, SUM(partialagg1020) AS globalagg1021
     175       175  SORT n_name, Expr1014
     175       175  INNER JOIN MERGE ON n_nationkey = s_nationkey
      25        25  │└TABLE SEEK nation
     175       175  SORT s_nationkey
     175       175  GROUP BY HASH AGGREGATE COUNT(Expr1017 - ps_supplycost * l_quantity) AS partialagg1018, SUM(Expr1017 - ps_supplycost * l_quantity) AS partialagg1020
   32840    245491  INNER JOIN MERGE ON o_orderkey = l_orderkey
 1500000   1499999  │└PROJECT datepart year,o_orderdate AS Expr1014
 1500000   1499999  │ TABLE SEEK orders
   32869    245491  SORT l_orderkey
   32869    245491  INNER JOIN HASH ON 
   10000     10000  │└TABLE SEEK supplier
   32873    245520  INNER JOIN HASH ON l_partkey = p_partkey AND l_suppkey = ps_suppkey
   43279     32792  │└INNER JOIN HASH ON ps_partkey = p_partkey
   10819     10939  │ │└TABLE SEEK part WHERE p_name LIKE '%lace%'
  600000    600000  │ TABLE SCAN partsupp
 5998820   5998820  PROJECT l_extendedprice * (1. - l_discount) AS Expr1017
 5998820   5998820  TABLE SCAN lineitem