BEGIN;
BEGIN
EXPLAIN
select
	100.00 * sum(case
		when p_type like 'PROMO%'
			then l_extendedprice * (1 - l_discount)
		else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
	lineitem,
	part
where
	l_partkey = p_partkey
	and l_shipdate >= date '1993-06-01'
	and l_shipdate < cast(date '1993-06-01' + interval '1 month' as date);
                                                                                                     QUERY PLAN                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=11843442.23..11843442.24 rows=1 width=29)
   ->  Remote Subquery Scan on all (datanode_1_1,datanode_2_1,datanode_3_1,datanode_4_1,datanode_5_1,datanode_6_1,datanode_7_1,datanode_8_1)  (cost=1148904.27..11823398.41 rows=1145361 width=29)
         ->  Aggregate  (cost=1148904.27..11823398.41 rows=1 width=29)
               ->  Hash Join  (cost=1148904.27..11823398.41 rows=1145361 width=29)
                     Hash Cond: (lineitem.l_partkey = part.p_partkey)
                     ->  Remote Subquery Scan on all (datanode_1_1,datanode_2_1,datanode_3_1,datanode_4_1,datanode_5_1,datanode_6_1,datanode_7_1,datanode_8_1)  (cost=157044.05..10533466.56 rows=7396668 width=12)
                           Distribute results by H: l_partkey
                           ->  Bitmap Heap Scan on lineitem  (cost=156944.05..10407623.20 rows=7396668 width=12)
                                 Recheck Cond: ((l_shipdate >= '1993-06-01'::date) AND (l_shipdate < '1993-07-01'::date))
                                 ->  Bitmap Index Scan on lineitem_l_shipdate_l_suppkey__idx  (cost=0.00..155094.88 rows=7396668 width=0)
                                       Index Cond: ((l_shipdate >= '1993-06-01'::date) AND (l_shipdate < '1993-07-01'::date))
                     ->  Hash  (cost=605241.10..605241.10 rows=20000010 width=25)
                           ->  Seq Scan on part  (cost=0.00..605241.10 rows=20000010 width=25)
(13 rows)

COMMIT;
COMMIT