Planner — TPC-H — TPCH-Q18

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
DuckDB	6,710,479 rows 6.7M	561,666 rows 562K	5 rows 5	150,038 rows 150K	5,998,855 rows 6M	0 rows 0
PostgreSQL	13,647,640 rows 14M	7,498,825 rows 7.5M	5,998,860 rows 6M	150,040 rows 150K	7,500,950 rows 7.5M	0 rows 0
SQL Server	11,997,650 rows 12M	5,998,830 rows 6M	5 rows 5	45 rows 45	5,998,855 rows 6M	0 rows 0

Actual Query Plans

Query Plan

Query Plan per Engine ?
	Query Plan
PostgreSQL	Estimate Actual Operator 1999602 5 SORT o_totalprice, o_orderdate 1999602 5 GROUP BY SORT c_custkey, o_orderkey AGGREGATE c_name, o_orderdate, o_totalprice, SUM(l_quantity) 1999602 35 SORT c_custkey, o_orderkey 1999602 35 INNER JOIN HASH ON l_orderkey = o_orderkey 147762 5 │└INNER JOIN HASH ON o_custkey = c_custkey 150000 150000 │ │└TABLE SCAN customer 147762 5 │ INNER JOIN HASH ON o_orderkey = l_orderkey 147762 5 │ │└GROUP BY SORT l_orderkey AGGREGATE 2216435 1502095 │ │ GROUP BY SORT l_orderkey AGGREGATE PARTIALSUM(l_quantity) 7498525 5998820 │ │ SORT l_orderkey 7498525 5998820 │ │ TABLE SCAN lineitem_1 1500000 1500000 │ TABLE SCAN orders 5998820 5998820 TABLE SCAN lineitem
DuckDB	Estimate Actual Operator 1279731 5 SORT o_totalprice, o_orderdate 1279731 5 GROUP BY HASH #0, #1, #2, #3, #4 AGGREGATE SUM(#5) 1279731 35 PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, l_quantity 1279731 35 INNER JOIN HASH ON l_orderkey = o_orderkey 317162 5 │└INNER JOIN HASH ON o_custkey = c_custkey 150000 149998 │ │└TABLE SCAN customer WHERE c_custkey >= 3 300000 5 │ LEFT SEMI JOIN HASH ON o_orderkey = #0 145107 5 │ │└FILTER SUM(l_quantity) > 314.000 725537 1500000 │ │ GROUP BY HASH #0 AGGREGATE sum_no_overflow #1 5998820 5998820 │ │ PROJECT l_orderkey, l_quantity 5998820 5998820 │ │ TABLE SCAN lineitem 1500000 162448 │ TABLE SCAN orders 5998820 399213 TABLE SCAN lineitem
SQL Server	Estimate Actual Operator 282 5 SORT o_totalprice, o_orderdate 282 5 INNER JOIN LOOP ON o_custkey = c_custkey 1 5 │└TABLE SEEK customer 282 5 INNER JOIN LOOP ON l_orderkey = o_orderkey 1 5 │└TABLE SEEK orders 282 5 PROJECT CASE WHEN Expr1024 = 0 THEN NULL ELSE Expr1025 END AS Expr1011 282 5 GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1024, SUM(l_quantity) AS Expr1025, IN(l_orderkey) AS l_orderkey 1276 35 INNER JOIN HASH ON l_orderkey = l_orderkey 282 5 │└FILTER Expr1010 > 314.00 1328270 1500000 │ PROJECT CASE WHEN Expr1022 = 0 THEN NULL ELSE Expr1023 END AS Expr1010 1328270 1500000 │ GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1022, SUM(l_quantity) AS Expr1023 5998820 5998820 │ TABLE SCAN lineitem 5998820 5998820 TABLE SCAN lineitem

PostgreSQL

Estimate    Actual  Operator
 1999602         5  SORT o_totalprice, o_orderdate
 1999602         5  GROUP BY SORT c_custkey, o_orderkey AGGREGATE c_name, o_orderdate, o_totalprice, SUM(l_quantity)
 1999602        35  SORT c_custkey, o_orderkey
 1999602        35  INNER JOIN HASH ON l_orderkey = o_orderkey
  147762         5  │└INNER JOIN HASH ON o_custkey = c_custkey
  150000    150000  │ │└TABLE SCAN customer
  147762         5  │ INNER JOIN HASH ON o_orderkey = l_orderkey
  147762         5  │ │└GROUP BY SORT l_orderkey AGGREGATE 
 2216435   1502095  │ │ GROUP BY SORT l_orderkey AGGREGATE PARTIALSUM(l_quantity)
 7498525   5998820  │ │ SORT l_orderkey
 7498525   5998820  │ │ TABLE SCAN lineitem_1
 1500000   1500000  │ TABLE SCAN orders
 5998820   5998820  TABLE SCAN lineitem

DuckDB

Estimate    Actual  Operator
 1279731         5  SORT o_totalprice, o_orderdate
 1279731         5  GROUP BY HASH #0, #1, #2, #3, #4 AGGREGATE SUM(#5)
 1279731        35  PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, l_quantity
 1279731        35  INNER JOIN HASH ON l_orderkey = o_orderkey
  317162         5  │└INNER JOIN HASH ON o_custkey = c_custkey
  150000    149998  │ │└TABLE SCAN customer WHERE c_custkey >= 3
  300000         5  │ LEFT SEMI JOIN HASH ON o_orderkey = #0
  145107         5  │ │└FILTER SUM(l_quantity) > 314.000
  725537   1500000  │ │ GROUP BY HASH #0 AGGREGATE sum_no_overflow #1
 5998820   5998820  │ │ PROJECT l_orderkey, l_quantity
 5998820   5998820  │ │ TABLE SCAN lineitem
 1500000    162448  │ TABLE SCAN orders
 5998820    399213  TABLE SCAN lineitem

SQL Server

Estimate    Actual  Operator
     282         5  SORT o_totalprice, o_orderdate
     282         5  INNER JOIN LOOP ON o_custkey = c_custkey
       1         5  │└TABLE SEEK customer
     282         5  INNER JOIN LOOP ON l_orderkey = o_orderkey
       1         5  │└TABLE SEEK orders
     282         5  PROJECT CASE WHEN Expr1024 = 0 THEN NULL ELSE Expr1025 END AS Expr1011
     282         5  GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1024, SUM(l_quantity) AS Expr1025, IN(l_orderkey) AS l_orderkey
    1276        35  INNER JOIN HASH ON l_orderkey = l_orderkey
     282         5  │└FILTER Expr1010 > 314.00
 1328270   1500000  │ PROJECT CASE WHEN Expr1022 = 0 THEN NULL ELSE Expr1023 END AS Expr1010
 1328270   1500000  │ GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1022, SUM(l_quantity) AS Expr1023
 5998820   5998820  │ TABLE SCAN lineitem
 5998820   5998820  TABLE SCAN lineitem