explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iyjt

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 15,500.556 ↓ 25.0 50 1

Limit (cost=171,910.32..171,910.32 rows=2 width=2,109) (actual time=15,500.491..15,500.556 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=1049 read=77394
2. 0.647 15,500.511 ↓ 25.0 50 1

Sort (cost=171,910.32..171,910.32 rows=2 width=2,109) (actual time=15,500.486..15,500.511 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: 107kB
  • Buffers: shared hit=1049 read=77394
3. 3.093 15,499.864 ↓ 58.0 116 1

Nested Loop (cost=8,214.40..171,910.31 rows=2 width=2,109) (actual time=157.534..15,499.864 rows=116 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 (...)
  • Buffers: shared hit=1046 read=77394
4. 15,175.767 15,304.101 ↓ 14.4 187 1

Bitmap Heap Scan on public.send_mail_email mail (cost=8,213.98..171,742.10 rows=13 width=1,220) (actual time=141.364..15,304.101 rows=187 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 (...)
  • Recheck Cond: ((mail.app_id = 1) AND (date(timezone('UTC'::text, mail.created_at)) >= '2018-12-12'::date) AND (date(timezone('UTC'::text, mail.created_at)) <= '2019-03-11'::date))
  • Rows Removed by Index Recheck: 1980
  • Filter: ((NOT mail.from_system) AND (lower((mail.email)::text) ~~* '%thiago.decastro2@gmail.com%'::text))
  • Rows Removed by Filter: 413846
  • Heap Blocks: exact=49134 lossy=26456
  • Buffers: shared read=77219
5. 128.334 128.334 ↓ 3.1 419,609 1

Bitmap Index Scan on send_mail_email_app_id_created_at_asdate_from_system (cost=0.00..8,213.97 rows=134,185 width=0) (actual time=128.334..128.334 rows=419,609 loops=1)

  • Index Cond: ((mail.app_id = 1) AND (date(timezone('UTC'::text, mail.created_at)) >= '2018-12-12'::date) AND (date(timezone('UTC'::text, mail.created_at)) <= '2019-03-11'::date) AND (mail.from_system = false))
  • Buffers: shared read=1629
6. 163.438 163.438 ↑ 1.0 1 187

Index Scan using collector_stats_fc41f286 on public.collector_stats stats (cost=0.42..8.47 rows=1 width=893) (actual time=0.873..0.874 rows=1 loops=187)

  • 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, stats.status_id
  • Index Cond: (stats.email_id = mail.id)
  • Filter: ((stats.status_id = 0) OR (stats.status_id = 1) OR (stats.status_id = 2) OR (stats.status_id = 3) OR (stats.status_id = 4) OR (stats.status_id = 5) OR (stats.status_id = 6) OR (stats.status_id = 7) OR (stats.status_id = 8) OR (s (...)
  • Buffers: shared hit=554 read=125
7.          

SubPlan (forNested Loop)

8. 0.348 2.320 ↑ 1.0 1 116

Result (cost=4.18..4.19 rows=1 width=0) (actual time=0.020..0.020 rows=1 loops=116)

  • Output: $1
  • Buffers: shared hit=118 read=2
9.          

Initplan (forResult)

10. 1.972 1.972 ↓ 0.0 0 116

Index Only Scan using track_linkemail_fdfd0ebf on public.track_linkemail (cost=0.15..8.22 rows=2 width=0) (actual time=0.017..0.017 rows=0 loops=116)

  • Index Cond: (track_linkemail.email_id = mail.id)
  • Heap Fetches: 4
  • Buffers: shared hit=118 read=2
11. 0.232 1.740 ↑ 1.0 1 116

Result (cost=8.17..8.18 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=116)

  • Output: $3
  • Buffers: shared hit=117 read=2
12.          

Initplan (forResult)

13. 1.508 1.508 ↓ 0.0 0 116

Index Only Scan using track_mailopen_fdfd0ebf on public.track_mailopen (cost=0.15..8.17 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=116)

  • Index Cond: (track_mailopen.email_id = mail.id)
  • Heap Fetches: 3
  • Buffers: shared hit=117 read=2
14. 20.764 20.764 ↓ 0.0 0 116

Index Scan using campaign_campaign_pkey on public.campaign_campaign (cost=0.28..8.29 rows=1 width=4) (actual time=0.179..0.179 rows=0 loops=116)

  • Output: campaign_campaign.status
  • Index Cond: (campaign_campaign.id = mail.campaign_id)
  • Buffers: shared hit=110 read=40
15. 4.408 4.408 ↓ 0.0 0 116

Index Scan using mail_template_template_pkey on public.mail_template_template (cost=0.27..8.29 rows=1 width=32) (actual time=0.038..0.038 rows=0 loops=116)

  • 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=147 read=6