Planner — TPC-H — TPCH-Q19

/* TPC-H Q19 */
SELECT SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM tpch_sf1.lineitem
INNER JOIN tpch_sf1.part ON p_partkey = l_partkey
WHERE (

        p_brand = 'Brand#11'
        AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        AND l_quantity >= 5 AND l_quantity <= 5 + 10
        AND p_size BETWEEN 1 AND 5
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    )
   OR ( p_brand = 'Brand#22'
        AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        AND l_quantity >= 15 AND l_quantity <= 15 + 10
        AND p_size BETWEEN 1 AND 10
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    )
   OR ( p_brand = 'Brand#33'
        AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        AND l_quantity >= 25 AND l_quantity <= 25 + 10
        AND p_size BETWEEN 1 AND 15
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    )

Engine Compare

Accuracy chart, rows processed ?
Scan
Scan
Seek
Seek
Join Probe
Join
Sort
Sort
Hash Build
Hash
Aggregate
Agg
Distribute
Dist
Native storage
Estimation Error
Est Err
829
829
Rank
Estimation Error
Est Err
318
318
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
511
511
Rank
Estimation Error
Est Err
127
127
Rank
Estimation Error
Est Err
0
Rank
Native storage
Estimation Error
Est Err
132,985
133K
Rank
Estimation Error
Est Err
132,509
133K
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
476
476
Rank
Estimation Error
Est Err
120
120
Rank
Estimation Error
Est Err
0
Rank
Native storage
Estimation Error
Est Err
132,136
132K
Rank
Estimation Error
Est Err
131,625
132K
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
511
511
Rank
Estimation Error
Est Err
129
129
Rank
Estimation Error
Est Err
513
513
Rank
Apache Iceberg
Estimation Error
Est Err
6,201,215
6.2M
Rank
Estimation Error
Est Err
511
511
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
132,509
133K
Rank
Estimation Error
Est Err
137
137
Rank
Estimation Error
Est Err
200,521
201K
Rank
Native storage
Estimation Error
Est Err
1,700,048
1.7M
Rank
Estimation Error
Est Err
214,377
214K
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
214,377
214K
Rank
Estimation Error
Est Err
127
127
Rank
Estimation Error
Est Err
0
Rank
Native storage
Estimation Error
Est Err
1,021
1K
Rank
Estimation Error
Est Err
511
511
Rank
Estimation Error
Est Err
511
511
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
510
510
Rank
Estimation Error
Est Err
129
129
Rank
Estimation Error
Est Err
0
Rank
Native storage
Estimation Error
Est Err
10,079
10K
Rank
Estimation Error
Est Err
318
318
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
511
511
Rank
Estimation Error
Est Err
127
127
Rank
Estimation Error
Est Err
0
Rank
Apache Iceberg
Estimation Error
Est Err
414,377
414K
Rank
Estimation Error
Est Err
214,377
214K
Rank
Estimation Error
Est Err
0
0
Rank
Estimation Error
Est Err
200,000
200K
Rank
Estimation Error
Est Err
129
129
Rank
Estimation Error
Est Err
200,002
200K
Rank

Actual Query Plans

Query Plan per Engine ?
Query Plan
Native storage
Estimate    Actual  Operator
       1         1  AGGREGATE sum
     229       127  INNER JOIN HASH ON p_partkey = l_partkey AND l_quantity BETWEEN 500 AND 1500 AND p_container IN(SM BOX,SM CASE,SM PACK,SM PKG) AND p_brand = Brand#11 AND p_size BETWEEN 1 AND 5 OR l_quantity BETWEEN 1500 AND 2500 AND p_container IN(MED BAG,MED BOX,MED PACK,MED PKG) AND p_brand = Brand#22 AND p_size BETWEEN 1 AND 10 OR l_quantity BETWEEN 2500 AND 3500 AND p_container IN(LG BOX,LG CASE,LG PACK,LG PKG) AND p_brand = Brand#33 AND p_size BETWEEN 1 AND 15
     585       511  │└TABLE SCAN part WHERE p_container IN(SM BOX,SM CASE,SM PACK,SM PKG) AND p_brand = Brand#11 AND p_size BETWEEN 1 AND 5 OR p_container IN(MED BAG,MED BOX,MED PACK,MED PKG) AND p_brand = Brand#22 AND p_size BETWEEN 1 AND 10 OR p_container IN(LG BOX,LG CASE,LG PACK,LG PKG) AND p_brand = Brand#33 AND p_size BETWEEN 1 AND 15
  158235       318  TABLE SCAN lineitem WHERE l_quantity BETWEEN 500 AND 3500 AND l_shipinstruct = DELIVER IN PERSON AND l_shipmode IN(AIR,AIR REG)
