Planner — TPC-H — TPCH-Q11

Engine Compare

Accuracy chart, rows processed ?
Scan
Scan
Join Probe
Join
Sort
Sort
Hash Build
Hash
Aggregate
Agg
Distribute
Dist
DuckDB
1,194,028 rows
1.2M
1,215,082 rows
1.2M
2,806 rows
2.8K
48,064 rows
48K
44,517 rows
45K
0 rows
0
PostgreSQL
610,001 rows
610K
610,000 rows
610K
25,062 rows
25K
23,740 rows
24K
22,256 rows
22K
0 rows
0
SQL Server
1,220,002 rows
1.2M
1,222,806 rows
1.2M
2,806 rows
2.8K
66,314 rows
66K
44,516 rows
45K
0 rows
0

Actual Query Plans

Query Plan per Engine ?
Query Plan
PostgreSQL
Estimate    Actual  Operator
    7996      2806  SORT SUM(ps_supplycost * ps_availqty)
    7996      2806  GROUP BY SORT ps_partkey AGGREGATE SUM(ps_supplycost * ps_availqty)
   30952     22256  SORT ps_partkey
   30952     22256  INNER JOIN HASH ON ps_suppkey = s_suppkey
    1600      1484  │└INNER JOIN HASH ON s_nationkey = n_nationkey
       1         1  │ │└TABLE SCAN nation WHERE n_name = 'JAPAN'
   10000     10000  │ TABLE SCAN supplier
  774192    600000  TABLE SCAN partsupp
DuckDB
Estimate    Actual  Operator
      51      2806  SORT SUM(ps_supplycost * ps_availqty)
      51      2806  PROJECT ps_partkey, v
      51      2806  INNER JOIN LOOP ON CAST(SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL 18,0)) AS DECIMAL 38,6) > SUBQUERY
       1         1  │└GROUP BY SIMPLE AGGREGATE "first" #0, count_star()
       1         1  │ PROJECT SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL 18,0)) * 0.0001
   26793         1  │ GROUP BY SIMPLE AGGREGATE SUM(#0)
   26793     22258  │ PROJECT ps_supplycost * CAST(ps_availqty AS DECIMAL 18,0)
   26793     22258  │ INNER JOIN HASH ON ps_suppkey = s_suppkey
     384       371  │ │└INNER JOIN HASH ON s_nationkey = n_nationkey
       1         1  │ │ │└TABLE SCAN nation WHERE n_name = 'JAPAN'
   10000       371  │ │ TABLE SCAN supplier
  600000    596642  │ TABLE SCAN partsupp WHERE ps_suppkey >= 1
   21744     21056  GROUP BY HASH #0 AGGREGATE SUM(#1)
   26793     22258  PROJECT ps_partkey, ps_supplycost * CAST(ps_availqty AS DECIMAL 18,0)
   26793     22258  INNER JOIN HASH ON ps_suppkey = s_suppkey
     384       371  │└INNER JOIN HASH ON s_nationkey = n_nationkey
       1         1  │ │└TABLE SCAN nation WHERE n_name = 'JAPAN'
   10000       371  │ TABLE SCAN supplier
  600000    596642  TABLE SCAN partsupp WHERE ps_suppkey >= 1
SQL Server
Estimate    Actual  Operator
    6778      2806  SORT Expr1007
    6778      2806  INNER JOIN LOOP ON n_name = 'JAPAN'
   22596     21056  │└PROJECT CASE WHEN Expr1029 = 0 THEN NULL ELSE Expr1030 END AS Expr1007
   22596     21056  │ GROUP BY HASH AGGREGATE COUNT(Expr1017) AS Expr1029, SUM(Expr1017) AS Expr1030
   23997     22258  │ INNER JOIN HASH ON s_suppkey = ps_suppkey
     400       371  │ │└INNER JOIN HASH ON n_nationkey = s_nationkey
       1         1  │ │ │└TABLE SEEK nation WHERE n_name = 'JAPAN'
   10000     10000  │ │ TABLE SEEK supplier
  600000    600000  │ PROJECT ps_supplycost * CONVERT_IMPLICIT(decimal 10,0 ,ps_availqty,0) AS Expr1017
  600000    600000  │ TABLE SCAN partsupp
       1         1  PROJECT CASE WHEN Expr1027 = 0 THEN NULL ELSE Expr1028 END AS Expr1015
       1         1  GROUP BY HASH AGGREGATE COUNT(Expr1018) AS Expr1027, SUM(Expr1018) AS Expr1028
   23997     22258  INNER JOIN HASH ON s_suppkey = ps_suppkey
     400       371  │└INNER JOIN HASH ON n_nationkey = s_nationkey
       1         1  │ │└TABLE SEEK nation WHERE n_name = 'JAPAN'
   10000     10000  │ TABLE SEEK supplier
  600000    600000  PROJECT ps_supplycost * CONVERT_IMPLICIT(decimal 10,0 ,ps_availqty,0) AS Expr1018
  600000    600000  TABLE SCAN partsupp