Planner — TPC-H — TPCH-Q20

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
DuckDB	1,242,881 rows 1.2M	1,257,476 rows 1.3M	145 rows 145	26,403 rows 26K	6,793 rows 6.8K	0 rows 0
PostgreSQL	1,396,951 rows 1.4M	401,023 rows 401K	145 rows 145	403,620 rows 404K	600,000 rows 600K	0 rows 0
SQL Server	1,466,333 rows 1.5M	1,481,552 rows 1.5M	25,361 rows 25K	49,100 rows 49K	40,470 rows 40K	0 rows 0

Actual Query Plans

Query Plan

Query Plan per Engine ?
	Query Plan
PostgreSQL	Estimate Actual Operator 81 145 SORT s_name 81 145 INNER JOIN LOOP ON n_nationkey = s_nationkey 1 1 │└TABLE SCAN nation WHERE n_name = 'KENYA' 2019 3397 LEFT SEMI JOIN LOOP ON s_suppkey = ps_suppkey 10000 10000 │└TABLE SCAN supplier 2020 4151 INNER JOIN LOOP ON p_partkey = ps_partkey 200000 393475 │└TABLE SCAN partsupp WHERE ps_availqty > (SubPlan 1) 600000 600000 │ GROUP BY SIMPLE AGGREGATE 0.5 * SUM(l_quantity) 600000 600000 │ TABLE SEEK lineitem WHERE (l_shipdate >= '1993-01-01') AND (l_shipdate < '1994-01-01') AND (l_suppkey = ps_suppkey) 393475 393475 TABLE SEEK part WHERE p_name LIKE'almond%'
DuckDB	Estimate Actual Operator 76 145 SORT s_name 76 145 RIGHT SEMI INNER JOIN HASH ON #0 = s_suppkey 384 391 │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation WHERE n_name = 'KENYA' 10000 391 │ TABLE SCAN supplier 120000 4151 FILTER CAST(ps_availqty AS DECIMAL 38,4) > SUBQUERY 0 0 RIGHT SEMI INNER JOIN HASH ON ps_partkey IS NOT DISTINCT FROM ps_partkeyps_suppkey IS NOT DISTINCT FROM ps_suppkey 119985 6276 │└GROUP BY HASH #0, #2 AGGREGATE 0 6276 │ RIGHT OUTER JOIN HASH ON ps_partkey IS NOT DISTINCT FROM ps_partkeyps_suppkey IS NOT DISTINCT FROM ps_suppkey 119985 0 │ │└SCAN EMPTY 188443 4157 │ PROJECT 0.5 * SUM(l_quantity), ps_partkey, ps_suppkey 188443 4157 │ GROUP BY HASH #0, #1 AGGREGATE SUM(#2) 376887 6793 │ PROJECT ps_partkey, ps_suppkey, l_quantity 376887 6793 │ INNER JOIN HASH ON l_partkey = ps_partkeyl_suppkey = ps_suppkey 119985 0 │ │└SCAN MATERIALISED 1199764 640958 │ TABLE SCAN lineitem WHERE l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01' 120000 6276 LEFT SEMI JOIN HASH ON ps_partkey = #0 40000 1569 │└FILTER p_partkey <= 150000 40000 2107 │ TABLE SCAN part WHERE p_name >= 'almond' AND p_name < 'almone' 600000 599424 TABLE SCAN partsupp
SQL Server	Estimate Actual Operator 45 145 SORT s_name 45 145 SORT s_suppkey 1 179 INNER JOIN LOOP ON l_partkey = p_partkey 1 179 │└TABLE SEEK part WHERE p_name LIKE 'almond%' 57 15399 GROUP BY HASH AGGREGATE IN(s_name) AS s_name, IN(s_address) AS s_address 3429 15399 FILTER ps_availqty > 0.5 * Expr1012 3429 15422 PROJECT CASE WHEN Expr1018 = 0 THEN NULL ELSE Expr1019 END AS Expr1012 3429 15422 GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1018, SUM(l_quantity) AS Expr1019, IN(s_suppkey) AS s_suppkey, IN(s_name) AS s_name, IN(s_address) AS s_address, IN(ps_availqty) AS ps_availqty, IN(l_partkey) AS l_partkey 3429 25071 SORT Bmk1004 3429 25071 INNER JOIN HASH ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey 23997 23459 │└INNER JOIN HASH ON s_suppkey = ps_suppkey 400 391 │ │└INNER JOIN HASH ON n_nationkey = s_nationkey 1 1 │ │ │└TABLE SEEK nation WHERE n_name = 'KENYA' 10000 10000 │ │ TABLE SEEK supplier 600000 600000 │ TABLE SCAN partsupp 856096 856153 TABLE SCAN lineitem

