s_v_f_promotion_history_email

Hash Left Join  (cost=34679.90..37396.37 rows=11644 width=148) (actual time=609.472..9070.675 rows=4559289 loops=1)
   Hash Cond: ((base.promo_hist_id = email.promo_hist_id) AND (base.audience_member_id = email.audience_member_id))
   ->  Nested Loop  (cost=32782.62..35004.21 rows=11644 width=74) (actual time=567.441..4096.969 rows=4559289 loops=1)
         Join Filter: (s_f_touchpoint_execution_status_history.touchpoint_execution_id = base.touchpoint_execution_id)
         ->  Nested Loop  (cost=32782.19..34504.16 rows=1 width=16) (actual time=337.484..884.438 rows=46454 loops=1)
               ->  Nested Loop  (cost=32781.90..34503.83 rows=1 width=24) (actual time=337.462..682.943 rows=71892 loops=1)
                     ->  Unique  (cost=32781.61..34495.50 rows=1 width=8) (actual time=337.428..478.619 rows=76088 loops=1)
                           ->  Merge Join  (cost=32781.61..34495.50 rows=1 width=8) (actual time=337.427..454.249 rows=77090 loops=1)
                                 Merge Cond: ((s_f_touchpoint_execution_status_history.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_ex
ecution_id) AND (s_f_touchpoint_execution_status_history.creation_dt = (max(s_f_touchpoint_execution_status_history_1.creation_dt))))
                                 ->  Sort  (cost=19697.87..20098.14 rows=160107 width=16) (actual time=132.938..155.325 rows=160898 loops=1)
                                       Sort Key: s_f_touchpoint_execution_status_history.touchpoint_execution_id, s_f_touchpoint_execution_status_history.creation_dt
                                       Sort Method: quicksort  Memory: 13687kB
                                       ->  Seq Scan on s_f_touchpoint_execution_status_history  (cost=0.00..5857.68 rows=160107 width=16) (actual time=0.012..60.421 row
s=160898 loops=1)
                                             Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))
                                             Rows Removed by Filter: 130206
                                 ->  Sort  (cost=13083.74..13254.76 rows=68410 width=16) (actual time=204.476..227.483 rows=77456 loops=1)
                                       Sort Key: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1.creat
ion_dt))
                                       Sort Method: quicksort  Memory: 6656kB
                                       ->  HashAggregate  (cost=6221.56..6905.66 rows=68410 width=16) (actual time=140.992..169.426 rows=76454 loops=1)
                                             ->  Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1  (cost=0.00..4766.04 rows
=291104 width=16) (actual time=0.013..39.851 rows=291104 loops=1)
                     ->  Index Scan using idx_s_d_touchpoint_execution_id on s_d_touchpoint_execution tpe  (cost=0.29..8.31 rows=1 width=16) (actual time=0.002..0.002 r
ows=1 loops=76088)
                           Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history.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) (actual time=0.002..0.002 rows=1 loops=71892)
                     Index Cond: ((touchpoint_id = tpe.touchpoint_id) AND (channel_type_id = 1))
                     Heap Fetches: 46454
         ->  Index Scan using idx_pr_history on s_f_promotion_history base  (cost=0.43..311.52 rows=15083 width=74) (actual time=0.002..0.033 rows=98 loops=46454)
               Index Cond: (touchpoint_execution_id = tpe.touchpoint_execution_id)
   ->  Hash  (cost=1192.71..1192.71 rows=46971 width=90) (actual time=41.978..41.978 rows=46971 loops=1)
         Buckets: 8192  Batches: 1  Memory Usage: 4775kB
         ->  Seq Scan on s_f_promotion_history_email email  (cost=0.00..1192.71 rows=46971 width=90) (actual time=0.010..19.620 rows=46971 loops=1)
		 
		 
		 
s_v_d_campaign_hierarchy

Nested Loop Left Join  (cost=18485.07..20366.44 rows=1 width=894) (actual time=2656.451..4041.952 rows=45360 loops=1)
   ->  Nested Loop  (cost=18484.94..20366.29 rows=1 width=776) (actual time=2445.487..3741.049 rows=45360 loops=1)
         ->  Nested Loop Left Join  (cost=18484.81..20366.12 rows=1 width=658) (actual time=2445.471..3648.007 rows=48926 loops=1)
               ->  Hash Join  (cost=18484.68..20365.96 rows=1 width=340) (actual time=2267.997..3359.148 rows=48926 loops=1)
                     Hash Cond: ((s_f_touchpoint_execution_status_history_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id) A
ND ((max(s_f_touchpoint_execution_status_history_1.creation_dt)) = s_f_touchpoint_execution_status_history.creation_dt))
                     ->  HashAggregate  (cost=6221.56..6905.66 rows=68410 width=16) (actual time=140.079..181.465 rows=76454 loops=1)
                           ->  Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1  (cost=0.00..4766.04 rows=291104 width=16)
