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 revenue2 (supplier_no, total_revenue) as
	select
		l_suppkey,
		sum(l_extendedprice * (1 - l_discount))
	from
		lineitem
	where
		l_shipdate >= '1994-05-01'
		and l_shipdate < date'1994-05-01' + interval '90 days'
	group by
		l_suppkey;
CREATE VIEW
select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue2
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue2
	)
order by
	s_suppkey;
NOTICE:  duration: 22433.183 ms  plan:
Query Text: select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue2
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue2
	)
order by
	s_suppkey;
Nested Loop  (cost=3274102.10..3303201.79 rows=497 width=104) (actual time=22283.372..22433.156 rows=1 loops=1)
  WAL: records=822 bytes=123240
  InitPlan 1 (returns $1)
    ->  Aggregate  (cost=1650625.99..1650626.00 rows=1 width=32) (actual time=13032.423..13032.574 rows=1 loops=1)
          WAL: records=822 bytes=123240
          ->  Finalize GroupAggregate  (cost=1623475.81..1649384.44 rows=99324 width=36) (actual time=12468.044..13017.067 rows=100000 loops=1)
                Group Key: lineitem_1.l_suppkey
                WAL: records=822 bytes=123240
                ->  Gather Merge  (cost=1623475.81..1646653.03 rows=198648 width=36) (actual time=12467.974..12638.226 rows=299814 loops=1)
                      Workers Planned: 2
                      Workers Launched: 2
                      WAL: records=822 bytes=123240
                      ->  Sort  (cost=1622475.79..1622724.10 rows=99324 width=36) (actual time=12391.541..12417.004 rows=99938 loops=3)
                            Sort Key: lineitem_1.l_suppkey
                            Sort Method: external merge  Disk: 7144kB
                            WAL: records=822 bytes=123240
                            Worker 0:  Sort Method: external merge  Disk: 7152kB
                            Worker 1:  Sort Method: external merge  Disk: 7152kB
                            ->  Partial HashAggregate  (cost=1601058.16..1611515.97 rows=99324 width=36) (actual time=10876.982..12293.411 rows=99938 loops=3)
                                  Group Key: lineitem_1.l_suppkey
                                  Planned Partitions: 8  Batches: 45  Memory Usage: 4305kB  Disk Usage: 31904kB
                                  WAL: records=822 bytes=123240
                                  Worker 0:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31992kB
                                  Worker 1:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31912kB
                                  ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..1535880.77 rows=943745 width=16) (actual time=0.083..9234.111 rows=748347 loops=3)
                                        Filter: ((l_shipdate >= '1994-05-01'::date) AND (l_shipdate < '1994-07-30 00:00:00'::timestamp without time zone))
                                        Rows Removed by Filter: 19246914
                                        WAL: records=822 bytes=123240
  ->  Finalize GroupAggregate  (cost=1623475.81..1649632.75 rows=497 width=36) (actual time=22283.304..22432.934 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=1623475.81..1646653.03 rows=198648 width=36) (actual time=8910.518..9043.517 rows=299819 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Sort  (cost=1622475.79..1622724.10 rows=99324 width=36) (actual time=8859.302..8884.276 rows=99940 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=1601058.16..1611515.97 rows=99324 width=36) (actual time=7999.681..8797.147 rows=99940 loops=3)
                          Group Key: lineitem.l_suppkey
                          Planned Partitions: 8  Batches: 49  Memory Usage: 4305kB  Disk Usage: 31832kB
                          Worker 0:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31928kB
                          Worker 1:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 32008kB
                          ->  Parallel Seq Scan on lineitem  (cost=0.00..1535880.77 rows=943745 width=16) (actual time=0.100..6775.866 rows=748347 loops=3)
                                Filter: ((l_shipdate >= '1994-05-01'::date) AND (l_shipdate < '1994-07-30 00:00:00'::timestamp without time zone))
                                Rows Removed by Filter: 19246914
  ->  Index Scan using pk_supplier on supplier  (cost=0.29..5.91 rows=1 width=72) (actual time=0.061..0.061 rows=1 loops=1)
        Index Cond: (s_suppkey = lineitem.l_suppkey)
 s_suppkey |          s_name           |             s_address              |     s_phone     | total_revenue 
-----------+---------------------------+------------------------------------+-----------------+---------------
     71901 | Supplier#000071901        | 06cpdc2VRhmWg2FmxfGTp9YTzh7F12e2oq | 34-507-436-8012 |  2236161.7562
(1 row)

drop view revenue2;
DROP VIEW
COMMIT;
COMMIT