PostgreSQL

Estimate    Actual  Operator
      81       145  SORT s_name
      81       145  INNER JOIN LOOP ON n_nationkey = s_nationkey
       1         1  │└TABLE SCAN nation WHERE n_name = 'KENYA'
    2019      3397  LEFT SEMI JOIN LOOP ON s_suppkey = ps_suppkey
   10000     10000  │└TABLE SCAN supplier
    2020      4151  INNER JOIN LOOP ON p_partkey = ps_partkey
  200000    393475  │└TABLE SCAN partsupp WHERE ps_availqty > (SubPlan 1)
  600000    600000  │ GROUP BY SIMPLE AGGREGATE 0.5 * SUM(l_quantity)
  600000    600000  │ TABLE SEEK lineitem WHERE (l_shipdate >= '1993-01-01') AND (l_shipdate < '1994-01-01') AND (l_suppkey = ps_suppkey)
  393475    393475  TABLE SEEK part WHERE p_name LIKE'almond%'

DuckDB

Estimate    Actual  Operator
      76       145  SORT s_name
      76       145  RIGHT SEMI INNER JOIN HASH ON #0 = s_suppkey
     384       391  │└INNER JOIN HASH ON s_nationkey = n_nationkey
       1         1  │ │└TABLE SCAN nation WHERE n_name = 'KENYA'
   10000       391  │ TABLE SCAN supplier
  120000      4151  FILTER CAST(ps_availqty AS DECIMAL 38,4) > SUBQUERY
       0         0  RIGHT SEMI INNER JOIN HASH ON ps_partkey IS NOT DISTINCT FROM ps_partkeyps_suppkey IS NOT DISTINCT FROM ps_suppkey
  119985      6276  │└GROUP BY HASH #0, #2 AGGREGATE 
       0      6276  │ RIGHT OUTER JOIN HASH ON ps_partkey IS NOT DISTINCT FROM ps_partkeyps_suppkey IS NOT DISTINCT FROM ps_suppkey
  119985         0  │ │└SCAN EMPTY
  188443      4157  │ PROJECT 0.5 * SUM(l_quantity), ps_partkey, ps_suppkey
  188443      4157  │ GROUP BY HASH #0, #1 AGGREGATE SUM(#2)
  376887      6793  │ PROJECT ps_partkey, ps_suppkey, l_quantity
  376887      6793  │ INNER JOIN HASH ON l_partkey = ps_partkeyl_suppkey = ps_suppkey
  119985         0  │ │└SCAN MATERIALISED
 1199764    640958  │ TABLE SCAN lineitem WHERE l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01'
  120000      6276  LEFT SEMI JOIN HASH ON ps_partkey = #0
   40000      1569  │└FILTER p_partkey <= 150000
   40000      2107  │ TABLE SCAN part WHERE p_name >= 'almond' AND p_name < 'almone'
  600000    599424  TABLE SCAN partsupp

SQL Server

Estimate    Actual  Operator
      45       145  SORT s_name
      45       145  SORT s_suppkey
       1       179  INNER JOIN LOOP ON l_partkey = p_partkey
       1       179  │└TABLE SEEK part WHERE p_name LIKE 'almond%'
      57     15399  GROUP BY HASH AGGREGATE IN(s_name) AS s_name, IN(s_address) AS s_address
    3429     15399  FILTER ps_availqty > 0.5 * Expr1012
    3429     15422  PROJECT CASE WHEN Expr1018 = 0 THEN NULL ELSE Expr1019 END AS Expr1012
    3429     15422  GROUP BY HASH  AGGREGATE COUNT(l_quantity) AS Expr1018, SUM(l_quantity) AS Expr1019, IN(s_suppkey) AS s_suppkey, IN(s_name) AS s_name, IN(s_address) AS s_address, IN(ps_availqty) AS ps_availqty, IN(l_partkey) AS l_partkey
    3429     25071  SORT Bmk1004
    3429     25071  INNER JOIN HASH ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
   23997     23459  │└INNER JOIN HASH ON s_suppkey = ps_suppkey
     400       391  │ │└INNER JOIN HASH ON n_nationkey = s_nationkey
       1         1  │ │ │└TABLE SEEK nation WHERE n_name = 'KENYA'
   10000     10000  │ │ TABLE SEEK supplier
  600000    600000  │ TABLE SCAN partsupp
  856096    856153  TABLE SCAN lineitem