Planner — TPC-H — TPCH-Q13

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
DuckDB	1,649,945 rows 1.6M	1,599,945 rows 1.6M	48 rows 48	1,599,945 rows 1.6M	1,749,945 rows 1.7M	0 rows 0
PostgreSQL	1,649,944 rows 1.6M	1,599,944 rows 1.6M	1,749,992 rows 1.7M	1,599,944 rows 1.6M	2,049,836 rows 2M	0 rows 0
SQL Server	1,649,945 rows 1.6M	150,000 rows 150K	48 rows 48	150,000 rows 150K	1,649,945 rows 1.6M	0 rows 0

Actual Query Plans

Query Plan

Query Plan per Engine ?
	Query Plan
PostgreSQL	`Estimate Actual Operator 200 48 SORT COUNT(), c_count 200 48 GROUP BY SORT c_count AGGREGATE COUNT() 150000 150000 SORT c_count 150000 150000 GROUP BY SORT c_custkey AGGREGATE COUNT(o_orderkey) 600000 299892 GROUP BY SORT c_custkey AGGREGATE PARTIALCOUNT(o_orderkey) 1935292 1599944 SORT c_custkey 1935292 1599944 RIGHT OUTER JOIN HASH ON o_custkey = c_custkey 187500 150000 │└TABLE SCAN customer 1935292 1499944 TABLE SCAN orders WHERE o_comment NOT LIKE' % special % requests % '`
DuckDB	`Estimate Actual Operator 82990 48 SORT count_star(), c_count 82990 48 GROUP BY HASH #0 AGGREGATE count_star() 124757 150000 PROJECT c_count 124757 150000 PROJECT COUNT(o_orderkey) 124757 150000 GROUP BY HASH #0 AGGREGATE COUNT(#1) 300000 1599945 PROJECT c_custkey, o_orderkey 300000 1599945 RIGHT OUTER JOIN HASH ON o_custkey = c_custkey 150000 150000 │└TABLE SCAN customer 300000 1499945 TABLE SCAN orders WHERE o_comment NOT LIKE' % special % requests % '`
SQL Server	Estimate Actual Operator 25 48 SORT Expr1005, Expr1004 25 48 PROJECT CONVERT_IMPLICIT int,Expr1013,0 AS Expr1005 25 48 GROUP BY HASH AGGREGATE COUNT() AS Expr1013 150000 150000 PROJECT CASE WHEN Expr1004 IS NULL THEN 0 ELSE Expr1004 END AS Expr1004 150000 150000 INNER JOIN HASH ON c_custkey = o_custkey 50198 50000 │└PROJECT CONVERT_IMPLICIT int,Expr1012,0 AS Expr1004 50198 50000 │ GROUP BY HASH AGGREGATE COUNT() AS Expr1012 1499400 1499945 │ TABLE SEEK orders WHERE NOT o_comment LIKE ' % special % requests % ' 150000 150000 TABLE SEEK customer

PostgreSQL

Estimate    Actual  Operator
     200        48  SORT COUNT(*), c_count
     200        48  GROUP BY SORT c_count AGGREGATE COUNT(*)
  150000    150000  SORT c_count
  150000    150000  GROUP BY SORT c_custkey AGGREGATE COUNT(o_orderkey)
  600000    299892  GROUP BY SORT c_custkey AGGREGATE PARTIALCOUNT(o_orderkey)
 1935292   1599944  SORT c_custkey
 1935292   1599944  RIGHT OUTER JOIN HASH ON o_custkey = c_custkey
  187500    150000  │└TABLE SCAN customer
 1935292   1499944  TABLE SCAN orders WHERE o_comment NOT LIKE' % special % requests % '

DuckDB

Estimate    Actual  Operator
   82990        48  SORT count_star(), c_count
   82990        48  GROUP BY HASH #0 AGGREGATE count_star()
  124757    150000  PROJECT c_count
  124757    150000  PROJECT COUNT(o_orderkey)
  124757    150000  GROUP BY HASH #0 AGGREGATE COUNT(#1)
  300000   1599945  PROJECT c_custkey, o_orderkey
  300000   1599945  RIGHT OUTER JOIN HASH ON o_custkey = c_custkey
  150000    150000  │└TABLE SCAN customer
  300000   1499945  TABLE SCAN orders WHERE o_comment NOT LIKE' % special % requests % '

SQL Server

Estimate    Actual  Operator
      25        48  SORT Expr1005, Expr1004
      25        48  PROJECT CONVERT_IMPLICIT int,Expr1013,0 AS Expr1005
      25        48  GROUP BY HASH AGGREGATE COUNT(*) AS Expr1013
  150000    150000  PROJECT CASE WHEN Expr1004 IS NULL THEN 0 ELSE Expr1004 END AS Expr1004
  150000    150000  INNER JOIN HASH ON c_custkey = o_custkey
   50198     50000  │└PROJECT CONVERT_IMPLICIT int,Expr1012,0 AS Expr1004
   50198     50000  │ GROUP BY HASH AGGREGATE COUNT(*) AS Expr1012
 1499400   1499945  │ TABLE SEEK orders WHERE  NOT o_comment LIKE ' % special % requests % '
  150000    150000  TABLE SEEK customer