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 '%misty%'
	) as profit
group by
	nation,
	o_year
order by
	nation,
	o_year desc;
                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=706313.87..706320.32 rows=49 width=90)
   Group Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate))
   ->  Gather Merge  (cost=706313.87..706319.19 rows=40 width=90)
         Workers Planned: 2
         ->  Partial GroupAggregate  (cost=705313.85..705314.55 rows=20 width=90)
               Group Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate))
               ->  Sort  (cost=705313.85..705313.90 rows=20 width=81)
                     Sort Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) DESC
                     ->  Hash Join  (cost=54026.69..705313.42 rows=20 width=81)
                           Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
                           ->  Nested Loop  (cost=54025.12..705311.74 rows=20 width=31)
                                 ->  Nested Loop  (cost=54024.69..705247.39 rows=20 width=35)
                                       Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
                                       ->  Parallel Hash Join  (cost=54024.12..270855.56 rows=66916 width=26)
                                             Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
                                             ->  Parallel Hash Join  (cost=51590.33..268244.95 rows=67353 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=16835 width=4)
                                                         ->  Parallel Seq Scan on part  (cost=0.00..51379.89 rows=16835 width=4)
                                                               Filter: ((p_name)::text ~~ '%misty%'::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