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-12-01'
	and l_shipdate < cast(date '1996-12-01' + interval '1 month' as date);
NOTICE:  duration: 12188.379 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-12-01'
	and l_shipdate < cast(date '1996-12-01' + interval '1 month' as date);
Finalize Aggregate  (cost=1617859.60..1617859.62 rows=1 width=32) (actual time=12002.422..12188.366 rows=1 loops=1)
  ->  Gather  (cost=1617859.37..1617859.58 rows=2 width=64) (actual time=12002.399..12188.345 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=1616859.37..1616859.38 rows=1 width=64) (actual time=11986.131..11986.137 rows=1 loops=3)
              ->  Parallel Hash Join  (cost=65410.40..1611080.93 rows=330196 width=33) (actual time=11250.887..11671.270 rows=257469 loops=3)
                    Hash Cond: (lineitem.l_partkey = part.p_partkey)
                    ->  Parallel Seq Scan on lineitem  (cost=0.00..1535880.77 rows=330196 width=16) (actual time=0.073..10228.936 rows=257469 loops=3)
                          Filter: ((l_shipdate >= '1996-12-01'::date) AND (l_shipdate < '1997-01-01'::date))
                          Rows Removed by Filter: 19737792
                    ->  Parallel Hash  (cost=49296.51..49296.51 rows=833351 width=25) (actual time=762.539..762.540 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.039..336.317 rows=666667 loops=3)
    promo_revenue    
---------------------
 16.5839904999307438
(1 row)

COMMIT;
COMMIT