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-10-01'
	and o_orderdate < cast(date '1994-10-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=1188346.60..1188346.65 rows=20 width=175) (actual time=82176.899..82176.907 rows=20 loops=1)
   Buffers: shared hit=1009294 read=414246 dirtied=1098 written=3210
   ->  Sort  (cost=1188346.60..1189760.14 rows=565417 width=175) (actual time=82176.896..82176.901 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=1009294 read=414246 dirtied=1098 written=3210
         ->  HashAggregate  (cost=1167646.89..1173301.06 rows=565417 width=175) (actual time=81618.653..82006.588 rows=381867 loops=1)
               Buffers: shared hit=1009291 read=414246 dirtied=1098 written=3210
               ->  Hash Join  (cost=398442.56..1153511.46 rows=565417 width=175) (actual time=14468.313..77288.186 rows=1150411 loops=1)
                     Hash Cond: (customer.c_nationkey = nation.n_nationkey)
                     Buffers: shared hit=1009291 read=414246 dirtied=1098 written=3210
                     ->  Hash Join  (cost=398441.00..1145735.42 rows=565417 width=153) (actual time=14468.264..76329.635 rows=1150411 loops=1)
                           Hash Cond: (orders.o_custkey = customer.c_custkey)
                           Buffers: shared hit=1009290 read=414246 dirtied=1098 written=3210
                           ->  Merge Join  (cost=329413.00..1065399.08 rows=565417 width=12) (actual time=8120.869..66921.137 rows=1150411 loops=1)
                                 Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
                                 Buffers: shared hit=1009288 read=378970 dirtied=1098 written=302
                                 ->  Sort  (cost=329413.00..330837.80 rows=569921 width=8) (actual time=8120.447..8345.931 rows=574394 loops=1)
                                       Sort Key: orders.o_orderkey
                                       Sort Method: quicksort  Memory: 51501kB
                                       Buffers: shared hit=223455 read=7391 dirtied=24 written=107
                                       ->  Bitmap Heap Scan on orders  (cost=12071.59..274927.40 rows=569921 width=8) (actual time=723.437..7791.641 rows=574394 loops=1)
                                             Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01'::date))
                                             Buffers: shared hit=223455 read=7391 dirtied=24 written=107
                                             ->  Bitmap Index Scan on orders_o_orderdate_o_orderkey_idx  (cost=0.00..11929.11 rows=569921 width=0) (actual time=614.327..614.327 rows=577248 loops=1)
                                                   Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01'::date))
                                                   Buffers: shared hit=105 read=1473 written=44
                                 ->  Index Scan using lineitem_l_orderkey_idx_l_returnflag on lineitem  (cost=0.00..690127.87 rows=14942456 width=12) (actual time=0.222..54722.298 rows=14808212 loops=1)
                                       Buffers: shared hit=785833 read=371579 dirtied=1074 written=195
                           ->  Hash  (cost=50278.00..50278.00 rows=1500000 width=145) (actual time=6345.279..6345.279 rows=1500000 loops=1)
                                 Buckets: 262144  Batches: 1  Memory Usage: 265117kB
                                 Buffers: shared hit=2 read=35276 written=2908
                                 ->  Seq Scan on customer  (cost=0.00..50278.00 rows=1500000 width=145) (actual time=0.018..3751.951 rows=1500000 loops=1)
                                       Buffers: shared hit=2 read=35276 written=2908
                     ->  Hash  (cost=1.25..1.25 rows=25 width=30) (actual time=0.027..0.027 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.017 rows=25 loops=1)
                                 Buffers: shared hit=1
 Total runtime: 82250.981 ms
(40 rows)

COMMIT;
COMMIT