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 = 'SAUDI ARABIA' and n2.n_name = 'CANADA') or (n1.n_name = 'CANADA' and n2.n_name = 'SAUDI ARABIA') ) 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: 25599.377 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 = 'SAUDI ARABIA' and n2.n_name = 'CANADA') or (n1.n_name = 'CANADA' and n2.n_name = 'SAUDI ARABIA') ) 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=25416.211..25599.329 rows=4 loops=1) Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) WAL: records=596 bytes=90886 -> Gather Merge (cost=1989406.70..1992315.61 rows=20048 width=116) (actual time=25384.631..25599.278 rows=8 loops=1) Workers Planned: 2 Workers Launched: 1 WAL: records=596 bytes=90886 -> Partial GroupAggregate (cost=1988406.67..1989001.55 rows=10024 width=116) (actual time=25354.773..25420.899 rows=4 loops=2) Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) WAL: records=596 bytes=90886 -> Sort (cost=1988406.67..1988470.17 rows=25401 width=96) (actual time=25337.732..25356.001 rows=29180 loops=2) Sort Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) Sort Method: external merge Disk: 2088kB WAL: records=596 bytes=90886 Worker 0: Sort Method: external merge Disk: 2520kB -> Parallel Hash Join (cost=400910.21..1986548.26 rows=25401 width=96) (actual time=24866.821..25279.944 rows=29180 loops=2) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Join Filter: (((n1.n_name = 'SAUDI ARABIA'::bpchar) AND (n2.n_name = 'CANADA'::bpchar)) OR ((n1.n_name = 'CANADA'::bpchar) AND (n2.n_name = 'SAUDI ARABIA'::bpchar))) Rows Removed by Join Filter: 29062 WAL: records=596 bytes=90886 -> Parallel Hash Join (cost=2083.94..1569118.69 rows=610369 width=50) (actual time=42.735..16349.576 rows=734884 loops=2) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) WAL: records=302 bytes=45058 -> Parallel Seq Scan on lineitem (cost=0.00..1535880.77 rows=7629607 width=28) (actual time=0.059..13181.517 rows=9114748 loops=2) Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date)) Rows Removed by Filter: 20878142 WAL: records=302 bytes=45058 -> Parallel Hash (cost=2042.28..2042.28 rows=3333 width=30) (actual time=38.561..38.564 rows=4032 loops=2) Buckets: 8192 Batches: 1 Memory Usage: 576kB -> Hash Join (cost=1.69..2042.28 rows=3333 width=30) (actual time=2.131..32.154 rows=4032 loops=2) 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.126..14.374 rows=50000 loops=2) Heap Fetches: 0 -> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.026..0.027 rows=2 loops=2) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation n1 (cost=0.00..1.38 rows=2 width=30) (actual time=0.016..0.023 rows=2 loops=2) Filter: ((n_name = 'SAUDI ARABIA'::bpchar) OR (n_name = 'CANADA'::bpchar)) Rows Removed by Filter: 23 -> Parallel Hash (cost=388629.91..388629.91 rows=501948 width=34) (actual time=7038.775..7038.780 rows=595280 loops=2) Buckets: 65536 Batches: 32 Memory Usage: 3200kB WAL: records=294 bytes=45828 -> Parallel Hash Join (cost=31140.12..388629.91 rows=501948 width=34) (actual time=403.458..6582.028 rows=595280 loops=2) Hash Cond: (orders.o_custkey = customer.c_custkey) WAL: records=294 bytes=45828 -> Parallel Seq Scan on orders (cost=0.00..331869.51 rows=6274351 width=12) (actual time=0.043..2164.964 rows=7500000 loops=2) WAL: records=294 bytes=45828 -> Parallel Hash (cost=30515.09..30515.09 rows=50002 width=30) (actual time=402.712..402.715 rows=59826 loops=2) Buckets: 131072 Batches: 1 Memory Usage: 8544kB -> Hash Join (cost=1.83..30515.09 rows=50002 width=30) (actual time=51.190..373.134 rows=59826 loops=2) 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.076..262.932 rows=750000 loops=2) Heap Fetches: 0 -> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.041..0.042 rows=2 loops=2) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation n2 (cost=0.00..1.38 rows=2 width=30) (actual time=0.026..0.034 rows=2 loops=2) Filter: ((n_name = 'CANADA'::bpchar) OR (n_name = 'SAUDI ARABIA'::bpchar)) Rows Removed by Filter: 23 supp_nation | cust_nation | l_year | revenue ---------------------------+---------------------------+--------+---------------- CANADA | SAUDI ARABIA | 1995 | 535051803.3709 CANADA | SAUDI ARABIA | 1996 | 539639571.1315 SAUDI ARABIA | CANADA | 1995 | 528525732.8591 SAUDI ARABIA | CANADA | 1996 | 532371951.6340 (4 rows) COMMIT; COMMIT