QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Sort (cost=410317.35..410317.37 rows=6 width=25) (actual time=10424.815..10424.816 rows=4 loops=1) Sort Key: l_returnflag, l_linestatus Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=410317.11..410317.27 rows=6 width=25) (actual time=10424.769..10424.775 rows=4 loops=1) Group Key: l_returnflag, l_linestatus -> Seq Scan on lineitem (cost=0.00..173620.19 rows=5917423 width=25) (actual time=0.017..1179.993 rows=5916591 loops=1) Filter: (l_shipdate <= '1998-09-02 00:00:00'::timestamp without time zone) Rows Removed by Filter: 84624 Planning time: 0.380 ms Execution time: 10424.894 ms (10 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=267628.27..267628.32 rows=20 width=24) (actual time=2227.928..2227.931 rows=20 loops=1) -> Sort (cost=267628.27..268428.35 rows=320034 width=24) (actual time=2227.927..2227.929 rows=20 loops=1) Sort Key: (sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount)))), orders.o_orderdate Sort Method: top-N heapsort Memory: 26kB -> HashAggregate (cost=255111.85..259112.28 rows=320034 width=24) (actual time=2222.023..2225.916 rows=11620 loops=1) Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority -> Hash Join (cost=61347.05..250311.34 rows=320034 width=24) (actual time=511.401..2197.516 rows=30519 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Seq Scan on lineitem (cost=0.00..173620.19 rows=3238338 width=16) (actual time=0.007..880.520 rows=3241776 loops=1) Filter: (l_shipdate > '1995-03-15'::date) Rows Removed by Filter: 2759439 -> Hash (cost=59494.05..59494.05 rows=148240 width=12) (actual time=511.151..511.151 rows=147126 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 6322kB -> Hash Join (cost=5841.56..59494.05 rows=148240 width=12) (actual time=32.502..482.473 rows=147126 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Seq Scan on orders (cost=0.00..43975.00 rows=728452 width=16) (actual time=0.006..226.220 rows=727305 loops=1) Filter: (o_orderdate < '1995-03-15'::date) Rows Removed by Filter: 772695 -> Hash (cost=5460.00..5460.00 rows=30525 width=4) (actual time=32.464..32.464 rows=30142 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 1060kB -> Seq Scan on customer (cost=0.00..5460.00 rows=30525 width=4) (actual time=0.006..28.441 rows=30142 loops=1) Filter: (c_mktsegment = 'BUILDING'::bpchar) Rows Removed by Filter: 119858 Planning time: 0.483 ms Execution time: 2230.504 ms (25 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=194199.92..194199.98 rows=25 width=38) (actual time=675.182..675.183 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount)))) Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=194199.03..194199.34 rows=25 width=38) (actual time=675.170..675.171 rows=5 loops=1) Group Key: nation.n_name -> Hash Join (cost=6797.46..194125.81 rows=7322 width=38) (actual time=37.295..666.937 rows=7243 loops=1) Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey)) -> Nested Loop (cost=6325.46..188087.98 rows=183087 width=50) (actual time=34.245..581.860 rows=184082 loops=1) -> Hash Join (cost=6325.03..55651.73 rows=45763 width=38) (actual time=34.224..379.780 rows=46008 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Seq Scan on orders (cost=0.00..47725.00 rows=228813 width=8) (actual time=0.005..210.170 rows=227597 loops=1) Filter: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 1272403 -> Hash (cost=5950.03..5950.03 rows=30000 width=38) (actual time=34.197..34.197 rows=30183 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 2123kB -> Hash Join (cost=2.53..5950.03 rows=30000 width=38) (actual time=0.026..29.180 rows=30183 loops=1) Hash Cond: (customer.c_nationkey = nation.n_nationkey) -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=8) (actual time=0.002..12.259 rows=150000 loops=1) -> Hash (cost=2.47..2.47 rows=5 width=30) (actual time=0.015..0.015 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Hash Join (cost=1.07..2.47 rows=5 width=30) (actual time=0.012..0.014 rows=5 loops=1) Hash Cond: (nation.n_regionkey = region.r_regionkey) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=34) (actual time=0.002..0.004 rows=25 loops=1) -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1) Filter: (r_name = 'ASIA'::bpchar) Rows Removed by Filter: 4 -> Index Scan using l_orderkey_idx on lineitem (cost=0.43..2.74 rows=15 width=20) (actual time=0.003..0.004 rows=4 loops=46008) Index Cond: (l_orderkey = orders.o_orderkey) -> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=3.028..3.028 rows=10000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 391kB -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.005..1.698 rows=10000 loops=1) Planning time: 1.136 ms Execution time: 675.249 ms (35 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=234212.12..234212.14 rows=1 width=12) (actual time=1091.744..1091.745 rows=1 loops=1) -> Seq Scan on lineitem (cost=0.00..233632.34 rows=115957 width=12) (actual time=0.014..1059.285 rows=114160 loops=1) Filter: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without time zone) AND (l_discount >= 0.05) AND (l_discount <= 0.07) AND (l_quantity < 24::numeric)) Rows Removed by Filter: 5887055 Planning time: 0.072 ms Execution time: 1091.763 ms (6 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=252248.87..252248.92 rows=20 width=182) (actual time=1864.614..1864.618 rows=20 loops=1) -> Sort (cost=252248.87..252394.97 rows=58442 width=182) (actual time=1864.613..1864.616 rows=20 loops=1) Sort Key: (sum((lineitem.l_extendedprice * (1::numeric - lineitem.l_discount)))) Sort Method: top-N heapsort Memory: 30kB -> HashAggregate (cost=249963.22..250693.75 rows=58442 width=182) (actual time=1836.884..1855.133 rows=37967 loops=1) Group Key: customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment -> Hash Join (cost=55425.61..248502.17 rows=58442 width=182) (actual time=258.113..1683.082 rows=114705 loops=1) Hash Cond: (customer.c_nationkey = nation.n_nationkey) -> Hash Join (cost=55424.05..247697.03 rows=58442 width=160) (actual time=258.097..1656.935 rows=114705 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Hash Join (cost=48464.05..239349.03 rows=58442 width=16) (actual time=200.985..1518.488 rows=114705 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Seq Scan on lineitem (cost=0.00..173620.19 rows=1482700 width=16) (actual time=0.009..865.622 rows=1478870 loops=1) Filter: (l_returnflag = 'R'::bpchar) Rows Removed by Filter: 4522345 -> Hash (cost=47725.00..47725.00 rows=59124 width=8) (actual time=200.939..200.939 rows=57069 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 2230kB -> Seq Scan on orders (cost=0.00..47725.00 rows=59124 width=8) (actual time=0.005..193.035 rows=57069 loops=1) Filter: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 1442931 -> Hash (cost=5085.00..5085.00 rows=150000 width=148) (actual time=57.066..57.066 rows=150000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 26813kB -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=148) (actual time=0.002..24.265 rows=150000 loops=1) -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.008..0.008 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.002..0.004 rows=25 loops=1) Planning time: 0.515 ms Execution time: 1864.759 ms (28 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=241770.28..241770.29 rows=1 width=41) (actual time=1331.551..1331.551 rows=2 loops=1) Sort Key: lineitem.l_shipmode Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=241770.26..241770.27 rows=1 width=41) (actual time=1331.519..1331.519 rows=2 loops=1) Group Key: lineitem.l_shipmode -> Nested Loop (cost=0.43..241752.36 rows=1023 width=41) (actual time=0.068..1313.268 rows=30988 loops=1) -> Seq Scan on lineitem (cost=0.00..233632.34 rows=1023 width=36) (actual time=0.061..1232.505 rows=30988 loops=1) Filter: ((l_shipmode = ANY ('{MAIL,SHIP}'::text[])) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1994-01-01'::date) AND (l_receiptdate < '1995-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 5970227 -> Index Scan using o_orderkey_idx on orders (cost=0.43..7.93 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=30988) Index Cond: (o_orderkey = lineitem.l_orderkey) Planning time: 0.225 ms Execution time: 1331.586 ms (13 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=200018.78..200018.79 rows=1 width=33) (actual time=1052.716..1052.716 rows=1 loops=1) -> Hash Join (cost=8597.00..198712.78 rows=74628 width=33) (actual time=60.716..1003.388 rows=75983 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Seq Scan on lineitem (cost=0.00..188623.22 rows=74628 width=16) (actual time=0.028..873.368 rows=75983 loops=1) Filter: ((l_shipdate >= '1995-09-01'::date) AND (l_shipdate < '1995-10-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 5925232 -> Hash (cost=6097.00..6097.00 rows=200000 width=25) (actual time=60.662..60.662 rows=200000 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 11552kB -> Seq Scan on part (cost=0.00..6097.00 rows=200000 width=25) (actual time=0.003..34.523 rows=200000 loops=1) Planning time: 0.269 ms Execution time: 1052.747 ms (11 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=69127.72..69127.73 rows=1 width=12) (actual time=83.373..83.373 rows=1 loops=1) -> Nested Loop (cost=4.61..69127.66 rows=8 width=12) (actual time=0.243..83.235 rows=121 loops=1) -> Seq Scan on part (cost=0.00..12597.00 rows=481 width=30) (actual time=0.023..59.944 rows=485 loops=1) Filter: ((p_size >= 1) AND (((p_brand = 'Brand#12'::bpchar) AND (p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (p_size <= 5)) OR ((p_brand = 'Brand#23'::bpchar) AND (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (p_size <= 10)) OR ((p_brand = 'Brand#34'::bpchar) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (p_size <= 15)))) Rows Removed by Filter: 199515 -> Bitmap Heap Scan on lineitem (cost=4.61..117.52 rows=1 width=21) (actual time=0.042..0.046 rows=0 loops=485) Recheck Cond: (l_partkey = part.p_partkey) Filter: ((l_shipmode = ANY ('{AIR,"AIR REG"}'::text[])) AND (l_shipinstruct = 'DELIVER IN PERSON'::text) AND (((l_quantity >= 1::numeric) AND (l_quantity <= 11::numeric)) OR ((l_quantity >= 10::numeric) AND (l_quantity <= 20::numeric)) OR ((l_quantity >= 20::numeric) AND (l_quantity <= 30::numeric))) AND (((part.p_brand = 'Brand#12'::bpchar) AND (part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (l_quantity >= 1::numeric) AND (l_quantity <= 11::numeric) AND (part.p_size <= 5)) OR ((part.p_brand = 'Brand#23'::bpchar) AND (part.p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (l_quantity >= 10::numeric) AND (l_quantity <= 20::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#34'::bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (l_quantity >= 20::numeric) AND (l_quantity <= 30::numeric) AND (part.p_size <= 15)))) Rows Removed by Filter: 29 Heap Blocks: exact=14299 -> Bitmap Index Scan on l_partkey_idx (cost=0.00..4.61 rows=30 width=0) (actual time=0.007..0.007 rows=29 loops=485) Index Cond: (l_partkey = part.p_partkey) Planning time: 0.399 ms Execution time: 83.411 ms (14 rows)