load 'auto_explain';
LOAD
set auto_explain.log_min_duration to 0;
SET
set auto_explain.log_analyze to on;
SET
set auto_explain.log_wal to on;
SET
set auto_explain.log_timing to on;
SET
set auto_explain.log_level to notice;
SET
BEGIN;
BEGIN
create or replace view revenue3 (supplier_no, total_revenue) as
	select
		l_suppkey,
		sum(l_extendedprice * (1 - l_discount))
	from
		lineitem
	where
		l_shipdate >= '1996-12-01'
		and l_shipdate < date'1996-12-01' + interval '90 days'
	group by
		l_suppkey;
CREATE VIEW
select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue3
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue3
	)
order by
	s_suppkey;
NOTICE:  duration: 27409.167 ms  plan:
Query Text: select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue3
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue3
	)
order by
	s_suppkey;
Nested Loop  (cost=3273359.54..3302459.23 rows=497 width=104) (actual time=26928.880..27409.143 rows=1 loops=1)
  InitPlan 1 (returns $1)
    ->  Aggregate  (cost=1650254.71..1650254.72 rows=1 width=32) (actual time=16320.658..16320.814 rows=1 loops=1)
          ->  Finalize GroupAggregate  (cost=1623104.53..1649013.16 rows=99324 width=36) (actual time=15975.443..16307.357 rows=100000 loops=1)
                Group Key: lineitem_1.l_suppkey
                ->  Gather Merge  (cost=1623104.53..1646281.75 rows=198648 width=36) (actual time=15975.423..16068.241 rows=199998 loops=1)
                      Workers Planned: 2
                      Workers Launched: 1
                      ->  Sort  (cost=1622104.51..1622352.82 rows=99324 width=36) (actual time=15943.606..15967.729 rows=99999 loops=2)
                            Sort Key: lineitem_1.l_suppkey
                            Sort Method: external merge  Disk: 7152kB
                            Worker 0:  Sort Method: external merge  Disk: 7152kB
                            ->  Partial HashAggregate  (cost=1600732.87..1611144.69 rows=99324 width=36) (actual time=14347.773..15865.389 rows=99999 loops=2)
                                  Group Key: lineitem_1.l_suppkey
                                  Planned Partitions: 8  Batches: 45  Memory Usage: 4305kB  Disk Usage: 48232kB
                                  Worker 0:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 48032kB
                                  ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..1535880.77 rows=939035 width=16) (actual time=0.084..10867.739 rows=1121786 loops=2)
                                        Filter: ((l_shipdate >= '1996-12-01'::date) AND (l_shipdate < '1997-03-01 00:00:00'::timestamp without time zone))
                                        Rows Removed by Filter: 28871104
  ->  Finalize GroupAggregate  (cost=1623104.53..1649261.47 rows=497 width=36) (actual time=26928.808..27408.911 rows=1 loops=1)
        Group Key: lineitem.l_suppkey
        Filter: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) = $1)
        Rows Removed by Filter: 99999
        ->  Gather Merge  (cost=1623104.53..1646281.75 rows=198648 width=36) (actual time=10554.250..10738.907 rows=299835 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Sort  (cost=1622104.51..1622352.82 rows=99324 width=36) (actual time=10516.653..10542.543 rows=99945 loops=3)
                    Sort Key: lineitem.l_suppkey
                    Sort Method: external merge  Disk: 7152kB
                    Worker 0:  Sort Method: external merge  Disk: 7152kB
                    Worker 1:  Sort Method: external merge  Disk: 7152kB
                    ->  Partial HashAggregate  (cost=1600732.87..1611144.69 rows=99324 width=36) (actual time=9335.809..10428.382 rows=99945 loops=3)
                          Group Key: lineitem.l_suppkey
                          Planned Partitions: 8  Batches: 45  Memory Usage: 4305kB  Disk Usage: 31808kB
                          Worker 0:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31992kB
                          Worker 1:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 32008kB
                          ->  Parallel Seq Scan on lineitem  (cost=0.00..1535880.77 rows=939035 width=16) (actual time=0.084..7880.338 rows=747858 loops=3)
                                Filter: ((l_shipdate >= '1996-12-01'::date) AND (l_shipdate < '1997-03-01 00:00:00'::timestamp without time zone))
                                Rows Removed by Filter: 19247403
  ->  Index Scan using pk_supplier on supplier  (cost=0.29..5.91 rows=1 width=72) (actual time=0.064..0.064 rows=1 loops=1)
        Index Cond: (s_suppkey = lineitem.l_suppkey)
 s_suppkey |          s_name           |           s_address            |     s_phone     | total_revenue 
-----------+---------------------------+--------------------------------+-----------------+---------------
     10905 | Supplier#000010905        | EoZ35MVPOmn5Od4ow2y3h,,,vJ7ZXv | 10-356-459-1255 |  2291280.4019
(1 row)

drop view revenue3;
DROP VIEW
COMMIT;
COMMIT