BEGIN; BEGIN EXPLAIN (ANALYZE, BUFFERS) 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 '1994-01-01' and o_orderdate < date '1994-01-01' + interval '1 year' group by n_name order by revenue desc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=1857591.08..1857591.14 rows=25 width=34) (actual time=18158.274..18158.274 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * (1::double precision - lineitem.l_discount)))) Sort Method: quicksort Memory: 25kB Buffers: shared hit=2022872 read=682874 dirtied=252 written=33958 -> HashAggregate (cost=1857590.25..1857590.50 rows=25 width=34) (actual time=18158.208..18158.209 rows=5 loops=1) Group Key: nation.n_name Buffers: shared hit=2022869 read=682874 dirtied=252 written=33958 -> Hash Join (cost=92221.56..1856859.25 rows=73100 width=34) (actual time=2376.718..18065.369 rows=72902 loops=1) Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey)) Buffers: shared hit=2022869 read=682874 dirtied=252 written=33958 -> Nested Loop (cost=87542.56..1837731.98 rows=1828969 width=46) (actual time=2309.976..16773.884 rows=1811088 loops=1) Buffers: shared hit=2021016 read=682545 dirtied=252 written=33958 -> Hash Join (cost=87542.00..388514.47 rows=457354 width=38) (actual time=2309.914..7707.083 rows=453087 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) Buffers: shared hit=178404 read=227256 dirtied=252 written=10778 -> Bitmap Heap Scan on orders (cost=48499.83..336323.38 rows=2286770 width=8) (actual time=1346.147..4964.868 rows=2275927 loops=1) Recheck Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone)) Heap Blocks: exact=253501 Buffers: shared hit=48081 read=211641 dirtied=252 written=10778 -> Bitmap Index Scan on orders_o_orderdate_o_orderkey_idx (cost=0.00..47928.13 rows=2286770 width=0) (actual time=1236.342..1236.342 rows=2278245 loops=1) Index Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone)) Buffers: shared read=6221 -> Hash (cost=35292.17..35292.17 rows=300000 width=38) (actual time=959.942..959.942 rows=299436 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 25151kB Buffers: shared hit=130323 read=15615 -> Nested Loop (cost=905.25..35292.17 rows=300000 width=38) (actual time=21.412..838.877 rows=299436 loops=1) Buffers: shared hit=130323 read=15615 -> Nested Loop (cost=0.14..13.95 rows=5 width=30) (actual time=0.052..0.112 rows=5 loops=1) Join Filter: (nation.n_regionkey = region.r_regionkey) Rows Removed by Join Filter: 20 Buffers: shared hit=3 -> Index Scan using pk_nation on nation (cost=0.14..12.51 rows=25 width=34) (actual time=0.009..0.030 rows=25 loops=1) Buffers: shared hit=2 -> Materialize (cost=0.00..1.07 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=25) Buffers: shared hit=1 -> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1) Filter: (r_name = 'AMERICA'::bpchar) Rows Removed by Filter: 4 Buffers: shared hit=1 -> Bitmap Heap Scan on customer (cost=905.11..6455.64 rows=60000 width=8) (actual time=20.052..153.453 rows=59887 loops=5) Recheck Cond: (c_nationkey = nation.n_nationkey) Heap Blocks: exact=145102 Buffers: shared hit=130320 read=15615 -> Bitmap Index Scan on customer_c_nationkey_c_custkey_idx (cost=0.00..890.11 rows=60000 width=0) (actual time=13.071..13.071 rows=59887 loops=5) Index Cond: (c_nationkey = nation.n_nationkey) Buffers: shared hit=9 read=824 -> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.56..3.02 rows=15 width=16) (actual time=0.016..0.019 rows=4 loops=453087) Index Cond: (l_orderkey = orders.o_orderkey) Buffers: shared hit=1842612 read=455289 written=23180 -> Hash (cost=3179.00..3179.00 rows=100000 width=8) (actual time=64.866..64.866 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 4931kB Buffers: shared hit=1850 read=329 -> Seq Scan on supplier (cost=0.00..3179.00 rows=100000 width=8) (actual time=0.010..35.563 rows=100000 loops=1) Buffers: shared hit=1850 read=329 Planning time: 5.695 ms Execution time: 18161.073 ms (56 rows) COMMIT; COMMIT