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
select
	100.00 * sum(case
		when p_type like 'PROMO%'
			then l_extendedprice * (1 - l_discount)
		else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
	lineitem,
	part
where
	l_partkey = p_partkey
	and l_shipdate >= date '1996-09-01'
	and l_shipdate < cast(date '1996-09-01' + interval '1 month' as date);
NOTICE:  duration: 11876.271 ms  plan:
Query Text: select
	100.00 * sum(case
		when p_type like 'PROMO%'
			then l_extendedprice * (1 - l_discount)
		else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
	lineitem,
	part
where
	l_partkey = p_partkey
	and l_shipdate >= date '1996-09-01'
	and l_shipdate < cast(date '1996-09-01' + interval '1 month' as date);
Finalize Aggregate  (cost=1612572.94..1612572.95 rows=1 width=32) (actual time=11800.214..11876.256 rows=1 loops=1)
  ->  Gather  (cost=1612572.70..1612572.91 rows=2 width=64) (actual time=11793.242..11876.224 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=1611572.70..1611572.71 rows=1 width=64) (actual time=11774.218..11774.313 rows=1 loops=3)
              ->  Parallel Hash Join  (cost=65410.40..1606229.51 rows=305325 width=33) (actual time=11043.783..11447.738 rows=249865 loops=3)
                    Hash Cond: (lineitem.l_partkey = part.p_partkey)
                    ->  Parallel Seq Scan on lineitem  (cost=0.00..1531338.63 rows=305325 width=16) (actual time=0.090..10045.123 rows=249865 loops=3)
                          Filter: ((l_shipdate >= '1996-09-01'::date) AND (l_shipdate < '1996-10-01'::date))
                          Rows Removed by Filter: 19745245
                    ->  Parallel Hash  (cost=49296.51..49296.51 rows=833351 width=25) (actual time=733.407..733.408 rows=666667 loops=3)
                          Buckets: 65536  Batches: 64  Memory Usage: 2464kB
                          ->  Parallel Seq Scan on part  (cost=0.00..49296.51 rows=833351 width=25) (actual time=0.038..347.477 rows=666667 loops=3)
    promo_revenue    
---------------------
 16.6345447139855123
(1 row)

COMMIT;
COMMIT