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 = 42
	and p_type like '%NICKEL'
	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=807322.68..807322.68 rows=1 width=190)
   ->  Sort  (cost=807322.68..807322.68 rows=1 width=190)
         Sort Key: supplier.s_acctbal, nation.n_name, supplier.s_name, part.p_partkey
         ->  Merge Join  (cost=381646.38..807322.67 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.43..102473.43 rows=8065 width=30)
                     Filter: (((p_type)::text ~~ '%NICKEL'::text) AND (p_size = 42))
               ->  Sort  (cost=381645.94..385645.86 rows=1599968 width=168)
                     Sort Key: partsupp.ps_partkey
                     ->  Nested Loop  (cost=2.00..216772.34 rows=1599968 width=168)
                           ->  Nested Loop  (cost=1.56..5806.62 rows=20000 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..4555.56 rows=100000 width=168)
                                       Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
                                       ->  Seq Scan on supplier  (cost=0.00..3179.00 rows=100000 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.43..9.75 rows=80 width=12)
                                 Index Cond: (ps_suppkey = supplier.s_suppkey)
               SubPlan 1
                 ->  Aggregate  (cost=48.83..48.84 rows=1 width=4)
                       ->  Nested Loop  (cost=0.86..48.83 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.86..47.71 rows=4 width=8)
                                   ->  Nested Loop  (cost=0.72..47.04 rows=4 width=8)
                                         ->  Index Scan using pk_partsupp on partsupp partsupp_1  (cost=0.43..13.75 rows=4 width=8)
                                               Index Cond: (part.p_partkey = ps_partkey)
                                         ->  Index Scan using pk_supplier on supplier supplier_1  (cost=0.29..8.31 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)
 Planning time: 3.847 ms
(37 rows)

COMMIT;
COMMIT