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 n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AFRICA' and o_orderdate >= date '1993-01-01' and o_orderdate < date '1993-01-01' + interval '1 year' group by n_name order by revenue desc; NOTICE: duration: 10400.279 ms plan: Query Text: select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AFRICA' and o_orderdate >= date '1993-01-01' and o_orderdate < date '1993-01-01' + interval '1 year' group by n_name order by revenue desc; Sort (cost=2082891.05..2082891.11 rows=25 width=58) (actual time=10161.375..10400.267 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: quicksort Memory: 25kB -> Finalize GroupAggregate (cost=2082501.53..2082890.47 rows=25 width=58) (actual time=10150.142..10400.240 rows=5 loops=1) Group Key: nation.n_name -> Gather Merge (cost=2082501.53..2082889.78 rows=50 width=58) (actual time=10147.340..10400.210 rows=15 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=2081501.51..2081883.99 rows=25 width=58) (actual time=10143.769..10154.883 rows=5 loops=3) Group Key: nation.n_name -> Sort (cost=2081501.51..2081577.94 rows=30573 width=38) (actual time=10140.973..10142.475 rows=24174 loops=3) Sort Key: nation.n_name Sort Method: quicksort Memory: 2659kB Worker 0: Sort Method: quicksort Memory: 2659kB Worker 1: Sort Method: quicksort Memory: 2653kB -> Parallel Hash Join (cost=421432.81..2079223.83 rows=30573 width=38) (actual time=9994.311..10131.855 rows=24174 loops=3) Hash Cond: ((orders.o_custkey = customer.c_custkey) AND (supplier.s_nationkey = customer.c_nationkey)) -> Parallel Hash Join (cost=381020.63..2017424.01 rows=764503 width=50) (actual time=8824.549..9641.222 rows=603018 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Parallel Hash Join (cost=2248.79..1523122.58 rows=5003652 width=54) (actual time=7.983..6363.723 rows=3969543 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Parallel Seq Scan on lineitem (cost=0.00..1406207.60 rows=25018260 width=24) (actual time=0.015..2558.392 rows=20015223 loops=3) -> Parallel Hash (cost=2144.63..2144.63 rows=8333 width=38) (actual time=7.893..7.897 rows=6612 loops=3) Buckets: 32768 Batches: 1 Memory Usage: 1696kB -> Merge Join (cost=2.80..2144.63 rows=8333 width=38) (actual time=0.168..6.000 rows=6612 loops=3) Merge Cond: (supplier.s_nationkey = nation.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.103..3.212 rows=22667 loops=3) Heap Fetches: 0 -> Sort (cost=2.50..2.52 rows=5 width=30) (actual time=0.062..0.393 rows=5305 loops=3) Sort Key: nation.n_nationkey Sort Method: quicksort Memory: 25kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB -> Hash Join (cost=1.07..2.45 rows=5 width=30) (actual time=0.036..0.043 rows=5 loops=3) Hash Cond: (nation.n_regionkey = region.r_regionkey) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=34) (actual time=0.012..0.014 rows=25 loops=3) -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=3) Filter: (r_name = 'AFRICA'::bpchar) Rows Removed by Filter: 4 -> Parallel Hash (cost=362157.46..362157.46 rows=955791 width=12) (actual time=1019.859..1019.860 rows=759631 loops=3) Buckets: 131072 Batches: 64 Memory Usage: 2752kB -> Parallel Seq Scan on orders (cost=0.00..362157.46 rows=955791 width=12) (actual time=0.021..851.573 rows=759631 loops=3) Filter: ((o_orderdate >= '1993-01-01'::date) AND (o_orderdate < '1994-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 4245369 -> Parallel Hash (cost=28594.88..28594.88 rows=625020 width=8) (actual time=152.551..152.552 rows=500000 loops=3) Buckets: 131072 Batches: 32 Memory Usage: 2880kB -> 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.037..71.412 rows=500000 loops=3) Heap Fetches: 0 n_name | revenue ---------------------------+---------------- KENYA | 540243387.8079 ETHIOPIA | 528525701.7613 MOROCCO | 527043999.5603 ALGERIA | 524770552.6065 MOZAMBIQUE | 518635807.9872 (5 rows) COMMIT; COMMIT