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 = 'AMERICA' 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: 30119.707 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 = 'AMERICA' 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=2088359.04..2088359.10 rows=25 width=58) (actual time=29622.395..30119.686 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: quicksort Memory: 25kB -> Finalize GroupAggregate (cost=2087968.43..2088358.46 rows=25 width=58) (actual time=29588.158..30119.638 rows=5 loops=1) Group Key: nation.n_name -> Gather Merge (cost=2087968.43..2088357.77 rows=50 width=58) (actual time=29582.075..30119.572 rows=15 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=2086968.41..2087351.97 rows=25 width=58) (actual time=29559.874..29591.951 rows=5 loops=3) Group Key: nation.n_name -> Sort (cost=2086968.41..2087045.06 rows=30660 width=38) (actual time=29555.407..29558.217 rows=24014 loops=3) Sort Key: nation.n_name Sort Method: quicksort Memory: 3232kB Worker 0: Sort Method: quicksort Memory: 2279kB Worker 1: Sort Method: quicksort Memory: 2423kB -> Parallel Hash Join (cost=422190.11..2084683.62 rows=30660 width=38) (actual time=29104.891..29515.959 rows=24014 loops=3) Hash Cond: ((orders.o_custkey = customer.c_custkey) AND (supplier.s_nationkey = customer.c_nationkey)) -> Parallel Hash Join (cost=381777.93..2022828.40 rows=766675 width=50) (actual time=25684.716..27790.321 rows=608202 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Parallel Hash Join (cost=2248.79..1527444.34 rows=5017870 width=54) (actual time=49.204..17354.208 rows=4009843 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Parallel Seq Scan on lineitem (cost=0.00..1410203.52 rows=25089352 width=24) (actual time=0.047..6384.717 rows=19995218 loops=3) -> Parallel Hash (cost=2144.63..2144.63 rows=8333 width=38) (actual time=43.844..43.848 rows=6684 loops=3) Buckets: 32768 Batches: 1 Memory Usage: 1728kB -> Merge Join (cost=2.80..2144.63 rows=8333 width=38) (actual time=3.401..31.673 rows=6684 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=2.874..22.929 rows=33333 loops=3) Heap Fetches: 0 -> Sort (cost=2.50..2.52 rows=5 width=30) (actual time=0.118..0.665 rows=5351 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.051..0.062 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.019..0.022 rows=25 loops=3) -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.022..0.024 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.018..0.020 rows=1 loops=3) Filter: (r_name = 'AMERICA'::bpchar) Rows Removed by Filter: 4 -> Parallel Hash (cost=362880.89..362880.89 rows=957700 width=12) (actual time=3158.096..3158.096 rows=758933 loops=3) Buckets: 131072 Batches: 64 Memory Usage: 2752kB -> Parallel Seq Scan on orders (cost=0.00..362880.89 rows=957700 width=12) (actual time=0.064..2613.787 rows=758933 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: 4241067 -> Parallel Hash (cost=28594.88..28594.88 rows=625020 width=8) (actual time=814.588..814.589 rows=500000 loops=3) Buckets: 131072 Batches: 32 Memory Usage: 2912kB -> Parallel Index Only Scan using customer_c_nationkey_c_custkey_idx on customer (cost=0.43..28594.88 rows=625020 width=8) (actual time=4.997..535.009 rows=500000 loops=3) Heap Fetches: 0 n_name | revenue ---------------------------+---------------- CANADA | 534984878.9806 UNITED STATES | 529199098.5675 BRAZIL | 525909292.2050 ARGENTINA | 524103160.0044 PERU | 516154601.8649 (5 rows) COMMIT; COMMIT