BEGIN;
BEGIN
EXPLAIN
select
	supp_nation,
	cust_nation,
	l_year,
	sum(volume) as revenue
from
	(
		select
			n1.n_name as supp_nation,
			n2.n_name as cust_nation,
			extract(year from l_shipdate) as l_year,
			l_extendedprice * (1 - l_discount) as volume
		from
			supplier,
			lineitem,
			orders,
			customer,
			nation n1,
			nation n2
		where
			s_suppkey = l_suppkey
			and o_orderkey = l_orderkey
			and c_custkey = o_custkey
			and s_nationkey = n1.n_nationkey
			and c_nationkey = n2.n_nationkey
			and (
				(n1.n_name = 'JAPAN' and n2.n_name = 'SAUDI ARABIA')
				or (n1.n_name = 'SAUDI ARABIA' and n2.n_name = 'JAPAN')
			)
			and l_shipdate between date '1995-01-01' and date '1996-12-31'
	) as shipping
group by
	supp_nation,
	cust_nation,
	l_year
order by
	supp_nation,
	cust_nation,
	l_year;
                                                                                          QUERY PLAN                                                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=1983572.15..1986880.70 rows=10024 width=116)
   Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate))
   ->  Gather Merge  (cost=1983572.15..1986479.74 rows=20048 width=116)
         Workers Planned: 2
         ->  Partial GroupAggregate  (cost=1982572.13..1983165.68 rows=10024 width=116)
               Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate))
               ->  Sort  (cost=1982572.13..1982635.44 rows=25325 width=96)
                     Sort Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate))
                     ->  Parallel Hash Join  (cost=399812.83..1980719.82 rows=25325 width=96)
                           Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
                           Join Filter: (((n1.n_name = 'JAPAN'::bpchar) AND (n2.n_name = 'SAUDI ARABIA'::bpchar)) OR ((n1.n_name = 'SAUDI ARABIA'::bpchar) AND (n2.n_name = 'JAPAN'::bpchar)))
                           ->  Parallel Hash Join  (cost=2083.94..1564443.87 rows=608548 width=50)
                                 Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
                                 ->  Parallel Seq Scan on lineitem  (cost=0.00..1531298.89 rows=7606846 width=28)
                                       Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
                                 ->  Parallel Hash  (cost=2042.28..2042.28 rows=3333 width=30)
                                       ->  Hash Join  (cost=1.69..2042.28 rows=3333 width=30)
                                             Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
                                             ->  Parallel Index Only Scan using supplier_s_nationkey_s_suppkey_idx on supplier  (cost=0.29..1912.96 rows=41667 width=8)
                                             ->  Hash  (cost=1.38..1.38 rows=2 width=30)
                                                   ->  Seq Scan on nation n1  (cost=0.00..1.38 rows=2 width=30)
                                                         Filter: ((n_name = 'JAPAN'::bpchar) OR (n_name = 'SAUDI ARABIA'::bpchar))
                           ->  Parallel Hash  (cost=387563.26..387563.26 rows=500450 width=34)
                                 ->  Parallel Hash Join  (cost=31140.12..387563.26 rows=500450 width=34)
                                       Hash Cond: (orders.o_custkey = customer.c_custkey)
                                       ->  Parallel Seq Scan on orders  (cost=0.00..330879.30 rows=6255630 width=12)
                                       ->  Parallel Hash  (cost=30515.09..30515.09 rows=50002 width=30)
                                             ->  Hash Join  (cost=1.83..30515.09 rows=50002 width=30)
                                                   Hash Cond: (customer.c_nationkey = n2.n_nationkey)
                                                   ->  Parallel Index Only Scan using customer_c_nationkey_c_custkey_idx on customer  (cost=0.43..28594.88 rows=625020 width=8)
                                                   ->  Hash  (cost=1.38..1.38 rows=2 width=30)
                                                         ->  Seq Scan on nation n2  (cost=0.00..1.38 rows=2 width=30)
                                                               Filter: ((n_name = 'SAUDI ARABIA'::bpchar) OR (n_name = 'JAPAN'::bpchar))
(33 rows)

COMMIT;
COMMIT