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-05-01'
	and l_shipdate < cast(date '1996-05-01' + interval '1 month' as date);
NOTICE:  duration: 12157.335 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-05-01'
	and l_shipdate < cast(date '1996-05-01' + interval '1 month' as date);
Finalize Aggregate  (cost=1617951.27..1617951.28 rows=1 width=32) (actual time=12074.051..12157.319 rows=1 loops=1)
  ->  Gather  (cost=1617951.03..1617951.24 rows=2 width=64) (actual time=12072.623..12157.295 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=1616951.03..1616951.04 rows=1 width=64) (actual time=12057.460..12057.573 rows=1 loops=3)
              ->  Parallel Hash Join  (cost=65410.40..1611118.98 rows=333260 width=33) (actual time=11339.893..11744.002 rows=257633 loops=3)
                    Hash Cond: (lineitem.l_partkey = part.p_partkey)
                    ->  Parallel Seq Scan on lineitem  (cost=0.00..1535880.77 rows=333260 width=16) (actual time=0.100..10349.499 rows=257633 loops=3)
                          Filter: ((l_shipdate >= '1996-05-01'::date) AND (l_shipdate < '1996-06-01'::date))
                          Rows Removed by Filter: 19737628
                    ->  Parallel Hash  (cost=49296.51..49296.51 rows=833351 width=25) (actual time=694.908..694.909 rows=666667 loops=3)
                          Buckets: 65536  Batches: 64  Memory Usage: 2432kB
                          ->  Parallel Seq Scan on part  (cost=0.00..49296.51 rows=833351 width=25) (actual time=0.042..312.055 rows=666667 loops=3)
    promo_revenue    
---------------------
 16.6104182758870086
(1 row)

COMMIT;
COMMIT