explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jtx6

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 31,868.958 ↓ 50.0 50 1

Limit (cost=44.43..44.44 rows=1 width=2,918) (actual time=31,868.889..31,868.958 rows=50 loops=1)

  • Output: mail.id, mail.app_id, mail.name_sender, mail.email_sender, mail.name_receiver, mail.email, mail.subject, mail.uuid, mail.content, mail.second_content, mail.created_at, mail.scheduled_at, mail.sent_at, mail.campaign_id, mail.sended, mail.not_sende (...)
  • Buffers: shared hit=2065632 dirtied=1
2. 1,147.005 31,868.920 ↓ 50.0 50 1

Sort (cost=44.43..44.44 rows=1 width=2,918) (actual time=31,868.888..31,868.920 rows=50 loops=1)

  • Output: mail.id, mail.app_id, mail.name_sender, mail.email_sender, mail.name_receiver, mail.email, mail.subject, mail.uuid, mail.content, mail.second_content, mail.created_at, mail.scheduled_at, mail.sent_at, mail.campaign_id, mail.sended, mail.not (...)
  • Sort Key: mail.created_at
  • Sort Method: top-N heapsort Memory: 77kB
  • Buffers: shared hit=2065632 dirtied=1
3. 7,713.872 30,721.915 ↓ 189,727.0 189,727 1

Nested Loop Left Join (cost=0.85..44.42 rows=1 width=2,918) (actual time=368.667..30,721.915 rows=189,727 loops=1)

  • Output: mail.id, mail.app_id, mail.name_sender, mail.email_sender, mail.name_receiver, mail.email, mail.subject, mail.uuid, mail.content, mail.second_content, mail.created_at, mail.scheduled_at, mail.sent_at, mail.campaign_id, mail.sended, ma (...)
  • Filter: (((mail.scheduled_at IS NULL) AND (NOT mail.sended) AND (NOT mail.not_sended) AND (mail.send_at IS NULL)) OR (mail.sended AND (NOT mail.not_sended) AND (stats.email_id IS NULL)) OR (lower((stats.notification_type)::text) = 'delivery': (...)
  • Rows Removed by Filter: 915
  • Buffers: shared hit=2065632 dirtied=1
4. 8,373.668 8,373.668 ↓ 190,634.0 190,634 1

Index Scan using send_mail_email_f382adfe on public.send_mail_email mail (cost=0.43..6.95 rows=1 width=1,552) (actual time=368.552..8,373.668 rows=190,634 loops=1)

  • Output: mail.id, mail.app_id, mail.name_sender, mail.email_sender, mail.name_receiver, mail.email, mail.subject, mail.uuid, mail.content, mail.second_content, mail.created_at, mail.scheduled_at, mail.sent_at, mail.campaign_id, mail.send (...)
  • Index Cond: (mail.app_id = 40)
  • Filter: ((NOT mail.from_system) AND (date(timezone('UTC'::text, mail.created_at)) >= '2018-12-02'::date) AND (date(timezone('UTC'::text, mail.created_at)) <= '2019-03-01'::date))
  • Rows Removed by Filter: 481875
  • Buffers: shared hit=164111 dirtied=1
5. 5,337.752 5,337.752 ↑ 1.0 1 190,634

Index Scan using collector_stats_fc41f286 on public.collector_stats stats (cost=0.42..8.44 rows=1 width=1,378) (actual time=0.026..0.028 rows=1 loops=190,634)

  • Output: stats.id, stats.registry_at, stats.stats, stats.app_id, stats.email_id, stats.diagnostic_code, stats.notification_type, stats.reject_reason_subtype, stats.reject_reason_type, stats.sent_by, stats.aws_ses_date
  • Index Cond: (stats.email_id = mail.id)
  • Buffers: shared hit=762607
6.          

SubPlan (forNested Loop Left Join)

7. 1,328.089 3,035.632 ↑ 1.0 1 189,727

Result (cost=4.30..4.31 rows=1 width=0) (actual time=0.015..0.016 rows=1 loops=189,727)

  • Output: $1
  • Buffers: shared hit=379460
8.          

Initplan (forResult)

9. 1,707.543 1,707.543 ↓ 0.0 0 189,727

Index Only Scan using track_linkemail_fdfd0ebf on public.track_linkemail (cost=0.29..4.30 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=189,727)

  • Index Cond: (track_linkemail.email_id = mail.id)
  • Heap Fetches: 5
  • Buffers: shared hit=379460
10. 948.635 2,466.451 ↑ 1.0 1 189,727

Result (cost=8.31..8.32 rows=1 width=0) (actual time=0.011..0.013 rows=1 loops=189,727)

  • Output: $3
  • Buffers: shared hit=380000
11.          

Initplan (forResult)

12. 1,517.816 1,517.816 ↓ 0.0 0 189,727

Index Only Scan using track_mailopen_fdfd0ebf on public.track_mailopen (cost=0.29..8.31 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=189,727)

  • Index Cond: (track_mailopen.email_id = mail.id)
  • Heap Fetches: 16
  • Buffers: shared hit=380000
13. 379.454 379.454 ↓ 0.0 0 189,727

Index Scan using campaign_campaign_pkey on public.campaign_campaign (cost=0.14..8.16 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=189,727)

  • Output: campaign_campaign.status
  • Index Cond: (campaign_campaign.id = mail.campaign_id)
14. 3,415.086 3,415.086 ↑ 1.0 1 189,727

Index Scan using mail_template_template_pkey on public.mail_template_template (cost=0.14..8.16 rows=1 width=34) (actual time=0.016..0.018 rows=1 loops=189,727)

  • Output: row_to_json(ROW(mail_template_template.name, mail_template_template.slug))
  • Index Cond: (mail_template_template.id = mail.template_id)
  • Buffers: shared hit=379454
Planning time : 1.109 ms
Execution time : 31,869.146 ms