BEGIN;
BEGIN
EXPLAIN (ANALYZE, BUFFERS)
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 = 34
	and p_type like '%NICKEL'
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'AFRICA'
	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 = 'AFRICA'
	)
order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey
LIMIT 100;
                                                                               QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=825309.69..825309.70 rows=1 width=190) (actual time=9652.529..9652.561 rows=100 loops=1)
   Buffers: shared hit=1651759 read=208619
   ->  Sort  (cost=825309.69..825309.70 rows=1 width=190) (actual time=9652.525..9652.545 rows=100 loops=1)
         Sort Key: public.supplier.s_acctbal, public.nation.n_name, public.supplier.s_name, part.p_partkey
         Sort Method: top-N heapsort  Memory: 51kB
         Buffers: shared hit=1651759 read=208619
         ->  Merge Join  (cost=395548.83..825309.68 rows=1 width=190) (actual time=6793.489..9645.775 rows=4752 loops=1)
               Merge Cond: (part.p_partkey = public.partsupp.ps_partkey)
               Join Filter: (public.partsupp.ps_supplycost = (SubPlan 1))
               Rows Removed by Join Filter: 1630
               Buffers: shared hit=1651751 read=208619
               ->  Index Scan using pk_part on part  (cost=0.00..102473.47 rows=8002 width=30) (actual time=0.103..1261.901 rows=8162 loops=1)
                     Filter: (((p_type)::text ~~ '%NICKEL'::text) AND (p_size = 34))
                     Rows Removed by Filter: 1991838
                     Buffers: shared hit=758 read=45983
               ->  Sort  (cost=395548.83..399548.83 rows=1600000 width=168) (actual time=6793.190..7316.035 rows=1586934 loops=1)
                     Sort Key: public.partsupp.ps_partkey
                     Sort Method: quicksort  Memory: 470689kB
                     Buffers: shared hit=1494315 read=158753
                     ->  Nested Loop  (cost=1.56..230671.71 rows=1600000 width=168) (actual time=0.105..5188.348 rows=1586960 loops=1)
                           Buffers: shared hit=1494315 read=158753
                           ->  Nested Loop  (cost=1.56..5806.62 rows=20000 width=164) (actual time=0.086..132.434 rows=19837 loops=1)
                                 Join Filter: (public.nation.n_regionkey = public.region.r_regionkey)
                                 Rows Removed by Join Filter: 80163
                                 Buffers: shared hit=2181
                                 ->  Seq Scan on region  (cost=0.00..1.06 rows=1 width=4) (actual time=0.009..0.019 rows=1 loops=1)
                                       Filter: (r_name = 'AFRICA'::bpchar)
                                       Rows Removed by Filter: 4
                                       Buffers: shared hit=1
                                 ->  Hash Join  (cost=1.56..4555.56 rows=100000 width=168) (actual time=0.070..114.536 rows=100000 loops=1)
                                       Hash Cond: (public.supplier.s_nationkey = public.nation.n_nationkey)
                                       Buffers: shared hit=2180
                                       ->  Seq Scan on supplier  (cost=0.00..3179.00 rows=100000 width=142) (actual time=0.014..42.487 rows=100000 loops=1)
                                             Buffers: shared hit=2179
                                       ->  Hash  (cost=1.25..1.25 rows=25 width=34) (actual time=0.032..0.032 rows=25 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 2kB
                                             Buffers: shared hit=1
                                             ->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=34) (actual time=0.005..0.014 rows=25 loops=1)
                                                   Buffers: shared hit=1
                           ->  Index Scan using partsupp_ps_suppkey_idx on partsupp  (cost=0.00..10.44 rows=80 width=12) (actual time=0.017..0.234 rows=80 loops=19837)
                                 Index Cond: (ps_suppkey = public.supplier.s_suppkey)
                                 Buffers: shared hit=1492134 read=158753
               SubPlan 1
                 ->  Aggregate  (cost=49.85..49.86 rows=1 width=4) (actual time=0.058..0.058 rows=1 loops=6382)
                       Buffers: shared hit=156678 read=3883
                       ->  Nested Loop  (cost=0.00..49.84 rows=1 width=4) (actual time=0.037..0.056 rows=2 loops=6382)
                             Join Filter: (public.nation.n_regionkey = public.region.r_regionkey)
                             Rows Removed by Join Filter: 2
                             Buffers: shared hit=156678 read=3883
                             ->  Seq Scan on region  (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=6382)
                                   Filter: (r_name = 'AFRICA'::bpchar)
                                   Rows Removed by Filter: 4
                                   Buffers: shared hit=6382
                             ->  Nested Loop  (cost=0.00..48.73 rows=4 width=8) (actual time=0.024..0.052 rows=4 loops=6382)
                                   Buffers: shared hit=150296 read=3883
                                   ->  Nested Loop  (cost=0.00..47.61 rows=4 width=8) (actual time=0.022..0.043 rows=4 loops=6382)
                                         Buffers: shared hit=99240 read=3883
                                         ->  Index Scan using pk_partsupp on partsupp  (cost=0.00..14.45 rows=4 width=8) (actual time=0.015..0.016 rows=4 loops=6382)
                                               Index Cond: (part.p_partkey = ps_partkey)
                                               Buffers: shared hit=22495 read=3883
                                         ->  Index Scan using pk_supplier on supplier  (cost=0.00..8.28 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=25528)
                                               Index Cond: (s_suppkey = public.partsupp.ps_suppkey)
                                               Buffers: shared hit=76745
                                   ->  Index Scan using pk_nation on nation  (cost=0.00..0.27 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=25528)
                                         Index Cond: (n_nationkey = public.supplier.s_nationkey)
                                         Buffers: shared hit=51056
 Total runtime: 9703.085 ms
(67 rows)

COMMIT;
COMMIT