Aggregate  (cost=338203.81..338203.82 rows=1 width=8)
   ->  Nested Loop  (cost=51266.84..338203.81 rows=1 width=8)
         Join Filter: (s_f_touchpoint_execution_status_history_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id)
         ->  Nested Loop  (cost=32782.19..317837.83 rows=1 width=32)
               ->  Nested Loop  (cost=32781.90..317837.50 rows=1 width=40)
                     ->  Nested Loop  (cost=32781.61..317829.18 rows=1 width=24)
                           Join Filter: (base.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_execution_id)
                           ->  Unique  (cost=32781.61..34495.50 rows=1 width=8)
                                 ->  Merge Join  (cost=32781.61..34495.50 rows=1 width=8)
                                       Merge Cond: ((s_f_touchpoint_execution_status_history_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1_1.tou
chpoint_execution_id) AND (s_f_touchpoint_execution_status_history_1.creation_dt = (max(s_f_touchpoint_execution_status_history_1_1.creation_dt))))
                                       ->  Sort  (cost=19697.87..20098.14 rows=160107 width=16)
                                             Sort Key: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, s_f_touchpoint_execution_status_history_1.crea
tion_dt
                                             ->  Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1  (cost=0.00..5857.68 rows
=160107 width=16)
                                                   Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))
                                       ->  Sort  (cost=13083.74..13254.76 rows=68410 width=16)
                                             Sort Key: s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history
_1_1.creation_dt))
                                             ->  HashAggregate  (cost=6221.56..6905.66 rows=68410 width=16)
                                                   ->  Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_1  (cost=0.00..4766
.04 rows=291104 width=16)
                           ->  Seq Scan on s_f_promotion_history base  (cost=0.00..283333.66 rows=1 width=32)
                                 Filter: ((send_dt >= '2014-03-13 00:00:00'::timestamp without time zone) AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time
zone))
                     ->  Index Scan using idx_s_d_touchpoint_execution_id on s_d_touchpoint_execution tpe  (cost=0.29..8.31 rows=1 width=16)
                           Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_execution_id)
               ->  Index Only Scan using s_d_touchpoint_pkey on s_d_touchpoint tp  (cost=0.29..0.32 rows=1 width=8)
                     Index Cond: ((touchpoint_id = tpe.touchpoint_id) AND (channel_type_id = 1))
         ->  Nested Loop  (cost=18484.65..20365.97 rows=1 width=24)
               ->  Hash Join  (cost=18484.52..20365.80 rows=1 width=26)
                     Hash Cond: ((s_f_touchpoint_execution_status_history_1_2.touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id)
 AND ((max(s_f_touchpoint_execution_status_history_1_2.creation_dt)) = s_f_touchpoint_execution_status_history.creation_dt))
                     ->  HashAggregate  (cost=6221.56..6905.66 rows=68410 width=16)
                           ->  Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_2  (cost=0.00..4766.04 rows=291104 width=16
)
                     ->  Hash  (cost=12262.93..12262.93 rows=2 width=26)
                           ->  Hash Join  (cost=6259.57..12262.93 rows=2 width=26)
                                 Hash Cond: (s_f_touchpoint_execution_status_history.touchpoint_execution_id = tp_exec.touchpoint_execution_id)
                                 ->  Seq Scan on s_f_touchpoint_execution_status_history  (cost=0.00..5493.80 rows=135878 width=16)
                                       Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))
                                 ->  Hash  (cost=6259.55..6259.55 rows=1 width=10)
                                       ->  Nested Loop Left Join  (cost=1955.27..6259.55 rows=1 width=10)
                                             ->  Nested Loop  (cost=1954.99..6259.24 rows=1 width=18)
                                                   ->  Nested Loop  (cost=1954.71..6258.92 rows=1 width=34)
                                                         Join Filter: (camp_exec.campaign_id = wave.campaign_id)
                                                         ->  Nested Loop  (cost=1954.42..6254.67 rows=13 width=42)
                                                               ->  Hash Join  (cost=1954.13..6249.67 rows=13 width=42)
                                                                     Hash Cond: ((tp_exec.touchpoint_id = tp_1.touchpoint_id) AND (wave_exec.wave_id = tp_1.wave_id))
                                                                     ->  Hash Join  (cost=1576.83..4595.51 rows=72956 width=40)
                                                                           Hash Cond: (tp_exec.wave_execution_id = wave_exec.wave_execution_id)
                                                                           ->  Seq Scan on s_d_touchpoint_execution tp_exec  (cost=0.00..1559.56 rows=72956 width=34)
                                                                           ->  Hash  (cost=1001.37..1001.37 rows=46037 width=24)
                                                                                 ->  Seq Scan on s_d_wave_execution wave_exec  (cost=0.00..1001.37 rows=46037 width=24)
                                                                     ->  Hash  (cost=212.72..212.72 rows=10972 width=18)
                                                                           ->  Seq Scan on s_d_touchpoint tp_1  (cost=0.00..212.72 rows=10972 width=18)
                                                               ->  Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec  (cost=0.29..0.37 row
s=1 width=18)
                                                                     Index Cond: (campaign_execution_id = wave_exec.campaign_execution_id)
                                                         ->  Index Scan using s_d_wave_pkey on s_d_wave wave  (cost=0.29..0.31 rows=1 width=16)
                                                               Index Cond: (wave_id = wave_exec.wave_id)
                                                   ->  Index Scan using s_d_campaign_pkey on s_d_campaign camp  (cost=0.29..0.32 rows=1 width=10)
                                                         Index Cond: (campaign_id = camp_exec.campaign_id)
                                             ->  Index Only Scan using s_d_content_pkey on s_d_content content  (cost=0.28..0.30 rows=1 width=8)
                                                   Index Cond: (content_id = tp_exec.content_id)
               ->  Index Only Scan using d_channel_pk on s_d_channel_type channel  (cost=0.13..0.15 rows=1 width=2)
                     Index Cond: (channel_type_id = tp_1.channel_type_id)