Planner — TPC-H — TPCH-Q21

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
DuckDB	18,754,973 rows 19M	14,924,939 rows 15M	396 rows 396	1,031,358 rows 1M	4,060 rows 4.1K	0 rows 0
PostgreSQL	13,599,420 rows 14M	7,613,884 rows 7.6M	4,456 rows 4.5K	6,308,500 rows 6.3M	4,060 rows 4.1K	0 rows 0
SQL Server	14,367,119 rows 14M	14,367,118 rows 14M	396 rows 396	518,976 rows 519K	4,060 rows 4.1K	0 rows 0

Actual Query Plans

Query Plan

Query Plan per Engine ?
	Query Plan
PostgreSQL	Estimate Actual Operator 1 396 SORT COUNT(), s_name 1 396 GROUP BY SORT s_name AGGREGATE COUNT() 1 4060 SORT s_name 1 4060 INNER JOIN LOOP ON o_orderkey = l_orderkey 5 7980 │└LEFT SEMI JOIN HASH ON l_orderkey = l_orderkey 7498525 5998820 │ │└TABLE SCAN l2 5 13285 │ RIGHT ANTI JOIN HASH ON l_orderkey = l_orderkey 99690 149860 │ │└INNER JOIN HASH ON l_suppkey = s_suppkey 2000 1980 │ │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY' 10000 10000 │ │ │ TABLE SCAN supplier 2499510 3791310 │ │ TABLE SCAN l1 WHERE l_receiptdate > l_commitdate 2499510 3791310 │ TABLE SCAN l3 WHERE l_receiptdate > l_commitdate 7979 7979 TABLE SEEK orders WHERE o_orderstatus = 'F'
DuckDB	Estimate Actual Operator 881 396 SORT count_star(), s_name 881 396 GROUP BY HASH #0 AGGREGATE count_star() 1086 4060 PROJECT s_name 1086 0 RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey 5430 74300 │└GROUP BY HASH #0, #1 AGGREGATE 1086 4060 │ RIGHT ANTI JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey 5430 0 │ │└SCAN EMPTY 4488 194446 │ INNER JOIN HASH ON l_orderkey = l_orderkeyl_suppkey != l_suppkey 5430 0 │ │└SCAN MATERIALISED 1199764 3791176 │ FILTER l_receiptdate > l_commitdate 5998820 5998605 │ TABLE SCAN lineitem 5431 0 RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey 27158 77151 │└GROUP BY HASH #1, #2 AGGREGATE 5431 74310 │ RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey 27158 0 │ │└SCAN EMPTY 112243 306977 │ INNER JOIN HASH ON l_orderkey = l_orderkeyl_suppkey != l_suppkey 27158 0 │ │└SCAN MATERIALISED 5998820 5998605 │ TABLE SCAN lineitem 27159 77161 INNER JOIN HASH ON o_orderkey = l_orderkey 53838 149859 │└INNER JOIN HASH ON l_suppkey = s_suppkey 384 396 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY' 10000 396 │ │ TABLE SCAN supplier 1199764 3780503 │ FILTER l_suppkey >= 1 1199764 3780503 │ FILTER l_receiptdate > l_commitdate 5998820 5981691 │ TABLE SCAN lineitem 750000 775675 TABLE SCAN orders WHERE o_orderstatus = 'F'
SQL Server	Estimate Actual Operator 400 396 SORT Expr1014, s_name 400 396 PROJECT CONVERT_IMPLICIT int,Expr1017,0 AS Expr1014 400 396 GROUP BY HASH AGGREGATE COUNT(*) AS Expr1017 42068 4060 INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 42068 74310 │└INNER JOIN HASH ON 71976 144552 │ │└INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 71976 149859 │ │ │└INNER JOIN HASH ON s_suppkey = l_suppkey as l_suppkey 400 396 │ │ │ │└INNER JOIN HASH ON n_nationkey = s_nationkey 1 1 │ │ │ │ │└TABLE SEEK nation WHERE n_name = 'GERMANY' 10000 10000 │ │ │ │ TABLE SEEK supplier 1799650 3791311 │ │ │ TABLE SCAN lineitem 5998820 5998820 │ │ TABLE SCAN lineitem 777011 775676 │ TABLE SEEK orders WHERE o_orderstatus = 'F' 1799650 3791311 TABLE SCAN lineitem

