BEGIN;
BEGIN
EXPLAIN
select
	s_acctbal,
	s_name,
	n_name,
	p_partkey,
	p_mfgr,
	s_address,
	s_phone,
	s_comment
from
	part,
	supplier,
	partsupp,
	nation,
	region
where
	p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and p_size = 6
	and p_type like '%COPPER'
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'EUROPE'
	and ps_supplycost = (
		select
			min(ps_supplycost)
		from
			partsupp,
			supplier,
			nation,
			region
		where
			p_partkey = ps_partkey
			and s_suppkey = ps_suppkey
			and s_nationkey = n_nationkey
			and n_regionkey = r_regionkey
			and r_name = 'EUROPE'
	)
order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey
LIMIT 100;
                                                                                                   QUERY PLAN                                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10992870.59..10992870.59 rows=1 width=190)
   ->  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=10992870.59..10992870.59 rows=1 width=190)
         ->  Limit  (cost=10992870.59..10992870.59 rows=1 width=190)
               ->  Sort  (cost=10992870.59..10992870.59 rows=1 width=190)
                     Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
                     ->  Merge Join  (cost=5312398.35..10992870.58 rows=1 width=190)
                           Merge Cond: (part.p_partkey = partsupp.ps_partkey)
                           Join Filter: (partsupp.ps_supplycost = (SubPlan 1))
                           ->  Index Scan using pk_part on part  (cost=0.19..2136138.24 rows=79798 width=30)
                                 Filter: (((p_type)::text ~~ '%COPPER'::text) AND (p_size = 6))
                           ->  Materialize  (cost=5312183.99..5392184.02 rows=16000006 width=168)
                                 ->  Sort  (cost=5312183.99..5352184.00 rows=16000006 width=168)
                                       Sort Key: partsupp.ps_partkey
                                       ->  Nested Loop  (cost=1.75..2085154.24 rows=16000006 width=168)
                                             ->  Nested Loop  (cost=1.56..58050.62 rows=200000 width=164)
                                                   Join Filter: (nation.n_regionkey = region.r_regionkey)
                                                   ->  Seq Scan on region  (cost=0.00..1.06 rows=1 width=4)
                                                         Filter: (r_name = 'EUROPE'::bpchar)
                                                   ->  Hash Join  (cost=1.56..45549.56 rows=1000000 width=168)
                                                         Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
                                                         ->  Seq Scan on supplier  (cost=0.00..31798.00 rows=1000000 width=142)
                                                         ->  Hash  (cost=1.25..1.25 rows=25 width=34)
                                                               ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=34)
                                             ->  Index Scan using partsupp_ps_suppkey_idx on partsupp  (cost=0.19..9.33 rows=81 width=12)
                                                   Index Cond: (ps_suppkey = supplier.s_suppkey)
                           SubPlan 1
                             ->  Aggregate  (cost=53.63..53.64 rows=1 width=4)
                                   ->  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=0.51..53.63 rows=1 width=4)
                                         ->  Aggregate  (cost=0.51..53.63 rows=1 width=4)
                                               ->  Nested Loop  (cost=0.51..53.63 rows=1 width=4)
                                                     Join Filter: (nation_1.n_regionkey = region_1.r_regionkey)
                                                     ->  Seq Scan on region region_1  (cost=0.00..1.06 rows=1 width=4)
                                                           Filter: (r_name = 'EUROPE'::bpchar)
                                                     ->  Nested Loop  (cost=0.51..52.51 rows=4 width=8)
                                                           ->  Nested Loop  (cost=0.37..51.85 rows=4 width=8)
                                                                 ->  Index Scan using pk_partsupp on partsupp partsupp_1  (cost=0.19..19.04 rows=4 width=8)
                                                                       Index Cond: (part.p_partkey = ps_partkey)
                                                                 ->  Index Scan using pk_supplier on supplier supplier_1  (cost=0.17..8.19 rows=1 width=8)
                                                                       Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
                                                           ->  Index Scan using pk_nation on nation nation_1  (cost=0.14..0.16 rows=1 width=8)
                                                                 Index Cond: (n_nationkey = supplier_1.s_nationkey)
(41 rows)

COMMIT;
COMMIT