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 = 'EUROPE' and o_orderdate >= date '1994-01-01' and o_orderdate < date '1994-01-01' + interval '1 year' group by n_name order by revenue desc; NOTICE: duration: 27211.860 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 = 'EUROPE' and o_orderdate >= date '1994-01-01' and o_orderdate < date '1994-01-01' + interval '1 year' group by n_name order by revenue desc; Sort (cost=2082353.15..2082353.22 rows=25 width=58) (actual time=26695.919..27211.842 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: quicksort Memory: 25kB -> Finalize GroupAggregate (cost=2081968.43..2082352.57 rows=25 width=58) (actual time=26670.864..27211.797 rows=5 loops=1) Group Key: nation.n_name -> Gather Merge (cost=2081968.43..2082351.89 rows=50 width=58) (actual time=26659.768..27211.729 rows=15 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=2080968.40..2081346.09 rows=25 width=58) (actual time=26640.407..26658.973 rows=5 loops=3) Group Key: nation.n_name -> Sort (cost=2080968.40..2081043.88 rows=30190 width=38) (actual time=26635.005..26637.832 rows=24418 loops=3) Sort Key: nation.n_name Sort Method: quicksort Memory: 2571kB Worker 0: Sort Method: quicksort Memory: 2300kB Worker 1: Sort Method: quicksort Memory: 3158kB -> Parallel Hash Join (cost=421225.23..2078722.00 rows=30190 width=38) (actual time=26056.977..26615.154 rows=24418 loops=3) Hash Cond: ((orders.o_custkey = customer.c_custkey) AND (supplier.s_nationkey = customer.c_nationkey)) -> Parallel Hash Join (cost=380813.06..2017158.43 rows=754931 width=50) (actual time=22590.303..25051.924 rows=607217 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Parallel Hash Join (cost=2248.79..1523122.58 rows=5003652 width=54) (actual time=37.587..16624.851 rows=4005319 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.047..6377.248 rows=19995110 loops=3) -> Parallel Hash (cost=2144.63..2144.63 rows=8333 width=38) (actual time=36.102..36.106 rows=6678 loops=3) Buckets: 32768 Batches: 1 Memory Usage: 1696kB -> Merge Join (cost=2.80..2144.63 rows=8333 width=38) (actual time=2.535..26.341 rows=6678 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.093..17.736 rows=32000 loops=3) Heap Fetches: 0 -> Sort (cost=2.50..2.52 rows=5 width=30) (actual time=0.101..0.640 rows=5355 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.049..0.058 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.015..0.018 rows=25 loops=3) -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.023..0.025 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.019..0.021 rows=1 loops=3) Filter: (r_name = 'EUROPE'::bpchar) Rows Removed by Filter: 4 -> Parallel Hash (cost=362157.46..362157.46 rows=943825 width=12) (actual time=2687.548..2687.549 rows=758642 loops=3) Buckets: 131072 Batches: 64 Memory Usage: 2752kB -> Parallel Seq Scan on orders (cost=0.00..362157.46 rows=943825 width=12) (actual time=0.068..2234.230 rows=758642 loops=3) Filter: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 4241358 -> Parallel Hash (cost=28594.88..28594.88 rows=625020 width=8) (actual time=427.982..427.983 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=0.082..218.385 rows=500000 loops=3) Heap Fetches: 0 n_name | revenue ---------------------------+---------------- ROMANIA | 542543295.5506 GERMANY | 537384920.8489 FRANCE | 531946816.0725 RUSSIA | 530044641.9118 UNITED KINGDOM | 525106165.6523 (5 rows) COMMIT; COMMIT