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
	l_orderkey,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	o_orderdate,
	o_shippriority
from
	customer,
	orders,
	lineitem
where
	c_mktsegment = 'BUILDING'
	and c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate < date '1995-03-18'
	and l_shipdate > date '1995-03-18'
group by
	l_orderkey,
	o_orderdate,
	o_shippriority
order by
	revenue desc,
	o_orderdate
LIMIT 10;
NOTICE:  duration: 24090.856 ms  plan:
Query Text: select
	l_orderkey,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	o_orderdate,
	o_shippriority
from
	customer,
	orders,
	lineitem
where
	c_mktsegment = 'BUILDING'
	and c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate < date '1995-03-18'
	and l_shipdate > date '1995-03-18'
group by
	l_orderkey,
	o_orderdate,
	o_shippriority
order by
	revenue desc,
	o_orderdate
LIMIT 10;
Limit  (cost=2771457.68..2771457.71 rows=10 width=48) (actual time=23619.749..24090.831 rows=10 loops=1)
  ->  Sort  (cost=2771457.68..2779222.60 rows=3105969 width=48) (actual time=23619.747..24090.827 rows=10 loops=1)
        Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Finalize GroupAggregate  (cost=2295580.76..2704338.81 rows=3105969 width=48) (actual time=23237.418..24044.196 rows=114001 loops=1)
              Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
              ->  Gather Merge  (cost=2295580.76..2633160.34 rows=2588308 width=48) (actual time=23237.395..23882.321 rows=114009 loops=1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Partial GroupAggregate  (cost=2294580.73..2333405.35 rows=1294154 width=48) (actual time=23220.562..23372.328 rows=38003 loops=3)
                          Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
                          ->  Sort  (cost=2294580.73..2297816.12 rows=1294154 width=28) (actual time=23220.536..23240.054 rows=100575 loops=3)
                                Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
                                Sort Method: external merge  Disk: 3776kB
                                Worker 0:  Sort Method: external merge  Disk: 4344kB
                                Worker 1:  Sort Method: external merge  Disk: 3560kB
                                ->  Parallel Hash Join  (cost=412251.03..2101271.95 rows=1294154 width=28) (actual time=20719.816..23125.672 rows=100575 loops=3)
                                      Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
                                      ->  Parallel Seq Scan on lineitem  (cost=0.00..1473147.97 rows=13415636 width=20) (actual time=0.076..11023.290 rows=10752755 loops=3)
                                            Filter: (l_shipdate > '1995-03-18'::date)
                                            Rows Removed by Filter: 9242505
                                      ->  Parallel Hash  (cost=401729.26..401729.26 rows=605262 width=16) (actual time=3930.861..3930.865 rows=488537 loops=3)
                                            Buckets: 131072  Batches: 32  Memory Usage: 3232kB
                                            ->  Parallel Hash Join  (cost=9570.25..401729.26 rows=605262 width=16) (actual time=2761.583..3721.323 rows=488537 loops=3)
                                                  Hash Cond: (orders.o_custkey = customer.c_custkey)
                                                  ->  Parallel Seq Scan on orders  (cost=0.00..347555.39 rows=3076037 width=20) (actual time=0.044..1783.923 rows=2436174 loops=3)
                                                        Filter: (o_orderdate < '1995-03-18'::date)
                                                        Rows Removed by Filter: 2563826
                                                  ->  Parallel Hash  (cost=7551.96..7551.96 rows=122983 width=4) (actual time=80.811..80.812 rows=100092 loops=3)
                                                        Buckets: 131072  Batches: 4  Memory Usage: 4000kB
                                                        ->  Parallel Index Only Scan using customer_c_mktsegment_c_custkey_idx on customer  (cost=0.43..7551.96 rows=122983 width=4) (actual time=0.114..46.589 rows=100092 loops=3)
                                                              Index Cond: (c_mktsegment = 'BUILDING'::bpchar)
                                                              Heap Fetches: 0
 l_orderkey |   revenue   | o_orderdate | o_shippriority 
------------+-------------+-------------+----------------
    4791171 | 440715.2185 | 1995-02-23  |              0
   46678469 | 439855.3250 | 1995-01-27  |              0
   29226981 | 431838.6706 | 1995-03-15  |              0
   23861382 | 428739.1368 | 1995-03-09  |              0
   59393639 | 426036.0662 | 1995-02-12  |              0
   53148064 | 425235.9089 | 1995-03-17  |              0
    3355202 | 425100.6657 | 1995-03-04  |              0
   22810436 | 423231.9690 | 1995-01-02  |              0
   16384100 | 421478.7294 | 1995-03-02  |              0
   59952421 | 418824.7718 | 1995-03-15  |              0
(10 rows)

COMMIT;
COMMIT