BEGIN;
BEGIN
EXPLAIN
select
	nation,
	o_year,
	sum(amount) as sum_profit
from
	(
		select
			n_name as nation,
			extract(year from o_orderdate) as o_year,
			l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
		from
			part,
			supplier,
			lineitem,
			partsupp,
			orders,
			nation
		where
			s_suppkey = l_suppkey
			and ps_suppkey = l_suppkey
			and ps_partkey = l_partkey
			and p_partkey = l_partkey
			and o_orderkey = l_orderkey
			and s_nationkey = n_nationkey
			and p_name like '%chartreuse%'
	) as profit
group by
	nation,
	o_year
order by
	nation,
	o_year desc;
                                                                                                                         QUERY PLAN                                                                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=19827893.21..23737573.75 rows=3 width=46)
   Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
   ->  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=19827893.21..23737573.65 rows=3 width=46)
         ->  GroupAggregate  (cost=19827893.21..23737573.65 rows=3 width=46)
               Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
               ->  Sort  (cost=23737573.68..23737573.68 rows=3 width=46)
                     Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
                     ->  Hash Join  (cost=19827893.21..23737573.65 rows=3 width=46)
                           Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
                           ->  Nested Loop  (cost=19827891.65..23737572.03 rows=3 width=24)
                                 ->  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=19827991.45..23737651.45 rows=3 width=24)
                                       Distribute results by H: l_orderkey
                                       ->  Hash Join  (cost=19827891.45..23737551.36 rows=3 width=24)
                                             Hash Cond: ((partsupp.ps_suppkey = supplier.s_suppkey) AND (partsupp.ps_partkey = lineitem.l_partkey))
                                             ->  Seq Scan on partsupp  (cost=0.00..2509659.32 rows=80000032 width=12)
                                             ->  Hash  (cost=19748022.98..19748022.98 rows=5324565 width=36)
                                                   ->  Hash Join  (cost=713865.60..19748022.98 rows=5324565 width=36)
                                                         Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
                                                         ->  Hash Join  (cost=669567.60..19630512.21 rows=5324565 width=28)
                                                               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=100.00..34058755.63 rows=600037888 width=24)
                                                                     Distribute results by H: l_partkey
                                                                     ->  Seq Scan on lineitem  (cost=0.00..16657556.88 rows=600037888 width=24)
                                                               ->  Hash  (cost=655241.12..655241.12 rows=1146118 width=4)
                                                                     ->  Seq Scan on part  (cost=0.00..655241.12 rows=1146118 width=4)
                                                                           Filter: ((p_name)::text ~~ '%chartreuse%'::text)
                                                         ->  Hash  (cost=31798.00..31798.00 rows=1000000 width=8)
                                                               ->  Seq Scan on supplier  (cost=0.00..31798.00 rows=1000000 width=8)
                                 ->  Index Scan using pk_orders on orders  (cost=0.20..6.88 rows=1 width=8)
                                       Index Cond: (o_orderkey = lineitem.l_orderkey)
                           ->  Hash  (cost=1.25..1.25 rows=25 width=30)
                                 ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=30)
(32 rows)

COMMIT;
COMMIT