BEGIN;
BEGIN
EXPLAIN (ANALYZE, BUFFERS)
select
	c_custkey,
	c_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	c_acctbal,
	n_name,
	c_address,
	c_phone,
	c_comment
from
	customer,
	orders,
	lineitem,
	nation
where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate >= date '1994-04-01'
	and o_orderdate < cast(date '1994-04-01' + interval '3 month' as date)
	and l_returnflag = 'R'
	and c_nationkey = n_nationkey
group by
	c_custkey,
	c_name,
	c_acctbal,
	c_phone,
	n_name,
	c_address,
	c_comment
order by
	revenue desc
LIMIT 20;
                                                                                                QUERY PLAN                                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1181543.18..1181543.23 rows=20 width=175) (actual time=59501.615..59501.623 rows=20 loops=1)
   Buffers: shared hit=1021641 read=401212 dirtied=115 written=2708
   ->  Sort  (cost=1181543.18..1182936.47 rows=557317 width=175) (actual time=59501.611..59501.616 rows=20 loops=1)
         Sort Key: (sum((lineitem.l_extendedprice * (1::double precision - lineitem.l_discount))))
         Sort Method: top-N heapsort  Memory: 30kB
         Buffers: shared hit=1021641 read=401212 dirtied=115 written=2708
         ->  HashAggregate  (cost=1161140.00..1166713.17 rows=557317 width=175) (actual time=58949.691..59336.550 rows=379090 loops=1)
               Buffers: shared hit=1021638 read=401212 dirtied=115 written=2708
               ->  Hash Join  (cost=398446.83..1147207.08 rows=557317 width=175) (actual time=10348.948..54383.056 rows=1138318 loops=1)
                     Hash Cond: (customer.c_nationkey = nation.n_nationkey)
                     Buffers: shared hit=1021638 read=401212 dirtied=115 written=2708
                     ->  Hash Join  (cost=398445.27..1139542.41 rows=557317 width=153) (actual time=10348.905..53543.960 rows=1138318 loops=1)
                           Hash Cond: (orders.o_custkey = customer.c_custkey)
                           Buffers: shared hit=1021637 read=401212 dirtied=115 written=2708
                           ->  Merge Join  (cost=329417.27..1059368.07 rows=557317 width=12) (actual time=6450.016..47100.958 rows=1138318 loops=1)
                                 Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
                                 Buffers: shared hit=998344 read=389227 dirtied=115 written=2708
                                 ->  Sort  (cost=329417.25..330837.86 rows=568244 width=8) (actual time=6449.810..6646.503 rows=568725 loops=1)
                                       Sort Key: orders.o_orderkey
                                       Sort Method: quicksort  Memory: 51235kB
                                       Buffers: shared hit=221423 read=8738 written=1
                                       ->  Bitmap Heap Scan on orders  (cost=12046.40..275104.06 rows=568244 width=8) (actual time=664.337..6152.767 rows=568725 loops=1)
                                             Recheck Cond: ((o_orderdate >= '1994-04-01'::date) AND (o_orderdate < '1994-07-01'::date))
                                             Buffers: shared hit=221423 read=8738 written=1
                                             ->  Bitmap Index Scan on orders_o_orderdate_o_orderkey_idx  (cost=0.00..11904.34 rows=568244 width=0) (actual time=556.787..556.787 rows=570996 loops=1)
                                                   Index Cond: ((o_orderdate >= '1994-04-01'::date) AND (o_orderdate < '1994-07-01'::date))
                                                   Buffers: shared read=1557
                                 ->  Index Scan using lineitem_l_orderkey_idx_l_returnflag on lineitem  (cost=0.00..684573.86 rows=14785027 width=12) (actual time=0.158..36779.801 rows=14823447 loops=1)
                                       Buffers: shared hit=776921 read=380489 dirtied=115 written=2707
                           ->  Hash  (cost=50278.00..50278.00 rows=1500000 width=145) (actual time=3896.702..3896.702 rows=1500000 loops=1)
                                 Buckets: 262144  Batches: 1  Memory Usage: 265117kB
                                 Buffers: shared hit=23293 read=11985
                                 ->  Seq Scan on customer  (cost=0.00..50278.00 rows=1500000 width=145) (actual time=0.023..2449.530 rows=1500000 loops=1)
                                       Buffers: shared hit=23293 read=11985
                     ->  Hash  (cost=1.25..1.25 rows=25 width=30) (actual time=0.019..0.019 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=30) (actual time=0.008..0.013 rows=25 loops=1)
                                 Buffers: shared hit=1
 Total runtime: 59556.497 ms
(40 rows)

COMMIT;
COMMIT