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 = 'ASIA' 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: 27175.750 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 = 'ASIA' 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=2088446.59..2088446.66 rows=25 width=58) (actual time=26741.746..27175.732 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: quicksort Memory: 25kB -> Finalize GroupAggregate (cost=2088060.74..2088446.01 rows=25 width=58) (actual time=26716.882..27175.676 rows=5 loops=1) Group Key: nation.n_name -> Gather Merge (cost=2088060.74..2088445.33 rows=50 width=58) (actual time=26707.794..27175.573 rows=15 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=2087060.72..2087439.53 rows=25 width=58) (actual time=26688.097..26709.286 rows=5 loops=3) Group Key: nation.n_name -> Sort (cost=2087060.72..2087136.42 rows=30280 width=38) (actual time=26683.472..26686.316 rows=24335 loops=3) Sort Key: nation.n_name Sort Method: quicksort Memory: 2832kB Worker 0: Sort Method: quicksort Memory: 2706kB Worker 1: Sort Method: quicksort Memory: 2471kB -> Parallel Hash Join (cost=422358.36..2084806.97 rows=30280 width=38) (actual time=26374.753..26668.280 rows=24335 loops=3) Hash Cond: ((orders.o_custkey = customer.c_custkey) AND (supplier.s_nationkey = customer.c_nationkey)) -> Parallel Hash Join (cost=381946.18..2023187.54 rows=757190 width=50) (actual time=23705.395..25438.802 rows=608344 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Parallel Hash Join (cost=2248.79..1527673.26 rows=5018624 width=54) (actual time=33.383..16517.111 rows=4007184 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Parallel Seq Scan on lineitem (cost=0.00..1410415.18 rows=25093118 width=24) (actual time=0.045..6352.119 rows=19995260 loops=3) -> Parallel Hash (cost=2144.63..2144.63 rows=8333 width=38) (actual time=26.987..26.992 rows=6679 loops=3) Buckets: 32768 Batches: 1 Memory Usage: 1728kB -> Merge Join (cost=2.80..2144.63 rows=8333 width=38) (actual time=3.507..20.867 rows=6679 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.097..15.738 rows=29335 loops=3) Heap Fetches: 0 -> Sort (cost=2.50..2.52 rows=5 width=30) (actual time=0.091..0.624 rows=5352 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.056..0.065 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.027..0.027 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.023..0.024 rows=1 loops=3) Filter: (r_name = 'ASIA'::bpchar) Rows Removed by Filter: 4 -> Parallel Hash (cost=363241.27..363241.27 rows=946650 width=12) (actual time=3762.138..3762.139 rows=758632 loops=3) Buckets: 131072 Batches: 64 Memory Usage: 2752kB -> Parallel Seq Scan on orders (cost=0.00..363241.27 rows=946650 width=12) (actual time=0.072..2429.105 rows=758632 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: 4241368 -> Parallel Hash (cost=28594.88..28594.88 rows=625020 width=8) (actual time=518.085..518.086 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=3.019..260.862 rows=500000 loops=3) Heap Fetches: 0 n_name | revenue ---------------------------+---------------- INDIA | 537438873.1945 CHINA | 535393225.9940 VIETNAM | 532336856.9740 JAPAN | 526513242.3335 INDONESIA | 522776666.9114 (5 rows) COMMIT; COMMIT