Planner — TPC-H — TPCH-Q08

Estimate    Actual  Operator
       -         2  PROJECT sumIf(volume,(nation = 'FRANCE')) / SUM(volume), sumIf(volume,(nation = 'FRANCE')) / SUM(volume)
       -         2  SORT o_year
       -         2  GROUP BY HASH o_year AGGREGATE sumIf(volume,(nation = 'FRANCE')), SUM(volume)
       -      2400  PROJECT EXTRACT(YEAR FROM o_orderdate), n_name, 1 - l_discount, EXTRACT(YEAR FROM o_orderdate), l_extendedprice * (1 - l_discount), nation = 'FRANCE', l_extendedprice * (1 - l_discount)
       -      2400  INNER JOIN HASH ON s_nationkey = n_nationkey
       -        25  │└TABLE SCAN nation
       -      2050  INNER JOIN HASH ON n_regionkey = r_regionkey
       -         1  │└TABLE SCAN region WHERE r_name = 'EUROPE'
       -     10392  INNER JOIN HASH ON c_nationkey = n_nationkey
       -        25  │└TABLE SCAN nation
       -     10392  INNER JOIN HASH ON o_custkey = c_custkey
       -    150000  │└TABLE SCAN customer
       -     10392  INNER JOIN HASH ON l_suppkey = s_suppkey
       -     10000  │└TABLE SCAN supplier
       -     10398  INNER JOIN HASH ON l_partkey = p_partkey
       -      1353  │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL'
       -   1549566  INNER JOIN HASH ON l_orderkey = o_orderkey
       -    428930  │└TABLE SCAN orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate <= '1996-12-31')
       -   5998820  TABLE SCAN lineitem

Estimate    Actual  Operator
       -         2  SORT o_year
    1752         2  PROJECT o_year, mkt_share
       -         2  GROUP BY HASH #0 AGGREGATE SUM(#1), SUM(#2)
    1961      2323  PROJECT o_year, CASE WHEN(nation = 'FRANCE') THEN volume ELSE 0.000000 END, volume
    1961      2323  PROJECT o_year, volume, nation
    1961      2323  INNER JOIN HASH ON s_nationkey = n_nationkey
      25        25  │└TABLE SCAN nation
    2040      2323  INNER JOIN HASH ON s_suppkey = l_suppkey
    1748      2323  │└INNER JOIN HASH ON c_nationkey = n_nationkey
       5         5  │ │└INNER JOIN HASH ON n_regionkey = r_regionkey
       1         1  │ │ │└TABLE SCAN region WHERE r_name = 'EUROPE'
      25         5  │ │ TABLE SCAN nation
    9093      8482  │ INNER JOIN HASH ON c_custkey = o_custkey
    8601     11654  │ │└INNER JOIN HASH ON o_orderkey = l_orderkey
   42624     40975  │ │ │└INNER JOIN HASH ON l_partkey = p_partkey
    1419      1362  │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL'
 5998820   5991648  │ │ │ TABLE SCAN lineitem WHERE l_suppkey >= 1
  300000    428372  │ │ TABLE SCAN orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31'
  150000    108026  │ TABLE SCAN customer WHERE c_custkey >= 3
   10000      9989  TABLE SCAN supplier

