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-16'
	and l_shipdate > date '1995-03-16'
group by
	l_orderkey,
	o_orderdate,
	o_shippriority
order by
	revenue desc,
	o_orderdate
LIMIT 10;
NOTICE:  duration: 28420.459 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-16'
	and l_shipdate > date '1995-03-16'
group by
	l_orderkey,
	o_orderdate,
	o_shippriority
order by
	revenue desc,
	o_orderdate
LIMIT 10;
Limit  (cost=2763618.08..2763618.10 rows=10 width=48) (actual time=27490.418..28420.430 rows=10 loops=1)
  ->  Sort  (cost=2763618.08..2771359.24 rows=3096463 width=48) (actual time=27490.416..28420.425 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=2289197.65..2696704.63 rows=3096463 width=48) (actual time=26861.845..28345.524 rows=114083 loops=1)
              Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
              ->  Gather Merge  (cost=2289197.65..2625744.01 rows=2580386 width=48) (actual time=26861.818..28104.594 rows=114090 loops=1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Partial GroupAggregate  (cost=2288197.63..2326903.42 rows=1290193 width=48) (actual time=26824.092..26994.268 rows=38030 loops=3)
                          Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
                          ->  Sort  (cost=2288197.63..2291423.11 rows=1290193 width=28) (actual time=26824.063..26844.270 rows=100800 loops=3)
                                Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
                                Sort Method: external merge  Disk: 3784kB
                                Worker 0:  Sort Method: external merge  Disk: 4032kB
                                Worker 1:  Sort Method: external merge  Disk: 3888kB
                                ->  Parallel Hash Join  (cost=410956.03..2095508.49 rows=1290193 width=28) (actual time=24269.487..26719.437 rows=100800 loops=3)
                                      Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
                                      ->  Parallel Seq Scan on lineitem  (cost=0.00..1468981.94 rows=13398055 width=20) (actual time=0.060..12368.879 rows=10769472 loops=3)
                                            Filter: (l_shipdate > '1995-03-16'::date)
                                            Rows Removed by Filter: 9225638
                                      ->  Parallel Hash  (cost=400484.05..400484.05 rows=602399 width=16) (actual time=5786.796..5786.800 rows=487761 loops=3)
                                            Buckets: 131072  Batches: 32  Memory Usage: 3200kB
                                            ->  Parallel Hash Join  (cost=9570.25..400484.05 rows=602399 width=16) (actual time=4479.198..5533.648 rows=487761 loops=3)
                                                  Hash Cond: (orders.o_custkey = customer.c_custkey)
                                                  ->  Parallel Seq Scan on orders  (cost=0.00..346518.38 rows=3061484 width=20) (actual time=0.058..2912.739 rows=2431973 loops=3)
                                                        Filter: (o_orderdate < '1995-03-16'::date)
                                                        Rows Removed by Filter: 2568027
                                                  ->  Parallel Hash  (cost=7551.96..7551.96 rows=122983 width=4) (actual time=168.213..168.214 rows=100092 loops=3)
                                                        Buckets: 131072  Batches: 4  Memory Usage: 4032kB
                                                        ->  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.146..84.863 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
    3355202 | 425100.6657 | 1995-03-04  |              0
    9806272 | 425088.0568 | 1995-03-13  |              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