PostgreSQL

Estimate    Actual  Operator
       1       396  SORT COUNT(*), s_name
       1       396  GROUP BY SORT s_name AGGREGATE COUNT(*)
       1      4060  SORT s_name
       1      4060  INNER JOIN LOOP ON o_orderkey = l_orderkey
       5      7980  │└LEFT SEMI JOIN HASH ON l_orderkey = l_orderkey
 7498525   5998820  │ │└TABLE SCAN l2
       5     13285  │ RIGHT ANTI JOIN HASH ON l_orderkey = l_orderkey
   99690    149860  │ │└INNER JOIN HASH ON l_suppkey = s_suppkey
    2000      1980  │ │ │└INNER JOIN HASH ON s_nationkey = n_nationkey
       1         1  │ │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY'
   10000     10000  │ │ │ TABLE SCAN supplier
 2499510   3791310  │ │ TABLE SCAN l1 WHERE l_receiptdate > l_commitdate
 2499510   3791310  │ TABLE SCAN l3 WHERE l_receiptdate > l_commitdate
    7979      7979  TABLE SEEK orders WHERE o_orderstatus = 'F'

DuckDB

Estimate    Actual  Operator
     881       396  SORT count_star(), s_name
     881       396  GROUP BY HASH #0 AGGREGATE count_star()
    1086      4060  PROJECT s_name
    1086         0  RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey
    5430     74300  │└GROUP BY HASH #0, #1 AGGREGATE 
    1086      4060  │ RIGHT ANTI JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey
    5430         0  │ │└SCAN EMPTY
    4488    194446  │ INNER JOIN HASH ON l_orderkey = l_orderkeyl_suppkey != l_suppkey
    5430         0  │ │└SCAN MATERIALISED
 1199764   3791176  │ FILTER l_receiptdate > l_commitdate
 5998820   5998605  │ TABLE SCAN lineitem
    5431         0  RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey
   27158     77151  │└GROUP BY HASH #1, #2 AGGREGATE 
    5431     74310  │ RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey
   27158         0  │ │└SCAN EMPTY
  112243    306977  │ INNER JOIN HASH ON l_orderkey = l_orderkeyl_suppkey != l_suppkey
   27158         0  │ │└SCAN MATERIALISED
 5998820   5998605  │ TABLE SCAN lineitem
   27159     77161  INNER JOIN HASH ON o_orderkey = l_orderkey
   53838    149859  │└INNER JOIN HASH ON l_suppkey = s_suppkey
     384       396  │ │└INNER JOIN HASH ON s_nationkey = n_nationkey
       1         1  │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY'
   10000       396  │ │ TABLE SCAN supplier
 1199764   3780503  │ FILTER l_suppkey >= 1
 1199764   3780503  │ FILTER l_receiptdate > l_commitdate
 5998820   5981691  │ TABLE SCAN lineitem
  750000    775675  TABLE SCAN orders WHERE o_orderstatus = 'F'

SQL Server

Estimate    Actual  Operator
     400       396  SORT Expr1014, s_name
     400       396  PROJECT CONVERT_IMPLICIT int,Expr1017,0 AS Expr1014
     400       396  GROUP BY HASH AGGREGATE COUNT(*) AS Expr1017
   42068      4060  INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey
   42068     74310  │└INNER JOIN HASH ON 
   71976    144552  │ │└INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey
   71976    149859  │ │ │└INNER JOIN HASH ON s_suppkey = l_suppkey as l_suppkey
     400       396  │ │ │ │└INNER JOIN HASH ON n_nationkey = s_nationkey
       1         1  │ │ │ │ │└TABLE SEEK nation WHERE n_name = 'GERMANY'
   10000     10000  │ │ │ │ TABLE SEEK supplier
 1799650   3791311  │ │ │ TABLE SCAN lineitem
 5998820   5998820  │ │ TABLE SCAN lineitem
  777011    775676  │ TABLE SEEK orders WHERE o_orderstatus = 'F'
 1799650   3791311  TABLE SCAN lineitem