Estimate    Actual  Operator
    2234         2  GROUP BY SORT EXTRACT(EXTRACT(YEAR FROM  o_orderdate) AGGREGATE SUM(CASE WHEN(n_name = 'FRANCE') THEN(l_extendedprice * ('1' - l_discount)) ELSE'0'END) / SUM(l_extendedprice * ('1' - l_discount))
    2793         6  GROUP BY SORT EXTRACT(EXTRACT(YEAR FROM  o_orderdate) AGGREGATE PARTIALSUM(CASE WHEN(n_name = 'FRANCE') THEN(l_extendedprice * ('1' - l_discount)) ELSE'0'END), PARTIALSUM(l_extendedprice * ('1' - l_discount))
    2793      2322  SORT EXTRACT(EXTRACT(YEAR FROM  o_orderdate)
    2793      2322  INNER JOIN HASH ON s_nationkey = n_nationkey
      25        25  │└TABLE SCAN n2
    2793      2322  INNER JOIN LOOP ON s_suppkey = l_suppkey
    2799      2322  │└INNER JOIN HASH ON c_nationkey = n_nationkey
      15        15  │ │└INNER JOIN HASH ON n_regionkey = r_regionkey
       1         1  │ │ │└TABLE SCAN region WHERE r_name = 'EUROPE'
      25        25  │ │ TABLE SCAN n1
   14004     11655  │ INNER JOIN LOOP ON c_custkey = o_custkey
   14004     11655  │ │└INNER JOIN LOOP ON o_orderkey = l_orderkey
   49752     40977  │ │ │└INNER JOIN LOOP ON l_partkey = p_partkey
    1659      1362  │ │ │ │└TABLE SCAN part WHERE p_type = 'SMALL POLISHED NICKEL'
   42222     40860  │ │ │ TABLE SEEK lineitem
   40978     40978  │ │ TABLE SEEK orders WHERE (o_orderdate >= '1995-01-01') AND (o_orderdate <= '1996-12-31')
   11656     11656  │ TABLE SEEK customer
    2323      2323  TABLE SEEK supplier

Estimate    Actual  Operator
       2         ∞  PROJECT Expr1017 / Expr1018 AS Expr1019
       2         2  PROJECT CASE WHEN globalagg1043 = 0 THEN NULL ELSE globalagg1045 END AS Expr1017, CASE WHEN globalagg1047 = 0 THEN NULL ELSE globalagg1049 END AS Expr1018
       2         2  GROUP BY HASH  AGGREGATE SUM(partialagg1042) AS globalagg1043, SUM(partialagg1044) AS globalagg1045, SUM(partialagg1046) AS globalagg1047, SUM(partialagg1048) AS globalagg1049
       2         2  SORT Expr1015
       2         2  INNER JOIN HASH ON r_regionkey = n_regionkey as n_regionkey
       1         1  │└TABLE SEEK region WHERE r_name = 'EUROPE'
       1         2  GROUP BY HASH AGGREGATE COUNT(Expr1065) AS partialagg1042, SUM(Expr1065) AS partialagg1044, COUNT(Expr1020) AS partialagg1046, SUM(Expr1020) AS partialagg1048
    1348      2323  PROJECT CASE WHEN n_name as n_name = 'FRANCE'THEN l_extendedprice * (1. - l_discount) ELSE 0.0000 END AS Expr1065
    1348      2323  INNER JOIN HASH ON n_nationkey as n_nationkey = s_nationkey
      25        25  │└TABLE SEEK nation
    1348      2323  INNER JOIN HASH ON n_nationkey as n_nationkey = c_nationkey
      25        25  │└TABLE SEEK nation
    1348      2323  INNER JOIN HASH ON s_suppkey = l_suppkey
    1348      2323  │└INNER JOIN HASH ON p_partkey = l_partkey
    1381      1362  │ │└TABLE SEEK part WHERE p_type = 'SMALL POLISHED NICKEL'
    1940      2323  │ INNER JOIN HASH ON c_custkey = o_custkey
  150000    150000  │ │└TABLE SEEK customer
    1940      2323  │ INNER JOIN HASH ON o_orderkey = l_orderkey
   42769     86366  │ │└PROJECT datepart(EXTRACT(YEAR FROM o_orderdate) AS Expr1015
   42769    428392  │ │ TABLE SEEK orders WHERE o_orderdate >= '1995-01-01' AND o_orderdate <= '1996-12-31'
    5998      2323  │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1020
    5998      2323  │ TABLE SEEK lineitem WHERE BLOOM(l_partkey) AND BLOOM(l_orderkey)
   10000     10000  TABLE SEEK supplier