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 supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'JAPAN' and n2.n_name = 'SAUDI ARABIA') or (n1.n_name = 'SAUDI ARABIA' and n2.n_name = 'JAPAN') ) and l_shipdate between date '1995-01-01' and date '1996-12-31' ) as shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year; NOTICE: duration: 18253.896 ms plan: Query Text: select supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'JAPAN' and n2.n_name = 'SAUDI ARABIA') or (n1.n_name = 'SAUDI ARABIA' and n2.n_name = 'JAPAN') ) and l_shipdate between date '1995-01-01' and date '1996-12-31' ) as shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year; Finalize GroupAggregate (cost=1989406.70..1992716.57 rows=10024 width=116) (actual time=18145.827..18253.856 rows=4 loops=1) Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) -> Gather Merge (cost=1989406.70..1992315.61 rows=20048 width=116) (actual time=18131.577..18253.783 rows=12 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=1988406.67..1989001.55 rows=10024 width=116) (actual time=18114.907..18137.795 rows=4 loops=3) Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) -> Sort (cost=1988406.67..1988470.17 rows=25401 width=96) (actual time=18108.966..18111.739 rows=19358 loops=3) Sort Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) Sort Method: quicksort Memory: 3170kB Worker 0: Sort Method: quicksort Memory: 3185kB Worker 1: Sort Method: quicksort Memory: 3568kB -> Parallel Hash Join (cost=400910.21..1986548.26 rows=25401 width=96) (actual time=17560.537..18069.343 rows=19358 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Join Filter: (((n1.n_name = 'JAPAN'::bpchar) AND (n2.n_name = 'SAUDI ARABIA'::bpchar)) OR ((n1.n_name = 'SAUDI ARABIA'::bpchar) AND (n2.n_name = 'JAPAN'::bpchar))) Rows Removed by Join Filter: 19254 -> Parallel Hash Join (cost=2083.94..1569118.69 rows=610369 width=50) (actual time=22.541..12353.088 rows=487597 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Parallel Seq Scan on lineitem (cost=0.00..1535880.77 rows=7629607 width=28) (actual time=4.751..9928.083 rows=6076499 loops=3) Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date)) Rows Removed by Filter: 13918762 -> Parallel Hash (cost=2042.28..2042.28 rows=3333 width=30) (actual time=17.528..17.532 rows=2673 loops=3) Buckets: 8192 Batches: 1 Memory Usage: 576kB -> Hash Join (cost=1.69..2042.28 rows=3333 width=30) (actual time=10.404..16.458 rows=2673 loops=3) Hash Cond: (supplier.s_nationkey = n1.n_nationkey) -> Parallel Index Only Scan using supplier_s_nationkey_s_suppkey_idx on supplier (cost=0.29..1912.96 rows=41667 width=8) (actual time=0.185..11.890 rows=33333 loops=3) Heap Fetches: 0 -> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.029..0.031 rows=2 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation n1 (cost=0.00..1.38 rows=2 width=30) (actual time=0.022..0.026 rows=2 loops=3) Filter: ((n_name = 'JAPAN'::bpchar) OR (n_name = 'SAUDI ARABIA'::bpchar)) Rows Removed by Filter: 23 -> Parallel Hash (cost=388629.91..388629.91 rows=501948 width=34) (actual time=4717.963..4717.967 rows=398177 loops=3) Buckets: 65536 Batches: 32 Memory Usage: 3200kB -> Parallel Hash Join (cost=31140.12..388629.91 rows=501948 width=34) (actual time=310.788..4443.690 rows=398177 loops=3) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Parallel Seq Scan on orders (cost=0.00..331869.51 rows=6274351 width=12) (actual time=0.048..1420.344 rows=5000000 loops=3) -> Parallel Hash (cost=30515.09..30515.09 rows=50002 width=30) (actual time=308.628..308.631 rows=39853 loops=3) Buckets: 131072 Batches: 1 Memory Usage: 8576kB -> Hash Join (cost=1.83..30515.09 rows=50002 width=30) (actual time=110.057..266.231 rows=39853 loops=3) Hash Cond: (customer.c_nationkey = n2.n_nationkey) -> Parallel Index Only Scan using customer_c_nationkey_c_custkey_idx on customer (cost=0.43..28594.88 rows=625020 width=8) (actual time=0.064..193.217 rows=500000 loops=3) Heap Fetches: 0 -> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.031..0.031 rows=2 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation n2 (cost=0.00..1.38 rows=2 width=30) (actual time=0.021..0.024 rows=2 loops=3) Filter: ((n_name = 'SAUDI ARABIA'::bpchar) OR (n_name = 'JAPAN'::bpchar)) Rows Removed by Filter: 23 supp_nation | cust_nation | l_year | revenue ---------------------------+---------------------------+--------+---------------- JAPAN | SAUDI ARABIA | 1995 | 518573285.6295 JAPAN | SAUDI ARABIA | 1996 | 526272932.5928 SAUDI ARABIA | JAPAN | 1995 | 536083157.1737 SAUDI ARABIA | JAPAN | 1996 | 522972027.0472 (4 rows) COMMIT; COMMIT