Planner — TPC-H — TPCH-Q12

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
DuckDB	2,334,081 rows 2.3M	1,499,971 rows 1.5M	2 rows 2	29,129 rows 29K	29,129 rows 29K	0 rows 0
PostgreSQL	58,259 rows 58K	29,130 rows 29K	29,130 rows 29K	29,130 rows 29K	29,140 rows 29K	0 rows 0
SQL Server	1,529,129 rows 1.5M	1,500,000 rows 1.5M	2 rows 2	29,129 rows 29K	29,129 rows 29K	0 rows 0

Actual Query Plans

Query Plan

Query Plan per Engine ?
	Query Plan
PostgreSQL	Estimate Actual Operator 7 2 GROUP BY SORT l_shipmode AGGREGATE SUM(CASE WHEN(o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') THEN 1 ELSE 0 END), SUM(CASE WHEN(o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH') THEN 1 ELSE 0 END) 35 10 GROUP BY SORT l_shipmode AGGREGATE PARTIALSUM(CASE WHEN(o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') THEN 1 ELSE 0 END), PARTIALSUM(CASE WHEN(o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH') THEN 1 ELSE 0 END) 33970 29130 SORT l_shipmode 33970 29130 INNER JOIN LOOP ON o_orderkey = l_orderkey 33970 29130 │└TABLE SCAN lineitem WHERE l_shipmode IN('AIR','TRUCK') AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01') 29129 29129 TABLE SEEK orders
DuckDB	Estimate Actual Operator 7 2 SORT l_shipmode 7 2 GROUP BY HASH #0 AGGREGATE SUM(#1), SUM(#2) 242095 29129 PROJECT l_shipmode, CASE WHEN(o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') THEN 1 ELSE 0 END, CASE WHEN(o_orderpriority != '1-URGENT') AND (o_orderpriority != '2-HIGH') THEN 1 ELSE 0 END 242095 29129 INNER JOIN HASH ON o_orderkey = l_orderkey 239952 29129 │└FILTER (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_shipmode = 'AIR') OR (l_shipmode = 'TRUCK') 1199764 834110 │ TABLE SCAN lineitem WHERE l_shipmode IN('AIR','TRUCK') AND l_commitdate < '1995-01-01' AND l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01' AND l_shipdate < '1995-01-01' 1500000 1499971 TABLE SCAN orders
SQL Server	`Estimate Actual Operator 2 2 SORT l_shipmode 2 2 GROUP BY HASH AGGREGATE SUM(Expr1007) AS Expr1005, SUM(Expr1008) AS Expr1006 285854 29129 INNER JOIN HASH ON l_orderkey = o_orderkey 286103 29129 │└TABLE SCAN lineitem 1500000 1500000 PROJECT CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH'THEN 1 ELSE 0 END AS Expr1007, CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH'THEN 1 ELSE 0 END AS Expr1008 1500000 1500000 TABLE SEEK orders`

PostgreSQL

Estimate    Actual  Operator
       7         2  GROUP BY SORT l_shipmode AGGREGATE SUM(CASE WHEN(o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') THEN 1 ELSE 0 END), SUM(CASE WHEN(o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH') THEN 1 ELSE 0 END)
      35        10  GROUP BY SORT l_shipmode AGGREGATE PARTIALSUM(CASE WHEN(o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') THEN 1 ELSE 0 END), PARTIALSUM(CASE WHEN(o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH') THEN 1 ELSE 0 END)
   33970     29130  SORT l_shipmode
   33970     29130  INNER JOIN LOOP ON o_orderkey = l_orderkey
   33970     29130  │└TABLE SCAN lineitem WHERE l_shipmode IN('AIR','TRUCK') AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01')
   29129     29129  TABLE SEEK orders

DuckDB

Estimate    Actual  Operator
       7         2  SORT l_shipmode
       7         2  GROUP BY HASH #0 AGGREGATE SUM(#1), SUM(#2)
  242095     29129  PROJECT l_shipmode, CASE WHEN(o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') THEN 1 ELSE 0 END, CASE WHEN(o_orderpriority != '1-URGENT') AND (o_orderpriority != '2-HIGH') THEN 1 ELSE 0 END
  242095     29129  INNER JOIN HASH ON o_orderkey = l_orderkey
  239952     29129  │└FILTER (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_shipmode = 'AIR') OR (l_shipmode = 'TRUCK')
 1199764    834110  │ TABLE SCAN lineitem WHERE l_shipmode IN('AIR','TRUCK') AND l_commitdate < '1995-01-01' AND l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01' AND l_shipdate < '1995-01-01'
 1500000   1499971  TABLE SCAN orders

SQL Server

Estimate    Actual  Operator
       2         2  SORT l_shipmode
       2         2  GROUP BY HASH AGGREGATE SUM(Expr1007) AS Expr1005, SUM(Expr1008) AS Expr1006
  285854     29129  INNER JOIN HASH ON l_orderkey = o_orderkey
  286103     29129  │└TABLE SCAN lineitem
 1500000   1500000  PROJECT CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH'THEN 1 ELSE 0 END AS Expr1007, CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH'THEN 1 ELSE 0 END AS Expr1008
 1500000   1500000  TABLE SEEK orders