(actual time=0.010..38.894 rows=291104 loops=1)
                     ->  Hash  (cost=12263.09..12263.09 rows=2 width=356) (actual time=2127.869..2127.869 rows=131427 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 37990kB
                           ->  Hash Join  (cost=6259.72..12263.09 rows=2 width=356) (actual time=1357.652..1990.177 rows=131427 loops=1)
                                 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) (actual time=0.011..52.932 rows=1362
80 loops=1)
                                       Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))
                                       Rows Removed by Filter: 154824
                                 ->  Hash  (cost=6259.71..6259.71 rows=1 width=340) (actual time=1357.605..1357.605 rows=72427 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 19510kB
                                       ->  Nested Loop Left Join  (cost=1955.40..6259.71 rows=1 width=340) (actual time=158.779..1269.938 rows=72427 loops=1)
                                             ->  Nested Loop Left Join  (cost=1955.27..6259.55 rows=1 width=222) (actual time=31.836..998.297 rows=72427 loops=1)
                                                   ->  Nested Loop  (cost=1954.99..6259.24 rows=1 width=197) (actual time=31.826..829.278 rows=72427 loops=1)
                                                         ->  Nested Loop  (cost=1954.71..6258.92 rows=1 width=173) (actual time=31.816..636.122 rows=72427 loops=1)
                                                               Join Filter: (camp_exec.campaign_id = wave.campaign_id)
                                                               Rows Removed by Join Filter: 243
                                                               ->  Nested Loop  (cost=1954.42..6254.67 rows=13 width=167) (actual time=31.804..435.461 rows=72670 loops=
1)
                                                                     ->  Hash Join  (cost=1954.13..6249.67 rows=13 width=108) (actual time=31.777..210.346 rows=72670 lo
ops=1)
                                                                           Hash Cond: ((tp_exec.touchpoint_id = tp.touchpoint_id) AND (wave_exec.wave_id = tp.wave_id))
                                                                           ->  Hash Join  (cost=1576.83..4595.51 rows=72956 width=90) (actual time=25.984..141.564 rows=
72956 loops=1)
                                                                                 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=
42) (actual time=0.005..14.945 rows=72956 loops=1)
                                                                                 ->  Hash  (cost=1001.37..1001.37 rows=46037 width=56) (actual time=25.922..25.922 rows=
46037 loops=1)
                                                                                       Buckets: 8192  Batches: 1  Memory Usage: 4104kB
                                                                                       ->  Seq Scan on s_d_wave_execution wave_exec  (cost=0.00..1001.37 rows=46037 widt
h=56) (actual time=0.007..10.424 rows=46037 loops=1)
                                                                           ->  Hash  (cost=212.72..212.72 rows=10972 width=26) (actual time=5.773..5.773 rows=10972 loop
s=1)
                                                                                 Buckets: 2048  Batches: 1  Memory Usage: 645kB
                                                                                 ->  Seq Scan on s_d_touchpoint tp  (cost=0.00..212.72 rows=10972 width=26) (actual time
=0.007..2.350 rows=10972 loops=1)
                                                                     ->  Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec  (cost=0.29..0.
37 rows=1 width=67) (actual time=0.002..0.002 rows=1 loops=72670)
                                                                           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=22) (actual time=0.002
..0.002 rows=1 loops=72670)
                                                                     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=40) (actual time=0.0
02..0.002 rows=1 loops=72427)
                                                               Index Cond: (campaign_id = camp_exec.campaign_id)
                                                   ->  Index Scan using s_d_content_pkey on s_d_content content  (cost=0.28..0.30 rows=1 width=33) (actual time=0.001..0
.002 rows=1 loops=72427)
                                                         Index Cond: (tp_exec.content_id = content_id)
                                             ->  Index Scan using s_d_message_type_pkey on s_d_message_type message_type  (cost=0.13..0.15 rows=1 width=120) (actual tim
e=0.003..0.003 rows=1 loops=72427)
                                                   Index Cond: (tp_exec.message_type_id = message_type_id)
               ->  Index Scan using s_d_group_pkey on s_d_group grup  (cost=0.13..0.15 rows=1 width=320) (actual time=0.005..0.005 rows=1 loops=48926)
                     Index Cond: (camp_exec.group_id = group_id)
         ->  Index Scan using d_channel_pk on s_d_channel_type channel  (cost=0.13..0.15 rows=1 width=120) (actual time=0.001..0.001 rows=1 loops=48926)
               Index Cond: (channel_type_id = tp.channel_type_id)
   ->  Index Scan using s_d_category_pkey on s_d_category "CATEGORY"  (cost=0.13..0.15 rows=1 width=120) (actual time=0.006..0.006 rows=1 loops=45360)
         Index Cond: (camp.category_id = category_id)