Native storage
Estimate    Actual  Operator
       -         1  PROJECT a1 AS revenue
       -         1  AGGREGATE SUM(a5) AS a1
       -       120  PROJECT l_extendedprice * (1 - l_discount) AS a5
       -       120  FILTER ((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) AND (p_size <= 15) AND (l_quantity >= 1)) OR ((p_brand = 'Brand#22') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (l_quantity >= 15) AND (l_quantity <= 25) AND (p_size <= 10)) OR ((p_brand = 'Brand#33') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (l_quantity >= 25) AND (l_quantity <= 35) AND (p_size <= 15))
       -       312  PROJECT l_extendedprice, l_discount, l_quantity, p_size, p_brand, p_container
       -       312  INNER JOIN HASH ON PROJECTION_432.l_partkey = PROJECTION_429.p_partkey
       -       476  │└PROJECT p_partkey, p_size, p_brand, p_container
       -       476  │ PROJECT p_partkey, p_size, p_brand, p_container
       -       476  │ 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) AND (p_size <= 15)) OR ((p_brand = 'Brand#22') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (p_size <= 10)) OR ((p_brand = 'Brand#33') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND (p_size <= 15)))
       -    132509  PROJECT l_partkey, l_extendedprice, l_discount, l_quantity
       -    132509  PROJECT l_partkey, l_extendedprice, l_discount, l_quantity
       -    132509  TABLE SCAN lineitem WHERE (((l_quantity >= 5) AND (l_quantity <= 15) AND (l_quantity >= 1)) OR ((l_quantity >= 15) AND (l_quantity <= 25)) OR ((l_quantity >= 25) AND (l_quantity <= 35))) AND l_shipmode IN('AIR','AIR REG') AND (l_shipinstruct = 'DELIVER IN PERSON')
