Planner — TPC-H — TPCH-Q08

Engine Compare

Accuracy chart, rows processed ?
Scan
Scan
Join Probe
Join
Sort
Sort
Hash Build
Hash
Aggregate
Agg
Distribute
Dist
DuckDB
6,539,428 rows
6.5M
6,548,845 rows
6.5M
2 rows
2
100,578 rows
101K
2,323 rows
2.3K
0 rows
0
PostgreSQL
97,230 rows
97K
109,936 rows
110K
2,322 rows
2.3K
100,590 rows
101K
2,328 rows
2.3K
0 rows
0
SQL Server
6,588,582 rows
6.6M
6,079,412 rows
6.1M
54,959 rows
55K
645,629 rows
646K
2,323 rows
2.3K
0 rows
0

Actual Query Plans

Query Plan per Engine ?
Query Plan
PostgreSQL
Estimate    Actual  Operator
    2234         2  GROUP BY SORT EXTRACT(year FROM o_orderdate) AGGREGATE SUM(CASE WHEN(n_name = 'FRANCE') THEN(l_extendedprice * ('1' - l_discount)) ELSE'0'END) / SUM(l_extendedprice * ('1' - l_discount))
    2793         6  GROUP BY SORT EXTRACT(year FROM o_orderdate) AGGREGATE PARTIALSUM(CASE WHEN(n_name = 'FRANCE') THEN(l_extendedprice * ('1' - l_discount)) ELSE'0'END), PARTIALSUM(l_extendedprice * ('1' - l_discount))
    2793      2322  SORT EXTRACT(year FROM o_orderdate)
    2793      2322  INNER JOIN HASH ON s_nationkey = n_nationkey
      25        25  │└TABLE SCAN n2
    2793      2322  INNER JOIN LOOP ON s_suppkey = l_suppkey
    2799      2322  │└INNER JOIN HASH ON c_nationkey = n_nationkey
      15        15  │ │└INNER JOIN HASH ON n_regionkey = r_regionkey
       1         1  │ │ │└TABLE SCAN region WHERE r_name = 'EUROPE'
      25        25  │ │ TABLE SCAN n1
   14004     11655  │ INNER JOIN LOOP ON c_custkey = o_custkey
   14004     11655  │ │└INNER JOIN LOOP ON o_orderkey = l_orderkey
   49752     40977  │ │ │└INNER JOIN LOOP ON l_partkey = p_partkey
    1659      1362  │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL'
   42222     40860  │ │ │ TABLE SEEK lineitem
   40978     40978  │ │ TABLE SEEK orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate <= '1996-12-31')
   11656     11656  │ TABLE SEEK customer
    2323      2323  TABLE SEEK supplier
DuckDB
Estimate    Actual  Operator
    1752         2  SORT o_year
    1752         2  PROJECT o_year, mkt_share
    1961         2  GROUP BY HASH #0 AGGREGATE SUM(#1), SUM(#2)
    1961      2323  PROJECT o_year, CASE WHEN(nation = 'FRANCE') THEN volume ELSE 0.000000 END, volume
    1961      2323  PROJECT o_year, volume, nation
    1961      2323  INNER JOIN HASH ON s_nationkey = n_nationkey
      25        25  │└TABLE SCAN nation
    2040      2323  INNER JOIN HASH ON s_suppkey = l_suppkey
    1748      2323  │└INNER JOIN HASH ON c_nationkey = n_nationkey
       5         5  │ │└INNER JOIN HASH ON n_regionkey = r_regionkey
       1         1  │ │ │└TABLE SCAN region WHERE r_name = 'EUROPE'
      25         5  │ │ TABLE SCAN nation
    9093      8482  │ INNER JOIN HASH ON c_custkey = o_custkey
    8601     11654  │ │└INNER JOIN HASH ON o_orderkey = l_orderkey
   42624     40975  │ │ │└INNER JOIN HASH ON l_partkey = p_partkey
    1419      1362  │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL'
 5998820   5991648  │ │ │ TABLE SCAN lineitem WHERE l_suppkey >= 1
  300000    428372  │ │ TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31'
  150000    108026  │ TABLE SCAN customer WHERE c_custkey >= 3
   10000      9989  TABLE SCAN supplier
SQL Server
Estimate    Actual  Operator
       2         2  SORT Expr1015
       2         ∞  PROJECT Expr1017 / Expr1018 AS Expr1019
       2         2  PROJECT CASE WHEN Expr1029 = 0 THEN NULL ELSE Expr1030 END AS Expr1017, CASE WHEN Expr1031 = 0 THEN NULL ELSE Expr1032 END AS Expr1018
       2         2  GROUP BY HASH AGGREGATE COUNT(CASE WHEN n_name as n_name = 'FRANCE'THEN l_extendedprice * (1. - l_discount) ELSE 0.0000 END) AS Expr1029, SUM(CASE WHEN n_name as n_name = 'FRANCE'THEN l_extendedprice * (1. - l_discount) ELSE 0.0000 END) AS Expr1030, COUNT(Expr1020) AS Expr1031, SUM(Expr1020) AS Expr1032
    2608      2323  INNER JOIN HASH ON n_nationkey as n_nationkey = s_nationkey
      25        25  │└TABLE SEEK nation
    7793      2323  INNER JOIN MERGE ON s_suppkey = l_suppkey
   10000     10000  │└TABLE SEEK supplier
    2608      2323  SORT l_suppkey
    2608      2323  INNER JOIN HASH ON r_regionkey = n_regionkey as n_regionkey
       1         1  │└TABLE SEEK region WHERE r_name = 'EUROPE'
   13041     11656  INNER JOIN HASH ON n_nationkey as n_nationkey = c_nationkey
      25        25  │└TABLE SEEK nation
   13041     11656  INNER JOIN MERGE ON c_custkey = o_custkey
  150000    149968  │└TABLE SEEK customer
   13053     11656  SORT o_custkey
   13053     11656  INNER JOIN MERGE ON o_orderkey = l_orderkey
  427133    428381  │└PROJECT datepart year,o_orderdate AS Expr1015
  427133    428381  │ TABLE SEEK orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31'
   40629     40978  SORT l_orderkey
   40629     40978  INNER JOIN HASH ON p_partkey = l_partkey
    1339      1362  │└TABLE SEEK part WHERE p_type = 'SMALL POLISHED NICKEL'
 5998820   5998820  PROJECT l_extendedprice * (1. - l_discount) AS Expr1020
 5998820   5998820  TABLE SCAN lineitem