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 = 'MIDDLE EAST' 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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=1874930.14..1874930.20 rows=25 width=34) (actual time=13940.462..13940.462 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * (1::double precision - lineitem.l_discount)))) Sort Method: quicksort Memory: 25kB Buffers: shared hit=2342466 read=377981 dirtied=26 written=3718 -> HashAggregate (cost=1874929.31..1874929.56 rows=25 width=34) (actual time=13940.395..13940.397 rows=5 loops=1) Group Key: nation.n_name Buffers: shared hit=2342463 read=377981 dirtied=26 written=3718 -> Hash Join (cost=93748.12..1874191.42 rows=73789 width=34) (actual time=2109.607..13857.833 rows=73580 loops=1) Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey)) Buffers: shared hit=2342463 read=377981 dirtied=26 written=3718 -> Nested Loop (cost=89069.12..1854938.72 rows=1844774 width=46) (actual time=2043.649..13121.863 rows=1821904 loops=1) Buffers: shared hit=2340281 read=377981 dirtied=26 written=3718 -> Hash Join (cost=89068.56..392436.61 rows=460916 width=38) (actual time=2043.576..5258.751 rows=455494 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) Buffers: shared hit=366722 read=41534 dirtied=26 written=2781 -> Bitmap Heap Scan on orders (cost=50026.39..340143.11 rows=2304581 width=8) (actual time=874.133..2912.230 rows=2277094 loops=1) Recheck Cond: ((o_orderdate >= '1993-01-01'::date) AND (o_orderdate < '1994-01-01 00:00:00'::timestamp without time zone)) Heap Blocks: exact=255536 Buffers: shared hit=249412 read=12688 dirtied=26 written=524 -> Bitmap Index Scan on orders_o_orderdate_o_orderkey_idx (cost=0.00..49450.24 rows=2304581 width=0) (actual time=786.587..786.587 rows=2299442 loops=1) Index Cond: ((o_orderdate >= '1993-01-01'::date) AND (o_orderdate < '1994-01-01 00:00:00'::timestamp without time zone)) Buffers: shared hit=392 read=6172 written=461 -> Hash (cost=35292.17..35292.17 rows=300000 width=38) (actual time=1166.348..1166.348 rows=299838 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 25179kB Buffers: shared hit=117310 read=28846 written=2257 -> Nested Loop (cost=905.25..35292.17 rows=300000 width=38) (actual time=21.093..1042.741 rows=299838 loops=1) Buffers: shared hit=117310 read=28846 written=2257 -> Nested Loop (cost=0.14..13.95 rows=5 width=30) (actual time=0.044..0.103 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.010..0.034 rows=25 loops=1) Buffers: shared hit=2 -> Materialize (cost=0.00..1.07 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=25) Buffers: shared hit=1 -> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1) Filter: (r_name = 'MIDDLE EAST'::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=18.882..195.356 rows=59968 loops=5) Recheck Cond: (c_nationkey = nation.n_nationkey) Heap Blocks: exact=145317 Buffers: shared hit=117307 read=28846 written=2257 -> Bitmap Index Scan on customer_c_nationkey_c_custkey_idx (cost=0.00..890.11 rows=60000 width=0) (actual time=12.312..12.312 rows=59968 loops=5) Index Cond: (c_nationkey = nation.n_nationkey) Buffers: shared hit=5 read=831 written=56 -> Index Scan using pk_lineitem on lineitem (cost=0.56..3.02 rows=15 width=16) (actual time=0.014..0.016 rows=4 loops=455494) Index Cond: (l_orderkey = orders.o_orderkey) Buffers: shared hit=1973559 read=336447 written=937 -> Hash (cost=3179.00..3179.00 rows=100000 width=8) (actual time=65.358..65.358 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 4931kB Buffers: shared hit=2179 -> Seq Scan on supplier (cost=0.00..3179.00 rows=100000 width=8) (actual time=0.011..38.592 rows=100000 loops=1) Buffers: shared hit=2179 Planning time: 7.168 ms Execution time: 13941.946 ms (56 rows) COMMIT; COMMIT