Planner — TPC-H — TPCH-Q12

Estimate    Actual  Operator
       -         2  SORT l_shipmode
       7         2  AGGREGATE SUM(#1), SUM(#2) GROUP BY HASH #0
  242051     30837  PROJECT l_shipmode, CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END, CASE WHEN ((o_orderpriority != '1-URGENT') AND (o_orderpriority != '2-HIGH')) THEN 1 ELSE 0 END
  242051     30837  INNER JOIN HASH ON o_orderkey = l_orderkey
  240048     30837  │└FILTER (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND ((l_shipmode = 'AIR') OR (l_shipmode = 'TRUCK'))
 1200243    885773  │ TABLE SCAN lineitem WHERE l_shipmode IN('AIR','TRUCK') AND l_commitdate < '1995-01-01' AND l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01' AND l_shipdate < '1995-01-01'
 1500000     34818  TABLE SCAN orders

Estimate    Actual  Operator
       1         2  SORT l_shipmode ASC NULLS FIRST
       1         2  AGGREGATE SUM(casewhen(in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY),1,0)), SUM(casewhen(( NOT in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY)),1,0)) GROUP BY HASH l_shipmode
       1         2  DISTRIBUTE HASH ON l_shipmode
       1         2  AGGREGATE SUM(casewhen(in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY),1,0)), SUM(casewhen(( NOT in(o_orderpriority,'1-URGENT'collate UTF8_BINARY,'2-HIGH'collate UTF8_BINARY)),1,0)) GROUP BY HASH l_shipmode
   51600     30837  INNER JOIN HASH ON l_orderkey = o_orderkey
   51600     30837  │└DISTRIBUTE GATHER
 1500000     30837  │ TABLE SCAN lineitem WHERE (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= DATE'1994-01-01') AND (l_receiptdate < DATE'1995-01-01') AND in(l_shipmode,'AIR'collate UTF8_BINARY,'TRUCK'collate UTF8_BINARY)
 6000000   1495983  TABLE SCAN orders

Estimate    Actual  Operator
       -         2  PROJECT l_shipmode, a1 AS high_line_count, a2 AS low_line_count
       -         2  SORT l_shipmode
       -         2  PROJECT l_shipmode, a1, a2
       -         2  AGGREGATE countIf a13 AS a1, countIf a16 AS a2 GROUP BY HASH l_shipmode
       -     30837  PROJECT l_shipmode, (o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH') AS a13, (o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH') AS a16
       -     30837  PROJECT l_shipmode, o_orderpriority
       -     30837  INNER JOIN HASH ON PROJECTION_317.l_orderkey = PROJECTION_314.o_orderkey
       -   1500000  │└PROJECT o_orderkey, o_orderpriority
       -   1500000  │ PROJECT o_orderpriority, o_orderkey
       -   1500000  │ TABLE SCAN orders
       -     30837  PROJECT l_orderkey, l_shipmode
       -     30837  PROJECT l_orderkey, l_shipmode
       -     30837  TABLE SCAN lineitem WHERE (l_commitdate < '1995-01-01') AND (l_shipdate < '1995-01-01') AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01') AND l_shipmode IN('AIR','TRUCK') AND (l_shipdate < l_commitdate) AND (l_commitdate < l_receiptdate)

Estimate    Actual  Operator
       7         2  AGGREGATE SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY SORT l_shipmode
      21         6  AGGREGATE PARTIAL SUM(CASE WHEN ((o_orderpriority = '1-URGENT') OR (o_orderpriority = '2-HIGH')) THEN 1 ELSE 0 END), PARTIAL SUM(CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) GROUP BY SORT l_shipmode
   36711     30837  SORT l_shipmode
   12237     10279  INNER JOIN HASH ON o_orderkey = l_orderkey
   36711     30837  │└TABLE SCAN lineitem AS lineitem WHERE (l_shipmode IN('AIR','TRUCK')) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01')
 1875000   1500000  TABLE SCAN orders AS orders

Estimate    Actual  Operator
       2         2  PROJECT l_shipmode, sum AS high_line_count, sum_4 AS low_line_count
       2         2  SORT l_shipmode
       2         2  AGGREGATE SUM(sum_5) AS sum, SUM(sum_6) AS sum_4 GROUP BY HASH l_shipmode
   85780         2  DISTRIBUTE HASH ON l_shipmode
   85780         2  AGGREGATE SUM(expr_2) AS sum_5, SUM(expr_3) AS sum_6 GROUP BY PARTIAL l_shipmode
   85780     30837  PROJECT CAST((CASE WHEN (o_orderpriority IN('1-URGENT','2-HIGH')) THEN 1 ELSE 0 END) AS bigint) AS expr_2, CAST((CASE WHEN ((o_orderpriority <> '1-URGENT') AND (o_orderpriority <> '2-HIGH')) THEN 1 ELSE 0 END) AS bigint) AS expr_3, l_shipmode
   85780     30837  INNER JOIN HASH ON o_orderkey = l_orderkey
 1500000   1500000  │└TABLE SCAN orders
   85780     30837  DISTRIBUTE HASH ON l_orderkey
 6001215     30837  FILTER (l_shipmode IN('AIR','TRUCK')) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1994-01-01') AND (l_receiptdate < '1995-01-01')
 6001215   6001215  TABLE SCAN lineitem

Estimate    Actual  Operator
       2         2  SORT l_shipmode
       2         2  AGGREGATE SUM(Expr1008) AS Expr1006, SUM(Expr1009) AS Expr1007 GROUP BY HASH l_shipmode
  311180     30837  INNER JOIN HASH ON o_orderkey = l_orderkey
  311173     30837  │└TABLE SCAN lineitem WHERE ((l_shipmode = 'AIR' OR l_shipmode = 'TRUCK') AND l_receiptdate >= '1994-01-01' AND l_receiptdate < '1995-01-01') AND (l_shipdate < l_commitdate AND l_commitdate < l_receiptdate)
  150000     28962  PROJECT CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END AS Expr1008, CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' THEN 1 ELSE 0 END AS Expr1009
  150000     28962  TABLE SCAN orders WHERE BLOOM(o_orderkey)