BEGIN;
BEGIN
create or replace view revenue6 (supplier_no, total_revenue) as
	select
		l_suppkey,
		sum(l_extendedprice * (1 - l_discount))
	from
		lineitem
	where
		l_shipdate >= '1993-12-01'
		and l_shipdate < date'1993-12-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,
	revenue6
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue6
	)
order by
	s_suppkey;
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=2360746.33..2365843.46 rows=3711 width=79) (actual time=38593.939..38593.943 rows=1 loops=1)
   Merge Cond: (supplier.s_suppkey = revenue6.supplier_no)
   Buffers: shared hit=421707 read=896788 dirtied=329 written=3628
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=1171885.00..1171885.01 rows=1 width=8) (actual time=17294.992..17294.992 rows=1 loops=1)
           Buffers: shared hit=211051 read=448085 written=22
           ->  HashAggregate  (cost=1171801.50..1171838.61 rows=3711 width=12) (actual time=17215.025..17277.561 rows=100000 loops=1)
                 Buffers: shared hit=211051 read=448085 written=22
                 ->  Bitmap Heap Scan on lineitem  (cost=47339.12..1149459.75 rows=2234175 width=12) (actual time=2250.630..13459.846 rows=2243375 loops=1)
                       Recheck Cond: ((l_shipdate >= '1993-12-01'::date) AND (l_shipdate < '1994-03-01 00:00:00'::timestamp without time zone))
                       Buffers: shared hit=211051 read=448085 written=22
                       ->  Bitmap Index Scan on lineitem_l_shipdate_l_suppkey__idx  (cost=0.00..46780.58 rows=2234175 width=0) (actual time=1788.059..1788.059 rows=2247739 loops=1)
                             Index Cond: ((l_shipdate >= '1993-12-01'::date) AND (l_shipdate < '1994-03-01 00:00:00'::timestamp without time zone))
                             Buffers: shared read=6128
   ->  Index Scan using pk_supplier on supplier  (cost=0.00..4791.46 rows=100000 width=71) (actual time=0.100..4.473 rows=7917 loops=1)
         Buffers: shared hit=221 read=1
   ->  Sort  (cost=1188861.33..1188870.60 rows=3711 width=12) (actual time=38588.133..38588.134 rows=1 loops=1)
         Sort Key: revenue6.supplier_no
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=421486 read=896787 dirtied=329 written=3628
         ->  Subquery Scan on revenue6  (cost=1188557.81..1188641.31 rows=3711 width=12) (actual time=38581.627..38588.093 rows=1 loops=1)
               Buffers: shared hit=421486 read=896787 dirtied=329 written=3628
               ->  HashAggregate  (cost=1188557.81..1188604.20 rows=3711 width=12) (actual time=38581.623..38588.087 rows=1 loops=1)
                     Filter: (sum((public.lineitem.l_extendedprice * (1::double precision - public.lineitem.l_discount))) = $0)
                     Rows Removed by Filter: 99999
                     Buffers: shared hit=421486 read=896787 dirtied=329 written=3628
                     ->  Bitmap Heap Scan on lineitem  (cost=47339.12..1149459.75 rows=2234175 width=12) (actual time=2431.342..16910.205 rows=2243375 loops=1)
                           Recheck Cond: ((l_shipdate >= '1993-12-01'::date) AND (l_shipdate < '1994-03-01 00:00:00'::timestamp without time zone))
                           Buffers: shared hit=210435 read=448702 dirtied=329 written=3606
                           ->  Bitmap Index Scan on lineitem_l_shipdate_l_suppkey__idx  (cost=0.00..46780.58 rows=2234175 width=0) (actual time=1980.942..1980.942 rows=2247739 loops=1)
                                 Index Cond: ((l_shipdate >= '1993-12-01'::date) AND (l_shipdate < '1994-03-01 00:00:00'::timestamp without time zone))
                                 Buffers: shared hit=484 read=5645 written=1025
 Total runtime: 38627.601 ms
(33 rows)

drop view revenue6;
DROP VIEW
COMMIT;
COMMIT