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)