Native storage
Estimate    Actual  Operator
       -         1  AGGREGATE SUM(#0)
  314635       127  PROJECT l_extendedprice * (1.00 - l_discount)
  314635       127  FILTER ((l_quantity >= 5.00) AND (l_quantity <= 15.00) 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.00) AND (l_quantity <= 25.00) 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.00) AND (l_quantity <= 35.00) 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')))
  314635    214377  INNER JOIN HASH ON p_partkey = l_partkey
  300060    214377  │└FILTER (l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG')
 1500304   1500048  │ TABLE SCAN lineitem WHERE l_shipmode IN('AIR','AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON'
  200000    200000  TABLE SCAN part
Apache Iceberg
Estimate    Actual  Operator
       1         1  PROJECT revenue
       1         1  AGGREGATE SUM(l_extendedprice * (1 - l_discount))
  240048        10  DISTRIBUTE GATHER
  240048        10  AGGREGATE SUM(l_extendedprice * (1 - l_discount))
  240048       127  INNER JOIN HASH ON p_partkey = l_partkey AND (((((((p_brand = 'Brand#11') AND p_container IN('SM CASE','SM BOX','SM PACK','SM PKG')) AND (l_quantity >= 5.00)) AND (l_quantity <= 15.00)) AND (p_size <= 5)) OR (((((p_brand = 'Brand#22') AND p_container IN('MED BAG','MED BOX','MED PKG','MED PACK')) AND (l_quantity >= 15.00)) AND (l_quantity <= 25.00)) AND (p_size <= 10))) OR (((((p_brand = 'Brand#33') AND p_container IN('LG CASE','LG BOX','LG PACK','LG PKG')) AND (l_quantity ...
 1200243    132509  │└FILTER (((((l_quantity >= 5.00) AND (l_quantity <= 15.00)) OR ((l_quantity >= 15.00) AND (l_quantity <= 25.00))) OR ((l_quantity >= 25.00) AND (l_quantity <= 35.00))) AND ((l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG'))) AND (l_shipinstruct = 'DELIVER IN PERSON')
 6001215   6001215  │ TABLE SCAN lineitem WHERE ((((((l_quantity >= 5.00) AND (l_quantity <= 15.00)) OR ((l_quantity >= 15.00) AND (l_quantity <= 25.00))) OR ((l_quantity >= 25.00) AND (l_quantity <= 35.00))) AND ((l_shipmode = 'AIR') OR (l_shipmode = 'AIR REG'))) AND (l_shipinstruct = 'DELIVER IN PERSON')) AND (((l_partkey >= 109) AND (l_partkey <= 199761)) AND TRUE)
   40000       511  DISTRIBUTE GATHER
   40000       511  FILTER (((((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))) AND (p_size >= 1)
  200000    200000  DISTRIBUTE ROUND ROBIN
  200000    200000  TABLE SCAN part WHERE (((((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))) AND (p_size >= 1)
Native storage
Estimate    Actual  Operator
       1         1  AGGREGATE SUM(l_extendedprice * (1BD - l_discount))
       1         2  DISTRIBUTE GATHER
       1         2  AGGREGATE SUM(l_extendedprice * (1BD - l_discount))
      1T       127  INNER JOIN HASH ON l_partkey = p_partkey
      1T       511  │└DISTRIBUTE HASH ON p_partkey
 6000000       511  │ 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)))
  200000    131625  TABLE SCAN lineitem WHERE (l_shipmode IN('AIR','AIR REG')) AND (l_shipinstruct = 'DELIVER IN PERSON') AND (l_quantity >= 5.00BD) AND (l_quantity <= 35.00BD)
Native storage
Estimate    Actual  Operator
       1         1  PROJECT CASE WHEN Expr1013 = 0 THEN NULL ELSE Expr1014 END AS Expr1006
       1         1  AGGREGATE COUNT(Expr1007) AS Expr1013, SUM(Expr1007) AS Expr1014
    5926       127  INNER JOIN HASH ON l_partkey = p_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...
    3099       511  │└TABLE SCAN 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'...
    3824       318  FILTER (l_shipmode = 'AIR' OR l_shipmode = 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON'
    3824      9568  PROJECT l_extendedprice * (1. - l_discount) AS Expr1007
    3824      9568  TABLE SCAN lineitem WHERE l_quantity >= 5.00 AND l_quantity <= 35.00 AND BLOOM(l_partkey)
Apache Iceberg
Estimate    Actual  Operator
       1         ∞  PROJECT sum AS revenue
       1         1  AGGREGATE SUM(sum_3) AS sum
       -         2  DISTRIBUTE GATHER
       -         2  AGGREGATE SUM(expr) AS sum_3
       -       127  PROJECT l_extendedprice * (1 - l_discount) AS expr
       -       127  INNER JOIN HASH ON (l_partkey = p_partkey) AND (((p_brand = 'Brand#11') AND (p_container IN('SM CASE','SM BOX','SM PACK','SM PKG')) AND (l_quantity >= 5.00) AND (l_quantity <= 15.00) AND (p_size BETWEEN 1 AND 5)) OR ((p_brand = 'Brand#22') AND (p_container IN('MED BAG','MED BOX','MED PKG','MED PACK')) AND (l_quantity >= 15.00) AND (l_quantity <= 25.00) AND (p_size BETWEEN 1 AND 10)) OR ((p_brand = 'Brand#33') AND (p_container IN('LG CASE','LG BOX','LG PACK','LG PKG')) AND ...
  200000    200000  │└DISTRIBUTE GATHER
  200000    200000  │ TABLE SCAN part
 5401094    214377  FILTER (l_shipinstruct = 'DELIVER IN PERSON') AND (l_shipmode IN('AIR','AIR REG'))
 5401094    214377  TABLE SCAN lineitem
Native storage
Estimate    Actual  Operator
       1         1  AGGREGATE SUM(l_extendedprice * ('1' - l_discount))
       3         3  AGGREGATE PARTIAL SUM(l_extendedprice * ('1' - l_discount))
     144       126  INNER JOIN LOOP ON l_partkey = p_partkey
     585       510  │└TABLE SCAN part AS 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)))
     511       511  TABLE SEEK lineitem AS 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...