Planner — TPC-H — TPCH-Q19

Engine Compare

Accuracy chart, rows processed ?
Scan
Scan
Join Probe
Join
Sort
Sort
Hash Build
Hash
Aggregate
Agg
Distribute
Dist
DuckDB
1,699,869 rows
1.7M
214,265 rows
214K
0 rows
0
214,265 rows
214K
97 rows
97
0 rows
0
PostgreSQL
1,026 rows
1K
513 rows
513
0 rows
0
513 rows
513
99 rows
99
0 rows
0
SQL Server
331,301 rows
331K
131,301 rows
131K
0 rows
0
200,000 rows
200K
97 rows
97
0 rows
0

Actual Query Plans

Query Plan per Engine ?
Query Plan
PostgreSQL
Estimate    Actual  Operator
       1         1  GROUP BY SIMPLE AGGREGATE SUM(l_extendedprice * ('1' - l_discount))
       3         3  GROUP BY SIMPLE AGGREGATE PARTIALSUM(l_extendedprice * ('1' - l_discount))
     129        96  INNER JOIN LOOP ON l_partkey = p_partkey
     594       513  │└TABLE SCAN part WHERE (p_size >= 1) AND (p_brand = 'Brand#11') AND p_container IN('"SM CASE"','"SM BOX"','"SM PACK"','"SM PKG"') AND (p_size <= 5) OR (p_brand = 'Brand#22') AND p_container IN('"MED BAG"','"MED BOX"','"MED PKG"','"MED PACK"') AND (p_size <= 10) OR (p_brand = 'Brand#33') AND p_container IN('"LG CASE"','"LG BOX"','"LG PACK"','"LG PKG"') AND (p_size <= 15)
     513       513  TABLE SEEK lineitem WHERE l_shipmode IN('AIR','"AIR REG"') AND (l_shipinstruct = 'DELIVER IN PERSON') AND (l_quantity >= '5') AND (l_quantity <= '15') OR (l_quantity >= '15') AND (l_quantity <= '25') OR (l_quantity >= '25') AND (l_quantity <= '35') AND (p_brand = 'Brand#11') AND p_container IN('"SM CASE"','"SM BOX"','"SM PACK"','"SM PKG"') AND (l_quantity >= '5') AND (l_quantity <= '15') AND (p_size <= 5) OR (p_brand = 'Brand#22') AND p_container IN('"MED BAG"','"MED BOX"'...
DuckDB
Estimate    Actual  Operator
  300387         1  GROUP BY SIMPLE AGGREGATE SUM(#0)
  300387        97  PROJECT l_extendedprice * (1.000 - l_discount)
  300387        97  FILTER (l_quantity >= 5.000) AND (l_quantity <= 15.000) AND (p_size <= 5) AND (p_brand = 'Brand#11') AND (p_container = 'SM CASE') OR (p_container = 'SM BOX') OR (p_container = 'SM PACK') OR (p_container = 'SM PKG') OR (l_quantity >= 15.000) AND (l_quantity <= 25.000) AND (p_size <= 10) AND (p_brand = 'Brand#22') AND (p_container = 'MED BAG') OR (p_container = 'MED BOX') OR (p_container = 'MED PKG') OR (p_container = 'MED PACK') OR (l_quantity >= 25.000) AND (l_quantity <= 35.000) AND (p_size <= 15) AND (p_brand = 'Brand#33') AND (p_container = 'LG CASE') OR (p_container = 'LG BOX') OR (p_container = 'LG PACK') OR (p_container = 'LG PKG')
  300387    214265  INNER JOIN HASH ON l_partkey = p_partkey
  200000    200000  │└TABLE SCAN part
  299941    214265  FILTER (l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG')
 1499705   1499869  TABLE SCAN lineitem WHERE l_shipmode IN('AIR','AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON'
SQL Server
Estimate    Actual  Operator
       1         1  PROJECT CASE WHEN Expr1011 = 0 THEN NULL ELSE Expr1012 END AS Expr1005
       1         1  GROUP BY HASH AGGREGATE COUNT(Expr1006) AS Expr1011, SUM(Expr1006) AS Expr1012
    5685        97  INNER JOIN HASH ON p_partkey = l_partkey AND (p_brand = 'Brand#11' AND (p_container = 'SM BOX' OR p_container = 'SM CASE' OR p_container = 'SM PACK' OR p_container = 'SM PKG') AND l_quantity >= 5.00 AND l_quantity <= 15.00 AND p_size <= 5 OR p_brand = 'Brand#22' AND (p_container = 'MED BAG' OR p_container = 'MED BOX' OR p_container = 'MED PACK' OR p_container = 'MED PKG') AND l_quantity >= 15.00 AND l_quantity <= 25.00 AND p_size <= 10 OR p_brand = 'Brand#33' AND (p_contai...
    3032    200000  │└TABLE SEEK part WHERE (p_size >= 1) AND (p_brand = 'Brand#11' AND (p_container = 'SM BOX' OR p_container = 'SM CASE' OR p_container = 'SM PACK' OR p_container = 'SM PKG') AND p_size <= 5 OR p_brand = 'Brand#22' AND (p_container = 'MED BAG' OR p_container = 'MED BOX' OR p_container = 'MED PACK' OR p_container = 'MED PKG') AND p_size <= 10 OR p_brand = 'Brand#33' AND (p_container = 'LG BOX' OR p_container = 'LG CASE' OR p_container = 'LG PACK' OR p_container = 'LG PKG'...
  374985    131301  PROJECT l_extendedprice * (1. - l_discount) AS Expr1006
  374985    131301  TABLE SCAN lineitem