select ps_suppkey from lineitem, partsupp where l_partkey = ps_partkey and l_suppkey = ps_suppkey and ps_partkey in ( select p_partkey from part ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop Semi Join (cost=23613854.76..24693102.68 rows=2863 width=4) (actual time=252662.703..884575.874 rows=119994608 loops=1) -> Merge Join (cost=23613854.33..24673749.14 rows=2863 width=12) (actual time=252662.656..458535.952 rows=119994608 loops=1) Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey)) -> Sort (cost=20971812.62..21271799.14 rows=119994608 width=8) (actual time=228806.169..336527.618 rows=119994608 loops=1) Sort Key: lineitem.l_partkey, lineitem.l_suppkey Sort Method: external merge Disk: 2111136kB -> Seq Scan on lineitem (cost=0.00..3228948.08 rows=119994608 width=8) (actual time=0.227..31541.384 rows=119994608 loops=1) -> Materialize (cost=2642034.49..2722034.49 rows=16000000 width=8) (actual time=23856.476..44148.011 rows=120001929 loops=1) -> Sort (cost=2642034.49..2682034.49 rows=16000000 width=8) (actual time=23856.465..28096.959 rows=16000000 loops=1) Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey Sort Method: external sort Disk: 281536kB -> Seq Scan on partsupp (cost=0.00..508759.00 rows=16000000 width=8) (actual time=0.013..4056.366 rows=16000000 loops=1) -> Index Only Scan using part_pkey on part (cost=0.43..6.75 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=119994608) Index Cond: (p_partkey = lineitem.l_partkey) Heap Fetches: 119994608 Planning time: 1.414 ms Execution time: 891781.476 ms (17 rows) select ps_suppkey from lineitem, partsupp where l_partkey = ps_partkey and l_suppkey = ps_suppkey and ps_partkey in ( select p_partkey from part where p_name like 'hot%' ); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=3973962.16..4902728.75 rows=35 width=4) (actual time=42931.190..51758.776 rows=1311974 loops=1) Merge Cond: (partsupp.ps_partkey = lineitem.l_partkey) Join Filter: (lineitem.l_suppkey = partsupp.ps_suppkey) Rows Removed by Join Filter: 3935922 -> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..764250.44 rows=16000000 width=8) (actual time=0.011..5189.382 rows=15998913 loops=1) -> Sort (cost=3973913.32..3977585.07 rows=1468702 width=12) (actual time=42930.688..43398.341 rows=5247893 loops=1) Sort Key: lineitem.l_partkey Sort Method: quicksort Memory: 110651kB -> Hash Join (cost=132424.70..3823473.36 rows=1468702 width=12) (actual time=899.792..42215.343 rows=1311974 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Seq Scan on lineitem (cost=0.00..3228948.08 rows=119994608 width=8) (actual time=0.005..17835.189 rows=119994608 loops=1) -> Hash (cost=131919.64..131919.64 rows=40405 width=4) (actual time=899.698..899.698 rows=43703 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 7498kB -> Seq Scan on part (cost=0.00..131919.64 rows=40405 width=4) (actual time=0.023..886.782 rows=43703 loops=1) Filter: ((p_name)::text ~~ 'hot%'::text) Rows Removed by Filter: 3956297 Planning time: 1.450 ms Execution time: 51809.901 ms (18 rows) select ps_suppkey from partsupp, ( select l_partkey agg_partkey, l_suppkey agg_suppkey, 0.5 * sum(l_quantity) AS agg_quantity from lineitem where l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year group by l_partkey, l_suppkey ) agg_lineitem where agg_partkey = ps_partkey and agg_suppkey = ps_suppkey and ps_partkey in ( select p_partkey from part ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=3039101.38..4313205.40 rows=160 width=4) (actual time=49355.090..220422.294 rows=10897186 loops=1) -> Hash Join (cost=3039100.94..3101066.89 rows=918014 width=12) (actual time=49354.508..69808.651 rows=10897186 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> HashAggregate (cost=2867180.80..2885541.08 rows=1836028 width=8) (actual time=47150.671..53455.449 rows=10897186 loops=1) Group Key: lineitem.l_partkey, lineitem.l_suppkey -> Bitmap Heap Scan on lineitem (cost=470973.36..2775379.44 rows=18360272 width=8) (actual time=7924.016..28786.995 rows=18214751 loops=1) Recheck Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone)) Heap Blocks: exact=1924458 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..466383.29 rows=18360272 width=0) (actual time=6847.338..6847.338 rows=18214751 loops=1) Index Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone)) -> Hash (cost=121919.51..121919.51 rows=4000051 width=4) (actual time=2196.673..2196.673 rows=4000000 loops=1) Buckets: 4194304 (originally 4194304) Batches: 4 (originally 1) Memory Usage: 229377kB -> Seq Scan on part (cost=0.00..121919.51 rows=4000051 width=4) (actual time=0.012..712.926 rows=4000000 loops=1) -> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..1.31 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=10897186) Index Cond: ((ps_partkey = lineitem.l_partkey) AND (ps_suppkey = lineitem.l_suppkey)) Heap Fetches: 10897186 Planning time: 0.855 ms Execution time: 221281.967 ms (18 rows) select ps_suppkey from partsupp, ( select l_partkey agg_partkey, l_suppkey agg_suppkey, 0.5 * sum(l_quantity) AS agg_quantity from lineitem where l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year group by l_partkey, l_suppkey ) agg_lineitem where agg_partkey = ps_partkey and agg_suppkey = ps_suppkey and ps_partkey in ( select p_partkey from part where p_name like 'hot%' ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=3690996.25..3715358.15 rows=160 width=4) (actual time=79478.109..83008.686 rows=119238 loops=1) Merge Cond: ((partsupp.ps_partkey = agg_lineitem.agg_partkey) AND (partsupp.ps_suppkey = agg_lineitem.agg_suppkey)) -> Sort (cost=596073.10..599603.57 rows=1412188 width=12) (actual time=1648.385..1659.393 rows=174812 loops=1) Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey Sort Method: quicksort Memory: 14339kB -> Nested Loop (cost=0.43..451821.62 rows=1412188 width=12) (actual time=0.071..1548.566 rows=174812 loops=1) -> Seq Scan on part (cost=0.00..131919.64 rows=40405 width=4) (actual time=0.047..1006.884 rows=43703 loops=1) Filter: ((p_name)::text ~~ 'hot%'::text) Rows Removed by Filter: 3956297 -> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..7.57 rows=35 width=8) (actual time=0.010..0.011 rows=4 loops=43703) Index Cond: (ps_partkey = part.p_partkey) -> Sort (cost=3094923.15..3099513.22 rows=1836028 width=8) (actual time=77829.655..79681.366 rows=10896449 loops=1) Sort Key: agg_lineitem.agg_partkey, agg_lineitem.agg_suppkey Sort Method: external sort Disk: 234352kB -> Subquery Scan on agg_lineitem (cost=2867180.80..2903901.36 rows=1836028 width=8) (actual time=48973.005..55717.145 rows=10897186 loops=1) -> HashAggregate (cost=2867180.80..2885541.08 rows=1836028 width=8) (actual time=48973.003..54254.198 rows=10897186 loops=1) Group Key: lineitem.l_partkey, lineitem.l_suppkey -> Bitmap Heap Scan on lineitem (cost=470973.36..2775379.44 rows=18360272 width=8) (actual time=7918.595..29778.138 rows=18214751 loops=1) Recheck Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone)) Heap Blocks: exact=1924458 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..466383.29 rows=18360272 width=0) (actual time=6849.743..6849.743 rows=18214751 loops=1) Index Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone)) Planning time: 0.723 ms Execution time: 83096.504 ms (24 rows)