Planner — TPC-H — TPCH-Q20

Estimate    Actual  Operator
       -       150  SORT s_name
       -       150  INNER JOIN HASH ON s_nationkey = n_nationkey
       -         1  │└TABLE SCAN nation WHERE n_name = 'KENYA'
       -      3550  TABLE SCAN supplier WHERE s_suppkey IN (SELECT ps_suppkey FROM tpch.partsupp WHERE ps_partkey IN (SELECT p_partkey FROM tpch.part WHERE p_name LIKE 'almond%') AND ps_availqty > (SELECT 0.5 * SUM(l_quantity) FROM tpch.lineitem WHERE l_partkey = ps_partkey AND l_suppkey = ps_suppkey AND l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01'))

Estimate    Actual  Operator
       -       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
       -         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

Estimate    Actual  Operator
      55       145  SORT s_name
      55       145  SORT s_suppkey
       1       179  INNER JOIN LOOP ON l_partkey = p_partkey
       1       179  │└TABLE SEEK part WHERE p_name LIKE 'almond%'
      56     15399  GROUP BY HASH AGGREGATE IN(s_name) AS s_name, IN(s_address) AS s_address
    3377     15399  FILTER ps_availqty > 0.5 * Expr1012
    3377     15422  PROJECT CASE WHEN Expr1018 = 0 THEN NULL ELSE Expr1019 END AS Expr1012
    3377     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
    3377     25071  SORT Bmk1004
    3377     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
  843142    856153  TABLE SCAN lineitem WHERE l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01'

Estimate    Actual  Operator
      81       145  SORT s_name
      81       145  INNER JOIN LOOP ON n_nationkey = s_nationkey AND (n_nationkey = s_nationkey)
       1         1  │└TABLE SCAN nation WHERE n_name = 'KENYA'
    2019      3397  LEFT SEMI JOIN LOOP ON s_suppkey = ps_suppkey AND (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%'