QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=23613912.46..24689677.34 rows=2340 width=4)
   ->  Merge Join  (cost=23613912.03..24673859.22 rows=2340 width=12)
         Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
         ->  Sort  (cost=20971852.46..21271839.58 rows=119994848 width=8)
               Sort Key: lineitem.l_partkey, lineitem.l_suppkey
               ->  Seq Scan on lineitem  (cost=0.00..3228950.48 rows=119994848 width=8)
         ->  Materialize  (cost=2642034.49..2722034.49 rows=16000000 width=8)
               ->  Sort  (cost=2642034.49..2682034.49 rows=16000000 width=8)
                     Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey
                     ->  Seq Scan on partsupp  (cost=0.00..508759.00 rows=16000000 width=8)
   ->  Index Only Scan using part_pkey on part  (cost=0.43..6.75 rows=1 width=4)
         Index Cond: (p_partkey = lineitem.l_partkey)
(12 rows)

                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Merge Join  (cost=3947865.64..4839365.50 rows=24 width=4)
   Merge Cond: (partsupp.ps_partkey = lineitem.l_partkey)
   Join Filter: (lineitem.l_suppkey = partsupp.ps_suppkey)
   ->  Index Scan using idx_partsupp_partkey on partsupp  (cost=0.43..764250.44 rows=16000000 width=8)
   ->  Sort  (cost=3947864.78..3950939.15 rows=1229750 width=12)
         Sort Key: lineitem.l_partkey
         ->  Hash Join  (cost=132424.16..3823475.97 rows=1229750 width=12)
               Hash Cond: (lineitem.l_partkey = part.p_partkey)
               ->  Seq Scan on lineitem  (cost=0.00..3228950.48 rows=119994848 width=8)
               ->  Hash  (cost=131919.11..131919.11 rows=40404 width=4)
                     ->  Seq Scan on part  (cost=0.00..131919.11 rows=40404 width=4)
                           Filter: ((p_name)::text ~~ 'hot%'::text)
(12 rows)

                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=5205236.43..6886133.98 rows=868 width=4)
   Join Filter: (lineitem.l_partkey = part.p_partkey)
   ->  Merge Join  (cost=5205236.00..6885310.01 rows=1737 width=12)
         Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
         ->  GroupAggregate  (cost=5205235.57..5438183.70 rows=9674242 width=40)
               Group Key: lineitem.l_partkey, lineitem.l_suppkey
               ->  Sort  (cost=5205235.57..5250637.47 rows=18160762 width=8)
                     Sort Key: lineitem.l_partkey, lineitem.l_suppkey
                     ->  Bitmap Heap Scan on lineitem  (cost=465856.38..2767269.81 rows=18160762 width=8)
                           Recheck Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone))
                           ->  Bitmap Index Scan on idx_lineitem_shipdate  (cost=0.00..461316.19 rows=18160762 width=0)
                                 Index Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone))
         ->  Index Only Scan using partsupp_pkey on partsupp  (cost=0.43..1221995.31 rows=16000000 width=8)
   ->  Index Only Scan using part_pkey on part  (cost=0.43..0.47 rows=1 width=4)
         Index Cond: (p_partkey = partsupp.ps_partkey)
(15 rows)

                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=5803124.91..6191904.83 rows=868 width=4)
   Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
   ->  GroupAggregate  (cost=5205235.57..5438183.70 rows=9674242 width=40)
         Group Key: lineitem.l_partkey, lineitem.l_suppkey
         ->  Sort  (cost=5205235.57..5250637.47 rows=18160762 width=8)
               Sort Key: lineitem.l_partkey, lineitem.l_suppkey
               ->  Bitmap Heap Scan on lineitem  (cost=465856.38..2767269.81 rows=18160762 width=8)
                     Recheck Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone))
                     ->  Bitmap Index Scan on idx_lineitem_shipdate  (cost=0.00..461316.19 rows=18160762 width=0)
                           Index Cond: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone))
   ->  Sort  (cost=597889.34..601461.54 rows=1428880 width=12)
         Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey
         ->  Nested Loop  (cost=0.43..451811.70 rows=1428880 width=12)
               ->  Seq Scan on part  (cost=0.00..131919.11 rows=40404 width=4)
                     Filter: ((p_name)::text ~~ 'hot%'::text)
               ->  Index Scan using idx_partsupp_partkey on partsupp  (cost=0.43..7.57 rows=35 width=8)
                     Index Cond: (ps_partkey = part.p_partkey)
(17 rows)