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 revenue1 (supplier_no, total_revenue) as
	select
		l_suppkey,
		sum(l_extendedprice * (1 - l_discount))
	from
		lineitem
	where
		l_shipdate >= '1996-09-01'
		and l_shipdate < date'1996-09-01' + interval '90 days'
	group by
		l_suppkey;
CREATE VIEW
select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue1
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue1
	)
order by
	s_suppkey;
NOTICE:  duration: 27579.453 ms  plan:
Query Text: select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue1
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue1
	)
order by
	s_suppkey;
Nested Loop  (cost=3275074.37..3304174.05 rows=497 width=104) (actual time=27379.221..27579.431 rows=1 loops=1)
  InitPlan 1 (returns $1)
    ->  Aggregate  (cost=1651112.12..1651112.13 rows=1 width=32) (actual time=15451.057..15451.206 rows=1 loops=1)
          ->  Finalize GroupAggregate  (cost=1623961.94..1649870.57 rows=99324 width=36) (actual time=15045.374..15436.185 rows=100000 loops=1)
                Group Key: lineitem_1.l_suppkey
                ->  Gather Merge  (cost=1623961.94..1647139.16 rows=198648 width=36) (actual time=15045.353..15174.147 rows=199998 loops=1)
                      Workers Planned: 2
                      Workers Launched: 1
                      ->  Sort  (cost=1622961.92..1623210.23 rows=99324 width=36) (actual time=14996.582..15023.112 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=1601484.07..1612002.10 rows=99324 width=36) (actual time=13385.502..14916.907 rows=99999 loops=2)
                                  Group Key: lineitem_1.l_suppkey
                                  Planned Partitions: 8  Batches: 45  Memory Usage: 4305kB  Disk Usage: 48064kB
                                  Worker 0:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 48224kB
                                  ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..1535880.77 rows=949912 width=16) (actual time=0.051..11334.553 rows=1123372 loops=2)
                                        Filter: ((l_shipdate >= '1996-09-01'::date) AND (l_shipdate < '1996-11-30 00:00:00'::timestamp without time zone))
                                        Rows Removed by Filter: 28869518
  ->  Finalize GroupAggregate  (cost=1623961.94..1650118.88 rows=497 width=36) (actual time=27379.166..27579.223 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=1623961.94..1647139.16 rows=198648 width=36) (actual time=11561.121..11760.353 rows=299830 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Sort  (cost=1622961.92..1623210.23 rows=99324 width=36) (actual time=11521.223..11547.730 rows=99943 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=1601484.07..1612002.10 rows=99324 width=36) (actual time=10369.189..11438.940 rows=99943 loops=3)
                          Group Key: lineitem.l_suppkey
                          Planned Partitions: 8  Batches: 45  Memory Usage: 4305kB  Disk Usage: 31904kB
                          Worker 0:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 32008kB
                          Worker 1:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31888kB
                          ->  Parallel Seq Scan on lineitem  (cost=0.00..1535880.77 rows=949912 width=16) (actual time=0.060..8823.560 rows=748915 loops=3)
                                Filter: ((l_shipdate >= '1996-09-01'::date) AND (l_shipdate < '1996-11-30 00:00:00'::timestamp without time zone))
                                Rows Removed by Filter: 19246346
  ->  Index Scan using pk_supplier on supplier  (cost=0.29..5.91 rows=1 width=72) (actual time=0.048..0.048 rows=1 loops=1)
        Index Cond: (s_suppkey = lineitem.l_suppkey)
 s_suppkey |          s_name           |  s_address  |     s_phone     | total_revenue 
-----------+---------------------------+-------------+-----------------+---------------
     67991 | Supplier#000067991        | NRFVp2OkcZx | 14-973-600-8118 |  2234643.7320
(1 row)

drop view revenue1;
DROP VIEW
COMMIT;
COMMIT