BEGIN;
BEGIN
EXPLAIN
select
	ps_partkey,
	sum(ps_supplycost * ps_availqty) as value
from
	partsupp,
	supplier,
	nation
where
	ps_suppkey = s_suppkey
	and s_nationkey = n_nationkey
	and n_name = 'KENYA'
group by
	ps_partkey having
		sum(ps_supplycost * ps_availqty) > (
			select
				sum(ps_supplycost * ps_availqty) * 0.0000010000
			from
				partsupp,
				supplier,
				nation
			where
				ps_suppkey = s_suppkey
				and s_nationkey = n_nationkey
				and n_name = 'KENYA'
		)
order by
	value desc;
                                                                                                QUERY PLAN                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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=1324340.55..1332340.55 rows=3200001 width=12)
   ->  Sort  (cost=1324340.55..1332340.55 rows=3200001 width=12)
         Sort Key: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::double precision))) DESC
         InitPlan 1 (returns $2)
           ->  Aggregate  (cost=453293.08..453293.09 rows=1 width=8)
                 ->  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=750.37..429293.07 rows=3200001 width=8)
                       ->  Aggregate  (cost=750.37..429293.07 rows=1 width=8)
                             ->  Nested Loop  (cost=750.37..429293.07 rows=3200001 width=8)
                                   ->  Nested Loop  (cost=750.17..23872.35 rows=40000 width=4)
                                         ->  Seq Scan on nation nation_1  (cost=0.00..1.31 rows=1 width=4)
                                               Filter: (n_name = 'KENYA'::bpchar)
                                         ->  Bitmap Heap Scan on supplier supplier_1  (cost=750.17..23471.03 rows=40000 width=8)
                                               Recheck Cond: (s_nationkey = nation_1.n_nationkey)
                                               ->  Bitmap Index Scan on supplier_s_nationkey_s_suppkey_idx  (cost=0.00..740.17 rows=40000 width=0)
                                                     Index Cond: (s_nationkey = nation_1.n_nationkey)
                                   ->  Index Scan using partsupp_ps_suppkey_idx on partsupp partsupp_1  (cost=0.19..9.33 rows=81 width=12)
                                         Index Cond: (ps_suppkey = supplier_1.s_suppkey)
         ->  HashAggregate  (cost=485293.09..525293.10 rows=3200001 width=12)
               Group Key: partsupp.ps_partkey
               Filter: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::double precision)) > $2)
               ->  Nested Loop  (cost=750.37..429293.07 rows=3200001 width=12)
                     ->  Nested Loop  (cost=750.17..23872.35 rows=40000 width=4)
                           ->  Seq Scan on nation  (cost=0.00..1.31 rows=1 width=4)
                                 Filter: (n_name = 'KENYA'::bpchar)
                           ->  Bitmap Heap Scan on supplier  (cost=750.17..23471.03 rows=40000 width=8)
                                 Recheck Cond: (s_nationkey = nation.n_nationkey)
                                 ->  Bitmap Index Scan on supplier_s_nationkey_s_suppkey_idx  (cost=0.00..740.17 rows=40000 width=0)
                                       Index Cond: (s_nationkey = nation.n_nationkey)
                     ->  Index Scan using partsupp_ps_suppkey_idx on partsupp  (cost=0.19..9.33 rows=81 width=16)
                           Index Cond: (ps_suppkey = supplier.s_suppkey)
(30 rows)

COMMIT;
COMMIT