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 revenue0 (supplier_no, total_revenue) as
	select
		l_suppkey,
		sum(l_extendedprice * (1 - l_discount))
	from
		lineitem
	where
		l_shipdate >= '1994-02-01'
		and l_shipdate < date'1994-02-01' + interval '90 days'
	group by
		l_suppkey;
CREATE VIEW
select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue0
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue0
	)
order by
	s_suppkey;
NOTICE:  duration: 10905.262 ms  plan:
Query Text: select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue0
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue0
	)
order by
	s_suppkey;
Nested Loop  (cost=3270916.05..3300015.73 rows=497 width=104) (actual time=10693.362..10905.250 rows=1 loops=1)
  InitPlan 1 (returns $1)
    ->  Aggregate  (cost=1649032.96..1649032.97 rows=1 width=32) (actual time=5494.499..5494.565 rows=1 loops=1)
          ->  Finalize GroupAggregate  (cost=1621882.78..1647791.41 rows=99324 width=36) (actual time=5237.575..5486.856 rows=100000 loops=1)
                Group Key: lineitem_1.l_suppkey
                ->  Gather Merge  (cost=1621882.78..1645060.00 rows=198648 width=36) (actual time=5237.561..5298.924 rows=299838 loops=1)
                      Workers Planned: 2
                      Workers Launched: 2
                      ->  Sort  (cost=1620882.76..1621131.07 rows=99324 width=36) (actual time=5229.619..5243.297 rows=99946 loops=3)
                            Sort Key: lineitem_1.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=1599094.86..1609922.94 rows=99324 width=36) (actual time=4584.064..5180.429 rows=99946 loops=3)
                                  Group Key: lineitem_1.l_suppkey
                                  Planned Partitions: 8  Batches: 45  Memory Usage: 4305kB  Disk Usage: 31944kB
                                  Worker 0:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31944kB
                                  Worker 1:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31920kB
                                  ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..1531298.89 rows=981661 width=16) (actual time=0.034..3848.996 rows=748288 loops=3)
                                        Filter: ((l_shipdate >= '1994-02-01'::date) AND (l_shipdate < '1994-05-02 00:00:00'::timestamp without time zone))
                                        Rows Removed by Filter: 19266935
  ->  Finalize GroupAggregate  (cost=1621882.78..1648039.72 rows=497 width=36) (actual time=10693.313..10905.133 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=1621882.78..1645060.00 rows=198648 width=36) (actual time=5180.364..5242.352 rows=299830 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Sort  (cost=1620882.76..1621131.07 rows=99324 width=36) (actual time=5169.731..5182.159 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=1599094.86..1609922.94 rows=99324 width=36) (actual time=4511.695..5119.905 rows=99943 loops=3)
                          Group Key: lineitem.l_suppkey
                          Planned Partitions: 8  Batches: 45  Memory Usage: 4305kB  Disk Usage: 31952kB
                          Worker 0:  Batches: 45  Memory Usage: 4305kB  Disk Usage: 31936kB
                          Worker 1:  Batches: 41  Memory Usage: 4305kB  Disk Usage: 31920kB
                          ->  Parallel Seq Scan on lineitem  (cost=0.00..1531298.89 rows=981661 width=16) (actual time=0.020..3788.017 rows=748288 loops=3)
                                Filter: ((l_shipdate >= '1994-02-01'::date) AND (l_shipdate < '1994-05-02 00:00:00'::timestamp without time zone))
                                Rows Removed by Filter: 19266935
  ->  Index Scan using pk_supplier on supplier  (cost=0.29..5.91 rows=1 width=72) (actual time=0.045..0.045 rows=1 loops=1)
        Index Cond: (s_suppkey = lineitem.l_suppkey)
 s_suppkey |          s_name           |          s_address           |     s_phone     | total_revenue 
-----------+---------------------------+------------------------------+-----------------+---------------
      8000 | Supplier#000008000        | zYtb gUcE,2oeUsUE5JSLBWeDwmX | 19-875-478-4736 |  2248395.3896
(1 row)

drop view revenue0;
DROP VIEW
COMMIT;
COMMIT