BEGIN;
BEGIN
create or replace view revenue14 (supplier_no, total_revenue) as
	select
		l_suppkey,
		sum(l_extendedprice * (1 - l_discount))
	from
		lineitem
	where
		l_shipdate >= '1993-06-01'
		and l_shipdate < date'1993-06-01' + interval '90 days'
	group by
		l_suppkey;
CREATE VIEW
EXPLAIN (ANALYZE, BUFFERS)
select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue14
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue14
	)
order by
	s_suppkey;
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=2372997.70..2378094.78 rows=3725 width=79) (actual time=20112.888..20112.893 rows=1 loops=1)
   Merge Cond: (supplier.s_suppkey = revenue14.supplier_no)
   Buffers: shared hit=684440 read=642317 written=11633
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=1177939.20..1177939.21 rows=1 width=8) (actual time=8991.869..8991.869 rows=1 loops=1)
           Buffers: shared hit=398098 read=264776 written=11290
           ->  HashAggregate  (cost=1177855.39..1177892.64 rows=3725 width=12) (actual time=8947.215..8980.874 rows=100000 loops=1)
                 Group Key: lineitem_1.l_suppkey
                 Buffers: shared hit=398098 read=264776 written=11290
                 ->  Bitmap Heap Scan on lineitem lineitem_1  (cost=47610.56..1155324.66 rows=2253073 width=12) (actual time=1283.669..7125.527 rows=2247910 loops=1)
                       Recheck Cond: ((l_shipdate >= '1993-06-01'::date) AND (l_shipdate < '1993-08-30 00:00:00'::timestamp without time zone))
                       Heap Blocks: exact=656734
                       Buffers: shared hit=398098 read=264776 written=11290
                       ->  Bitmap Index Scan on lineitem_l_shipdate_l_suppkey__idx  (cost=0.00..47047.29 rows=2253073 width=0) (actual time=976.209..976.209 rows=2263678 loops=1)
                             Index Cond: ((l_shipdate >= '1993-06-01'::date) AND (l_shipdate < '1993-08-30 00:00:00'::timestamp without time zone))
                             Buffers: shared read=6140 written=13
   ->  Index Scan using pk_supplier on supplier  (cost=0.29..4791.49 rows=100000 width=71) (actual time=0.031..15.440 rows=36837 loops=1)
         Buffers: shared hit=1009
   ->  Sort  (cost=1195058.20..1195067.51 rows=3725 width=12) (actual time=20093.664..20093.667 rows=1 loops=1)
         Sort Key: revenue14.supplier_no
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=683431 read=642317 written=11633
         ->  Subquery Scan on revenue14  (cost=1194753.44..1194837.25 rows=3725 width=12) (actual time=20073.241..20093.631 rows=1 loops=1)
               Buffers: shared hit=683431 read=642317 written=11633
               ->  HashAggregate  (cost=1194753.44..1194800.00 rows=3725 width=12) (actual time=20073.240..20093.630 rows=1 loops=1)
                     Group Key: lineitem.l_suppkey
                     Filter: (sum((lineitem.l_extendedprice * (1::double precision - lineitem.l_discount))) = $0)
                     Rows Removed by Filter: 99999
                     Buffers: shared hit=683431 read=642317 written=11633
                     ->  Bitmap Heap Scan on lineitem  (cost=47610.56..1155324.66 rows=2253073 width=12) (actual time=1344.842..9134.261 rows=2247910 loops=1)
                           Recheck Cond: ((l_shipdate >= '1993-06-01'::date) AND (l_shipdate < '1993-08-30 00:00:00'::timestamp without time zone))
                           Heap Blocks: exact=656734
                           Buffers: shared hit=285333 read=377541 written=343
                           ->  Bitmap Index Scan on lineitem_l_shipdate_l_suppkey__idx  (cost=0.00..47047.29 rows=2253073 width=0) (actual time=1025.902..1025.902 rows=2263678 loops=1)
                                 Index Cond: ((l_shipdate >= '1993-06-01'::date) AND (l_shipdate < '1993-08-30 00:00:00'::timestamp without time zone))
                                 Buffers: shared read=6140 written=8
 Planning time: 3.190 ms
 Execution time: 20126.946 ms
(38 rows)

drop view revenue14;
DROP VIEW
COMMIT;
COMMIT