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 '%magenta%'
	) as profit
group by
	nation,
	o_year
order by
	nation,
	o_year desc;
                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=1358573.67..1358589.77 rows=121 width=90)
   Group Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate))
   ->  Gather Merge  (cost=1358573.67..1358586.96 rows=100 width=90)
         Workers Planned: 2
         ->  Partial GroupAggregate  (cost=1357573.64..1357575.39 rows=50 width=90)
               Group Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate))
               ->  Sort  (cost=1357573.64..1357573.77 rows=50 width=81)
                     Sort Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) DESC
                     ->  Hash Join  (cost=54342.35..1357572.23 rows=50 width=81)
                           Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
                           ->  Nested Loop  (cost=54340.79..1357570.39 rows=50 width=31)
                                 ->  Nested Loop  (cost=54340.35..1357409.51 rows=50 width=35)
                                       Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
                                       ->  Parallel Hash Join  (cost=54339.79..271436.44 rows=167289 width=26)
                                             Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
                                             ->  Parallel Hash Join  (cost=51905.99..268560.61 rows=168383 width=18)
                                                   Hash Cond: (partsupp.ps_partkey = part.p_partkey)
                                                   ->  Parallel Seq Scan on partsupp  (cost=0.00..207902.88 rows=3333988 width=14)
                                                   ->  Parallel Hash  (cost=51379.89..51379.89 rows=42088 width=4)
                                                         ->  Parallel Seq Scan on part  (cost=0.00..51379.89 rows=42088 width=4)
                                                               Filter: ((p_name)::text ~~ '%magenta%'::text)
                                             ->  Parallel Hash  (cost=1912.96..1912.96 rows=41667 width=8)
                                                   ->  Parallel Index Only Scan using supplier_s_nationkey_s_suppkey_idx on supplier  (cost=0.29..1912.96 rows=41667 width=8)
                                       ->  Index Scan using lineitem_l_partkey_l_suppkey_l_shipdate_l_quantity_idx on lineitem  (cost=0.56..6.48 rows=1 width=33)
                                             Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
                                 ->  Index Only Scan using orders_o_orderkey_o_orderdate_idx on orders  (cost=0.43..3.22 rows=1 width=12)
                                       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)
(29 rows)

COMMIT;
COMMIT